系列文章目录
文章目录
前言
在实际生产环境中,数据库可能随时面临着一些使用操作问题，例如代码中执行误操作删表、删数据或者是where条件不对被批量执行update更新了表字段等操作语句，都会给当前环境带来不可描述的过错。为了及时挽救这误操作的部分，就需要运维/DBA同事恢复数据至误操作前,那么这个恢复操作就是今天进行分享的知识。前提:本次恢复演示基于MySQL全量备份+GTID同步的主从架构,且MySQL版本为5.7.42版本，背景:误操作删除一张表进行恢复。
提示：以下是本篇文章正文内容，下面案例可供参考
一、环境准备
| ip | port | 作用 |
|---|---|---|
| 192.168.56.130 | 3306 | 主 |
| 192.168.56.131 | 3306 | 从 |
| 192.168.56.132 | 3306 | 临时数据库 |
1、192.168.56.130、131两台服务器提前搭建好主从复制架构,132服务搭建临时数据库。搭建可以参考顶部文章
2、主库安装sysbench命令,用于构建测试数据
二、构建测试数据
sysbench相关操作可以参考https://blog.csdn.net/weixin_50902636/article/details/142182691本篇博客
1.安装sysbench
在主库上执行
[root@python1 my3306]# yum -y install sysbench
2.构建测试数据
主库执行
mysql> create database dbtest; #创建测试库
Query OK, 1 row affected (0.01 sec)
mysql> grant all on dbtest.* to dbtest@'%' identified by 'dbtest'; #创建测试用户
Query OK, 0 rows affected, 1 warning (0.00 sec)
准备测试数据,创建10个临时表，每个表有50万条数据
[root@python1 my3306]# sysbench --db-driver=mysql --mysql-host=127.0.0.1 --mysql-port=3306 \
--mysql-user=dbtest --mysql-password=dbtest --mysql-db=dbtest --mysql-storage-engine=innodb \
--tables=10 --table-size=500000 /usr/share/sysbench/oltp_common.lua --forced-shutdown=1 \
--threads=16 --time=600 --report-interval=1 prepare
验证构建的数据
mysql> use dbtest;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables
-> ;
+------------------+
| Tables_in_dbtest |
+------------------+
| sbtest1 |
| sbtest10 |
| sbtest2 |
| sbtest3 |
| sbtest4 |
| sbtest5 |
| sbtest6 |
| sbtest7 |
| sbtest8 |
| sbtest9 |
+------------------+
10 rows in set (0.01 sec)
mysql> select count(*) from sbtest1;
+----------+
| count(*) |
+----------+
| 500000 |
+----------+
1 row in set (0.08 sec)
mysql> select count(*) from sbtest2;
+----------+
| count(*) |
+----------+
| 500000 |
+----------+
1 row in set (0.10 sec)
mysql> select count(*) from sbtest3;
+----------+
| count(*) |
+----------+
| 500000 |
+----------+
1 row in set (0.08 sec)
3.准备全量备份
主库执行
[root@python1 xtrback]# /usr/bin/innobackupex --defaults-file=/export/servers/data/my3306/my.cnf \
--user=root --password=0gvzJr66iNs5 --backup --lock-ddl-per-table \
--socket=/export/servers/data/my3306/run/mysqld.sock --slave-info /export/backup
4.将全量备份和binlog拷贝到临时数据库服务器
主库执行
[root@python1 backup]# tar -zcf full_20240917.tar.gz 2024-09-17_11-47-13
[root@python1 backup]# scp full_20240917.tar.gz 192.168.56.132:/tmp/
full_20240917.tar.gz 100% 525MB 11.7MB/s 00:45
[root@python1 my3306]# scp -rp binlog 192.168.56.132:/tmp/
mysql-bin.000001 100% 154 5.4KB/s 00:00
mysql-bin.000002 100% 177 216.9KB/s 00:00
mysql-bin.000003 100% 910MB 10.9MB/s 01:23
mysql-bin.index 100% 156 0.1KB/s 00:01
5.模拟误删除表操作
主库执行
mysql> drop table sbtest1;
Query OK, 0 rows affected (0.32 sec)
mysql> show tables;
+------------------+
| Tables_in_dbtest |
+------------------+
| sbtest10 |
| sbtest2 |
| sbtest3 |
| sbtest4 |
| sbtest5 |
| sbtest6 |
| sbtest7 |
| sbtest8 |
| sbtest9 |
+------------------+
9 rows in set (0.00 sec)
三、恢复数据到指定时间点
1.临时数据库恢复数据
临时库操作
检查临时库是否为新库
开始恢复数据至新库
#生成回滚日志
[root@python3 tmp]# /usr/bin/innobackupex --defaults-file=/export/servers/data/my3306/my.cnf \
--user=root --apply-log /tmp/2024-09-17_11-47-13
#执行恢复操作,执行恢复操作前，先情况临时数据库data、binlog、ibdata、iblog目录
[root@python3 my3306]# innobackupex --defaults-file=/export/servers/data/my3306/my.cnf --copy-back /tmp/2024-09-17_11-47-13
修改数据目录权限
[root@python3 data]# chown mysql.myinstall /export/servers/data/my3306/* -R
重启临时库
[root@python3 my3306]# /etc/init.d/mysql3306.server start
Starting MySQL SUCCESS!
[root@python3 my3306]# ps -ef |grep mysql
root 6401 1 2 13:00 pts/0 00:00:00 /bin/sh /export/servers/app/mysql-5.7.42/bin/mysqld_safe --defaults-file=/export/servers/data/my3306/my.cnf --user=mysql
mysql 7831 6401 21 13:00 pts/0 00:00:00 /export/servers/app/mysql-5.7.42/bin/mysqld --defaults-file=/export/servers/data/my3306/my.cnf --basedir=/export/servers/app/mysql-5.7.42 --datadir=/export/servers/data/my3306/data --plugin-dir=/export/servers/app/mysql-5.7.42/lib/plugin --user=mysql --log-error=/export/servers/data/my3306/log/mysqld-err.log --open-files-limit=655340 --pid-file=/export/servers/data/my3306/run/mysqld.pid --socket=/export/servers/data/my3306/run/mysqld.sock --port=3306
root 7877 2977 0 13:00 pts/0 00:00:00 grep --color=auto mysql
检查临时库中的数据
mysql> use dbtest;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+------------------+
| Tables_in_dbtest |
+------------------+
| sbtest1 |
| sbtest10 |
| sbtest2 |
| sbtest3 |
| sbtest4 |
| sbtest5 |
| sbtest6 |
| sbtest7 |
| sbtest8 |
| sbtest9 |
+------------------+
10 rows in set (0.00 sec)
mysql> select count(*) from sbtest1;
+----------+
| count(*) |
+----------+
| 500000 |
+----------+
1 row in set (0.18 sec)
2.找到误操作的位置(GTID或binlog pos)
在主库根据时间查找执行删除操作的binlog
当时根据完全备份时间看是2024-09-17_11-47-13。那么执行删除操作应该是在2024-09-17_11-47-13往后几分钟时间内，所有查看binlog
[root@python1 my3306]# cd binlog/
[root@python1 binlog]# ll
total 932180
-rw-r----- 1 mysql myinstall 154 Sep 17 10:39 mysql-bin.000001
-rw-r----- 1 mysql myinstall 177 Sep 17 10:58 mysql-bin.000002
-rw-r----- 1 mysql myinstall 954536549 Sep 17 12:06 mysql-bin.000003
-rw-r----- 1 mysql myinstall 156 Sep 17 10:58 mysql-bin.index
利用mysqlbinlog格式化二进制binlog日志，查找drop操作前对应的gtid
#两种方式任意一种都可以
[root@python1 binlog]# mysqlbinlog -v --base64-output=auto mysql-bin.000003 >/export/backup/test.sql
[root@python1 binlog]# mysqlbinlog -vv --base64-output=decode-rows mysql-bin.000003
如上图所示,在binlog中找到了drop操作,并找到了drop操作前一个事务的GTID
3.临时库升级临时从库
mysql> reset master;
Query OK, 0 rows affected (0.01 sec)
mysql> SET GLOBAL gtid_purged='eb9c1fa5-7421-11ef-a597-00505631e679:1-1894'; #这个gtid来自于备份文件xtrbackup_info中的gtid
Query OK, 0 rows affected (0.00 sec)
mysql> change master to master_host='192.168.56.130', master_port=3306,master_user='repl' , master_password='123456' ,master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
4.指定恢复到的位置
通过:start slave sql_thread until sql_before_gtids=''eb9c1fa5-7421-11ef-a597-00505631e679:1895' 设置SQL回放位置停止在drop操作前一个事务. Executed_Gtid_Set应该停在: 'eb9c1fa5-7421-11ef-a597-00505631e679:1894
mysql> start slave until sql_before_gtids='eb9c1fa5-7421-11ef-a597-00505631e679:1895'; #这个gtid就是上方通过binlog查出来的，位于drop操作前的一个gtid操作。
Query OK, 0 rows affected (5.29 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.56.130
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 315278297
Relay_Log_File: mysqld-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File:
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 0
Relay_Log_Space: 315278718
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1303306
Master_UUID: eb9c1fa5-7421-11ef-a597-00505631e679
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: eb9c1fa5-7421-11ef-a597-00505631e679:1-628 #恢复到1-1894停止
Executed_Gtid_Set:
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.56.130
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 954536549
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 414
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 954536360
Relay_Log_Space: 811
Until_Condition: SQL_BEFORE_GTIDS
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1303306
Master_UUID: eb9c1fa5-7421-11ef-a597-00505631e679
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: eb9c1fa5-7421-11ef-a597-00505631e679:1895
Executed_Gtid_Set: eb9c1fa5-7421-11ef-a597-00505631e679:1-1894 #查看sql_thread 执行位置停在1894
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
5.验证临时库是否有数据
临时库的数据
mysql> show tables;
+------------------+
| Tables_in_dbtest |
+------------------+
| sbtest1 |
| sbtest10 |
| sbtest2 |
| sbtest3 |
| sbtest4 |
| sbtest5 |
| sbtest6 |
| sbtest7 |
| sbtest8 |
| sbtest9 |
+------------------+
10 rows in set (0.00 sec)
mysql> select count(*) from sbtest1; #临时库已存在主库被删掉的库
+----------+
| count(*) |
+----------+
| 500000 |
+----------+
1 row in set (0.32 sec)
如上所示, 当执行到了指定的GTID，SQL线程便会停止，但IO线程还会继续复制，此时就可以导出需要还原的数据
6.导出临时库数据
这个导出指的是导出临时库有的数据而主库没有的数据，也就是导出被主库执行drop命令删掉的sbtest1库的数据
[root@python3 tmp]# /export/servers/app/mysql-5.7.42/bin/mysqldump -uroot -p -S \
/export/servers/data/my3306/run/mysqld.sock --default-character-set=utf8 \
--opt --hex-blob --skip-tz-utc --add-drop-database=FALSE --add-drop-table=FALSE --single-transaction \
--set-gtid-purged=OFF --log-error=sbtest1.full.sql.`date +%Y%m%d_%H%M%S`.err \
dbtest --tables sbtest1 > sbtest1.full.sql.`date +%Y%m%d_%H%M%S` 2>sbtest1.full.sql.`date +%Y%m%d_%H%M%S`.log
Enter password:
[root@python3 tmp]#
-rw-r----- 1 root root 99893617 Sep 17 12:20 sbtest1.full.sql.20240917_122011
-rw-r----- 1 root root 0 Sep 17 12:20 sbtest1.full.sql.20240917_122011.err
-rw-r----- 1 root root 0 Sep 17 12:20 sbtest1.full.sql.20240917_122011.log
7.拷贝导出的数据至主库
[root@python3 tmp]# scp sbtest1.full.sql.20240917_122011 192.168.56.130:/tmp/
sbtest1.full.sql.20240917_122011 100% 95MB 60.2MB/s 00:01
8.主库恢复被删除的数据
主库执行
备份文件重命名并授权
[root@python1 tmp]# ll sbtest1.full.sql.20240917_122011
-rw-r----- 1 root root 99893617 Sep 17 12:21 sbtest1.full.sql.20240917_122011
[root@python1 tmp]# mv sbtest1.full.sql.20240917_122011 sbtest1.full.sql
[root@python1 tmp]# chmod +x sbtest1.full.sql
执行恢复
[root@python1 tmp]# mysql -uroot -p -S /export/servers/data/my3306/run/mysqld.sock
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 680
Server version: 5.7.42-log Source distribution
mysql> use dbtest;
Database changed
mysql> show tables; #恢复数据之前的表只有9个
+------------------+
| Tables_in_dbtest |
+------------------+
| sbtest10 |
| sbtest2 |
| sbtest3 |
| sbtest4 |
| sbtest5 |
| sbtest6 |
| sbtest7 |
| sbtest8 |
| sbtest9 |
+------------------+
9 rows in set (0.02 sec)
mysql> source /tmp/sbtest1.full.sql; #开始执行恢复。因为50w条数据,就不一一粘贴了
Query OK, 5206 rows affected (1.63 sec)
Records: 5206 Duplicates: 0 Warnings: 0
......
验证主库的数据是否恢复
mysql> show tables;
+------------------+
| Tables_in_dbtest |
+------------------+
| sbtest1 |
| sbtest10 |
| sbtest2 |
| sbtest3 |
| sbtest4 |
| sbtest5 |
| sbtest6 |
| sbtest7 |
| sbtest8 |
| sbtest9 |
+------------------+
10 rows in set (0.01 sec)
mysql> select count(*) from sbtest1;
+----------+
| count(*) |
+----------+
| 500000 |
+----------+
1 row in set (0.15 sec)
`与产生的压测数据条数一致,至此通过此方法已完成对误操作删除表数据的恢复`
扫一扫
643
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
