程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MYSQL入門知識 >> MySQL備份與恢復之MySQL 延時備份教程

MySQL備份與恢復之MySQL 延時備份教程

編輯：MYSQL入門知識

一為什麼需要延時備份

在上一篇文章中，我們講到MySQL備份與恢復之percona-xtrabackup實現增量備份及恢復，percona-xtrabackup是一個優秀的用於增量備份的工具。今天我們講到的延時備份也是使用他們的產品。

以前在MySQL AB復制一文中提到了AB復制。我們首先回顧下MySQL復制的相關要點。AB復制又稱主從復制，實現的是數據同步。經過以下步驟：

1)主服務器把數據更改記錄到二進制日志中，這個操作叫做二進制日志事件；
2)從服務器把主服務器的二進制日志事件拷貝到自己的中繼日志（relay log）中；
3)從服務器執行中繼日志中的事件，把更改應用到自己的數據上。

在生產中，我們在使用 mysql AB 復制技術不但可以起到數據庫層面負載均衡的能力，還可以起到備份數據的功能，但有的時候我們可能由於不小心誤操作導致數據被刪除，這這個時候 slave服務器上的數據也會同時被刪除，如果我們能夠能是的其中的一台 slave 延時備份的話，這樣就可以從 slave服務器上找回被誤刪的數據了。

從服務器到主服務器中拷貝二進制日志文件，如果在並發量高，網絡延時嚴重的情況下，會對主服務器造成相當大的壓力，負載高，必定會出現很多問題，比如訪問延遲，IO瓶頸，網絡擁塞等等。服務器壓力過大是我們都不願看到的情況，那有沒有方案緩解這種情況呢？有，這就是本文講到的延時備份。延時備份通過第三方工具，將檢查同步和真正同步的時間控制在一定的范圍內，而不是主服務器數據發生變化，從服務器立即去同步二進制事件到自己的中繼日志中，這樣的話可以大大減輕主服務器的壓力，並且基於AB復制的優點，可以達到備份數據的目的。

好了，看下延時備份的示意圖。

二延時備份示意圖

MySQL備份與恢復之MySQL
MySQL備份與恢復之MySQL 延時備份示意圖

三延時備份模擬

網絡拓撲圖

MySQL備份與恢復之MySQL
MySQL備份與恢復之MySQL 延時備份網絡拓撲圖

實驗環境簡介。

主機

IP地址

主機名

備注

serv01 192.168.1.11 serv01.host.com 主服務器
serv08 192.168.1.18 serv08.host.com 及時同步服務器
serv09 192.168.1.19 serv09.host.com 延時同步服務器

操作系統版本
RHEL Server6.1 64位系統

使用到的軟件包版本
•mysql-5.5.29-linux2.6-x86_64.tar.gz
•percona-toolkit-2.1.7-1.noarch.rpm

第一步，搭建環境。修改配置文件，注意每台的server-id不一致。

# serv01
cat /etc/my.cnf | grep server-id
server-id = 1
#server-id = 2
/etc/init.d/mysqld start
Starting MySQL SUCCESS!

# serv08
cat /etc/my.cnf | grep server-id
server-id = 2
#server-id = 2
/etc/init.d/mysqld start
Starting MySQL SUCCESS!

# serv09
cat /etc/my.cnf | grep server-id
server-id = 3
#server-id = 2
/etc/init.d/mysqld start
Starting MySQL SUCCESS!

第二步，serv01、serv08、serv09清空日志。

mysql> reset master;
Query OK, 0 rows affected (0.01 sec)

--serv08
mysql> reset master;
Query OK, 0 rows affected (0.02 sec)

mysql> reset master;
Query OK, 0 rows affected (0.00 sec)

第三步，主服務器serv01創建授權用戶。

mysql> grant replication client, \
replication slave on *.* to 'larry'@'192.168.1.%' \
identified by 'larry';

第四步，serv08修改master設置，開啟slave，查看slave狀態。

mysql> change master to
-> master_host='192.168.1.11',
-> master_user='larry',
-> master_password='larry',
-> master_port=3306,
-> master_log_file='mysql-bin.000001',
-> master_log_pos=107;
Query OK, 0 rows affected (0.03 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.11
Master_User: larry
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 107
Relay_Log_File: serv08-relay-bin.000002
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000001
**Slave_IO_Running: Yes**
**Slave_SQL_Running: Yes**
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 107
Relay_Log_Space: 410
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
1 row in set (0.00 sec)

ERROR:
No query specified

第五步，serv09延時服務器修改master狀態，開啟slave，查看slave狀態。

mysql> change master to \
master_host='192.168.1.11', \
master_user='larry', \
master_password='larry', \
master_port=3306, \
master_log_file='mysql-bin.000001', \
master_log_pos=107;
Query OK, 0 rows affected (0.02 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.11
Master_User: larry
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 107
Relay_Log_File: serv09-relay-bin.000002
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000001
**Slave_IO_Running: Yes**
**Slave_SQL_Running: Yes**
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 107
Relay_Log_Space: 410
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
1 row in set (0.00 sec)

ERROR:
No query specified

第六步，在沒有使用延時服務器時，serv01創建測試數據庫，可以看到同步服務器serv08和延時服務器serv09已經同步了。

--serv01
mysql> create database justdb;
Query OK, 1 row affected (0.01 sec)

第七步，拷貝percona-toolkit-2.1.7-1.noarch.rpm。

scp percona-toolkit-2.1.7-1.noarch.rpm 192.168.1.11:/opt

第八步，主服務器中通過yum安裝percona-toolkit-2.1.7-1.noarch.rpm。

yum install percona-toolkit-2.1.7-1.noarch.rpm -y

第九步，使用pt-slave-delay工具進行延時設置。可以先查看幫助。

pt-slave-delay --help

第十步，serv09延時服務器中創建授權用戶

mysql> grant all on *.* to 'rep'@'192.168.1.%' identified by 'larry';
Query OK, 0 rows affected (0.00 sec)

第十一步，實現功能。

pt-slave-delay --user='rep' \
--password='larry' \
--delay=3m \
--interval=20s \
--run-time=30m \
192.168.1.19
2013-10-06T19:43:30 slave running 0 seconds behind
2013-10-06T19:43:30 STOP SLAVE until 2013-10-06T19:46:30
at master position mysql-bin.000001/199

命令解釋

–user=’rep’：延時服務器中授權用戶的用戶名，這裡設置為rep
–password=’larry’：延時服務器中授權用戶的密碼，這裡設置為larry
–delay=3m：延時同步的時間，這裡設置為3分鐘
–interval=20s：檢查同步的時間，這裡設置為20s
–run-time=30m：pt-slave-delay的運行時間，這裡設置為30分鐘
192.168.1.19：延時服務器的IP地址

第十二步，測試，主服務器serv01創建測試數據庫，可以發現同步服務器立馬更新，而延時同步服務器要等3分鐘之後才更新。

--serv01
mysql> use justdb;
Database changed
mysql> create table test(id int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test values(1);
Query OK, 1 row affected (0.00 sec)

--serv08
mysql> select * from justdb.test;
+------+
| id |
+------+
| 1 |
+------+
1 row in set (0.00 sec)

--serv09
mysql> select * from justdb.test;
ERROR 1146 (42S02): Table 'justdb.test' doesn't exist

三分鐘過後查看延時服務器已經同步成功。

pt-slave-delay --user='rep' \
--password='larry' \
--delay=3m \
--interval=20s \
--run-time=30m \
192.168.1.19

2013-10-06T19:43:30 slave running 0 seconds behind
2013-10-06T19:43:30 STOP SLAVE until 2013-10-06T19:46:30
at master position mysql-bin.000001/199
2013-10-06T19:43:50 slave stopped at master position mysql-bin.000001/199
2013-10-06T19:44:10 slave stopped at master position mysql-bin.000001/199
2013-10-06T19:44:30 slave stopped at master position mysql-bin.000001/492
2013-10-06T19:44:50 slave stopped at master position mysql-bin.000001/492
2013-10-06T19:45:10 slave stopped at master position mysql-bin.000001/492
2013-10-06T19:45:30 slave stopped at master position mysql-bin.000001/492
2013-10-06T19:45:50 slave stopped at master position mysql-bin.000001/492
2013-10-06T19:46:10 slave stopped at master position mysql-bin.000001/492
2013-10-06T19:46:30 no new binlog events
2013-10-06T19:46:50 slave stopped at master position mysql-bin.000001/492
2013-10-06T19:47:10 slave stopped at master position mysql-bin.000001/492
2013-10-06T19:47:30 START SLAVE until master 2013-10-06T19:44:30 mysql-bin.000001/492
**2013-10-06T19:47:50 slave running 0 seconds behind**
**2013-10-06T19:47:50 STOP SLAVE until 2013-10-06T19:50:50
at master position mysql-bin.000001/492**
2013-10-06T19:48:10 slave stopped at master position mysql-bin.000001/492
2013-10-06T19:48:30 slave stopped at master position mysql-bin.000001/492
2013-10-06T19:48:50 slave stopped at master position mysql-bin.000001/492
2013-10-06T19:49:10 slave stopped at master position mysql-bin.000001/492
2013-10-06T19:49:30 slave stopped at master position mysql-bin.000001/492
2013-10-06T19:49:50 slave stopped at master position mysql-bin.000001/492
2013-10-06T19:50:10 slave stopped at master position mysql-bin.000001/492
2013-10-06T19:50:30 slave stopped at master position mysql-bin.000001/492
2013-10-06T19:50:50 no new binlog events
2013-10-06T19:51:10 slave stopped at master position mysql-bin.000001/492
2013-10-06T19:51:30 slave stopped at master position mysql-bin.000001/492
2013-10-06T19:51:50 slave stopped at master position mysql-bin.000001/492
2013-10-06T19:52:10 slave stopped at master position mysql-bin.000001/492
2013-10-06T19:52:30 slave stopped at master position mysql-bin.000001/492
2013-10-06T19:52:50 slave stopped at master position mysql-bin.000001/492
2013-10-06T19:53:10 slave stopped at master position mysql-bin.000001/492
2013-10-06T19:53:30 slave stopped at master position mysql-bin.000001/492
2013-10-06T19:53:50 slave stopped at master position mysql-bin.000001/492
2013-10-06T19:54:10 no new binlog events
2013-10-06T19:54:30 slave stopped at master position mysql-bin.000001/492
2013-10-06T19:54:50 slave stopped at master position mysql-bin.000001/492
2013-10-06T19:55:10 slave stopped at master position mysql-bin.000001/492
2013-10-06T19:55:30 slave stopped at master position mysql-bin.000001/492
2013-10-06T19:55:50 slave stopped at master position mysql-bin.000001/492
2013-10-06T19:56:10 slave stopped at master position mysql-bin.000001/492
2013-10-06T19:56:30 slave stopped at master position mysql-bin.000001/492
2013-10-06T19:56:50 slave stopped at master position mysql-bin.000001/492
2013-10-06T19:57:10 slave stopped at master position mysql-bin.000001/492
2013-10-06T19:57:30 no new binlog events
2013-10-06T19:57:50 slave stopped at master position mysql-bin.000001/492
2013-10-06T19:58:10 slave stopped at master position mysql-bin.000001/492
2013-10-06T19:58:30 slave stopped at master position mysql-bin.000001/492
2013-10-06T19:58:50 slave stopped at master position mysql-bin.000001/492
2013-10-06T19:59:10 slave stopped at master position mysql-bin.000001/492
2013-10-06T19:59:30 slave stopped at master position mysql-bin.000001/492
2013-10-06T19:59:50 slave stopped at master position mysql-bin.000001/492
2013-10-06T20:00:10 slave stopped at master position mysql-bin.000001/492
2013-10-06T20:00:30 slave stopped at master position mysql-bin.000001/492
2013-10-06T20:00:50 no new binlog events
2013-10-06T20:01:10 slave stopped at master position mysql-bin.000001/492
2013-10-06T20:01:30 slave stopped at master position mysql-bin.000001/492
2013-10-06T20:01:50 slave stopped at master position mysql-bin.000001/492
2013-10-06T20:02:10 slave stopped at master position mysql-bin.000001/492
2013-10-06T20:02:30 slave stopped at master position mysql-bin.000001/492
2013-10-06T20:02:50 slave stopped at master position mysql-bin.000001/492
2013-10-06T20:03:10 slave stopped at master position mysql-bin.000001/492
2013-10-06T20:03:30 slave stopped at master position mysql-bin.000001/492
2013-10-06T20:03:50 slave stopped at master position mysql-bin.000001/492
2013-10-06T20:04:10 no new binlog events
2013-10-06T20:04:30 slave stopped at master position mysql-bin.000001/492
2013-10-06T20:04:50 slave stopped at master position mysql-bin.000001/492
2013-10-06T20:05:10 slave stopped at master position mysql-bin.000001/492
2013-10-06T20:05:30 slave stopped at master position mysql-bin.000001/492
2013-10-06T20:05:50 slave stopped at master position mysql-bin.000001/492
2013-10-06T20:06:10 slave stopped at master position mysql-bin.000001/492
2013-10-06T20:06:30 slave stopped at master position mysql-bin.000001/492
2013-10-06T20:06:50 slave stopped at master position mysql-bin.000001/492
2013-10-06T20:07:10 slave stopped at master position mysql-bin.000001/492
2013-10-06T20:07:30 no new binlog events
2013-10-06T20:07:50 slave stopped at master position mysql-bin.000001/492
2013-10-06T20:08:10 slave stopped at master position mysql-bin.000001/492
2013-10-06T20:08:30 slave stopped at master position mysql-bin.000001/492
2013-10-06T20:08:50 slave stopped at master position mysql-bin.000001/492
2013-10-06T20:09:10 slave stopped at master position mysql-bin.000001/492
2013-10-06T20:09:30 slave stopped at master position mysql-bin.000001/492
2013-10-06T20:09:50 slave stopped at master position mysql-bin.000001/492
2013-10-06T20:10:10 slave stopped at master position mysql-bin.000001/492
2013-10-06T20:10:30 slave stopped at master position mysql-bin.000001/492
2013-10-06T20:10:50 no new binlog events
2013-10-06T20:11:10 slave stopped at master position mysql-bin.000001/492
2013-10-06T20:11:30 slave stopped at master position mysql-bin.000001/492
2013-10-06T20:11:50 slave stopped at master position mysql-bin.000001/492
2013-10-06T20:12:10 slave stopped at master position mysql-bin.000001/492
2013-10-06T20:12:30 slave stopped at master position mysql-bin.000001/492
2013-10-06T20:12:50 slave stopped at master position mysql-bin.000001/492
2013-10-06T20:13:10 slave stopped at master position mysql-bin.000001/492
2013-10-06T20:13:30 slave stopped at master position mysql-bin.000001/492
**2013-10-06T20:13:30 Setting slave to run normally**

延時服務器登錄查看。

mysql> select * from justdb.test;
+------+
| id |
+------+
| 1 |
+------+
1 row in set (0.00 sec)

四附延時備份腳本

#!/bin/bash
#
# chkconfig: - 88 12
# description: the mysql ab delay scripts

host=192.168.100.54
user=rep
password=larry

delay=2m
in=15s

prog=/usr/bin/pt-slave-delay

. /etc/init.d/functions

start() {
echo -n "Starting `basename $prog`..."
daemon $prog --host=$host --user=$user \
--password=$password \
--delay=$delay \
--interval=$in \
--daemonize \
--log=/var/log/mysql-delay.log
echo
}

stop() {
echo -n "Stopping `basename $prog`..."
killproc $prog
echo
}

case "$1" in
start)
start
;;
stop)
stop
;;
restart)
stop
start
;;
*)
echo "Usage: $0 {start|stop|restart}"
exit 1
esac

五資源下載

percona-toolkit-2.1.7-1.noarch.rpm：percona-toolkit-2.1.7-1.noarch.rpm

–EOF–