Overview of Administrative Duties

The MySQL database system consists of several components. You should be familiar with what these components are and the purpose of each, so that you understand both the nature of the system you're administering and the tools available to help you do your job. If you take the time to understand what you're overseeing, your work will be much easier. To that end, you should acquaint yourself with the following aspects of MySQL:

• The MySQL server. The server, mysqld, is the hub of a MySQL installation; it performs all manipulation of databases and tables. On Unix, several related scripts are available to assist in server startup. mysqld_safe is a related program used to start the server, monitor it, and restart it in case it goes down. The mysql.server script is useful on versions of Unix that use run-level directories for starting system services. If you run multiple servers on a single host, mysqld_multi can help you manage them more easily. On Windows, you have the choice of running the server from the command line or as a Windows service.

• The MySQL clients and utilities. Several MySQL programs are available to help you communicate with the server. For administrative tasks, some of the most important ones are listed here:

  • mysql An interactive program that allows you to send SQL statements to the server and to view the results. You can also use mysql to execute batch scripts (text files containing SQL statements).

  • mysqladmin An administrative program for performing tasks such as shutting down the server, checking its configuration, or monitoring its status if it appears not to be functioning properly.

  • myisamchk and mysqlcheck Programs that help you perform table checking, analysis, and optimization, as well as repairs if tables become damaged. myisamchk is for use only with MyISAM tables. mysqlcheck works with MyISAM tables as well, and to a limited extent with other table types.

  • mysqldump and mysqlhotcopy Tools for backing up your databases or copying databases to another server.

  • MySQL Administrator This is a tool that provides a graphical interface for many administrative tasks. MySQL Administrator is one of the newest MySQL programs. I don't cover it here, but it's available from the MySQL AB Web site at http://dev.mysql.com/. I recommend that you grab a copy to see how you like it.

• The server's language, SQL. You should be able to talk to the server in its own language. As a simple example, you might need to find out why a user's privileges aren't working the way you expect them to work. There is no substitute for being able to go in and communicate with the server directly, which you can do by using the mysql client program to issue SQL statements that let you examine the grant tables.

  If you don't know any SQL, be sure to acquire at least a basic understanding of it. A lack of SQL fluency will only hinder you in your administrative tasks, whereas the time you take to learn will be repaid many times over. A real mastery of SQL takes some time, but the basic skills can be attained quickly. For instruction in SQL and the use of the mysql command-line client, see Chapter 1, "Getting Started with MySQL and SQL."

• The MySQL data directory. The data directory is where the server stores its databases and status files. It's important to understand the structure and contents of the data directory so that you know how the server uses the filesystem to represent databases and tables, as well as where the log files are located and what they contain. You should also know your options for managing allocation of disk space across filesystems should you find that the filesystem on which the data directory is located is becoming too full.