Why do I need a delayed backup?
Percona-xtrabackup is an excellent tool for incremental backups. The delayed backups we talked about today are also using their products.
AB replication was previously mentioned in the MySQL AB replication article. Let's first review the key points of MySQL replication. AB replication is also known as master-slave replication, the implementation of data synchronization. After the following steps:
1 The primary server records data changes to the binary log, which is called binary log event;
2 Copy the binary log event of the primary server from the server to its own relay log (relay log);
3 Perform relay log events from the server and apply the changes to their own data.
In production, we are using the MySQL AB replication technology can not only play the database level load balancing capacity, but also can play the function of backup data, but sometimes we may inadvertently cause the data to be deleted, this time the data on the slave server will also be deleted, If we can be one of the slave delayed backup, this can be retrieved from the slave server mistakenly deleted data.
Copy binary log files from the server to the primary server, if the concurrent volume is high, network delay serious situation, will cause considerable pressure on the main server, high load, there will be many problems, such as access delays, IO bottlenecks, network congestion and so on. The pressure on the server is too much for us to see, is there any solution to this situation? Yes, this is the deferred backup mentioned in this article. Delayed backup through Third-party tools, the time to check for synchronization and real synchronization is within a certain range, rather than changing the primary server data, and immediately synchronizing binary events from the server to its own relay log, which can greatly reduce the pressure on the primary server and, based on the advantages of AB replication, You can achieve the purpose of backing up your data.
Introduction to the Environment
SERV01: Master server 192.168.1.11serv01.host.com
SERV08: Sync server 192.168.1.18serv01.host.com in time
SERV09: Delayed synchronization server 192.168.1.19serv08.host.com
Operating system version
RHEL Server6.1 64-bit system
Version of the package to use
Mysql-5.5.29-linux2.6-x86_64.tar.gz
percona-toolkit-2.1.7-1.noarch.rpm
The first step is to build the environment. Modify the configuration file, note that each server-id inconsistent;
Copy Code code as follows:
[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!
Step two, serv01serv08 serv09 empty the log
Copy Code code as follows:
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)
Step three, the primary server SERV01 create an authorized user
Copy Code code as follows:
Mysql> grant replication client, replication Slave on *.* of ' Larry ' @ ' 192.168.1.% ' identified by ' Larry ';
Step fourth, serv08 Modify master settings, open Slave, view slave status
Copy Code code as follows:
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
Fifth step, serv09 delay server to modify master state, open Slave, view slave status
Copy Code code as follows:
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
The sixth step, when the delay server is not used, SERV01 to create a test database, you can see synchronization server serv08 and Delay server serv09 has been synchronized
Copy Code code as follows:
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)
Step seventh, copy percona-toolkit-2.1.7-1.noarch.rpm
Copy Code code as follows:
[Root@larrywen ule-mysql]# SCP percona-toolkit-2.1.7-1.noarch.rpm 192.168.1.11:/opt
root@192.168.1.11 ' s Password:
percona-toolkit-2.1.7-1.noarch.rpm 100% 1767KB 1.7mb/s 00:00
The eighth step is to install percona-toolkit-2.1.7-1.noarch.rpm through Yum in the master server
Copy Code code as follows:
[Root@serv01 opt]# Yum Install percona-toolkit-2.1.7-1.noarch.rpm-y
The Nineth step is to use the Pt-slave-delay tool for delay setting. You can view help first.
Copy Code code as follows:
[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,
In 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 while connecting to MySQL
--charset=s-a Default Character Set
--config=a Read This comma-separated list of config files; If
Specified, this must is the the
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 the connection
--defaults-file=s-f only read MySQL options from the given file
--delay=m How to 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 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-to Olkit.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
Step Tenth, create an authorized user in the SERV09 delay server
Copy Code code as follows:
Mysql> Grant all in *.* to ' rep ' @ ' 192.168.1.% ' identified by ' Larry ';
Query OK, 0 rows Affected (0.00 sec)
The 11th step is to implement the function.
Copy Code code as follows:
[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> Command Interpretation </strong>
--user= ' rep ': the user name of the authorized user in the delay server, set to Rep
--password= ' Larry ': the password for the authorized user in the server, which is set to Larry
--DELAY=3M: Time delay synchronization, set to 3 minutes
--interval=20s: Check synchronization time, set to 20s
--run-time=30m:pt-slave-delay run time, set here to 30 minutes
192.168.1.19: IP address of the delayed server
The 12th step, the test, the primary server SERV01 create a test database, you can find that the synchronization server immediately updated, and the delayed synchronization server to wait 3 minutes before updating
Copy Code code as follows:
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
Three minutes to view the delayed server has been synchronized successfully
[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)
Four-attached delayed backup script
Copy Code code as follows:
#!/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 "$" in
Start
Start
Stop
Stop
Restart)
Stop
Start
*)
echo "Usage: $ {Start|stop|restart}"
Exit 1
Esac
