How to use MySQL to update log files

How can I use MySQL to update log files? Many new users who are new to the MySQL database may have this question. You can find the answer to the question after reading the following.

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 emphasized that, in one of the following cases, the sequence of using the new log file-log file will automatically increase without specifying file_name 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? Ct? Cr? Cl 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.

Mysql Log File description

How to relocate the MySQL DATA DIRECTORY

MySQL database table Representation

Location of MySQL DATA DIRECTORY

How to change the MySQL Data File Path