欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

mysql数据库备份设置延时备份方法(mysql主从配置)

程序员文章站 2024-04-03 12:28:34
一 为什么需要延时备份percona-xtrabackup是一个优秀的用于增量备份的工具。今天我们讲到的延时备份也是使用他们的产品。以前在mysql ab复制一文中提到了a...

一 为什么需要延时备份

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-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!

第二步,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)

第三步,主服务器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: 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

第五步,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: 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

第六步,在没有使用延时服务器时,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)

第七步,拷贝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

第八步,主服务器中通过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 --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, 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 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.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地址

第十二步,测试,主服务器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)

四 附延时备份脚本

复制代码 代码如下:

#!/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