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

轉載于:https://blog.51cto.com/bingdonghuoyan/1651069

總結

以上是生活随笔為你收集整理的使用pt-slave-delay实现mysql的延迟备份的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。