Use the Update log file in MySQL

You cannot back up data at any time, but when your data is lost or the files in the database directory are damaged, you can only restore the files that have been backed up, and insert or update the data after this, you can't do anything about it. To solve this problem, you must use the Update log. The Update log records the SQL statements for updating, inserting, and deleting records in real time. Enable logging when

You cannot back up data at any time, but when your data is lost or the files in the database directory are damaged, you can only restore the files that have been backed up, and insert or update the data after this, you can't do anything about it. To solve this problem, you must use the Update log. The Update log records the SQL statements for updating, inserting, and deleting records in real time. Enable logging when

You cannot back up data at any time, but when your data is lost or the files in the database directory are damaged, you can only restore the files that have been backed up, the inserted or updated data after this will be powerless. To solve this problem, you must use the Update log. The Update log records the SQL statements for updating, inserting, and deleting records in real time.

Enable log

When the -- log-update = file_name option is enabled, mysqld writes all SQL commands for updating data to the record file. The file is written to the data directory and has a name: file_name. #, here # Is a number. It adds 1 to each execution of mysqladmin refresh, mysqladmin flush-logs, flush logs statements, or restart the server.

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

If you hit the specified extension in the file, the updated log will no longer use the ordered file, and the specified file will be used. However, when you execute mysqladmin refresh, mysqladmin flush-logs, flush logs statements, or restart the server, the log file is cleared.

Update records are clever because they only record statements that actually update data. Therefore, if a where update or DELETE statement cannot find a row, it is not written to the record file. It even skips the UPDATE statement that sets a column as its existing value.

Re-write log

It must be emphasized that, in one of the following cases, the sequence of using the new log file-log file will automatically increase (the file_name is not specified or the specified file_name does not include the extension) or clear the file (the specified file_name includes the extension ):

Command mysqladmin refresh

Command mysqladmin flush-logs

SQL statement FLUSH LOGS

Server restart

Restore log Content

For all the Update log files, you can specify it as the input of the mysql client to execute the SQL statement and restore data. For example:

Shell> mysql

However, you may have deleted the DATABASE by executing the DROP database operation and want to restore the content of the DATABASE. For this purpose, you can use the -- one-database option:

Shell> mysql -- one-database db_name
If you want to restore the updated log data in batches, you can do this in Unix:

$ Ls-t-r-l hostname. [0-9] * | xargs cat | mysql -- one-database db_name

Note that files are sorted by time. If any file you modify, the wrong data may be imported due to an incorrect sequence.

If you restore data in the file order, remove the-t and-r options:

$ Ls-l hostname. [0-9] * | xargs cat | mysql -- one-database db_name

Summary

This section describes operations related to log files, especially log updates. Note how to enable the Update log, when the server re-writes the log, and how to restore the updated log Content. In particular, there are various techniques, such as how to restore logs according to the time sequence generated by log files and how to restore only the content of the specified database.