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 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, is the hub of a MySQL installation; it performs all manipulation of databases and tables. mysqld_safe is a related program used to start up the server, monitor it, and restart it in case it goes down. (Prior to MySQL 4, mysqld_safe is named safe_mysqld.) If you run multiple servers on a single host, mysqld_multi can help you manage them more easily.

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

mysql— An interactive program that allows you to send 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 or checking its status if it appears not to be functioning properly

mysqlcheck, isamchk, and myisamchk—, Utilities that help you perform table analysis and optimization, as well as crash recovery if tables become damaged

mysqldump and mysqlhotcopy— Tools 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're also able to 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 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 queries that let you examine the grant tables. And if your version of MySQL predates the introduction of the GRANT statement, mysql can be used to set up each user's privileges by manipulating the grant tables directly.

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. For instruction in SQL and the use of the mysql command-line client, , "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 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.