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

Source: Internet
Author: User
Tags percona

########################################################################
# # # # # # 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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.