Not finished yet... To be edited

To ensure that there is no other parameter configuration impact, a CentOS7 virtual machine with minimized installation is reinstalled

1. Install mysql5.6 database https://my.oschina.net/sgmder/blog/1631045

2. Configure mysql, enable binlog, and change binlog mode to Row Level mode

vi /etc/my.cnf 

Modify the mysql configuration file and add the following under [mysqld]

# Note: open binlog file name starts with MySQL bin
log-bin = mysql-bin
# Note: backup recovery mode does not need to turn on Row mode flashback needs to turn on Row mode
binlog_format="ROW"

Restart the MySQL database binlog to open the generated file / var/lib/mysql/mysql-bin.000001

service mysqld restart

Log in to the database and create the test database demo and test table user

mysql> create database demo;
Query OK, 1 row affected (0.00 sec)
mysql> use demo;
Database changed
mysql> CREATE TABLE `user` ( `id` int(8) NOT NULL AUTO_INCREMENT, `name` varchar(32) NOT NULL, `type` int(8) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)

After preparation, refresh the binlog file. At this time, the binlog file mysql-bin.000001 ends, and enter mysql-bin.000002 record

mysql> flush logs;
Query OK, 0 rows affected (0.00 sec)

Backup database

[root@localhost ~]# mysqldump -u root -p --databases demo > db_demo_bak.sql

3. Use backup + binlog to rollback the database

Insert 2 pieces of test data before entering the database

[root@localhost ~]# mysql -u root -p
Enter password: 
mysql> use demo;
Database changed
mysql> insert into user (id,name,type) value (10001,'A','1');
Query OK, 1 row affected (0.00 sec)
mysql> insert into user (id,name,type) value (10002,'B','1');
Query OK, 1 row affected (0.00 sec)

View data

mysql> select * from user;
+-------+------+------+
| id    | name | type |
+-------+------+------+
| 10001 | A    |    1 |
| 10002 | B    |    1 |
+-------+------+------+
2 rows in set (0.00 sec)

Simulate wrong operation and update to modify database data

mysql> update user set name = 'C';
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select * from user;
+-------+------+------+
| id    | name | type |
+-------+------+------+
| 10001 | C    |    1 |
| 10002 | C    |    1 |
+-------+------+------+
2 rows in set (0.00 sec)

The name field of the user table has been modified by misoperation. Refresh the binlog file as soon as possible. mysql-bin.000002 is over, and the subsequent operation will enter mysql-bin.000003

mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)

At this point, check the mysql binlog file

[root@localhost ~]# ls /var/lib/mysql
auto.cnf  ibdata1      ib_logfile1  mysql-bin.000001  mysql-bin.000003  mysql.sock
demo      ib_logfile0  mysql        mysql-bin.000002  mysql-bin.index   performance_schema

Then, the mysql-bin.000002 file records all the database execution from backup to misoperation. Now we need to recover the data misoperated by update

Use backup to restore the database first. At this time, the table user has no data

mysql> user demo;
mysql> drop table user;
mysql> source /root/db_demo_bak.sql
mysql> select * from user;
Empty set (0.00 sec)

View the operation of mysql-bin.000002 record

mysql> show binlog events in 'mysql-bin.000002';
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                  |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| mysql-bin.000002 |   4 | Format_desc |         1 |         120 | Server ver: 5.6.39-log, Binlog ver: 4 |
| mysql-bin.000002 | 120 | Query       |         1 |         192 | BEGIN                                 |
| mysql-bin.000002 | 192 | Table_map   |         1 |         243 | table_id: 72 (demo.user)              |
| mysql-bin.000002 | 243 | Write_rows  |         1 |         289 | table_id: 72 flags: STMT_END_F        |
| mysql-bin.000002 | 289 | Xid         |         1 |         320 | COMMIT /* xid=147 */                  |
| mysql-bin.000002 | 320 | Query       |         1 |         392 | BEGIN                                 |
| mysql-bin.000002 | 392 | Table_map   |         1 |         443 | table_id: 72 (demo.user)              |
| mysql-bin.000002 | 443 | Write_rows  |         1 |         489 | table_id: 72 flags: STMT_END_F        |
| mysql-bin.000002 | 489 | Xid         |         1 |         520 | COMMIT /* xid=148 */                  |
| mysql-bin.000002 | 520 | Query       |         1 |         592 | BEGIN                                 |
| mysql-bin.000002 | 592 | Table_map   |         1 |         643 | table_id: 72 (demo.user)              |
| mysql-bin.000002 | 643 | Update_rows |         1 |         723 | table_id: 72 flags: STMT_END_F        |
| mysql-bin.000002 | 723 | Xid         |         1 |         754 | COMMIT /* xid=149 */                  |
| mysql-bin.000002 | 754 | Rotate      |         1 |         801 | mysql-bin.000003;pos=4                |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
14 rows in set (0.00 sec)

120 - 320 first insert

320 - 520 second insert

520 - 754 misoperation update

Using mysqlbinlog tool to recover insert operation 120 - 520

[root@localhost ~]# mysqlbinlog --stop-position=471 --database=ops /var/lib/mysql/mysql-bin.000003 | /usr/bin/mysql -uroot -p123456 -v ops

View the following user table data

mysql> select * from user;
+-------+------+------+
| id    | name | type |
+-------+------+------+
| 10001 | A    |    1 |
| 10002 | B    |    1 |
+-------+------+------+
2 rows in set (0.00 sec)

Ok, data recovery successful