MySQL备份与恢复之MySQL延时备份_MySQL

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)

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

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

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

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)

[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 

[root@serv01 opt]# yum install percona-toolkit-2.1.7-1.noarch.rpm -y

[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

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地址

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