The Error Log
The error log, as the name indicates, logs any MySQL errors that occur while the server is running, as well as startup and shutdown data. This includes data about invalid passwords, as well as syntax errors in the configuration file (my.cnf or my.ini) which can help when a startup fails. The error log is found in the data directory (usually c:MySQLdata on Windows, /usr/local/mysql/data for binary Unix installations, /usr/local/var for source Unix installations or /var/lib/mysql on Red Hat Linux) and called either mysql.err (on Windows) or hostname.err (hostname being the name of the host). Here is a sample error log:
020805 13:51:24 mysqld started Unknown prefix used for variable value 'wait_timeout=1000;' /usr/local/build/mysql/libexec/mysqld Ver 3.23.47 for unknown-freebsdelf4.5 on i386 Use '--help' or '--no-defaults --help' for a list of available options 020805 13:51:24 mysqld ended 020805 13:52:13 mysqld started /usr/local/build/mysql/libexec/mysqld: ready for connections 020806 1:18:51 /usr/local/build/mysql/libexec/mysqld: Normal shutdown 020806 1:18:52 /usr/local/build/mysql/libexec/mysqld: Shutdown Complete 020806 01:18:52 mysqld ended 020806 01:18:54 mysqld started /usr/local/build/mysql/libexec/mysqld: ready for connections
Keeping a close eye on the log can help you identify problems before they get too out of hand. Looking at the above log, you can see two problems. The first startup failed, because the config fie was not correct, (see the error regarding Unknown prefix used for variable value ‘wait_timeout=1000;’), while the second error occurred when the user ian attempted to login with an incorrect password. If this is repeated a number of times in quick succession, that should be a cause for alarm, but a once-off like the above is probably just typical user error. The user is also identified with the hostname, so if the host is foreign to you, that should also raise your suspicions. A well-designed system should not allow access to your database server from outside, but sometimes this is unavoidable (especially on small websites, where a webserver and database server are on the same machine). There are many possible errors, and this article can’t help you solve them all, but knowing what the error is, is a good first step, and that’s where the error log is invaluable.
The Query Log
The query log logs all connections and all executed queries. It can be useful to see who is logging on, and from where, as well as identify what queries are being run at what time. It very quickly becomes a big log and can have performance implications, so is not used that frequently, and is switched off by default. To activate it, place:
log = [querylog_filename]
in the my.cnf or my.ini configuration file. The name is optional, if you don’t supply a name the query log will be given the hostname. If you’re just planning to use the log to see what changes have been made to the database, but don’t want the overhead that goes with recording each and every query, a much better log to use is the binary update log.
Binary Update Log
The binary update log is activated by placing
log-bin[=binary_update_log_filename]
in your configuration file (you should know where that is by now!). The filename is optional, and if you do not supply it, the log will be named hostname-bin, with hostname being the name of the host. Any extension (the last 3 letters after the dot) you supply will be dropped, as MySQL creates its own, starting at 001. This log stores all updates to the database in an efficient binary format (the deprecated Update Log stores the same data in a less compressed format). The binary log stores each SQL statement that makes a change to the database in the same order as they were executed, and for this reason is useful for restoring backups, or for replication. MySQL comes with an executable binary that allows you to view the contents of the binary update log; (since it is a binary format, viewing it in a text editor is no use). It’s called mysqlbinlog, and can be accessed as follows, to view the sample binary update log.
% mysqbinlog homeserver-bin.001 # at 4 #020812 1:44:12 server id 1 Start: binlog v 1, server v 3.23.47-log created 020812 1:44:12 # at 73 #020812 1:44:12 server id 1 Query thread_id=237010 exec_time=0 error_code=0 use news; SET TIMESTAMP=1029109452; update storytracker set view_count = 929, last_time = '2002-08-12 01:18:08' where article_id = 'qw1028980982556B216'; # at 214 #020812 1:44:12 server id 1 Query thread_id=237002 exec_time=0 error_code=0 SET TIMESTAMP=1029109452;
You can see that the time each query started, as well as the time it took to execute, is stored.
A new binary update log is created each time the server is restarted, or the database server is flushed (with FLUSH LOGS, mysqladmin flush-logs or mysqladmin refresh), as well as when a single update log becomes too big (determined by the value of max_bin_log_size, which you can set in your config file. Each time a new one is created, the extension increments by one, from 001 to 002 and so on. The highest number will then always be the most recent log. The names of the update logs are stored in a binary update log index file. This is given the same name as the other binary update logs, but with the extension .index. A sample index file looks as follows:
./homeserver-bin.001 ./homeserver-bin.002 ./homeserver-bin.003 ./homeserver-bin.004 ./homeserver-bin.005 ./homeserver-bin.006 ./homeserver-bin.007 ./homeserver-bin.008 ./homeserver-bin.009
To remove the excess binary update logs, and start again with 001, you can run the SQL command:
RESET MASTER
Be very careful when doing this though, as if you’re replicating your databases you must ensure that all SQL commands have been replicated, and ensure that the databases stay in sync. In addition, if you plan to restore a backup, you need to make sure you only delete binary logs that were created before your latest backup. A full discussion of backups is beyond the scope of this article, but to restore the contents of a binary log to a MySQL server, you can use the following command:
% mysqlbinlog homeserver-bin.001 | mysql
Since the binary log stores the databases (note for example the use news statement in the above sample), there’s no need to specify a database.
You don’t need to store all updates in the binary log. You can tell MySQL to ignore certain databases, or to only store updates for specific databases. For example, to ignore the test database, place the following in your configuration file:
binlog-ignore-db = test
To only store updates for the critical_data database, place the following in your config file:
binlog-do-db = critical_data
You can have multiple binlog-do-db options in your configuration.