bitscn.com
一 为什么需要延时备份在上一篇文章中，我们讲到mysql备份与恢复之percona-xtrabackup实现增量备份及恢复，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-idserver-id = 1#server-id = 2[root@serv01 ~]# /etc/init.d/mysqld startstarting mysql success! [root@serv08 ~]# cat /etc/my.cnf | grep server-idserver-id = 2#server-id = 2[root@serv08 ~]# /etc/init.d/mysqld startstarting mysql success! [root@serv09 ~]# cat /etc/my.cnf | grep server-idserver-id = 3#server-id = 2[root@serv09 ~]# /etc/init.d/mysqld startstarting mysql success!
第二步，serv01serv08 serv09清空日志
serv01mysql> 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)serv08mysql> 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)serv09mysql> 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: 0master_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: 11 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: 0master_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: 11 row in set (0.00 sec)error: no query specified
第六步，在没有使用延时服务器时，serv01创建测试数据库，可以看到同步服务器serv08和延时服务器serv09已经同步了
serv01mysql> 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)serv08mysql> show databases;+--------------------+| database |+--------------------+| information_schema || justdb || larrydb || mysql || performance_schema || test |+--------------------+6 rows in set (0.03 sec)serv09mysql> 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:/optroot@192.168.1.11'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 --helppt-slave-delay starts and stops a slave server as needed to make it lag behindthe master. the slave-host and master-host use dsn syntax, and values arecopied 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' forcomplete 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=timerules: 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.192013-10-06t19:43:30 slave running 0 seconds behind2013-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分钟之后才更新
serv01mysql> use justdb;database changedmysql> 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)serv09mysql> 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.192013-10-06t19:43:30 slave running 0 seconds behind2013-10-06t19:43:30 stop slave until 2013-10-06t19:46:30 at master position mysql-bin.000001/1992013-10-06t19:43:50 slave stopped at master position mysql-bin.000001/1992013-10-06t19:44:10 slave stopped at master position mysql-bin.000001/1992013-10-06t19:44:30 slave stopped at master position mysql-bin.000001/4922013-10-06t19:44:50 slave stopped at master position mysql-bin.000001/4922013-10-06t19:45:10 slave stopped at master position mysql-bin.000001/4922013-10-06t19:45:30 slave stopped at master position mysql-bin.000001/4922013-10-06t19:45:50 slave stopped at master position mysql-bin.000001/4922013-10-06t19:46:10 slave stopped at master position mysql-bin.000001/4922013-10-06t19:46:30 no new binlog events2013-10-06t19:46:50 slave stopped at master position mysql-bin.000001/4922013-10-06t19:47:10 slave stopped at master position mysql-bin.000001/4922013-10-06t19:47:30 start slave until master 2013-10-06t19:44:30 mysql-bin.000001/4922013-10-06t19:47:50 slave running 0 seconds behind2013-10-06t19:47:50 stop slave until 2013-10-06t19:50:50 at master position mysql-bin.000001/4922013-10-06t19:48:10 slave stopped at master position mysql-bin.000001/4922013-10-06t19:48:30 slave stopped at master position mysql-bin.000001/4922013-10-06t19:48:50 slave stopped at master position mysql-bin.000001/4922013-10-06t19:49:10 slave stopped at master position mysql-bin.000001/4922013-10-06t19:49:30 slave stopped at master position mysql-bin.000001/4922013-10-06t19:49:50 slave stopped at master position mysql-bin.000001/4922013-10-06t19:50:10 slave stopped at master position mysql-bin.000001/4922013-10-06t19:50:30 slave stopped at master position mysql-bin.000001/4922013-10-06t19:50:50 no new binlog events2013-10-06t19:51:10 slave stopped at master position mysql-bin.000001/4922013-10-06t19:51:30 slave stopped at master position mysql-bin.000001/4922013-10-06t19:51:50 slave stopped at master position mysql-bin.000001/4922013-10-06t19:52:10 slave stopped at master position mysql-bin.000001/4922013-10-06t19:52:30 slave stopped at master position mysql-bin.000001/4922013-10-06t19:52:50 slave stopped at master position mysql-bin.000001/4922013-10-06t19:53:10 slave stopped at master position mysql-bin.000001/4922013-10-06t19:53:30 slave stopped at master position mysql-bin.000001/4922013-10-06t19:53:50 slave stopped at master position mysql-bin.000001/4922013-10-06t19:54:10 no new binlog events2013-10-06t19:54:30 slave stopped at master position mysql-bin.000001/4922013-10-06t19:54:50 slave stopped at master position mysql-bin.000001/4922013-10-06t19:55:10 slave stopped at master position mysql-bin.000001/4922013-10-06t19:55:30 slave stopped at master position mysql-bin.000001/4922013-10-06t19:55:50 slave stopped at master position mysql-bin.000001/4922013-10-06t19:56:10 slave stopped at master position mysql-bin.000001/4922013-10-06t19:56:30 slave stopped at master position mysql-bin.000001/4922013-10-06t19:56:50 slave stopped at master position mysql-bin.000001/4922013-10-06t19:57:10 slave stopped at master position mysql-bin.000001/4922013-10-06t19:57:30 no new binlog events2013-10-06t19:57:50 slave stopped at master position mysql-bin.000001/4922013-10-06t19:58:10 slave stopped at master position mysql-bin.000001/4922013-10-06t19:58:30 slave stopped at master position mysql-bin.000001/4922013-10-06t19:58:50 slave stopped at master position mysql-bin.000001/4922013-10-06t19:59:10 slave stopped at master position mysql-bin.000001/4922013-10-06t19:59:30 slave stopped at master position mysql-bin.000001/4922013-10-06t19:59:50 slave stopped at master position mysql-bin.000001/4922013-10-06t20:00:10 slave stopped at master position mysql-bin.000001/4922013-10-06t20:00:30 slave stopped at master position mysql-bin.000001/4922013-10-06t20:00:50 no new binlog events2013-10-06t20:01:10 slave stopped at master position mysql-bin.000001/4922013-10-06t20:01:30 slave stopped at master position mysql-bin.000001/4922013-10-06t20:01:50 slave stopped at master position mysql-bin.000001/4922013-10-06t20:02:10 slave stopped at master position mysql-bin.000001/4922013-10-06t20:02:30 slave stopped at master position mysql-bin.000001/4922013-10-06t20:02:50 slave stopped at master position mysql-bin.000001/4922013-10-06t20:03:10 slave stopped at master position mysql-bin.000001/4922013-10-06t20:03:30 slave stopped at master position mysql-bin.000001/4922013-10-06t20:03:50 slave stopped at master position mysql-bin.000001/4922013-10-06t20:04:10 no new binlog events2013-10-06t20:04:30 slave stopped at master position mysql-bin.000001/4922013-10-06t20:04:50 slave stopped at master position mysql-bin.000001/4922013-10-06t20:05:10 slave stopped at master position mysql-bin.000001/4922013-10-06t20:05:30 slave stopped at master position mysql-bin.000001/4922013-10-06t20:05:50 slave stopped at master position mysql-bin.000001/4922013-10-06t20:06:10 slave stopped at master position mysql-bin.000001/4922013-10-06t20:06:30 slave stopped at master position mysql-bin.000001/4922013-10-06t20:06:50 slave stopped at master position mysql-bin.000001/4922013-10-06t20:07:10 slave stopped at master position mysql-bin.000001/4922013-10-06t20:07:30 no new binlog events2013-10-06t20:07:50 slave stopped at master position mysql-bin.000001/4922013-10-06t20:08:10 slave stopped at master position mysql-bin.000001/4922013-10-06t20:08:30 slave stopped at master position mysql-bin.000001/4922013-10-06t20:08:50 slave stopped at master position mysql-bin.000001/4922013-10-06t20:09:10 slave stopped at master position mysql-bin.000001/4922013-10-06t20:09:30 slave stopped at master position mysql-bin.000001/4922013-10-06t20:09:50 slave stopped at master position mysql-bin.000001/4922013-10-06t20:10:10 slave stopped at master position mysql-bin.000001/4922013-10-06t20:10:30 slave stopped at master position mysql-bin.000001/4922013-10-06t20:10:50 no new binlog events2013-10-06t20:11:10 slave stopped at master position mysql-bin.000001/4922013-10-06t20:11:30 slave stopped at master position mysql-bin.000001/4922013-10-06t20:11:50 slave stopped at master position mysql-bin.000001/4922013-10-06t20:12:10 slave stopped at master position mysql-bin.000001/4922013-10-06t20:12:30 slave stopped at master position mysql-bin.000001/4922013-10-06t20:12:50 slave stopped at master position mysql-bin.000001/4922013-10-06t20:13:10 slave stopped at master position mysql-bin.000001/4922013-10-06t20:13:30 slave stopped at master position mysql-bin.000001/4922013-10-06t20:13:30 setting slave to run normallymysql> select * from justdb.test;+------+| id |+------+| 1 |+------+1 row in set (0.00 sec)
四 附延时备份脚本
#!/bin/bash## chkconfig: - 88 12# description: the mysql ab delay scriptshost=192.168.100.54user=reppassword=larrydelay=2min=15sprog=/usr/bin/pt-slave-delay. /etc/init.d/functionsstart() { 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 1esac
bitscn.com