More MySQL logs

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.

»


See All Articles by Columnist
Ian Gilfillan

Ian Gilfillan
Ian Gilfillan
Ian Gilfillan lives in Cape Town, South Africa. He is the author of the book 'Mastering MySQL 4', published by Sybex, and has been working with MySQL since 1997. These days he develops mainly in PHP and MySQL, although confesses to starting out with BASIC and COBOL, way back when, and still has a soft spot for Perl. He developed South Africa's first online grocery store, and has developed and taught internet development and other technical courses for various institutions. He has majors in Programming and Information Systems, as well as English and Philosophy. For 5 years he was Lead Developer and IT Manager for Independent Online, South Africa's premier news portal. However, he has now 'retired' from fulltime work, and is hoping that his next book will be more in the style of William Blake and Allen Ginsberg.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles