This is the fifth day of my participation in the August More text Challenge. For details, see: August More Text Challenge

Use binLog to quickly roll back mysql data

In the daily use of mysql, especially in the development environment, there are often some foolish operations, manual operation of the database, or delete table import and other operations, in addition to the operation and maintenance of backup mysql, you can also record some binlog information, such as record the time point and position before manual operation, After the operation is finished, record the data separately, so that the data will be more secure, in the development environment can also be a wave of SAO operation to destroy the database and quickly roll back.

Simulation operation

Before all kinds of honey operation, special is not sure of the operation, such as various batch operation, structure update, and manual execution of some SQL, if the operation error such as not adding where, etc. It will contaminate the database directly

It is strongly recommended to back up or record the binlog before the operation. Backup is often a full amount of large text data, whether export or import will lead to slow speed is very troublesome, and record the binlog point in time or position is more convenient, if the operation does not appear major problems will save the above export time and most of the import time

Here, we simulate an operation. There is a requirement to change some value information of some fields in the database, so we need to write a compensation script to update the historical data. These operations are usually large in number (full history), and it is more important if they involve privacy and other information.

This is often done not just once, but multiple times in the development or test environment, which involves restoring the data to the starting state each time, then executing the script, and finally verifying the integrity and accuracy of the data

---

Open the binlog

[mysqld]
skip-grant-tables
log-bin=mysql-bin
server-id=1
binlog_format=ROW
Copy the code

Docker word 5.7 is mounted to this place

 {
    "Type": "bind",
    "Source": "/Users/xxxxx/mysql/conf/mysql",
    "Destination": "/etc/mysql/conf.d",
    "Mode": "",
    "RW": true,
    "Propagation": "rprivate"
 }
Copy the code

Before executing the script, we need to record the current binlog location, enter the mysql terminal, get the latest binlog file and position

mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB |  Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000013 | 16275505 | | | | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC)Copy the code

At this point you are ready to execute the script data and specify restore to location 16275505 if there is a problem

mysqlbinlog --skip-gtids --stop-position=16275505 mysql-bin.000013 | mysql -u root -p
Copy the code

---

If we delete all the tables, import the structure + data, and then execute the script, it won’t work

In order we can delete the table first, and then look at the time point and position

Show binlog events in 'mysql-bin.000013'; The default binlog is CD /var/lib/mysqlCopy the code

We need to know where was the last time the table was dropped

mysqlbinlog mysql-bin.000013 | grep -C  2 "DROP TABLE"
Copy the code

We can see the point in time and position to delete each command. Record them.

# at 13836902 #210813 7:31:27 server id 1 end_log_pos 13837051 CRC32 0xff733779 Query thread_id=5 exec_time=6133 error_code=0 SET TIMESTAMP=1628839887/*! * /. SET @@session.sql_mode=1436549152/*! * /. DROP TABLE IF EXISTS `dept_info` /* generated by server */ -- # at 13842194 #210813 7:31:27 server id 1 end_log_pos 13842347 CRC32 0x5490a31e Query thread_id=5 exec_time=6133 error_code=0 SET TIMESTAMP=1628839887/*! * /. SET @@session.sql_mode=1436549152/*! * /. DROP TABLE IF EXISTS `operation_log` /* generated by server */ -- # at 15136041 #210813 7:31:28 server id 1 end_log_pos 15136201 CRC32 0xe265dc77 Query thread_id=5 exec_time=6133 error_code=0 SET TIMESTAMP=1628839888/*! * /. SET @@session.sql_mode=1436549152/*! * /. DROP TABLE IF EXISTS `templates_change_log` /* generated by server */ -- # at 15138969 #210813 7:31:28 server id 1 end_log_pos 15139129 CRC32 0x17543742 Query thread_id=5 exec_time=6133 error_code=0 SET TIMESTAMP=1628839888/*! * /. SET @@session.sql_mode=1436549152/*! * /. DROP TABLE IF EXISTS `user_department_info` /* generated by server */ -- # at 15588672 #210813 7:31:29 server id 1 end_log_pos 15588821 CRC32 0x7eaf9bd6 Query thread_id=5 exec_time=6132 error_code=0 SET TIMESTAMP=1628839889/*! * /. SET @@session.sql_mode=1436549152/*! * /. DROP TABLE IF EXISTS `ironbank_user_info` /* generated by server */Copy the code

Then we import the data, just like above, after executing the SQL script, we record the point in time and position, and then we can ravage the data at will. If something goes wrong or contaminates the data, You can use the binlog to roll back to the state where the data was just imported, and I’m using position to roll back, and the same thing happens if you record the point in time, and it’s not always easy to do that if you don’t know the range, because if you only record the start and not the end it’s going to be overwritten from the start to the end, and so on.

mysqlbinlog --start-position=3775337 --skip-gtids --stop-position=6796448 mysql-bin.000013 | mysql -u root -p
Copy the code