模拟数据与模拟故障篇:

  • 整体恢复思路
    • 同一台实例要修改server_id值( 不是同一台实例可以不修改 )
    • 要关闭从节点实时拉取日志relay-log-recovery参数( 防止MySQL冲掉我们通过binlog模拟的relay-log日志文件 )
    • 根据binlog信息去模拟relay-log日志,然后把模拟的relay-log拷贝到MySQL的relay-log目录。
    • 根据全量备份时的binlog信息去创建从节点角色( 只有这样才可以开启SQL线程 )
安装MySQL与配置文件编写:
  • 以下文档是根据当前SQL回放日志所准备的MySQL安装步骤( 主要是方便观阅读 )
[root@db01 ~]# mkdir -p /home/tools
[root@db01 ~]# yum -y install bash-com* vim wget lrzsz tree libaio
[root@db01 ~]# cd /home/tools
[root@db01 tools]# wget http://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz
[root@db01 tools]# tar xf mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
[root@db01 tools]# mv /usr/local/mysql-5.7.20-linux-glibc2.12-x86_64/ /usr/local/mysql-5.7.20
[root@db01 tools]# ln -s /usr/local/mysql-5.7.20 /usr/local/mysql
[root@db01 tools]# echo 'export PATH=/usr/local/mysql/bin:$PATH' >> /etc/profile
[root@db01 tools]# source /etc/profile
[root@db01 tools]# useradd -s /sbin/nologin mysql -M
[root@db01 tools]# mkdir -p /mysql/3306/{binlog,relaylog}
[root@db01 tools]# chown -R mysql.mysql /mysql/3306
[root@db01 tools]# mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/mysql/3306/data
[root@db01 tools]# cat /etc/my.cnf
[mysqld]
server-id=3306                     # server-id设置为3306
user=mysql
socket=/mysql/3306/mysql.sock
port=3306
datadir=/mysql/3306/data
log-error=/mysql/3306/error.log
log-bin=/mysql/3306/binlog/mysql-bin       # 指定binlog存放路径及名称
relay-log=/mysql/3306/relaylog/relay-bin   # 指定relaylog存放路径及名称
log-slave-updates
report-port=3306
report-host=localhost
relay-log-recovery=1    # 默认为 1 (如果未 1 就会从主库拉取日志信息,如果未 0 拉取日志就保持暂停状态)
master-info-repository=TABLE
relay-log-info-repository=TABLE
gtid-mode=on
enforce-gtid-consistency=on
max_binlog_size=100M      # 设置当binlog超过100M就刷新一个新binlog文件

[root@db01 tools]# vim /etc/systemd/system/mysqld.service
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf
LimitNOFILE = 5000

[root@db01 tools]# systemctl start mysqld.service && systemctl enable mysqld.service
[root@db01 tools]# netstat -tunpl |grep mysql
tcp6       0      0 :::3306                 :::*                    LISTEN      21473/mysqld



[root@db01 ~]# mysql -S /mysql/3306/mysql.sock

mysql> alter user user() identified by '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye
模拟1万条数据:
  • 存储过程是先从0开始计算到1万后停止插入数据。
[root@db01 ~]# mysql -uroot -p -S /mysql/3306/mysql.sock
Enter password: 

mysql> create database wspdb;
Query OK, 1 row affected (0.01 sec)

mysql> use wspdb
Database changed
mysql> create table temp (id int primary key auto_increment,num int,k1 char(2),k2 char(4),dt timestamp);
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter //
mysql> create  procedure rand_data(in num int)
begin
declare str char(62) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
declare str2 char(2);
declare str4 char(4);
declare i int default 0;
while i<num do
set str2=concat(substring(str,1+floor(rand()*61),1),substring(str,1+floor(rand()*61),1));
set str4=concat(substring(str,1+floor(rand()*61),2),substring(str,1+floor(rand()*61),2));
set i=i+1;
insert into temp values (i,floor(rand()*num),str2,str4,now());
end while;
end;
//
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> call rand_data(10000);
Query OK, 1 row affected (6.58 sec)

mysql> select count(1) from temp;
+----------+
| count(1) |
+----------+
|    10000 |
+----------+
1 row in set (0.00 sec)
物理全量备份数据库:
  • 如果使用mysqldump可以加–master-data=2也可以记录当时备份时binlog状态。
[root@db01 ~]# mkdir -p /backup/{full,binlog}
[root@db01 ~]# wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.13/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.13-1.el7.x86_64.rpm
[root@db01 ~]# yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL
[root@db01 ~]# yum -y localinstall percona-xtrabackup-24-2.4.13-1.el7.x86_64.rpm
[root@db01 ~]# innobackupex --defaults-file=/etc/my.cnf --host=127.0.0.1 --user=root --password=123456 --no-timestamp /backup/full
再次模拟49万条数据:
  • 存储过程中定义从10000开始插入数据。
mysql> delimiter //
mysql> create  procedure rand_data_play(in num int)
begin
declare str char(62) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
declare str2 char(2);
declare str4 char(4);
declare i int default 10000;
while i<num do
set str2=concat(substring(str,1+floor(rand()*61),1),substring(str,1+floor(rand()*61),1));
set str4=concat(substring(str,1+floor(rand()*61),2),substring(str,1+floor(rand()*61),2));
set i=i+1;
insert into temp values (i,floor(rand()*num),str2,str4,now());
end while;
end;
//
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;

mysql> call rand_data_play(500000);
Query OK, 1 row affected (5 min 16.63 sec)

mysql> select count(1) from temp;
+----------+
| count(1) |
+----------+
|   500000 |
+----------+
1 row in set (0.09 sec)

模拟数据库损坏并全量恢复:

  • 从这一步才是开始操作SQL线程回放日志。
[root@db01 ~]# systemctl stop mysqld
[root@db01 ~]# rm -rf /mysql/3306/data/*

[root@db01 ~]# innobackupex --defaults-file=/etc/my.cnf --host=127.0.0.1 --user=root --password=123456 --apply-log /backup/full
[root@db01 ~]# innobackupex --defaults-file=/etc/my.cnf --host=127.0.0.1 --user=root --password=123456 --copy-back /backup/full
关闭relaylog不拉取日志:
  • 修改以下参数为 0
[root@db01 ~]# vim /etc/my.cnf
relay-log-recovery=0
修改server-id值:
  • 如果是不同的MySQL实例恢复不需要修改server-id,因为当前是同一台实例,所以需要修改。

  • 原因:在MySQL复制中同server-id不可以进行复制,当前环境在同一台实例,如果不修改会导致只读取relaylog日志,但是MySQL发现这些binlog日志就是当前实例所生成的便不会执行应用。

# 之前是 3306 , 现在改为 33060
[root@db01 ~]# vim /etc/my.cnf
server-id=33060
拷贝binlog并模拟为relaylog日志:
  • 可以看到当binlog达到100M时自动刷新了新的binlog文件
[root@db01 ~]# ll -sh /mysql/3306/binlog/
total 161M
4.0K -rw-r----- 1 mysql mysql  177 Apr 27 09:20 mysql-bin.000001
101M -rw-r----- 1 mysql mysql 101M Apr 27 09:32 mysql-bin.000002
 61M -rw-r----- 1 mysql mysql  39M Apr 27 09:33 mysql-bin.000003
4.0K -rw-r----- 1 mysql mysql  108 Apr 27 09:32 mysql-bin.index

[root@db01 ~]# cp -a /mysql/3306/binlog/mysql-bin.00000* /backup/binlog

[root@db01 ~]# cd /backup/binlog/
[root@db01 binlog]# ll -sh
mysql-bin.000001 mysql-bin.000002 mysql-bin.000003

[root@db01 binlog]# rename mysql-bin relay-bin mysql-bin*

[root@db01 binlog]# ls
relay-bin.000001 relay-bin.000002 relay-bin.000003

[root@db01 binlog]# ls ./relay-bin.00000* > relay-bin.index

[root@db01 binlog]# ls
relay-bin.000001  relay-bin.000002  relay-bin.000003  relay-bin.index

[root@db01 binlog]# cat relay-bin.index
./relay-bin.000001
./relay-bin.000002
./relay-bin.000003
拷贝模拟的日志到数据库relaylog目录:
  • 默认在data数据目录,当前在配置文件中指定存放在relaylog目录中
[root@db01 binlog]# cp ./relay-bin.* /mysql/3306/relaylog

[root@db01 binlog]# ls /mysql/3306/relaylog/
relay-bin.000001  relay-bin.000002  relay-bin.000003  relay-bin.index

[root@db01 binlog]# cd 
[root@db01 ~]# chown -R mysql.mysql /mysql/3306
启动数据库并建立虚假主从复制:
[root@db01 ~]# chown -R mysql.mysql /mysql/3306
[root@db01 ~]# systemctl start mysqld
[root@db01 ~]# netstat -tunpl | grep mysqld
tcp6       0      0 :::3306                 :::*                    LISTEN      31186/mysqld
[root@db01 ~]# mysql -uroot -p -S /mysql/3306/mysql.sock
Enter password: 

mysql> reset master;
Query OK, 0 rows affected (0.04 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
根据备份时的binlog信息创建从节点信息:
  • 可以认为这个relay-log就是binlog,binlog名称与relay-log名称要一样,position也要一致。

  • 在物理备份信息中看到是在binlog文件mysql-bin.0000022901493 Position位置点备份的。

[root@db01 ~]# cat /backup/full/xtrabackup_binlog_info 
mysql-bin.000002    2901493    62271f73-8868-11ea-9bba-02000aba3d09:1-10004

# 指定 xtrabackup_binlog_info 文件中对应的binlog位点
mysql> CHANGE MASTER TO MASTER_HOST='1.1.1.1',RELAY_LOG_FILE='relay-bin.000002',RELAY_LOG_POS=2901493;
Query OK, 0 rows affected (0.38 sec)

mysql> select * from mysql.slave_relay_log_info;
+-----------------+---------------------------------------+---------------+-----------------+----------------+-----------+-------------------+----+--------------+
| Number_of_lines | Relay_log_name                        | Relay_log_pos | Master_log_name | Master_log_pos | Sql_delay | Number_of_workers | Id | Channel_name |
+-----------------+---------------------------------------+---------------+-----------------+----------------+-----------+-------------------+----+--------------+
|               7 | /mysql/3306/relaylog/relay-bin.000002 |       2901493 |                 |              0 |         0 |                 0 |  1 |              |
+-----------------+---------------------------------------+---------------+-----------------+----------------+-----------+-------------------+----+--------------+
1 row in set (0.00 sec)
开启SQL线程恢复数据:
  • 可以修改slave-parallel-workers=5参数创建5个SQL线程并发回放日志。

  • 可以看到两次查询的slave从库状态是不一致的,Executed_Gtid_Set状态一致在更新,说明SQL在运运行回放日志增加的新事物。

  • Seconds_Behind_Master:延迟的时间从2176秒变为 0

  • Relay_Log_File:读取的relay-log文件从000002 到 000004

mysql> start slave sql_thread;
Query OK, 0 rows affected (0.00 sec)

mysql> show processlist;
+----+-------------+-----------+------+---------+------+-------------+------------------+
| Id | User        | Host      | db   | Command | Time | State       | Info             |
+----+-------------+-----------+------+---------+------+-------------+------------------+
|  3 | root        | localhost | NULL | Query   |    0 | starting    | show processlist |
|  4 | system user |           | NULL | Connect | 2175 | System lock | NULL             |
+----+-------------+-----------+------+---------+------+-------------+------------------+
2 rows in set (0.00 sec)
mysql> show slave status\G
*************************** 1. row ***************************
                  Master_Host: 1.1.1.1
                  Master_User: 
                  Master_Port: 3306
               Relay_Log_File: relay-bin.000002
                Relay_Log_Pos: 7203460
             Slave_IO_Running: No
            Slave_SQL_Running: Yes
        Seconds_Behind_Master: 2176
             Master_Info_File: mysql.slave_master_info
      Slave_SQL_Running_State: System lock
           Retrieved_Gtid_Set: 62271f73-8868-11ea-9bba-02000aba3d09:1-500005
            Executed_Gtid_Set: 62271f73-8868-11ea-9bba-02000aba3d09:10005-24837
1 row in set (0.00 sec)

mysql> 
mysql> show slave status\G
*************************** 1. row ***************************
                  Master_Host: 1.1.1.1
                  Master_User: 
                  Master_Port: 3306
               Relay_Log_File: relay-bin.000004
                Relay_Log_Pos: 4
             Slave_IO_Running: No
            Slave_SQL_Running: Yes
        Seconds_Behind_Master: 0
             Master_Info_File: mysql.slave_master_info
      Slave_SQL_Running_State: System lock
           Retrieved_Gtid_Set: 62271f73-8868-11ea-9bba-02000aba3d09:1-500005
            Executed_Gtid_Set: 62271f73-8868-11ea-9bba-02000aba3d09:10005-500005
mysql> select count(1) from temp;
+----------+
| count(1) |
+----------+
|   500000 |
+----------+
1 row in set (0.11 sec)

过滤库与表恢复

  • 当前只恢复testdb.sbtest1表
[root@db ~]# mv /mysql/3306/data/* /mnt/

# 如果已经应用过redo与undo就直接--copy-back就可以
[root@db ~]# innobackupex --defaults-file=/etc/my.cnf --apply-log  /backup/full
[root@db ~]# innobackupex --defaults-file=/etc/my.cnf --copy-back  /backup/full

[root@db ~]# rm -f /mysql/3306/relaylog/relay-bin.*
[root@db ~]# ls /mysql/3306/data
ib_buffer_pool  ibdata1  ib_logfile0  ib_logfile1  ibtmp1  mysql  performance_schema  sys  testdb  xtrabackup_binlog_pos_innodb  xtrabackup_info  xtrabackup_master_key_id

[root@db ~]# cp -a /data/backup/relay-bin.* /mysql/3306/relaylog/
[root@db ~]# chown -R mysql.mysql /mysql/3306

[root@db ~]# systemctl start mysqld
[root@db ~]# mysql -uroot -p -S /mysql/3306/mysql.sock

mysql> reset master;
Query OK, 0 rows affected (0.18 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)

[root@db ~]# cat /backup/full/xtrabackup_binlog_info
mysql-bin.000009    2164928 58adb22e-9f20-11ea-b4f6-02000aba3d99:1-51166

mysql> CHANGE MASTER TO MASTER_HOST='1.1.1.1',RELAY_LOG_FILE='relay-bin.000009',RELAY_LOG_POS=2164928;
Query OK, 0 rows affected (0.10 sec)

mysql> CHANGE REPLICATION FILTER REPLICATE_WILD_DO_TABLE = ('testdb.sbtest1');
Query OK, 0 rows affected (0.00 sec)

mysql> start slave sql_thread;
Query OK, 0 rows affected (0.00 sec)

mysql> show processlist;
+----+-------------+-----------+--------+---------+--------+-------------+------------------+
| Id | User        | Host      | db     | Command | Time   | State       | Info             |
+----+-------------+-----------+--------+---------+--------+-------------+------------------+
|  3 | root        | localhost | testdb | Query   |      0 | starting    | show processlist |
|  4 | system user |           | NULL   | Connect | 166369 | System lock | NULL             |
+----+-------------+-----------+--------+---------+--------+-------------+------------------+
2 rows in set (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: 1.1.1.1
                  Master_User:
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File:
          Read_Master_Log_Pos: 4
               Relay_Log_File: relay-bin.000010
                Relay_Log_Pos: 28123234
        Relay_Master_Log_File: mysql-bin.000010
             Slave_IO_Running: No
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table: testdb.sbtest1
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 28123234
              Relay_Log_Space: 740627043
              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: 166340
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: 0
                  Master_UUID:
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: System lock
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set: 58adb22e-9f20-11ea-b4f6-02000aba3d99:1-552545
            Executed_Gtid_Set: 58adb22e-9f20-11ea-b4f6-02000aba3d99:51167-128941
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)

-----------------------------------------------------------------------------------------------------

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: 1.1.1.1
                  Master_User:
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File:
          Read_Master_Log_Pos: 4
               Relay_Log_File: relay-bin.000011
                Relay_Log_Pos: 3592946
        Relay_Master_Log_File: mysql-bin.000011
             Slave_IO_Running: No
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table: testdb.sbtest1
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 3592946
              Relay_Log_Space: 635767558
              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: 166325
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: 0
                  Master_UUID:
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: System lock
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set: 58adb22e-9f20-11ea-b4f6-02000aba3d99:1-552545
            Executed_Gtid_Set: 58adb22e-9f20-11ea-b4f6-02000aba3d99:51167-176699
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)

开启SQL并发与指定GTID或Position终止恢复

[root@db ~]# echo 'slave-parallel-workers=5' >> /etc/my.cnf
[root@db backup]# mysqlbinlog -vvv relay-bin.000015 > aaaa

mysql> start slave sql_thread until sql_before_gtids = '58adb22e-9f20-11ea-b4f6-02000aba3d99:423933';
Query OK, 0 rows affected (0.01 sec)

mysql> start slave sql_thread;
Query OK, 0 rows affected (0.00 sec)

mysql> show processlist;
+----+-------------+-----------+------+---------+--------+---------------------------------------+------------------+
| Id | User        | Host      | db   | Command | Time   | State                                 | Info             |
+----+-------------+-----------+------+---------+--------+---------------------------------------+------------------+
|  3 | root        | localhost | NULL | Query   |      0 | starting                              | show processlist |
| 10 | system user |           | NULL | Connect |      0 | Reading event from the relay log      | NULL             |
| 11 | system user |           | NULL | Connect |      1 | Waiting for an event from Coordinator | NULL             |
| 12 | system user |           | NULL | Connect |      1 | Waiting for an event from Coordinator | NULL             |
| 13 | system user |           | NULL | Connect |      1 | Waiting for an event from Coordinator | NULL             |
| 14 | system user |           | NULL | Connect |      1 | Waiting for an event from Coordinator | NULL             |
| 15 | system user |           | NULL | Connect | 168317 | System lock                           | NULL             |
+----+-------------+-----------+------+---------+--------+---------------------------------------+------------------+
7 rows in set (0.00 sec)