MySQL基于时间点的恢复

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'