Using the update log file in MySQL

You cannot back up your data at any time, but when your data is lost, or if the files in the database directory are corrupted, you can only restore the files that you have backed up, and the data that is inserted or updated after that is powerless. To resolve this problem, you must use the update log. The update log enables real-time recording of SQL statements that update, insert, and delete records.

Enable logging

when started with the--log-update=file_name option, Mysqld writes all SQL commands that update data to the record file. The file is written to the data directory and has a name file_name.#, here # is a number that is added 1 each time the mysqladmin refresh or mysqladmin flush-logs, flush logs statement is executed, or the server is restarted.

If you do not specify file_name, the host name of the server is used by default.

If you specify an extension in a file hit, the update log no longer uses the sequential file, using the specified file. But when you do it, the log file is emptied each time the mysqladmin refresh or mysqladmin flush-logs, flush logs statement is executed, or the server is restarted. The

Update record is smart because it only records statements that actually update the data. Therefore, an update or delete with a where cannot find a row, and it is not written to the record file. It even skips the UPDATE statement that will set a value that it already has.

Overriding the log

It is important to stress that in one of the following cases, the new log file will be used-automatically incremented in the order of the log files (no file_name is specified or the specified file_name does not include an extension), or the file is emptied (the specified file_ Name includes extension):

Command mysqladmin refresh

Command mysqladmin flush-logs

SQL statement flush logs

Server reboot
br> Recovery log Content

for all update log files, you can designate it as the input of the MySQL client to execute the SQL statements and recover the data. For example,

Shell>mysql

However, you may have deleted the drop database and wanted to restore only the contents of the databases, and for this purpose you can use the--one-database option:

Shell>mysql--one-database db_name < hostname.nnn

If you want to bulk recover the data for the update log, you can do this in Unix:

$ ls–t–r–l hostname.[ 0-9]* | Xargs Cat | MySQL--one-database db_name

Note that because files are sorted by time, if you modify any of these files, you may be able to import the wrong data because of a sequential error.

If the data is recovered in file order, remove the-T and-R options:

$ ls-l hostname.[ 0-9]* | Xargs Cat | MySQL--one-database db_name

Summary

This section describes the operation of log files, especially the update log. You need to be aware of how to enable the update log, the timing of the server rewrite log, and how to recover the contents of the update log. In particular, there are a variety of techniques, such as how to recover logs in chronological order generated by log files, and how to recover only the contents of a specified database.