MySQL维护之--如何挽救误操作

mysql> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000023 | 4317 |
| mysql-bin.000024 |     11992 |
| mysql-bin.000025 | 12180 |
| mysql-bin.000026 |     12049 |
| mysql-bin.000027 | 1029 |
| mysql-bin.000028 |       217 |
| mysql-bin.000029 | 647 |
| mysql-bin.000030 |       623 |
| mysql-bin.000031 | 1409 |
+------------------+-----------+
mysql> update tt set CREATE_TIME ='2021-03-25 16:04:00' where TABLE_SCHEMA='sys' and TABLE_NAME='waits_global_by_latency';
Query OK, 1 row affected (0.13 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> update tt set CREATE_TIME ='2021-03-25 16:04:00' where TABLE_SCHEMA='sys' and TABLE_NAME='x$waits_by_user_by_latency';
Query OK, 1 row affected (0.14 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> select * from tt where TABLE_NAME='waits_global_by_latency' or TABLE_NAME='x$waits_by_user_by_latency';
+--------------+----------------------------+---------------------+
| TABLE_SCHEMA | TABLE_NAME | CREATE_TIME |
+--------------+----------------------------+---------------------+
| sys | waits_global_by_latency | 2021-03-25 16:04:00 |
| sys | x$waits_by_user_by_latency | 2021-03-25 16:04:00 |
+--------------+----------------------------+---------------------+

mysql> show binlog events in 'mysql-bin.000031';

mysqlbinlog --base64-output=decode-rows -v -v --start-position=620 --stop-position=1409 --database=htest mysql-bin.000031
# at 620
#200324 3:55:21 server id 2330103 end_log_pos 758 CRC32 0xe0578aa4 Rows_query
# update tt set CREATE_TIME ='2021-03-25 16:04:00' where TABLE_SCHEMA='sys' and TABLE_NAME='waits_global_by_latency'
# at 758
#200324 3:55:21 server id 2330103 end_log_pos 811 CRC32 0x9417f454 Table_map: `htest`.`tt` mapped to number 108
# at 811
…
# at 1079
#200324 3:55:38 server id 2330103 end_log_pos 1220 CRC32 0x066519aa Rows_query
# update tt set CREATE_TIME ='2021-03-25 16:04:00' where TABLE_SCHEMA='sys' and TABLE_NAME='x$waits_by_user_by_latency'
# at 1220
#200324 3:55:38 server id 2330103 end_log_pos 1273 CRC32 0x018171c7 Table_map: `htest`.`tt` mapped to number 108

#./binlog_rollback -m file -w rollback -M mysql -t 4 -H xxx.x.0.1 -P 23301 -u root -p system -dbs htest -tbs tt -sdt "2021-03-24 3:55:00" -edt "2021-03-24 3:56:00" -e -f -r 20 -k -b 100 -l 10  -o /tmp/binlog -dj tbs_all_def.json data/mysql/db_order/blog/mysql-bin.000031
# ll -lth
total 28K
-rw-r--r-- 1 root root 288 Mar 24 04:00 binlog_stats.txt
-rw-r--r-- 1 root root 563 Mar 24 04:00 htest.tt.rollback.31.sql
-rw-r--r-- 1 root root 107 Mar 24 04:00 big_long_trx.txt
-rw-r--r-- 1 root root 64 Mar 24 04:00 ddl_info.txt
-rw-r--r-- 1 root root 492 Mar 24 04:00 tbs_all_def.json

mysql> source tmp/binlog/htest.tt.rollback.31.sql;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from tt where TABLE_NAME='waits_global_by_latency' or TABLE_NAME='x$waits_by_user_by_latency';
+--------------+----------------------------+-------------+
| TABLE_SCHEMA | TABLE_NAME | CREATE_TIME |
+--------------+----------------------------+-------------+
| sys | waits_global_by_latency | NULL |
| sys | x$waits_by_user_by_latency | NULL |
+--------------+----------------------------+-------------+

# pwd
/data/DBbackup/bk_order/20210324/base_20210324
# ls
backup-my.cnf.qp ib_buffer_pool.qp mysql test undo003.qp xtrabackup_info.qp
htest ibdata1.qp performance_schema undo001.qp xtrabackup_binlog_info.qp xtrabackup_logfile.qp

# innobackupex --decompress --parallel=8 .
…
200324 18:45:30 innobackupex: Starting the decrypt and decompress operation
200324 18:45:32 [06] decompressing ./backup-my.cnf.qp
200324 18:45:32 [02] decompressing ./xtrabackup_info.qp
200324 18:45:32 completed OK!

# innobackupex --apply-log .
200324 18:46:47 innobackupex: Starting the apply-log operation

IMPORTANT: Please check that the apply-log run completes successfully.
At the end of a successful apply-log run innobackupex
prints "completed OK!".
…
InnoDB: 5.7.13 started; log sequence number 7385621
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 7385640
200324 18:46:51 completed OK!

# innobackupex --defaults-file=/data/mysql/db_slave/conf/slave.cnf --copy-back /data/DBbackup/bk_order/20210324/base_20210324
…
200324 18:49:16 [01] Copying ./ibtmp1 to /data/mysql/db_slave/data/ibtmp1
200324 18:49:16 [01] ...done
200324 18:49:16 completed OK!

#sh startup.sh
# sh login.sh
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.25-log Source distribution
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>

# mysqldump -uroot -psystem -S 
/data/mysql/db_order/mysql.sock --single-transaction --
default-character-set=utf8 --set-gtid-purged=off --add-drop-
table --triggers --events --routines htest tt>tt.sql

#scp tt.sql mysql@xxx.xxx.xxx.xxx:/tmp
mysql>source /tmp/tt.sql;

# ./binlog_rollback -m repl -w 2sql -M mysql -t 4 -mid 3331 
-H xxx.x.0.1 -P 23301 -u root -p system -dbs htest -tbs tt -
sbin /data/mysql/db_order/blog/mysql-bin.000046 -spos 1551 -
ebin /data/mysql/db_order/blog/mysql-bin.000046 -epos 12722 
-e -f -r 20 -k -b 100 -l 10 -o /tmp/binlog -dj 
tbs_all_def.json

#cd /tmp/binlog
# ls
big_long_trx.txt binlog_stats.txt ddl_info.txt forward htest.tt.forward.46.sql tbs_all_def.json

mysql>source /tmp/binlog/htest.tt.forward.46.sql;