使用binlog恢复数据

目录

• 数据模拟
• 恢复到
• 查找最初始的建prod表
• 查找updata语句
• 查找delete语句
• 查找删除语句

数据模拟

[root@db01 ~]# cat 6.sh 
#!/bin/bash


mysql -uroot -p123 -e "drop database if exists prod;"
mysql -uroot -p123 -e "create database if not exists prod;"
mysql -uroot -p123 -e "create table if not exists prod.t1(id int);"

num=1
while true;do
  mysql -uroot -p123 -e "insert into prod.t1 values($num);commit;"
  ((num++))
  sleep 1
done

mysql> create database hht;
Query OK, 1 row affected (0.00 sec)

mysql> create table hht.hht(id int);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into hht.hht values(1);
Query OK, 1 row affected (0.01 sec)

mysql> insert into hht.hht values(2);
Query OK, 1 row affected (0.00 sec)

mysql> insert into hht.hht values(3);
Query OK, 1 row affected (0.00 sec)

mysql> update hht.hht set id=10 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from hht.hht;
+------+
| id   |
+------+
|   10 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

mysql> delete from hht.hht where id=3;
Query OK, 1 row affected (0.00 sec)

mysql> select * from hht.hht;
+------+
| id   |
+------+
|   10 |
|    2 |
+------+
2 rows in set (0.00 sec)

mysql> drop table hht.hht;
Query OK, 0 rows affected (0.01 sec)

mysql> drop database hht;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from prod.t1;

mysql> drop database prod;
Query OK, 1 row affected (0.01 sec)

mysql> select * from prod.t1;
ERROR 1146 (42S02): Table 'prod.t1' doesn't exist

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000018 |    62052 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

恢复到

# 数据恢复到这里
mysql> select * from hht.hht;
+------+
| id   |
+------+
|   10 |
|    2 |
|    3 |
+------+

# 生产数据恢复
1.1546--33715
mysqlbinlog --start-position=1546 --stop-position=33715 mysql-bin.000018 > /tmp/1.sql
2.33761--39609
mysqlbinlog --start-position=33761 --stop-position=39609 mysql-bin.000018 > /tmp/2.sql
3.39649--41888
mysqlbinlog --start-position=39649 --stop-position=41888 mysql-bin.000018 > /tmp/3.sql
4.42005--47525
mysqlbinlog --start-position=42005 --stop-position=47525 mysql-bin.000018 > /tmp/4.sql
5.47608--61960
mysqlbinlog --start-position=47608 --stop-position=61960 mysql-bin.000018 > /tmp/5.sql

# 查看/tmp目录
[root@db01 data]# ll /tmp/
-rw-r--r-- 1 root root 112073 Aug  1 19:51 1.sql
-rw-r--r-- 1 root root  21487 Aug  1 19:51 2.sql
-rw-r--r-- 1 root root   9032 Aug  1 19:51 3.sql
-rw-r--r-- 1 root root  20491 Aug  1 19:52 4.sql
-rw-r--r-- 1 root root  51307 Aug  1 19:52 5.sql

## 导入数据
mysql> source /tmp/1.sql
mysql> source /tmp/2.sql
mysql> source /tmp/3.sql
mysql> source /tmp/4.sql
mysql> source /tmp/5.sql

# 数据导入成功
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| hht                |
| prod               |
+--------------------+
mysql> select * from hht.hht;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
mysql> select * from prod.t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|。。等 |
+------+

查找最初始的建prod表

mysqlbinlog --base64-output=decode-rows -vvv /app/mysql/data/mysql-bin.000018| grep 'create database if not exists prod' -B 5

drop database if exists prod
/*!*/;
# at 1546
#230801 17:59:42 server id 1  end_log_pos 1654 CRC32 0x0d4aa900 	Query	thread_id=7	exec_time=0	error_code=0
SET TIMESTAMP=1690883982/*!*/;
create database if not exists prod

查找updata语句

mysqlbinlog --base64-output=decode-rows -vvv /app/mysql/data/mysql-bin.000018| grep -i 'update' -C 10


# at 33715
#230801 18:02:32 server id 1  end_log_pos 33761 CRC32 0x2956997f 	Update_rows: table id 77 flags: STMT_END_F
### UPDATE `hht`.`hht`
### WHERE
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
### SET
###   @1=10 /* INT meta=0 nullable=1 is_null=0 */
# at 33761
#230801 18:02:32 server id 1  end_log_pos 33792 CRC32 0x4ff0a33b 	Xid = 859
COMMIT/*!*/;
# at 33792

查找delete语句

mysqlbinlog --base64-output=decode-rows -vvv /app/mysql/data/mysql-bin.000018| grep -i 'delete' -C 10

# at 39609
#230801 18:03:04 server id 1  end_log_pos 39649 CRC32 0xed321051 	Delete_rows: table id 77 flags: STMT_END_F
### DELETE FROM `hht`.`hht`
### WHERE
###   @1=3 /* INT meta=0 nullable=1 is_null=0 */
# at 39649
#230801 18:03:04 server id 1  end_log_pos 39680 CRC32 0xd3d3afcc 	Xid = 985
COMMIT/*!*/;
# at 39680

查找删除语句

mysqlbinlog --base64-output=decode-rows -vvv /app/mysql/data/mysql-bin.000018| grep -i 'drop' -C 10

# at 41888
#230801 18:03:16 server id 1  end_log_pos 42005 CRC32 0x3d773fc1 	Query	thread_id=20	exec_time=0	error_code=0
SET TIMESTAMP=1690884196/*!*/;
SET @@session.pseudo_thread_id=20/*!*/;
DROP TABLE `hht`.`hht` /* generated by server */
/*!*/;
# at 42005
#230801 18:03:16 server id 1  end_log_pos 42073 CRC32 0x1e9d2a68 	Query	thread_id=223	exec_time=0	error_code=0
SET TIMESTAMP=1690884196/*!*/;
BEGIN
/*!*/;
# at 42073

# at 47525
#230801 18:03:45 server id 1  end_log_pos 47608 CRC32 0xc29b7ccb 	Query	thread_id=20	exec_time=0	error_code=0
SET TIMESTAMP=1690884225/*!*/;
drop database hht
/*!*/;
# at 47608
#230801 18:03:46 server id 1  end_log_pos 47676 CRC32 0xec075fc3 	Query	thread_id=253	exec_time=0	error_code=0
SET TIMESTAMP=1690884226/*!*/;
BEGIN
/*!*/;
# at 47676

# at 61960
#230801 18:05:05 server id 1  end_log_pos 62052 CRC32 0x94bcde20 	Query	thread_id=291	exec_time=0	error_code=0
SET TIMESTAMP=1690884305/*!*/;
SET @@session.pseudo_thread_id=291/*!*/;
drop database prod
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;