Analysis of binary log rollback in MySQL database

Concepts & Functions
Binary logging means that when you do a data flow operation, the database records the SQL operations in addition to the Select, such as UPDATE, DELETE, alter ..., and the type of record contains the full SQL statement, operation time, execution time, error code, and so on.

With binary logs, you can roll back data to any moment, such as the eve of a database crash, or a moment before any other disaster, and you can almost do 0 loss of data and experience the flow of time backwards, like "crossing".

How to use

To use the binary log, first determine your database version support, starting from the mysql5.1 binary log to replace the original "update log."

Open profile my.cnf for option settings

The code is as follows	Copy Code
[Mysqld] Log-bin=<path>

This will create the appropriate directories and files when you restart the MySQL server. FileName is a file with a numeric name extension, if a split size value is set in the configuration file, a new file is automatically generated when a single binary reaches this limit, or it can be manually refreshed using the Flush-logs command running in tool mysqladmin. And then run mysqldump again for a full backup with the parameter –flush-logs or –deletel-masger-logs to refresh or delete the log.

These MySQL logs we can use the Mysqlbinlog binary log file tool to handle

The server-generated binary log file is written in binary format. To check files in these text formats, you should use the Mysqlbinlog utility.
You should call Mysqlbinlog this way:

The code is as follows	Copy Code
Shell> mysqlbinlog [options] log-files ...

For example, to display the contents of the binary log binlog.000003, use the following command:

The code is as follows	Copy Code
Shell> Mysqlbinlog binlog.0000003

The output includes all the statements contained in the BINLOG.000003, as well as other information such as the time spent by each statement, the thread ID issued by the customer, the timestamp of the outgoing thread, and so on.

To view the binary log:

The code is as follows	Copy Code
1 Mysqlbinlog xxx-bin.000989

With grep filter:

The code is as follows	Copy Code
1 Mysqlbinlog xxxx-bin.000989 |grep uid=9527

Of course, you can also blur the view by regular:

The code is as follows	Copy Code
1 Mysqlbinlog xxxx-bin. [0-9]*

To run a rollback operation:

#如果不是进行回滚, do not execute this mysqlbinlog xxxx-bin. [0-9]* | Mysql

Precautions:

• If the default encoding is specified in MY.CNF, it will cause the binary view times to be wrong unknown variable ' default-character-set=utf8′, this is a bug, Personal understanding is due to the client and configuration file in this way the binary is not supported by the corresponding update. This bug is expected to be eliminated in future releases. The temporary solution is to comment out the configuration file in this sentence, do not restart the MySQL service, with the use of the tool, the annotation can be removed.
• If binary logs are used, performance has a certain impact, according to official estimates of around 1%. Of course, the 1% performance loss is entirely worthwhile for the powerful role it plays.