基于MySQL全量备份+GTID同步的主从架构恢复数据至指定时间点

系列文章目录

基于GTID同步搭建主从复制
MySQL全量备份

前言

在实际生产环境中,数据库可能随时面临着一些使用操作问题，例如代码中执行误操作删表、删数据或者是where条件不对被批量执行update更新了表字段等操作语句，都会给当前环境带来不可描述的过错。为了及时挽救这误操作的部分，就需要运维/DBA同事恢复数据至误操作前,那么这个恢复操作就是今天进行分享的知识。前提:本次恢复演示基于MySQL全量备份+GTID同步的主从架构,且MySQL版本为5.7.42版本，背景:误操作删除一张表进行恢复。

提示：以下是本篇文章正文内容，下面案例可供参考

一、环境准备

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)

`与产生的压测数据条数一致,至此通过此方法已完成对误操作删除表数据的恢复`