Meeting MySQL as an Administrator

As an administrator, you'll need to know a lot more about how MySQL works than if you were just running queries. You'll need to be familiar with the utilities supplied with the MySQL distribution, as well as how your MySQL is set up. The main utilities you will use as an administrator are the following:

mysqladmin This is probably the most useful administration utility; it allows you to create and drop databases, stop the server, view the server variables, view and kill MySQL processes, set passwords, and flush log files…among other things!

mysqldump Creates SQL backups of the database.

mysqld This is not really a utility, as it's the MySQL server. You'll come across terms such as the mysqld variables, and you should know this is nothing more esoteric than the server variables.

mysqlimport Imports text files into database tables.

mysqlcheck Checks, analyzes, and repairs database tables.

mysqlhotcopy A Perl script that backs up database tables quickly.

myisampack Compresses MyISAM tables.

The first question you should ask about a new system is fairly fundamental: Where is the data stored?

The default location is usually /usr/local/var on a source Unix distribution, /usr/local/mysql/data on a binary Unix distribution, and C:\mysql\data on Windows. When MySQL is compiled, a data directory is selected, but this can be set to another location by specifying the new data directory in the configuration file. You'll learn about the configuration file in more detail later in this chapter in the section "Configuring MySQL," but for now, it's sufficient to know it's usually called my.cnf in Unix, or my.ini in Windows only, and it contains the MySQL configuration data. If you wanted to force MySQL to use a new location, the line would be something like this:

datadir = C:/mysqldata

To discover what the data directory is on an existing installation, you can use the variables option of mysqladmin. The sample output is a little intimidating for novices; there are many, many variables (and each new MySQL update seems to add more), but they're listed alphabetically:

% mysqladmin -uroot -pg00r002b variables; +-----------------------+---------------------------------------------+ | Variable_name         | Value                                       | +-----------------------+---------------------------------------------+ | back_log              | 50                                          | | basedir               | /usr/local/mysql-max-4.0.1-alpha-pc-        | |                       |   linux-gnu-i686/                           | | bdb_cache_size        | 8388600                                     | | bdb_log_buffer_size   | 32768                                       | | bdb_home              | /usr/local/mysql/data/                      | | bdb_max_lock          | 10000                                       | | bdb_logdir            |                                             | | bdb_shared_data       | OFF                                         | | bdb_tmpdir            | /tmp/                                       | | bdb_version           | Sleepycat Software: Berkeley DB 3.2.9a:     | |                       |  (December 23, 2001)                        | | binlog_cache_size     | 32768                                       | | character_set         | latin1                                      | | character_sets        | latin1 big5 czech euc_kr gb2312 gbk         | |                       |  latin1_de sjis tis620 ujis dec8 dos        | |                       |  german1 hp8 koi8_ru latin2 swe7 usa7       | |                       |  cp1251 danish hebrew win1251 estonia       | |                       |  hungarian koi8_ukr win1251ukr greek        | |                       |  win1250 croat cp1257 latin5                | | concurrent_insert     | ON                                          | | connect_timeout       | 5                                           | | datadir               | /usr/local/mysql/data/                      |

You can also just get the variable you're interested in by using grep (Unix) or find (Windows), as follows, first on Unix, then on Windows:

% mysqladmin -uroot -pg00r002b variables | grep 'datadir' | datadir               | /usr/local/mysql/data/                      |

C:\mysql\bin>mysqladmin variables | find "datadir" | datadir               | C:\mysql\data\                              |

The data directory in this case is /usr/local/mysql/data, the default for most binary installations on Unix, and c:\mysql\data with the Windows example.

The data directory usually contains the log files (they are placed there by default, though you can change this) as well as the actual data. In the case of MyISAM tables (the default), each database has its own directory, and within this directory each table has three corresponding files: an .MYD file for the data, an .MYI for the indexes, and an .frm file for the definition. BDB tables are also stored in the same directory, but they consist of a .db file and an .frm definition file. InnoDB tables have their .frm definition file in the database directory, but the actual data is stored one level up, on the same level as the database directories.

MySQL clients can access the server in three ways:

Unix sockets These are used on Unix machines when connecting to a server on the same machine. The socket file is placed in the default location (usually /tmp/mysql.sock or /var/lib/mysql.sock), unless otherwise specified.

Named pipes These are used on Windows NT/2000/XP machines where the --enable-named-pipe option is used with an executable that allows named pipes (mysqld-max-nt or mysqld-nt).

TCP/IP through a port This is the slowest method but is the only way to connect to a server running on Windows 95/98/Me or to connect remotely to a Unix machine.