Using Pt-slave-delay to implement delayed backup of MySQL

########################################################################
# # # # # # MySQL installation initialization # # #
########################################################################
Slightly

########################################################################
# # # on three servers to build a master-slave environment (VM_WEB1-based, VM_WEB2 and VM_MYSQL1 from) # # #
########################################################################
1) Create a backup user on the master (VM_WEB1)
MariaDB [(None)]> CREATE USER ' mysql_slave ' @ ' 192.168.1.% ' identified by ' je8mg2zhmrvq23uq ';
Query OK, 0 rows Affected (0.00 sec)

MariaDB [(None)]> GRANT REPLICATION SLAVE, REPLICATION CLIENT on *. * to ' mysql_slave ' @ ' 192.168.1.% ' identified by ' Je8mg2zhmrvq23uq ' with max_queries_per_hour 0 max_connections_per_hour 0 Max_updates_per_hour 0 max_user_connections 0;
Query OK, 0 rows Affected (0.00 sec)

MariaDB [(None)]> flush privileges;
Query OK, 0 rows Affected (0.00 sec)

2) Modify each server's configuration file separately
VM_WEB1:
[Mysqld]
Log-bin =/data/mysql/binlog/mysql-bin
Server-id = 1 #设置server-id value (the server_id value can be an integer number (1 to 2^31-1), and the replicating of each server in the same replication group (server_id Group) must be unique)
[[Email protected]_web1 ~]# service MySQL restart

VM_WEB2:
[Mysqld]
Log-bin =/data/mysql/binlog/mysql-bin
Server-id = 2
[[Email protected]_web2 ~]# service MySQL restart

VM_MYSQL1:VM_MYSQL1 uses multiple instances of MySQL, so the configuration is a bit special.
[mysqld3306]
# occupied ports (each instance consumes one port)
port=3306
Server-id = 3
# Specify the directory where the socket file resides
Socket=/tmp/mysql3306.sock
# Specify where the lock file is located
Pid-file=/tmp/mysql3306.pid
# Specify the DB instance directory
datadir=/mariadb/data3306
Log-bin=/data/mysql/3306/binlog/mysql-bin
[[Email protected]_mysql1 ~]# mysqld_multi--defaults-extra-file=/etc/my.cnf Start 3306 # Multiple instances of MySQL startup and other different, specific reference to previous log

3) Turn on master/slave

Execute on VM_WEB2:
MariaDB [(none)]> change master to master_host= ' 192.168.1.211 ', master_user= ' mysql_slave ', master_password= ' Je8mg2zhmrvq23uq ', master_port=3306,master_log_file= ' mysql-bin.000001 ', master_log_pos=312;
Query OK, 0 rows affected (0.01 sec)
MariaDB [(None)]> start slave;

Execute on VM_MYSQL1
MariaDB [(none)]> change master to master_host= ' 192.168.1.211 ', master_user= ' mysql_slave ', master_password= ' Je8mg2zhmrvq23uq ', master_port=3306,master_log_file= ' mysql-bin.000001 ', master_log_pos=312;
Query OK, 0 rows affected (0.04 sec)
MariaDB [(None)]> start slave;


4) Testing
On VM_WEB1:
MariaDB [master_slave_test_db]> CREATE DATABASE master_slave_test_db;
MariaDB [master_slave_test_db]> CREATE TABLE m_s_test1 (ID int,name varchar (20));
Synchronizes to VM_WEB2 and VM_MYSQL1 in real time

####################################################################
# # # put VM_MYSQL1 into delayed sync # #
####################################################################
1) Install Percona-xtrabackup on VM_WEB1
[Email protected]_web1 ~]# yum-y Install http://www.percona.com/downloads/percona-release/redhat/0.1-3/ percona-release-0.1-3.noarch.rpm
[Email protected]_web1 ~]# yum-y Install Percona-toolkit

2) Create an authorized user on the deferred server
VM_MYSQL1:
MariaDB [mysql]> Grant All on * * to ' delay_user ' @ ' 192.168.1.% ' identified by ' 5BBCZ4WLURWVBFBX ';
MariaDB [mysql]> flush Privileges;

3) Set up delay synchronization on the primary server
[Email protected]_web1 ~]# pt-slave-delay--user= ' delay_user '--password= ' 5bbcz4wlurwvbfbx '--delay=2m--interval= 20s--run-time=6m--port 3306 192.168.1.217
Parameter description:--user= ' delay_user ' # delay the authorized user name in the server Delay_user
--password= ' 5BBCZ4WLURWVBFBX ' # delay the password of the authorized user in the server
--DELAY=2M time delay synchronization, here I'm testing for 2 minutes
--interval=20s Check the time of synchronization, set here to 20s
--run-time=6m Pt-slave-delay run time, set here to 6 minutes
--port 3306 delay server MySQL open port
192.168.1.217 IP address of the delayed server

4) Insert data on the primary server for testing
VM_WEB1:
MariaDB [master_slave_test_db]> INSERT INTO m_s_test1 values (1, ' Zhangsan ');

Execute the above command and we'll look at the changes in the data on both servers.
VM_WEB2:
MariaDB [master_slave_test_db]> SELECT * from M_s_test1;
+------+----------+
| ID | name |
+------+----------+
| 1 | Zhangsan |
+------+----------+
1 row in Set (0.00 sec) # Real-time synchronization

VM_MYSQL1:
MariaDB [master_slave_test_db]> SELECT * from M_s_test1;
Empty Set (0.00 sec) # on VM_MYSQL1 with no data synchronized to
After a while to see
MariaDB [master_slave_test_db]> SELECT * from M_s_test1;
+------+----------+
| ID | name |
+------+----------+
| 1 | Zhangsan |
+------+----------+
1 row in Set (0.00 sec) # Discovery data is synchronized

5) Pt-slave-delay output log view and analysis
[Email protected]_web1 ~]# pt-slave-delay--user= ' delay_user '--password= ' 5bbcz4wlurwvbfbx '--delay=2m--interval= 20s--run-time=6m--port 3306 192.168.1.217
# Connect to Slave server
2015-05-13t23:06:59 slave running 0 seconds behind # Check the time from the server behind the master server (here is 0 seconds < 2 minutes)
2015-05-13t23:06:59 stop SLAVE until 2015-05-13t23:08:59 at master position mysql-bin.000001/629 # stops the execution process on the SLAVE until 2015- 05-13t23:08:59 is turned on (just the delay we set for 2 minutes)
2015-05-13t23:07:19 slave stopped at master position mysql-bin.000001/629 # 20s again to see that the slave execution process is stopped, And the current time also did not reach 2015-05-13t23:08:59. So you don't have to do anything.
2015-05-13t23:07:39 slave stopped at master position mysql-bin.000001/629 # Ibid.
2015-05-13t23:07:59 slave stopped at master position mysql-bin.000001/629 # Ibid.
2015-05-13t23:08:19 slave stopped at master position mysql-bin.000001/815 # has detected data updates from our Lord
2015-05-13t23:08:39 slave stopped at master position mysql-bin.000001/815
2015-05-13t23:08:59 no new Binlog events # opens slave execution process, but no data operations are performed from the top
2015-05-13t23:09:19 slave stopped at master position mysql-bin.000001/815
2015-05-13t23:09:39 slave stopped at master position mysql-bin.000001/815
2015-05-13t23:09:59 slave stopped at master position mysql-bin.000001/815
2015-05-13t23:10:19 START SLAVE until Master 2015-05-13t23:08:19 mysql-bin.000001/815 # Perform 23:08:19 actions detected by the Lord Update
2015-05-13t23:10:39 slave running 0 seconds behind
2015-05-13t23:10:39 STOP SLAVE until 2015-05-13t23:12:39 at master position mysql-bin.000001/815
2015-05-13t23:10:59 slave stopped at master position mysql-bin.000001/815
2015-05-13t23:11:19 slave stopped at master position mysql-bin.000001/815
2015-05-13t23:11:39 slave stopped at master position mysql-bin.000001/815
2015-05-13t23:11:59 slave stopped at master position mysql-bin.000001/815
2015-05-13t23:12:19 slave stopped at master position mysql-bin.000001/815
2015-05-13t23:12:39 no new Binlog events
2015-05-13t23:12:59 slave stopped at master position mysql-bin.000001/815
2015-05-13t23:12:59 Setting slave to run normally # Pt-slave-delay exit, sync back to real time.
It can be seen from the above explanation that the maximum delay of 2 minutes from the Lord than the Lord is 20s at least 2 minutes delay

6) If we want Pt-slave-delay to run in the background and not catch up with Master when the process exits:
[Email protected]_web1 ~]# nohup pt-slave-delay--user= ' delay_user '--password= ' 5bbcz4wlurwvbfbx '--nocontinue--dela y=2m--interval=20s--port 3306 192.168.1.217 &
--continue: Continue replication, normal exit (default is YES) if the restart thread does not have until condition after exiting, he will run after master
--nocontinue: means not catching up
######################################################################################
# # # Recovery on a delayed backup server after mistakenly deleting data on the master server # #
######################################################################################
VM_WEB1:
[Email protected]_web1 ~]# pt-slave-delay--user= ' delay_user '--password= ' 5bbcz4wlurwvbfbx '--delay=20m--interval= 100s--run-time=30m--port 3306 192.168.1.217 # delayed synchronous start

VM_WEB1:
MariaDB [master_slave_test_db]> Delete from m_s_test1 where id=1;
Query OK, 1 row Affected (0.00 sec)

MariaDB [master_slave_test_db]> Show Master status;
+------------------+----------+--------------+------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+------------------+----------+--------------+------------------+
|     mysql-bin.000001 |              1349 |                  | |
+------------------+----------+--------------+------------------+
1 row in Set (0.00 sec)

VM_MYSQL1:
MariaDB [master_slave_test_db]> stop Slave; # delay on server stopping from
Query OK, 0 rows Affected (0.00 sec)

MariaDB [master_slave_test_db]> Change Master to master_log_file= ' mysql-bin.000001 ', master_log_pos=1349; # Skip the Accidental delete point and perform the subsequent operation directly.

MariaDB [master_slave_test_db]> start slave;
Query OK, 0 rows Affected (0.00 sec)

Once again, we add data to the VM_WEB1, and we can see that the original piece of data is still on the VM_MYSQL1.
VM_WEB1:
MariaDB [master_slave_test_db]> INSERT INTO m_s_test1 values (3, ' Wangwu ');
Query OK, 1 row Affected (0.00 sec)
MariaDB [master_slave_test_db]> SELECT * from M_s_test1;
+------+--------+
| ID | name |
+------+--------+
| 3 | Wangwu |
+------+--------+
1 row in Set (0.00 sec)

VM_WEB2:

MariaDB [master_slave_test_db]> SELECT * from M_s_test1;
+------+--------+
| ID | name |
+------+--------+
| 3 | Wangwu |
+------+--------+
1 row in Set (0.00 sec)

VM_MYSQL1:
MariaDB [master_slave_test_db]> SELECT * from M_s_test1;
+------+----------+
| ID | name |
+------+----------+
| 1 | Zhangsan |
| 3 | Wangwu |
+------+----------+
2 rows in Set (0.00 sec)

Using Pt-slave-delay to implement delayed backup of MySQL