数据模拟
[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*/;