Background Today’s project needs to repair MySQL data. By comparing various solutions and tools, we prepare to use binlog2SQL tool to perform “data flashback”. How to use it?

MySQL database ready to restore a table in a library as an example,

Yum + RPM = yum+ RPM = yum+ RPM = yum+ RPM = yum+ RPM

Source library: MySQL 5.7.30

Target library: MySQL 5.7.30

Modify the my.cnf configuration file by adding the following contents:

Copy code server_id=1# id, which must and cannot be duplicated when clustering

Log_bin = mysql-bin # must be enabled

Binlog_format = ROW# mandatory

Expire_logs_days = 0 # Number of days for saving logs on demand. 0 indicates that code is copied permanently

PIP binlog2SQL installation requires a Python runtime environment. PIP is installed first.

PIP is a Python package installation and management tool. Check whether PIP is installed on this machine.

Make sure your server has access to the public network before downloading the device.

wget bootstrap.pypa.io/get-pip.py python get-pip.py

View the command again

If the pip-V sees the following, the installation is successful

Install Git using yum to install Git:

Yum -y install git install binlog2sql install binlog2sql

Git clone github.com/danfengcao/… && CD Binlog2sql will generate a binlog2SQL directory in your current directory. Run the following command in the current directory:

PIP install -r requirements. TXT install -r requirements. TXT

At this point, the binglog2SQL tool is installed.

Use the procedure source library to view the current binlog master file as follows:

You can see that the source database is currently writing the main log is mysql-bin.000003. Copy this file to any directory of the target database through SSH.

Target library also run show master status\G; If the number of the file is smaller than the number of the source library, for example, the target library is mysql-bin.000002, manually flush one or more binlog files until the binlog number (file name) of the source library can be overwritten.

Data recovery at this point, the dishes are ready, stir fry

Go to the binlog2SQL directory in your binlog2SQL home directory and execute the following command:

Py -h127.0.0.1 -p Your database port -u database user -p’ database password ‘–flashback -d’ database name ‘-t’ table name –start-file=’mysql-bin.000003′ –start-datetime=’ start time ‘–stop-datetime=’ end time’ > flashback-test. Time depends on your filter criteria, binlog file data size, time interval size and other conditions, according to your server resources set filter criteria. If the amount of data is too large, the results may not be available.

No message is good news. If you wait for a while and finally see the command prompt with the generated file size > 0, congratulations!

This SQL file is the reverse of the SQL that was executed within the specified time interval (if you entered it). Execute the SQL in the target table and the data will return to the point in time you expected.

Installation problems and solve the problem a Python script to connect Mysql error pymysql. Err. OperationalError: (2003, “Can ‘t connect to Mysql server on’ 127.0.0.1 ‘… . There are many articles on the web that suggest that you have a formatting problem. If you are not in this situation, then the following solution will be the ultimate solution

Use mysql_native_password instead of caching_sha2_password.

ALTER USER ‘root’ @ ‘localhost’ IDENTIFIED WITH mysql_native_password BY ‘your password’;