MySQL database backup set up the method of delayed backup (MySQL master-slave configuration) _mysql

Source: Internet
Author: User
Tags dsn vars create database percona

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&gt; use Justdb;


Database changed


Mysql&gt; CREATE TABLE test (id int);


Query OK, 0 rows affected (0.01 sec)





mysql&gt; INSERT INTO test values (1);


Query OK, 1 row Affected (0.00 sec)





Serv08


Mysql&gt; select * from Justdb.test;


+------+


| ID |


+------+


| 1 |


+------+


1 row in Set (0.00 sec)





serv09


Mysql&gt; 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&gt; 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


Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.