MYSQL数据库mysql数据库备份设置延时备份方法(mysql主从配置)
发布时间：2020-12-12 01:15:59 所属栏目：MySql教程 来源：网络整理
导读：《MYSQL数据库mysql数据库备份设置延时备份方法(mysql主从配置)》要点： 本文介绍了MYSQL数据库mysql数据库备份设置延时备份方法(mysql主从配置)，希望对您有用。如果有疑问，可以联系我们。 MYSQL实例 一 为什么需要延时备份 percona-xtrabackup是一个优秀
|
《MYSQL数据库mysql数据库备份设置延时备份方法(mysql主从配置)》要点： MYSQL实例一 为什么需要延时备份 MYSQL实例1)主服务器把数据更改记录到二进制日志中,这个操作叫做二进制日志事件； MYSQL实例2)从服务器把主服务器的二进制日志事件拷贝到自己的中继日志(relay log)中； MYSQL实例3)从服务器执行中继日志中的事件,把更改应用到自己的数据上. MYSQL实例在生产中,我们在使用 mysql AB 复制技术不但可以起到数据库层面负载均衡的能力,还可以起到备份数据的功能,但有的时候我们可能由于不小心误操作导致数据被删除,这这个时候 slave服务器上的数据也会同时被删除,如果我们能够能是的其中的一台 slave 延时备份的话,这样就可以从 slave服务器上找回被误删的数据了. MYSQL实例 MYSQL实例serv01：主服务器 192.168.1.11serv01.host.com MYSQL实例serv08：及时同步服务器 192.168.1.18serv01.host.com MYSQL实例serv09：延时同步服务器 192.168.1.19serv08.host.com MYSQL实例操作系统版本 MYSQL实例RHEL Server6.1 64位系统 MYSQL实例使用到的软件包版本 MYSQL实例mysql-5.5.29-linux2.6-x86_64.tar.gz MYSQL实例percona-toolkit-2.1.7-1.noarch.rpm MYSQL实例第一步,搭建环境.修改配置文件,注意每台的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! MYSQL实例第二步,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) MYSQL实例第三步,主服务器serv01创建授权用户 代码如下:mysql> grant replication client,replication slave on *.* to 'larry'@'192.168.1.%' identified by 'larry'; MYSQL实例第四步,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 MYSQL实例第五步,serv09延时服务器修改master状态,查看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 MYSQL实例第六步,在没有使用延时服务器时,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) MYSQL实例第七步,拷贝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 root@192.168.1.11's password: percona-toolkit-2.1.7-1.noarch.rpm?????????????????????? 100% 1767KB?? 1.7MB/s?? 00:00 MYSQL实例第八步,主服务器中通过yum安装percona-toolkit-2.1.7-1.noarch.rpm 代码如下:[root@serv01 opt]# yum install percona-toolkit-2.1.7-1.noarch.rpm -y MYSQL实例第九步,使用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,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,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 MYSQL实例第十步,serv09延时服务器中创建授权用户 代码如下:mysql> grant all on *.* to 'rep'@'192.168.1.%' identified by 'larry'; Query OK,0 rows affected (0.00 sec) MYSQL实例第十一步,实现功能. 代码如下:[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地址 MYSQL实例第十二步,测试,主服务器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) MYSQL实例四 附延时备份脚本 代码如下:#!/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 （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |