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. This allows you to understand both the nature of the system you're administrating 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 performs all manipulation of databases and tables. safe_mysqld is a related program used to start up the server, monitor it, and restart it in case it goes down.

• The MySQL clients and utilities. Several MySQL programs are available to help you communicate with the server and to perform administrative tasks. The most important of these are:

  • mysql, an interactive program that allows you to issue SQL statements to the server and to view the results.

  • mysqladmin, an administrative program that lets you perform tasks such as shutting down the server and creating or dropping databases. You can also use mysqladmin to check the server's status if it appears not to be functioning properly.

  • isamchk and myisamchk, utilities that help you perform table analysis and optimization, as well as crash recovery if tables become damaged.

  • mysqldump, a tool for backing up your databases or copying databases to another server.

• The server's language, SQL. Some administrative duties can be performed using only the mysqladmin command-line utility, but you're better off if you can also talk to the server in its own language. As a simple example, you may need to find out why a user's privileges aren't working the way you expected them to work. There is no substitute for being able to go in and communicate with the server directly. You can do this by using the mysql client program to issue SQL queries that let you examine the grant tables. And if your version of MySQL predates the introduction of the GRANT statement, you'll need to use mysql to set up each user's privileges in the first place.

  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, 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. If you need some instruction in SQL or the use of the mysql command-line client, see Chapter 1, "Introduction to 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 file system to represent databases and tables, as well as where files such as the logs are located and what's in them. You should also know your options for managing allocation of disk space across file systems should you find that the file system on which the data directory is located is becoming too full.