Administering MySQL: International Usage and Log Files - 4.8 The MySQL Log Files
(Page 3 of 5 )
MySQL has several different log files that can help you find out what's going on inside mysqld:
| Log File | Types of Information Logged to File |
|---|
| The error log | Logs problems encountered starting, running, or stopping mysqld. |
| The isam log | Logs all changes to the ISAM tables. Used only for debugging the isam code. |
| The query log | Logs established client connections and executed statements. |
| The update log | Logs statements that change data. This log is deprecated. |
| The binary log | Logs all statements that change data. Also used for replication. |
| The slow log | Logs all queries that took more than long_query_time seconds to execute or didn't use indexes. |
By default, all logs are created in the mysqld data directory. You can force mysqld to close and reopen the log files (or in some cases switch to a new log) by flushing the logs. Log flushing occurs when you issue a FLUSH LOGS statement or execute mysqladmin flush-logs or mysqladmin refresh.
If you are using MySQL replication capabilities, slave replication servers maintain additional log files called relay logs. These are discussed in Chapter 5, "Replication in MySQL."
4.8.1 The Error Log
The error log file contains information indicating when mysqld was started and stopped and also any critical errors that occur while the server is running.
If mysqld dies unexpectedly and mysqld_safe needs to restart it, mysqld_safe will write a restarted mysqld message to the error log. If mysqld notices a table that needs to be automatically checked or repaired, it writes a message to the error log.
On some operating systems, the error log will contain a stack trace if mysqld dies. The trace can be used to determine where mysqld died.
Beginning with MySQL 4.0.10, you can specify where mysqld stores the error log file with the --log-error[=file_name] option. If no file_name value is given, mysqld uses the name host_name.err and writes the file in the data directory. (Prior to MySQL 4.0.10, the Windows error log name is mysql.err.) If you execute FLUSH LOGS, the error log is renamed with a suffix of -old and mysqld creates a new empty log file.
In older MySQL versions on Unix, error log handling was done by mysqld_safe which redirected the error file to host_name.err. You could change this filename by specifying a --err-log=filename option to mysqld_safe.
If you don't specify --log-error, or (on Windows) if you use the --console option, errors are written to stderr, the standard error output. Usually this is your terminal.
On Windows, error output is always written to the .err file if --console is not given.
4.8.2 The General Query Log
If you want to know what happens within mysqld, you should start it with the --log[=file_name] or -l [file_name] option. If no file_name value is given, the default name is host_name.log This will log all connections and statements to the log file. This log can be very useful when you suspect an error in a client and want to know exactly what the client sent to mysqld.
Older versions of the mysql.server script (from MySQL 3.23.4 to 3.23.8) pass a --log option to safe_mysqld to enable the general query log. If you need better performance when you start using MySQL in a production environment, you can remove the --log option from mysql.server or change it to --log-bin. See Section 4.8.4, "The Binary Log."
mysqld writes statements to the query log in the order that it receives them. This may be different from the order in which they are executed. This is in contrast to the update log and the binary log, which are written after the query is executed, but before any locks are released.
Server restarts and log flushing do not cause a new general query log file to be generated (although flushing closes and reopens it). On Unix, you can rename the file and create a new one by using the following commands:
shell> mv hostname.log hostname-old.log
shell> mysqladmin flush-logs
shell> cp hostname-old.log to-backup-directory
shell> rm hostname-old.log
On Windows, you cannot rename the log file while the server has it open. You must stop the server and rename the log. Then restart the server to create a new log.
4.8.3 The Update Log
Note: The update log has been deprecated and replaced by the binary log. See Section 4.8.4, "The Binary Log." The binary log can do anything the old update log could do, and more. The update log is unavailable as of MySQL 5.0.0.
When started with the --log-update[=file_name] option, mysqld writes a log file containing all SQL statements that update data. If no file_name value is given, the default name is name of the host machine. If a filename is given, but it doesn't contain a leading path, the file is written in the data directory. If file_name doesn't have an extension, mysqld creates log files with names of the form file_name.###, where ### is a number that is incremented each time you start the server or flush the logs.
Note: For this naming scheme to work, you must not create your own files with the same names as those that might be used for the log file sequence.
Update logging is smart because it logs only statements that really update data. So, an UPDATE or a DELETE with a WHERE that finds no rows is not written to the log. It even skips UPDATE statements that set a column to the value it already has.
The update logging is done immediately after a query completes but before any locks are released or any commit is done. This ensures that statements are logged in execution order.
If you want to update a database from update log files, you could do the following (assuming that your update logs have names of the form file_name.###):
shell> ls -1 -t -r file_name.[0-9]* | xargs cat |
mysql
ls is used to sort the update log filenames into the right order.
This can be useful if you have to revert to backup files after a crash and you want to redo the updates that occurred between the time of the backup and the crash.
Next: 4.8.4 The Binary Log >>
More MySQL Articles
More By Sams Publishing
|
 This article is excerpted from chapter four of the book MySQL Administrator's Guide, written by Paul Dubois (Sams; ISBN: 0672326345). Check it out today at your favorite bookstore. Buy this book now.
|
|
