使用pt-slave-delay实现mysql的延迟备份

########################################################################
####                         mysql的安装初始化                        ####
########################################################################
略

########################################################################
###    在三台服务器上搭建主从环境（vm_web1为主，vm_web2和vm_mysql1为从）###    
########################################################################
1) 在主（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) 分别修改各个服务器的配置文件
vm_web1:
[mysqld]
log-bin = /data/mysql/binlog/mysql-bin
server-id       = 1 #设置server-id的值（server_id值可以是整数型的数字(1 ~ 2^31-1), 在同一个复制组(replicating group)中的每台服务器的server_id都必须是唯一的）
[root@vm_web1 ~]# service mysql restart

vm_web2:
[mysqld]
log-bin = /data/mysql/binlog/mysql-bin
server-id       = 2
[root@vm_web2 ~]# service mysql restart

vm_mysql1:    vm_mysql1采用的是mysql的多实例所以配置有点特殊
[mysqld3306]
# 占用的端口（每一个实例占用一个端口）
port=3306
server-id       = 3  
# 指定套接字文件所在的目录
socket=/tmp/mysql3306.sock
# 指定锁文件所在的位置
pid-file=/tmp/mysql3306.pid
# 指定数据库实例目录
datadir=/mariadb/data3306
log-bin=/data/mysql/3306/binlog/mysql-bin
[root@vm_mysql1 ~]# mysqld_multi --defaults-extra-file=/etc/my.cnf start 3306  # 多实例的mysql启动和其他不同，具体参考上一篇日志

3) 开启主从

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;

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) 测试
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));
会实时同步到vm_web2 和 vm_mysql1上

####################################################################
###                把vm_mysql1做成延迟同步                            ####
####################################################################
1) vm_web1上安装percona-xtrabackup
[root@vm_web1 ~]# yum -y install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm
[root@vm_web1 ~]# yum -y install percona-toolkit

2) 在延迟服务器上创建授权用户
vm_mysql1:
MariaDB [mysql]> grant all on *.* to ‘delay_user‘@‘192.168.1.%‘ identified by ‘5BbCZ4WLurwvBFbX‘;
MariaDB [mysql]> flush PRIVILEGES;

3) 主服务器上设置延迟同步
[root@vm_web1 ~]# pt-slave-delay --user=‘delay_user‘ --password=‘5BbCZ4WLurwvBFbX‘ --delay=2m --interval=20s --run-time=6m --port 3306 192.168.1.217
参数说明： --user=‘delay_user‘ # 延迟服务器中授权的用户名 delay_user
           --password=‘5BbCZ4WLurwvBFbX‘ # 延迟服务器中授权用户的密码
           --delay=2m 延时同步的时间，这里我测试用的是2分钟
           --interval=20s 检查同步的时间，这里设置为20s
           --run-time=6m pt-slave-delay的运行时间，这里设置为6分钟
           --port 3306 延时服务器mysql开启端口
           192.168.1.217 延时服务器的IP地址
           
4）主服务器上插入数据，进行测试
vm_web1:
MariaDB [master_slave_test_db]> insert into m_s_test1 values(1,‘zhangsan‘);

执行完上面这条命令我们分别在两台服务器上查看数据的变化
vm_web2:
MariaDB [master_slave_test_db]> select * from m_s_test1;
+------+----------+
| id   | name     |
+------+----------+
|    1 | zhangsan |
+------+----------+
1 row in set (0.00 sec)  # 实时同步

vm_mysql1:
MariaDB [master_slave_test_db]> select * from m_s_test1;
Empty set (0.00 sec) # 在vm_mysql1上并没有数据同步到
过一段时间再去查看
MariaDB [master_slave_test_db]> select * from m_s_test1;
+------+----------+
| id   | name     |
+------+----------+
|    1 | zhangsan |
+------+----------+
1 row in set (0.00 sec)  # 发现数据已经同步了

5）pt-slave-delay 输出日志查看与分析
[root@vm_web1 ~]# pt-slave-delay --user=‘delay_user‘ --password=‘5BbCZ4WLurwvBFbX‘ --delay=2m --interval=20s --run-time=6m --port 3306 192.168.1.217
# 连接到从服务器
2015-05-13T23:06:59 slave running 0 seconds behind  # 检查从服务器落后主服务器的时间（这里是0秒 < 2分钟）
2015-05-13T23:06:59 STOP SLAVE until 2015-05-13T23:08:59 at master position mysql-bin.000001/629  # 停止SLAVE上的执行进程，直到2015-05-13T23:08:59才开启（刚好是我们设置的延迟2分钟）
2015-05-13T23:07:19 slave stopped at master position mysql-bin.000001/629 # 20s后再次查看，发现slave执行进程是停止的，并且当前时间也没有达到2015-05-13T23:08:59。所以什么事情都不用做
2015-05-13T23:07:39 slave stopped at master position mysql-bin.000001/629 # 同上
2015-05-13T23:07:59 slave stopped at master position mysql-bin.000001/629 # 同上
2015-05-13T23:08:19 slave stopped at master position mysql-bin.000001/815 # 检测到主上有数据更新
2015-05-13T23:08:39 slave stopped at master position mysql-bin.000001/815
2015-05-13T23:08:59 no new binlog events  # 开启slave执行进程，但是从上没有进行任何数据操作
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 # 执行23:08:19检测到的主上更新的操作
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退出，同步恢复到实时。
从上面的解释可以看出来从上比主上最多延迟2分钟20s最少会延迟2分钟

6) 如果我们想让pt-slave-delay在后台运行并且当进程退出时也不要追上master 则：
[root@vm_web1 ~]# nohup  pt-slave-delay --user=‘delay_user‘ --password=‘5BbCZ4WLurwvBFbX‘  --nocontinue  --delay=2m --interval=20s  --port 3306 192.168.1.217 &
--continue:继续复制，正常退出（默认是yes）如果退出后重启线程没有until条件，他会正常运行追上master
--nocontinue：表示不追上
######################################################################################
###              主服务器上误删除数据后在延迟备份服务器上恢复                        ##
######################################################################################
vm_web1:
[root@vm_web1 ~]# pt-slave-delay --user=‘delay_user‘ --password=‘5BbCZ4WLurwvBFbX‘ --delay=20m --interval=100s --run-time=30m --port 3306 192.168.1.217  # 延迟同步启动

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; # 延迟服务器上停止从
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; #  跳过误删除点直接执行后面的操作。

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

我们再次往vm_web1上添加数据，可以看到在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)