【MySQL 8.0】通过延迟复制恢复误删除的表
原创
©著作权归作者所有:来自51CTO博客作者dbprofessional的原创作品,请联系作者获取转载授权,否则将追究法律责任
(root@node02) > stop replica;
Query OK, 0 rows affected (0.08 sec)
(root@node02) > change replication source to source_delay = 3600;
Query OK, 0 rows affected (0.07 sec)
(root@node02) > start replica user='repl' password='repl';
Query OK, 0 rows affected (0.03 sec)
(root@node02) > show replica status\G;
...
SQL_Delay: 3600
...
(root@node01) > select count(*) from orders;
+----------+
| count(*) |
+----------+
| 300000 |
+----------+
1 row in set (0.19 sec)
[mysql@node01 ~]$ ./tpcc-mysql/tpcc_start -h 192.168.1.101 -P 3306 -d tpcc10 -u tpcc -p tpcc -w 10 -c 10 -l 60
(root@node01) > select count(*) from orders;
+----------+
| count(*) |
+----------+
| 300580 |
+----------+
1 row in set (0.19 sec)
(root@node01) > drop table orders;
Query OK, 0 rows affected (0.54 sec)
(root@node01) > show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000005 | 11333054 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.06 sec)
(root@node01) > pager grep -i -B 5 "drop table"
PAGER set to 'grep -i -B 5 "drop table"'
(root@node01) > show binlog events in 'mysql-bin.000005';
| mysql-bin.000005 | 11331423 | Rows_query | 101 | 11331595 | # UPDATE customer SET c_balance = c_balance + 470.3599853515625 , c_delivery_cnt = c_delivery_cnt + 1 WHERE c_id = 1118 AND c_d_id = 10 AND c_w_id = 7
| mysql-bin.000005 | 11331595 | Table_map | 101 | 11331707 | table_id: 155 (tpcc10.customer)
| mysql-bin.000005 | 11331707 | Update_rows | 101 | 11332811 | table_id: 155 flags: STMT_END_F
| mysql-bin.000005 | 11332811 | Xid | 101 | 11332842 | COMMIT /* xid=49986 */
| mysql-bin.000005 | 11332842 | Anonymous_Gtid | 101 | 11332919 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
| mysql-bin.000005 | 11332919 | Query | 101 | 11333054 | use `tpcc10`; DROP TABLE `orders` /* generated by server */ /* xid=50299 */
(root@node02) > select count(*) from orders;
+----------+
| count(*) |
+----------+
| 300000 |
+----------+
1 row in set (0.46 sec)
(root@node02) > stop replica;
Query OK, 0 rows affected (0.07 sec)
(root@node02) > change replication source to source_delay = 0;
Query OK, 0 rows affected (0.08 sec)
(root@node02) > start replica until source_log_file='mysql-bin.000005',source_log_pos=11332811 user='repl' password='repl';
Query OK, 0 rows affected (0.10 sec)
(root@node02) > select count(*) from orders;
+----------+
| count(*) |
+----------+
| 300580 |
+----------+
1 row in set (0.20 sec)
[mysql@node02 ~]$ mysqldump -uroot -pabcd.1234 --databases tpcc10 --tables orders > orders.sql
[mysql@node02 ~]$ scp orders.sql node01:/home/mysql/
[mysql@node01 ~]$ mysql -uroot -pabcd.1234 -D tpcc10 < orders.sql
(root@node01) > select count(*) from orders;
+----------+
| count(*) |
+----------+
| 300580 |
+----------+
1 row in set (0.20 sec)
(root@node02) > stop replica;
Query OK, 0 rows affected (0.07 sec)
(root@node02) > start replica user='repl' password='repl';
Query OK, 0 rows affected (0.05 sec)