4.9.3 The Update Log

Note: the update log is replaced by the binary log. See section 4.9.4 The Binary Log. With this you can do anything that you can do with the update log. The update log will be removed in MySQL 5.0.

When started with the --log-update[=file_name] option, mysqld writes a log file containing all SQL commands that update data. If no filename is given, it defaults to the name of the host machine. If a filename is given, but it doesn't contain a path, the file is written in the data directory. If `file_name' doesn't have an extension, mysqld will create log file names like so: `file_name.###', where ### is a number that is incremented each time you execute mysqladmin refresh, execute mysqladmin flush-logs, execute the FLUSH LOGS statement, or restart the server.

Note: for the above scheme to work, you must not create your own files with the same filename as the update log + some extensions that may be regarded as a number, in the directory used by the update log!

If you use the --log or -l options, mysqld writes a general log with a filename of `hostname.log', and restarts and refreshes do not cause a new log file to be generated (although it is closed and reopened). In this case you can copy it (on Unix) by doing:

mv hostname.log hostname-old.log
mysqladmin flush-logs
cp hostname-old.log to-backup-directory
rm hostname-old.log

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 the log will be logged in the execution order.

If you want to update a database from update log files, you could do the following (assuming 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 get all the log files in 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.

User Comments

Add your own comment.