Two ways MySQL uses Binlog to recover data to solve mis-operation problems

为保证没有其他参数配置影响，重新安装配置了一台最小化安装的CentOS7虚拟机

1. Basic knowledge?

Installing the mysql5.6 Database
Mysql Binlog Preliminary understanding

2. Configure MySQL to turn on Binlog, modify Binlog mode to row level mode?

[[email protected] ~]# vi /etc/my.cnf

Modify the MySQL configuration file and add the following under [Mysqld]

# 注释: 开启binlog 文件名以mysql-bin开头log-bin = mysql-bin# 注释: 备份恢复模式不需要开启Row模式 闪回需要开启Row模式binlog_format=”ROW”

3. Restart the MySQL database Binlog open the build 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 changedmysql> 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)

? Ready to finish, refresh the Binlog file, Binlog file mysql-bin.000001 end, enter mysql-bin.000002 record

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

Backing Up the database

[[email protected] ~]# mysqldump -u root -p –databases demo > db_demo_bak.sql

4. Roll back the database using Backup +binlog logging 1. Insert 2 test data into the database first

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)

2. View data

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

3. Analog error operation update modifies database data

mysql> update user set name = ‘C’;Query OK, 2 rows affected (0.00 sec)Rows matched: 2 Changed: 2 Warnings: 0mysql> select * from user;+——-+——+——+| id | name | type |+——-+——+——+| 10001 | C | 1 || 10002 | C | 1 |+——-+——+——+2 rows in set (0.00 sec)

The name field of the 4.user table is modified by mistake, and the Binlog file is refreshed, mysql-bin.000002 ends, and subsequent operations enter MYSQL-BIN.000003

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

5. At this point, check out the MySQL binlog file

[[email protected] ~]# ls /var/lib/mysql auto.cnf ibdata1 ib_logfile1 mysql-bin.000001 mysql-bin.000003 mysql.sockdemo ib_logfile0 mysql mysql-bin.000002 mysql-bin.index performance_schema?

So, the mysql-bin.000002 file records all database executions from backup to Misoperation, and now we need to recover the data that was accidentally deleted by the update 6. Restore the database using a backup first the table user has no data

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

7. View mysql-bin.000002 recorded operations to view POS and end_log_pos for each operation

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, Binlogver: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)

Can you see?

120 - 320 第一次insert320 - 520 第二次insert520 - 754 误操作update

8. Use the Mysqlbinlog tool to restore insert operations 120-520

[[email protected] ~]# mysqlbinlog –start-position=120 –stop-position=520 -database=demo /var/lib/mysql/mysql-bin.000003 | /usr/bin/mysql -u root -p mima666 -v demo

See the official documentation for the detailed use of Mysqlbinlog Https://dev.mysql.com/doc

9. View the 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 is successful

5. Roll back the database using the Binlog+myflash Flash back method?

See our Open Source project Myflash
Myflash-gitee Myflash-github

Two ways MySQL uses Binlog to recover data to solve mis-operation problems