MySQL study notes 15th Update Log File restore data _ MySQL

You cannot back up data at any time when the log file is updated during the 15 days of MySQL study notes. However, 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. 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.

1. Enable logging

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.

Execute mysqladmin refresh, mysqladmin flush-logs, flush logs statements, or add 1 when restarting 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 the mysqladmin refresh, mysqladmin flush-logs, flush logs statements,

Or the log file is cleared when the server is restarted.

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 setting a column as its existing value

UPDATE statement.

II. rewrite logs

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

3. 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