Use the update log file in MySQL _ MySQL

Use the update log file bitsCN.com 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 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 with the 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 noted that, in one of the following cases, the sequence of using the new log file-log files is automatically increased (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