资讯专栏INFORMATION COLUMN

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

IT那活儿 / 2905人阅读
MySQL维护之--如何挽救误操作
点击上方“IT那活儿”公众号,关注后了解更多内容,不管IT什么活儿,干就完了!!!

  
在我们日常的数据库维护工作中,可能会遇到这种情况:虽然专业维护人员极力去避免意外情况对数据库系统稳定性、安全性的影响,但是很多情况下依然会超出我们的控制。

比如:业务人员误操作操作部分表记录的误差;甚至可能是对表的误删除操作。

一旦发生了这些情况,作为数据库维护人员,我们的职责是第一时间恢复数据,维持数据库的可用性。下面就说明一下如何处理这些突发状况。


表中部分记录insert/update/delete回滚

如果对表执行DML操作后,发现有误,需要回滚,最快速的方法就是使用工具binlog-rollback生成回滚语句,完成回滚操作。
1. 查看binlog信息,并对表进行异动操作
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 |
+--------------+----------------------------+---------------------+
2. 查看binlog pos开始620及结束点1409
mysql> show binlog events in mysql-bin.000031;
3. 解析binlog,开始及结束时间点
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

4. 使用工具binlog-rollbak产生回滚语句

#./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
5. 登录数据库执行回滚语句,回滚完成
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 |
+--------------+----------------------------+-------------+


drop/truncate命令误删除表

第二种则是非常糟糕的一种状况:误执行了删除表命令
这种操作会同步应用于备库,即主备库会同时缺失数据。
针对此种情况,我们可以通过分三个步骤恢复:
步骤一使用数据库当日物理备份进行异地恢复
1)复制备份文件至目标端
# 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

2)解压缩备份文件

# 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!
3)应用数据
# 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!
4)数据库恢复
# 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!
5)启动数据库,至此数据库完整恢复完成
#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>
步骤二:通过逻辑备份恢复单表至生产环境。
1)逻辑备份单表
# 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

2)传输备份文件到目标主机,恢复表

#scp tt.sql mysql@xxx.xxx.xxx.xxx:/tmp
mysql>source /tmp/tt.sql;
步骤三:针对备份时间点后差异的数据,使用工具binlog-rollback生成前滚语句,同步差异数据。
1)使用工具生成前滚语句,注意先确认binlog,pos点信息
# ./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
2)建议先在dba_bak库(测试库)恢复表,验证数据的正确性
mysql>source /tmp/binlog/htest.tt.forward.46.sql;
完成差异恢复。


本文作者:沈亚威(上海新炬王翦团队)

本文来源:“IT那活儿”公众号

文章版权归作者所有,未经允许请勿转载,若此文章存在违规行为,您可以联系管理员删除。

转载请注明本文地址:https://www.ucloud.cn/yun/129371.html

相关文章

  • 4.1 开发环境目录结构配置文件功能梳理-博客后端Api-NodeJs+Express+Mys

    摘要:从本章开始,正式学习如何使用搭建一个博客。但通常我们都会有许多环境,如本地开发环境测试环境和线上环境等,不同的环境的配置不同,我们不可能每次部署时都要去修改引用或者。会根据环境变量的不同从当前执行进程目录下的目录加载不同的配置文件。 从本章开始,正式学习如何使用 Nodejs + Express + Mysql 搭建一个博客。 开发环境 首先说下开发环境安装的核心依赖版本: Node....

    DevWiki 评论0 收藏0

发表评论

0条评论

IT那活儿

|高级讲师

TA的文章

阅读更多
最新活动
阅读需要支付1元查看
<