爱游戏平台登录入口

  • mysql数据库备份设置延时备份方式(mysql主从设置爱游戏平台登录入口备摆设)
  • 2017年12月10日
  • 搜集搜集

一 为甚么须要延时备份

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