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 file name is given, but it does not contain a leading path, the file is written in the data directory. If file_name does not have an extension, mysqld creates log files with names of the form file_name.nnnnnn, where nnnnnn is a number that is incremented each time you start the server or flush the logs.

Update logging is “smart” in that it logs only statements that actually update data. Thus, an UPDATE or DELETE with a WHERE clause that finds no rows is not written to the log. Update logging also skips UPDATE statements that merely set a column to its existing value.

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.nnnnnn):

shell> ls -1 -t -r file_name.[0-9]* | xargs cat | mysql

ls is used to sort the update log file names 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.

The update log should be protected because logged statements might contain passwords. See Section 5.4.2.1, “Administrator Guidelines for Password Security”.