本文档详细介绍了如何在三台服务器上搭建MySQL主从环境，并通过pt-slave-delay工具实现实时和延迟备份。首先创建备份用户，然后配置各服务器，设置server-id，启动主从。接着，在vm_mysql1上安装percona-xtrabackup并创建延迟同步用户。通过pt-slave-delay设置延迟同步，最后展示了如何在主服务器误删除数据后在延迟备份服务器上恢复。
########################################################################
#### 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)
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
