Understanding the Log Files

MySQL keeps various log files that may be useful to you. Most of these logs are not enabled by default, so if you want logging, you will have to switch it on. Each of these logs can be turned on with a command-line option on server startup or via the set command.

These are the logs you can keep:

• Error log: Tracks all the errors that have occurred. This one is logged by default and will appear in your data directory. The file is called hostname.err on Linux and mysql.err on Windows. You can set the location to something else with the option log-error= filename in your my.ini or my.cnf file.

• Query log: Logs all the queries run on the system. You can turn on this log and specify the location with the option log= filename .

• Binary log: Logs all the queries that change data. This replaces the update log, which will still be around until MySQL version 5.0, but is deprecated. You can turn on this log and specify the location with the option log-bin= filename .

• Slow query log: Logs all queries that took longer to execute than the value stored in the variable long_query_time . You can turn on this log and specify the location with the option log-slow-queries= filename

All of these except the binary log are simply text files. The binary log can be viewed using

 mysqlbinlog logfile 

We will look at the use of the slow query log in Chapter 19.

Log files will continue to grow larger so you should regularly rotate your log files. If you are using Linux, MySQL comes with a script called mysql-log-rotate to do this for you.

If you are using another operating system, you can move the old log files to a safe location manually and then tell MySQL to start using a new log file with the command

 mysqladmin flush-logs 

mysqladmin Option Summary

There are many options, with varying degrees of usefulness , that control mysqladmin .

Certain tasks can be done in SQL or using mysqladmin , such as creating and dropping databases:

 mysqladmin create databasename mysqladmin drop databasename 

A common use for mysqladmin is to get information about the server and current status. The information can be as simple as "Is the server up?" ( ping ) or much more detailed, giving a list of available variables or processes. Many of the uses for mysqladmin follow.

To find out whether the server is up, use this:

 mysqladmin ping 

To find out what version of the MySQL server software is on this machine, use this:

 mysqladmin version 

To retrieve a short or long status message from the server, use this:

 mysqladmin status mysqladmin extended-status 

To get a list of current active threads within the server, use this:

 mysqladmin processlist 

If you do get a list of processes (threads), you can selectively kill them like so:

 mysqladmin kill id1,id2,id3... 

To print the value of MySQL variables, use this:

 mysqladmin variables