MySQL Point in Time Recovery the Right Way
In this blog, I’ll look at how to do MySQL point in time recovery (PITR) correctly.
Sometimes we need to restore from a backup, and then replay the transactions that happened after the backup was taken. This is a common procedure in most disaster recovery plans, when for example you accidentally drop a table/database or run an update/delete without the “where” clause and lose data.
The usual way is to get a copy of your binlogs and use mysqlbinlog to replay those transactions. But this approach has many pitfalls that can make the whole PITR process a nightmare. Some examples:
- You need to make sure to run a single mysqlbinlog command with all related binlogs, and pipe them to mysql at once. Otherwise, if binlog.000001 creates a temporary table, and binlog.000002 requires that temporary table, it will not be present. Each execution of MySQL creates a new connection:
|
1 2 |
shell> mysqlbinlog binlog.000001 | mysql -u root -p # Creates tmp table X shell> mysqlbinlog binlog.000002 | mysql -u root -p # Uses tmp table X |
- We can say that it has to be an atomic operation. If it fails halfway through, it will be very difficult to know where it failed and even more difficult to resume from that point forward. There are many reasons for it to fail: InnoDB lock wait timeout / deadlock with some concurrent transaction, server and client have different
max_allowed_packet and you get a
Lost connection to MySQL server during queryerror, and so on.
So how can we overcome those limitations and have a reliable way to do Point In Time Recovery?
We can restore the backup on the desired server, build a second server with just the minimal MySQL required data and move the all binary logs to this “fake” server datadir. Then we need to configure the server where we want the PITR to happen as a slave of the fake server. From this point forward, it’s going to be pure MySQL replication happening.
To illustrate it, I create a Docker container on the machine. I have Percona Server for MySQL running on the box listening on 3306, and have already restored the backup on it. There is a tarball there with all binlogs required. The saved positions for PITR are as follows:
|
1 2 |
[root@localhost ~]# cat /var/lib/mysql/xtrabackup_binlog_info master-bin.000007 1518932 |
I create a folder to store the Docker MySQL datadir:
|
1 2 |
mkdir /tmp/pitr chown -R 1001 /tmp/pitr |
I start the Docker container. As we can see from xtrabackup_binlog_info, my binlogs are named master-bin and I’ll be setting the same server-id as original master:
|
1 2 3 4 |
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 |
In case you want to make usage of GTID, append --gtid-mode=ON --enforce_gtid_consistency=ON to the end of the Docker command.
The command above starts a MySQL instance, invokes mysqld –initialize, sets the root password to secret and it’s port 3306 is mapped back to my local 3307 port. Now I’ll stop it, remove the binlogs that it created, uncompress and move all required binlogs to its datadir mapped folder and start it again:
|
1 2 3 4 5 |
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 |
If it all worked correctly, at this point we can see the full list of binary logs on the Docker container by connecting on port 3307:
|
1 2 3 4 5 6 7 8 9 10 11 |
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 | +-------------------+-----------+ |
Now, all we need to do is connect to our server, which has the backup restored, and configure it as a slave from 3307:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 |
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) |
If you want to apply logs up to a particular time you can make use of mysqlbinlog to verify what the last position / GTID it should apply, and use START SLAVE UNTIL MASTER_LOG_FILE = 'log_name', MASTER_LOG_POS = log_pos or START SLAVE SQL_THREAD UNTIL SQL_AFTER_GTIDS = 3E11FA47-71CA-11E1-9E33-C80AA9429562:11-56.
Special thanks to Marcos Albe, who originally showed me this MySQL point in time recovery approach.
Comments (9)
Nice trick, faking a master and using the replication protocol.
One more option to restore could be to export the binlogs to a single file and then import that file in mysql ?
mysqlbinlog –base64-output=decode -v binlog.000001 >> binlog.txt
mysqlbinlog –base64-output=decode -v binlog.000002 >> binlog.txt
and then
mysql < binlog.txt
Although if there are too many files to execute then this single file might become large.
Hi Akshay.
Thanks for your comment. Yes, if the file becomes big and fail for some reason, it becomes a problem.
But will that append the existing binlog?
Hi Marcelo,
There is even a better approach then playing with a second instance of MySQL, check this post: http://lefred.be/content/howto-make-mysql-point-in-time-recovery-faster/
Cheers 😉
Nice trick as well lefred 🙂
Hi Lefred.
Thanks for pointing it out. Similar approach and can be done with a single instance.
Cheers.
You should never use mysqlbinlog this way:
shell> mysqlbinlog binlog.000001 | mysql -u root -p # Creates tmp table X
shell> mysqlbinlog binlog.000002 | mysql -u root -p # Uses tmp table X
but
shell> mysqlbinlog binlog.000001 binlog.000002 | mysql -u root -p
instead.
See the docs: https://dev.mysql.com/doc/refman/5.7/en/point-in-time-recovery.html
Hi Wolfsrudel.
That is what I explain in the second paragraph. I will quote here for clearness:
> You need to make sure to run a single mysqlbinlog command with all related binlogs, and pipe them to mysql at once.
Thanks for stopping by.
Hey Marcelo,
Awesome article, So I think its a nice trick if you don’t use xtrabackup or meb(mysql enterprise backup), because It would be more efficient using one of these tools for your backups strategies. Thanks for sharing this one with us.
Take care.