mysql數據庫備份設置延時備份辦法(mysql主從設置裝備擺設)。本站提示廣大學習愛好者：（mysql數據庫備份設置延時備份辦法(mysql主從設置裝備擺設)）文章只能為提供參考，不一定能成為您想要的結果。以下是mysql數據庫備份設置延時備份辦法(mysql主從設置裝備擺設)正文
一 為何須要延時備份
percona-xtrabackup是一個優良的用於增量備份的對象。明天我們講到的延時備份也是應用他們的產物。
之前在MySQL AB復制一文中提到了AB復制。我們起首回想下MySQL復制的相干要點。AB復制又稱主從復制，完成的是數據同步。經由以下步調：
1)主辦事器把數據更改記載到二進制日記中，這個操作叫做二進制日記事宜；
2)從辦事器把主辦事器的二進制日記事宜拷貝到本身的中繼日記（relay log）中；
3)從辦事器履行中繼日記中的事宜，把更改運用到本身的數據上。
在臨盆中，我們在應用 mysql AB 復制技巧不只可以起到數據庫層面負載平衡的才能，還可以起到備份數據的功效，但有的時刻我們能夠因為不當心誤操作招致數據被刪除，這這個時刻 slave辦事器上的數據也會同時被刪除，假如我們可以或許能是的個中的一台 slave 延時備份的話， 如許便可以從 slave辦事器上找回被誤刪的數據了。
從辦事器到主辦事器中拷貝二進制日記文件，假如在並發量高，收集延時嚴重的情形下，會對主辦事器形成相當年夜的壓力，負載高，一定會湧現許多成績，好比拜訪延遲，IO瓶頸，收集堵塞等等。辦事器壓力過年夜是我們都不肯看到的情形，那有無計劃減緩這類情形呢？有，這就是本文講到的延時備份。延時備份經由過程第三方對象，將檢討同步和真正同步的時光掌握在必定的規模內，而不是主辦事器數據產生變更，從辦事器立刻去同步二進制事宜到本身的中繼日記中，如許的話可以年夜年夜加重主辦事器的壓力，而且基於AB復制的長處，可以到達備份數據的目標。
情況簡介
serv01：主辦事器 192.168.1.11serv01.host.com
serv08：實時同步辦事器 192.168.1.18serv01.host.com
serv09：延時同步辦事器 192.168.1.19serv08.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紛歧致；
[root@serv01 ~]# cat /etc/my.cnf | grep server-id
server-id = 1
#server-id = 2
[root@serv01 ~]# /etc/init.d/mysqld start
Starting MySQL SUCCESS!
[root@serv08 ~]# cat /etc/my.cnf | grep server-id
server-id = 2
#server-id = 2
[root@serv08 ~]# /etc/init.d/mysqld start
Starting MySQL SUCCESS!
[root@serv09 ~]# cat /etc/my.cnf | grep server-id
server-id = 3
#server-id = 2
[root@serv09 ~]# /etc/init.d/mysqld start
Starting MySQL SUCCESS!
第二步，serv01serv08 serv09清空日記
serv01
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 683 |
+------------------+-----------+
1 row in set (0.01 sec)
mysql> reset master;
Query OK, 0 rows affected (0.01 sec)
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 107 |
+------------------+-----------+
1 row in set (0.00 sec)
serv08
mysql> reset master;
Query OK, 0 rows affected (0.02 sec)
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 107 |
+------------------+-----------+
1 row in set (0.00 sec)
serv09
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 107 |
+------------------+-----------+
1 row in set (0.00 sec)
mysql> reset master;
Query OK, 0 rows affected (0.00 sec)
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 107 |
+------------------+-----------+
1 row in set (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)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| crm |
| justdb |
| larry |
| larrydb |
| mysql |
| performance_schema |
| test |
+--------------------+
8 rows in set (0.00 sec)
serv08
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| justdb |
| larrydb |
| mysql |
| performance_schema |
| test |
+--------------------+
6 rows in set (0.03 sec)
serv09
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| justdb |
| larry |
| larrydb |
| mysql |
| performance_schema |
| test |
+--------------------+
7 rows in set (0.00 sec)
第七步，拷貝percona-toolkit-2.1.7-1.noarch.rpm
[root@larrywen ule-mysql]# scp percona-toolkit-2.1.7-1.noarch.rpm 192.168.1.11:/opt
[email protected]'s password:
percona-toolkit-2.1.7-1.noarch.rpm 100% 1767KB 1.7MB/s 00:00
第八步，主辦事器中經由過程yum裝置percona-toolkit-2.1.7-1.noarch.rpm
[root@serv01 opt]# yum install percona-toolkit-2.1.7-1.noarch.rpm -y
第九步，應用pt-slave-delay對象停止延時設置。可以先檢查贊助。
[root@serv01 opt]# pt-slave-delay --help
pt-slave-delay starts and stops a slave server as needed to make it lag behind
the master. The SLAVE-HOST and MASTER-HOST use DSN syntax, and values are
copied from the SLAVE-HOST to the MASTER-HOST if omitted. For more details,
please use the --help option, or try 'perldoc /usr/bin/pt-slave-delay' for
complete documentation.
Usage: pt-slave-delay [OPTION...] SLAVE-HOST [MASTER-HOST]
Options:
--ask-pass Prompt for a password when connecting to MySQL
--charset=s -A Default character set
--config=A Read this comma-separated list of config files; if
specified, this must be the first option on the command
line
--[no]continue Continue replication normally on exit (default yes)
--daemonize Fork to the background and detach from the shell
--database=s -D The database to use for the connection
--defaults-file=s -F Only read mysql options from the given file
--delay=m How far the slave should lag its master (default 1h).
Optional suffix s=seconds, m=minutes, h=hours, d=days;
if no suffix, s is used.
--help Show help and exit
--host=s -h Connect to host
--interval=m How frequently pt-slave-delay should check whether the
slave needs to be started or stopped (default 1m).
Optional suffix s=seconds, m=minutes, h=hours, d=days;
if no suffix, s is used.
--log=s Print all output to this file when daemonized
--password=s -p Password to use when connecting
--pid=s Create the given PID file when daemonized
--port=i -P Port number to use for connection
--quiet -q Don't print informational messages about operation
--run-time=m How long pt-slave-delay should run before exiting.
Optional suffix s=seconds, m=minutes, h=hours, d=days;
if no suffix, s is used.
--set-vars=s Set these MySQL variables (default wait_timeout=10000)
--socket=s -S Socket file to use for connection
--use-master Get binlog positions from master, not slave
--user=s -u User for login if not current user
--version Show version and exit
--version-check=s Send program versions to Percona and print suggested
upgrades and problems (default off)
Option types: s=string, i=integer, f=float, h/H/a/A=comma-separated list, d=DSN, z=size, m=time
Rules:
This tool accepts additional command-line arguments. Refer to the SYNOPSIS and usage information for details.
DSN syntax is key=value[,key=value...] Allowable DSN keys:
KEY COPY MEANING
=== ==== =============================================
A yes Default character set
D yes Default database
F yes Only read default options from the given file
P yes Port number to use for connection
S yes Socket file to use for connection
h yes Connect to host
p yes Password to use when connecting
u yes User for login if not current user
If the DSN is a bareword, the word is treated as the 'h' key.
Options and values after processing arguments:
--ask-pass FALSE
--charset (No value)
--config /etc/percona-toolkit/percona-toolkit.conf,/etc/percona-toolkit/pt-slave-delay.conf,/root/.percona-toolkit.conf,/root/.pt-slave-delay.conf
--continue TRUE
--daemonize FALSE
--database (No value)
--defaults-file (No value)
--delay 3600
--help TRUE
--host (No value)
--interval 60
--log (No value)
--password (No value)
--pid (No value)
--port (No value)
--quiet FALSE
--run-time (No value)
--set-vars wait_timeout=10000
--socket (No value)
--use-master FALSE
--user (No value)
--version FALSE
--version-check off
第十步，serv09延時辦事器中創立受權用戶
mysql> grant all on *.* to 'rep'@'192.168.1.%' identified by 'larry';
Query OK, 0 rows affected (0.00 sec)
第十一步，完成功效。
[root@serv01 ~]# 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
<strong>敕令說明</strong>
--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
三分鐘事後檢查延時辦事器曾經同步勝利
[root@serv01 ~]# 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
