代码改变世界

MySQL基于时间点的恢复

2022-03-05 13:35  abce  阅读(787)  评论(0编辑  收藏  举报

MySQL基于时间点的恢复通常是拷贝一份binglogs,用mysqlbinlog来重放事务。但是,这个方式有很多的缺点,使得基于时间点的恢复很难。比如:

·需要确保对所有的binlogs执行单个mysqlbinlog命令,并通过管道传递给mysql客户端。要不然,binlog.000001创建一个临时表,binlog.000002需要该临时表的时候去看不到该临时表。每次执行mysql都会创建一个新的连接

shell> mysqlbinlog binlog.000001 | mysql -u root -p # Creates tmp table X
shell> mysqlbinlog binlog.000002 | mysql -u root -p # Uses tmp table X

正确的做法是:
shell> mysqlbinlog binlog.000001 binlog.000002 | mysql -u root -p
或者
mysqlbinlog –base64-output=decode -v binlog.000001 >> binlog.txt
mysqlbinlog –base64-output=decode -v binlog.000002 >> binlog.txt
mysql < binlog.txt

·必须要是原子操作。如果中途断开,想知道是在哪个位置断开就很难了,继续执行基于时间点的恢复更难。失败的原因有很多种:innodb锁超时、死锁、mysqlserver和mysqlclient的max_allowed_packet设置不一样等等

 

那我们如何克服这些限制,实现一个可靠的基于时间点的恢复呢?

我们可以在需要恢复的server上还原一个备份;用所需的最少的数据再构建第二个server,并将所有的binlogs拷贝到这个“假的”server的datadir中。将第一个server作为第二个server的slave,然后进行纯碎的主从复制。

 

为了描述这个过程,我用了一个docker容器。本地实例运行在3306的端口上,并将数据还原到本地运行的mysql下。(本地实例相当于上面说的第一个实例,docker容器实例相当于上面说的第二个实例)

基于时间恢复的日志位置是:

[root@localhost ~]# cat /var/lib/mysql/xtrabackup_binlog_info
master-bin.000007 1518932

创建目录用于存放docker容器中的mysql的数据:

mkdir /tmp/pitr
chown -R 1001 /tmp/pitr

启动容器,从上面的xtrabackup_binlog_info可以看到binlog的名字是master-bin

docker run --name ps_pitr -v /tmp/pitr:/var/lib/mysql 
-p 3307:3306 -e MYSQL_ROOT_PASSWORD=secret
-d percona/percona-server:5.7.18
--log_bin=master-bin --server-id=10

这里将server-id设置成之前一样,为了以防需要使用GTID,这里将设置了--gtid_mode=on,--enforce_gtid_consistency=on。

上面的命令启动了一个mysql 实例,调用了mysqld -initialize,设置了root的密码和端口。端口映射到本地的3307。现在,我要关闭docker容器中的实例,移除已经生成的binlog,并将用来做基于时间点恢复的日志全部拷贝到docker容器对应的数据目录下:

docker stop ps_pitr
rm /tmp/pitr/master-bin.*
tar -zxf binlogs.tgz -C /tmp/pitr
chown -R 1001 /tmp/pitr/master-bin.*
docker start ps_pitr

如果一切都正确,此时我们可以看到,docker容器中的mysql实例下,有了所有的binary日志清单:

mysql -u root -psecret -P 3307 --protocol=TCP -e "SHOW BINARY LOGS"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------------+-----------+
| Log_name         | File_size |
+-------------------+-----------+
| master-bin.000005 | 26216208 |
| master-bin.000006 | 26214614 |
| master-bin.000007 | 26214902 |
. . .
| master-bin.000074 |       154 |
+-------------------+-----------+

现在,我们连接到本地实例(数据库已经做了restore),并将其配置成3307的从库:

mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 6
Server version: 5.7.18-16 Percona Server (GPL), Release 16, Revision d7301f8

Copyright (c) 2009-2017 Percona LLC and/or its affiliates
Copyright (c) 2000, 2017, 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> CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_PORT=3307, MASTER_USER='root', MASTER_PASSWORD='secret', MASTER_LOG_FILE='master-bin.000007', MASTER_LOG_POS=1518932;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> START SLAVE;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW SLAVE STATUSG
*************************** 1. row ***************************
              Slave_IO_State: Waiting for master to send event
                Master_Host: 127.0.0.1
                Master_User: root
                Master_Port: 3307
              Connect_Retry: 60
            Master_Log_File: master-bin.000008
        Read_Master_Log_Pos: 449696
              Relay_Log_File: localhost-relay-bin.000002
              Relay_Log_Pos: 28957
      Relay_Master_Log_File: master-bin.000007
            Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            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: 15217950
            Relay_Log_Space: 11476311
            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: 4382
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: 10
                Master_UUID: 80b9fe26-a945-11e7-aa1d-0242ac110002
            Master_Info_File: /var/lib/mysql/master.info
                  SQL_Delay: 0
        SQL_Remaining_Delay: NULL
    Slave_SQL_Running_State: Opening tables
          Master_Retry_Count: 86400
                Master_Bind:
    Last_IO_Error_Timestamp:
    Last_SQL_Error_Timestamp:
              Master_SSL_Crl:
          Master_SSL_Crlpath:
          Retrieved_Gtid_Set:
          Executed_Gtid_Set:
              Auto_Position: 0
        Replicate_Rewrite_DB:
                Channel_Name:
          Master_TLS_Version:
1 row in set (0.17 sec)

. . .

mysql> SHOW SLAVE STATUSG
*************************** 1. row ***************************
              Slave_IO_State: Waiting for master to send event
                Master_Host: 127.0.0.1
                Master_User: root
                Master_Port: 3307
              Connect_Retry: 60
            Master_Log_File: master-bin.000074
        Read_Master_Log_Pos: 154
              Relay_Log_File: localhost-relay-bin.000133
              Relay_Log_Pos: 381
      Relay_Master_Log_File: master-bin.000074
            Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            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: 154
            Relay_Log_Space: 819
            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: 0
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: 10
                Master_UUID: 80b9fe26-a945-11e7-aa1d-0242ac110002
            Master_Info_File: /var/lib/mysql/master.info
                  SQL_Delay: 0
        SQL_Remaining_Delay: NULL
    Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
          Master_Retry_Count: 86400
                Master_Bind:
    Last_IO_Error_Timestamp:
    Last_SQL_Error_Timestamp:
              Master_SSL_Crl:
          Master_SSL_Crlpath:
          Retrieved_Gtid_Set:
          Executed_Gtid_Set:
              Auto_Position: 0
        Replicate_Rewrite_DB:
                Channel_Name:
          Master_TLS_Version:
1 row in set (0.01 sec)

如果只是想将日志应用到某个具体的时间点,可以用mysqlbinlog来确认位置(或gtid),使用START SLAVE UNTIL MASTER_LOG_FILE = 'log_name', MASTER_LOG_POS = log_pos或START SLAVE SQL_THREAD UNTIL SQL_AFTER_GTIDS = '3E11FA47-71CA-11E1-9E33-C80AA9429562:11-56'