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'