您的位置:金沙游乐场85155 > 大数据库 > 恢复误删除表黑科技之relay log大法金沙游乐场8

恢复误删除表黑科技之relay log大法金沙游乐场8

发布时间:2019-10-07 00:29编辑:大数据库浏览(111)

     

     

    Preface

    Preface

     

     

        I've stuck twice in my previous experiments in backing up dropped tables.I am still not sure that why I got failure yesterday.Therefore,I decide to do that again with the same steps on another environment.Let's see the details.

        Today I'm gonna test how to rescue a dropped table from binlog server based on a full Xtrabackup backup set.

     

     

    Procedure

    Framework

     

     

    1. All the operations on master zlm1.

    Hostname IP/Port Identity OS Version MySQL Version GTID Mode Binlog Format
    zlm1 192.168.56.100/3306 master CentOS 7.0 5.7.21 on row
    zlm2 192.168.56.101/3306 slave CentOS 7.0 5.7.21 on row
    zlm3 192.168.56.102/3306 binlog server CentOS 7.0 5.7.21 on row
      1 //Check data at first.
      2 zlm@192.168.56.100:3306 [sysbench]>show tables;
      3 +--------------------+
      4 | Tables_in_sysbench |
      5 +--------------------+
      6 | sbtest1            |
      7 | sbtest2            |
      8 | sbtest3            |
      9 | sbtest4            |
     10 | sbtest5            |
     11 +--------------------+
     12 5 rows in set (0.00 sec)
     13 
     14 zlm@192.168.56.100:3306 [sysbench]>select count(*) from sbtest5;
     15 +----------+
     16 | count(*) |
     17 +----------+
     18 |    10000 |
     19 +----------+
     20 1 row in set (0.00 sec)
     21 
     22 zlm@192.168.56.100:3306 [sysbench]>show binary logs;
     23 +------------------+-----------+
     24 | Log_name         | File_size |
     25 +------------------+-----------+
     26 | mysql-bin.000091 |      1288 |
     27 | mysql-bin.000092 |       217 |
     28 | mysql-bin.000093 |       241 |
     29 | mysql-bin.000094 |       217 |
     30 | mysql-bin.000095 |      4128 |
     31 | mysql-bin.000096 |       241 |
     32 | mysql-bin.000097 |  11461585 |
     33 | mysql-bin.000098 |       410 |
     34 | mysql-bin.000099 |       241 |
     35 | mysql-bin.000100 |       974 |
     36 | mysql-bin.000101 |       217 |
     37 | mysql-bin.000102 |       217 |
     38 | mysql-bin.000103 |       194 |
     39 +------------------+-----------+
     40 13 rows in set (0.01 sec)
     41 
     42 zlm@192.168.56.100:3306 [sysbench]>show master status;
     43 +------------------+----------+--------------+------------------+-------------------------------------------------+
     44 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                               |
     45 +------------------+----------+--------------+------------------+-------------------------------------------------+
     46 | mysql-bin.000103 |      194 |              |                  | 2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715696 |
     47 +------------------+----------+--------------+------------------+-------------------------------------------------+
     48 1 row in set (0.00 sec)
     49 
     50 //Generate a Xtrabackup then send it to the slave zlm2.
     51 [root@zlm1 16:27:18 /data/backup]
     52 #innobackupex --defaults-file=/data/mysql/mysql3306/my3306.cnf --user=root --password=Passw0rd /data/backup
     53 
     54 [root@zlm1 16:34:09 /data/backup]
     55 #scp -r 2018-07-31_16-31-46/ zlm2:/data/backup/
     56 
     57 //Continue to do some operations(DML).
     58 zlm@192.168.56.100:3306 [sysbench]>delete from sbtest5 limit 2000;
     59 Query OK, 2000 rows affected (0.19 sec)
     60 
     61 zlm@192.168.56.100:3306 [sysbench]>delete from sbtest5 limit 3000;
     62 Query OK, 3000 rows affected (0.04 sec)
     63 
     64 //Drop the table.
     65 zlm@192.168.56.100:3306 [sysbench]>drop table sbtest5;
     66 Query OK, 0 rows affected (0.04 sec)
     67 
     68 zlm@192.168.56.100:3306 [sysbench]>flush logs;
     69 Query OK, 0 rows affected (0.05 sec)
     70 
     71 zlm@192.168.56.100:3306 [sysbench]>show master status;
     72 +------------------+----------+--------------+------------------+-------------------------------------------------+
     73 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                               |
     74 +------------------+----------+--------------+------------------+-------------------------------------------------+
     75 | mysql-bin.000104 |      194 |              |                  | 2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715699 |
     76 +------------------+----------+--------------+------------------+-------------------------------------------------+
     77 1 row in set (0.00 sec)
     78 
     79 //Shutdown the master.
     80 zlm@192.168.56.100:3306 [sysbench]>exit
     81 Bye
     82 
     83 [root@zlm1 16:36:01 ~]
     84 #mysqladmin shutdown
     85 
     86 //Send the binlogs which contain the operations above to slave zlm2 either.
     87 [root@zlm1 16:36:10 ~]
     88 #cd /data/mysql/mysql3306/logs
     89 
     90 [root@zlm1 16:41:45 /data/mysql/mysql3306/logs]
     91 #ls -l
     92 total 12188
     93 -rw-r----- 1 mysql mysql     1288 Jul 22 11:27 mysql-bin.000091
     94 -rw-r----- 1 mysql mysql      217 Jul 22 11:28 mysql-bin.000092
     95 -rw-r----- 1 mysql mysql      241 Jul 25 19:18 mysql-bin.000093
     96 -rw-r----- 1 mysql mysql      217 Jul 25 19:18 mysql-bin.000094
     97 -rw-r----- 1 mysql mysql     4128 Jul 29 08:42 mysql-bin.000095
     98 -rw-r----- 1 mysql mysql      241 Jul 29 08:42 mysql-bin.000096
     99 -rw-r----- 1 mysql mysql 11461585 Jul 29 09:52 mysql-bin.000097
    100 -rw-r----- 1 mysql mysql      410 Jul 29 16:27 mysql-bin.000098
    101 -rw-r----- 1 mysql mysql      241 Jul 29 16:27 mysql-bin.000099
    102 -rw-r----- 1 mysql mysql      974 Jul 29 16:57 mysql-bin.000100
    103 -rw-r----- 1 mysql mysql      217 Jul 30 17:01 mysql-bin.000101
    104 -rw-r----- 1 mysql mysql      217 Jul 30 18:12 mysql-bin.000102
    105 -rw-r----- 1 mysql mysql   954995 Jul 31 16:35 mysql-bin.000103
    106 -rw-r----- 1 mysql mysql      217 Jul 31 16:36 mysql-bin.000104
    107 -rw-r----- 1 mysql mysql      616 Jul 31 16:35 mysql-bin.index
    108 
    109 [root@zlm1 16:42:01 /data/mysql/mysql3306/logs]
    110 #scp mysql-bin.00010{3,4} zlm2:/data/backup
    111 mysql-bin.000103                                                                                                   100%  933KB 932.6KB/s   00:00    
    112 mysql-bin.000104                                                                                                   100%  217     0.2KB/s   00:00
    

     

     

    Precedure

    **2. Operations on slave zlm2.**

     

      1 //Prepare to restore the backup of Xtrabackup.
      2 [root@zlm2 16:37:11 /data/backup]
      3 #innobackupex --defaults-file=/data/mysql/mysql3306/my3306.cnf -uroot -pPassw0rd --apply-log /data/backup/2018-07-31_16-31-46/
      4 
      5 [root@zlm2 16:36:42 /data/mysql/mysql3306/data]
      6 #ls -l
      7 total 409732
      8 -rw-r----- 1 mysql mysql        56 Jul 30 17:25 auto.cnf
      9 -rw-r----- 1 mysql mysql     31332 Jul 30 21:08 error.log
     10 -rw-r----- 1 mysql mysql       871 Jul 30 21:08 ib_buffer_pool
     11 -rw-r----- 1 mysql mysql 104857600 Jul 30 21:08 ibdata1
     12 -rw-r----- 1 mysql mysql 104857600 Jul 30 21:08 ib_logfile0
     13 -rw-r----- 1 mysql mysql 104857600 Jul 30 17:22 ib_logfile1
     14 -rw-r----- 1 mysql mysql 104857600 Jul 30 17:22 ib_logfile2
     15 -rw-r----- 1 mysql mysql       131 Jul 30 21:08 master.info
     16 drwxr-x--- 2 mysql mysql      4096 Jul 30 17:22 mrbs
     17 drwxr-x--- 2 mysql mysql      4096 Jul 30 17:22 mysql
     18 drwxr-x--- 2 mysql mysql      8192 Jul 30 17:23 performance_schema
     19 -rw-r----- 1 mysql mysql       201 Jul 30 20:08 relay-bin.000001
     20 -rw-r----- 1 mysql mysql       936 Jul 30 20:08 relay-bin.000002
     21 -rw-r----- 1 mysql mysql       454 Jul 30 20:08 relay-bin.000003
     22 -rw-r----- 1 mysql mysql       454 Jul 30 20:08 relay-bin.000004
     23 -rw-r----- 1 mysql mysql       454 Jul 30 20:08 relay-bin.000005
     24 -rw-r----- 1 mysql mysql       454 Jul 30 20:08 relay-bin.000006
     25 -rw-r----- 1 mysql mysql       430 Jul 30 21:08 relay-bin.000007
     26 -rw-r----- 1 mysql mysql       133 Jul 30 20:11 relay-bin.index
     27 -rw-r----- 1 mysql mysql        53 Jul 30 21:08 relay-log.info
     28 -rw-r----- 1 mysql mysql       346 Jul 30 18:17 slow.log
     29 drwxr-x--- 2 mysql mysql      8192 Jul 30 17:22 sys
     30 drwxr-x--- 2 mysql mysql      4096 Jul 30 17:23 sysbench
     31 -rw-r----- 1 mysql mysql        21 Jul 30 17:23 xtrabackup_binlog_pos_innodb
     32 -rw-r----- 1 mysql mysql       595 Jul 30 17:23 xtrabackup_info
     33 -rw-r----- 1 mysql mysql         1 Jul 30 17:23 xtrabackup_master_key_id
     34 
     35 [root@zlm2 16:36:41 /data/mysql/mysql3306/data]
     36 #ps aux|grep mysqld
     37 root      3900  0.0  0.0 112640   960 pts/1    R+   16:38   0:00 grep --color=auto mysqld
     38 
     39 [root@zlm2 16:36:44 /data/mysql/mysql3306/data]
     40 #rm -rf *
     41 
     42 //Copy back the datafiles.
     43 [root@zlm2 16:38:31 /data/backup]
     44 #innobackupex --defaults-file=/data/mysql/mysql3306/my3306.cnf -uroot -pPassw0rd --copy-back /data/backup/2018-07-31_16-31-46/
     45 
     46 [root@zlm2 16:38:48 /data/mysql/mysql3306/data]
     47 #ls -l
     48 total 421940
     49 -rw-r----- 1 root root       784 Jul 31 16:39 ib_buffer_pool
     50 -rw-r----- 1 root root 104857600 Jul 31 16:39 ibdata1
     51 -rw-r----- 1 root root 104857600 Jul 31 16:39 ib_logfile0
     52 -rw-r----- 1 root root 104857600 Jul 31 16:39 ib_logfile1
     53 -rw-r----- 1 root root 104857600 Jul 31 16:39 ib_logfile2
     54 -rw-r----- 1 root root  12582912 Jul 31 16:39 ibtmp1
     55 drwxr-x--- 2 root root      4096 Jul 31 16:39 mrbs
     56 drwxr-x--- 2 root root      4096 Jul 31 16:39 mysql
     57 drwxr-x--- 2 root root      8192 Jul 31 16:39 performance_schema
     58 drwxr-x--- 2 root root      8192 Jul 31 16:39 sys
     59 drwxr-x--- 2 root root      4096 Jul 31 16:39 sysbench
     60 -rw-r----- 1 root root        21 Jul 31 16:39 xtrabackup_binlog_pos_innodb
     61 -rw-r----- 1 root root       595 Jul 31 16:39 xtrabackup_info
     62 -rw-r----- 1 root root         1 Jul 31 16:39 xtrabackup_master_key_id
     63 
     64 [root@zlm2 16:40:26 /data/mysql/mysql3306/data]
     65 #sh /root/mysqld.sh
     66 
     67 [root@zlm2 16:40:33 /data/mysql/mysql3306/data]
     68 #mysql
     69 ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.56.101' (111)
     70 
     71 [root@zlm2 16:40:37 /data/mysql/mysql3306/data]
     72 #chown -R mysql.mysql *
     73 
     74 [root@zlm2 16:41:00 /data/mysql/mysql3306/data]
     75 #sh /root/mysqld.sh
     76 
     77 [root@zlm2 16:41:02 /data/mysql/mysql3306/data]
     78 #mysql
     79 Welcome to the MySQL monitor.  Commands end with ; or g.
     80 Your MySQL connection id is 2
     81 Server version: 5.7.21-log MySQL Community Server (GPL)
     82 
     83 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
     84 
     85 Oracle is a registered trademark of Oracle Corporation and/or its
     86 affiliates. Other names may be trademarks of their respective
     87 owners.
     88 
     89 Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
     90 
     91 zlm@192.168.56.101:3306 [(none)]>select count(*) from sysbench.sbtest5;
     92 +----------+
     93 | count(*) |
     94 +----------+
     95 |    10000 |
     96 +----------+
     97 1 row in set (0.03 sec)
     98 
     99 //Continue to restore the incremental data with sql_thread.
    100 [root@zlm2 16:39:55 /data/backup]
    101 #ls -l
    102 total 944
    103 drwxr-x--- 7 root root   4096 Jul 31 16:38 2018-07-31_16-31-46
    104 -rw-r----- 1 root root 954995 Jul 31 16:42 mysql-bin.000103
    105 -rw-r----- 1 root root    217 Jul 31 16:42 mysql-bin.000104
    106 
    107 [root@zlm2 16:43:19 /data/backup]
    108 #for i in $(ls mysql-bin.0*)
    109 > do
    110 >     ext=$(echo $i | cut -d'.' -f2);
    111 >     cp $i relay-bin.$ext;
    112 > done
    113 
    114 [root@zlm2 16:45:20 /data/backup]
    115 #ls -l
    116 total 1884
    117 drwxr-x--- 7 root root   4096 Jul 31 16:38 2018-07-31_16-31-46
    118 -rw-r----- 1 root root 954995 Jul 31 16:42 mysql-bin.000103
    119 -rw-r----- 1 root root    217 Jul 31 16:42 mysql-bin.000104
    120 -rw-r----- 1 root root 954995 Jul 31 16:45 relay-bin.000103
    121 -rw-r----- 1 root root    217 Jul 31 16:45 relay-bin.000104
    122 
    123 [root@zlm2 16:45:23 /data/backup]
    124 #ls ./relay-bin.0* > relay-bin.index
    125 
    126 [root@zlm2 16:45:31 /data/backup]
    127 #ls -l
    128 total 1888
    129 drwxr-x--- 7 root root   4096 Jul 31 16:38 2018-07-31_16-31-46
    130 -rw-r----- 1 root root 954995 Jul 31 16:42 mysql-bin.000103
    131 -rw-r----- 1 root root    217 Jul 31 16:42 mysql-bin.000104
    132 -rw-r----- 1 root root 954995 Jul 31 16:45 relay-bin.000103
    133 -rw-r----- 1 root root    217 Jul 31 16:45 relay-bin.000104
    134 -rw-r--r-- 1 root root     38 Jul 31 16:45 relay-bin.index
    135 
    136 [root@zlm2 16:45:33 /data/backup]
    137 #cat relay-bin.index 
    138 ./relay-bin.000103
    139 ./relay-bin.000104
    140 
    141 [root@zlm2 16:45:37 /data/backup]
    142 #chown mysql.mysql relay*
    143 
    144 [root@zlm2 16:45:51 /data/backup]
    145 #ls -l
    146 total 1888
    147 drwxr-x--- 7 root  root    4096 Jul 31 16:38 2018-07-31_16-31-46
    148 -rw-r----- 1 root  root  954995 Jul 31 16:42 mysql-bin.000103
    149 -rw-r----- 1 root  root     217 Jul 31 16:42 mysql-bin.000104
    150 -rw-r----- 1 mysql mysql 954995 Jul 31 16:45 relay-bin.000103
    151 -rw-r----- 1 mysql mysql    217 Jul 31 16:45 relay-bin.000104
    152 -rw-r--r-- 1 mysql mysql     38 Jul 31 16:45 relay-bin.index
    153 
    154 [root@zlm2 16:45:52 /data/backup]
    155 #cp relay* /data/mysql/mysql3306/data
    156 
    157 [root@zlm2 16:46:08 /data/backup]
    158 #cd /data/mysql/mysql3306/data
    159 
    160 [root@zlm2 16:46:25 /data/mysql/mysql3306/data]
    161 #ls -l
    162 total 422908
    163 -rw-r----- 1 mysql mysql        56 Jul 31 16:41 auto.cnf
    164 -rw-r----- 1 mysql mysql      5989 Jul 31 16:46 error.log
    165 -rw-r----- 1 mysql mysql       784 Jul 31 16:39 ib_buffer_pool
    166 -rw-r----- 1 mysql mysql 104857600 Jul 31 16:41 ibdata1
    167 -rw-r----- 1 mysql mysql 104857600 Jul 31 16:41 ib_logfile0
    168 -rw-r----- 1 mysql mysql 104857600 Jul 31 16:39 ib_logfile1
    169 -rw-r----- 1 mysql mysql 104857600 Jul 31 16:39 ib_logfile2
    170 -rw-r----- 1 mysql mysql  12582912 Jul 31 16:41 ibtmp1
    171 -rw-r----- 1 mysql mysql         0 Jul 31 16:40 innodb_status.3949
    172 -rw-r----- 1 mysql mysql      3835 Jul 31 16:46 innodb_status.3979
    173 drwxr-x--- 2 mysql mysql      4096 Jul 31 16:39 mrbs
    174 drwxr-x--- 2 mysql mysql      4096 Jul 31 16:39 mysql
    175 -rw-r----- 1 mysql mysql         5 Jul 31 16:41 mysql.pid
    176 drwxr-x--- 2 mysql mysql      8192 Jul 31 16:39 performance_schema
    177 -rw-r----- 1 root  root     954995 Jul 31 16:46 relay-bin.000103
    178 -rw-r----- 1 root  root        217 Jul 31 16:46 relay-bin.000104
    179 -rw-r--r-- 1 root  root         38 Jul 31 16:46 relay-bin.index
    180 -rw-r----- 1 mysql mysql       173 Jul 31 16:41 slow.log
    181 drwxr-x--- 2 mysql mysql      8192 Jul 31 16:39 sys
    182 drwxr-x--- 2 mysql mysql      4096 Jul 31 16:39 sysbench
    183 -rw-r----- 1 mysql mysql        21 Jul 31 16:39 xtrabackup_binlog_pos_innodb
    184 -rw-r----- 1 mysql mysql       595 Jul 31 16:39 xtrabackup_info
    185 -rw-r----- 1 mysql mysql         1 Jul 31 16:39 xtrabackup_master_key_id
    186 
    187 [root@zlm2 16:46:26 /data/mysql/mysql3306/data]
    188 #chown mysql.mysql relay*
    189 
    190 [root@zlm2 16:46:37 /data/mysql/mysql3306/data]
    191 #ls -l
    192 total 422908
    193 -rw-r----- 1 mysql mysql        56 Jul 31 16:41 auto.cnf
    194 -rw-r----- 1 mysql mysql      6137 Jul 31 16:52 error.log
    195 -rw-r----- 1 mysql mysql       784 Jul 31 16:39 ib_buffer_pool
    196 -rw-r----- 1 mysql mysql 104857600 Jul 31 16:41 ibdata1
    197 -rw-r----- 1 mysql mysql 104857600 Jul 31 16:41 ib_logfile0
    198 -rw-r----- 1 mysql mysql 104857600 Jul 31 16:39 ib_logfile1
    199 -rw-r----- 1 mysql mysql 104857600 Jul 31 16:39 ib_logfile2
    200 -rw-r----- 1 mysql mysql  12582912 Jul 31 16:41 ibtmp1
    201 -rw-r----- 1 mysql mysql         0 Jul 31 16:40 innodb_status.3949
    202 -rw-r----- 1 mysql mysql      3835 Jul 31 16:53 innodb_status.3979
    203 drwxr-x--- 2 mysql mysql      4096 Jul 31 16:39 mrbs
    204 drwxr-x--- 2 mysql mysql      4096 Jul 31 16:39 mysql
    205 -rw-r----- 1 mysql mysql         5 Jul 31 16:41 mysql.pid
    206 drwxr-x--- 2 mysql mysql      8192 Jul 31 16:39 performance_schema
    207 -rw-r----- 1 mysql mysql    954995 Jul 31 16:46 relay-bin.000103
    208 -rw-r----- 1 mysql mysql       217 Jul 31 16:46 relay-bin.000104
    209 -rw-r--r-- 1 mysql mysql        38 Jul 31 16:46 relay-bin.index
    210 -rw-r----- 1 mysql mysql       173 Jul 31 16:41 slow.log
    211 drwxr-x--- 2 mysql mysql      8192 Jul 31 16:39 sys
    212 drwxr-x--- 2 mysql mysql      4096 Jul 31 16:39 sysbench
    213 -rw-r----- 1 mysql mysql        21 Jul 31 16:39 xtrabackup_binlog_pos_innodb
    214 -rw-r----- 1 mysql mysql       595 Jul 31 16:39 xtrabackup_info
    215 -rw-r----- 1 mysql mysql         1 Jul 31 16:39 xtrabackup_master_key_id
    216 
    217 zlm@192.168.56.101:3306 [(none)]>show master status;
    218 +------------------+----------+--------------+------------------+-------------------------------------------------+
    219 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                               |
    220 +------------------+----------+--------------+------------------+-------------------------------------------------+
    221 | mysql-bin.000002 |      194 |              |                  | 2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715696 |
    222 +------------------+----------+--------------+------------------+-------------------------------------------------+
    223 1 row in set (0.00 sec)
    224 
    225 zlm@192.168.56.101:3306 [(none)]>change master to relay_log_file='relay-bin.000103',relay_log_pos=194,master_host='xxx';
    226 Query OK, 0 rows affected (0.01 sec)
    227 
    228 zlm@192.168.56.101:3306 [(none)]>show slave statusG
    229 *************************** 1. row ***************************
    230                Slave_IO_State: 
    231                   Master_Host: xxx
    232                   Master_User: 
    233                   Master_Port: 3306
    234                 Connect_Retry: 60
    235               Master_Log_File: 
    236           Read_Master_Log_Pos: 4
    237                Relay_Log_File: relay-bin.000103
    238                 Relay_Log_Pos: 194
    239         Relay_Master_Log_File: 
    240              Slave_IO_Running: No
    241             Slave_SQL_Running: No
    242               Replicate_Do_DB: 
    243           Replicate_Ignore_DB: 
    244            Replicate_Do_Table: 
    245        Replicate_Ignore_Table: 
    246       Replicate_Wild_Do_Table: 
    247   Replicate_Wild_Ignore_Table: 
    248                    Last_Errno: 0
    249                    Last_Error: 
    250                  Skip_Counter: 0
    251           Exec_Master_Log_Pos: 0 //No data here yet.
    252               Relay_Log_Space: 955366
    253               Until_Condition: None
    254                Until_Log_File: 
    255                 Until_Log_Pos: 0
    256            Master_SSL_Allowed: No
    257            Master_SSL_CA_File: 
    258            Master_SSL_CA_Path: 
    259               Master_SSL_Cert: 
    260             Master_SSL_Cipher: 
    261                Master_SSL_Key: 
    262         Seconds_Behind_Master: NULL
    263 Master_SSL_Verify_Server_Cert: No
    264                 Last_IO_Errno: 0
    265                 Last_IO_Error: 
    266                Last_SQL_Errno: 0
    267                Last_SQL_Error: 
    268   Replicate_Ignore_Server_Ids: 
    269              Master_Server_Id: 0
    270                   Master_UUID: 
    271              Master_Info_File: /data/mysql/mysql3306/data/master.info
    272                     SQL_Delay: 0
    273           SQL_Remaining_Delay: NULL
    274       Slave_SQL_Running_State: 
    275            Master_Retry_Count: 86400
    276                   Master_Bind: 
    277       Last_IO_Error_Timestamp: 
    278      Last_SQL_Error_Timestamp: 
    279                Master_SSL_Crl: 
    280            Master_SSL_Crlpath: 
    281            Retrieved_Gtid_Set: //No data here.
    282             Executed_Gtid_Set: 2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715696
    283                 Auto_Position: 0
    284          Replicate_Rewrite_DB: 
    285                  Channel_Name: 
    286            Master_TLS_Version: 
    287 1 row in set (0.00 sec)
    288 
    289 [root@zlm2 16:59:42 /data/backup]
    290 #ls -l
    291 total 1888
    292 drwxr-x--- 7 root  root    4096 Jul 31 16:38 2018-07-31_16-31-46
    293 -rw-r----- 1 root  root  954995 Jul 31 16:42 mysql-bin.000103
    294 -rw-r----- 1 root  root     217 Jul 31 16:42 mysql-bin.000104
    295 -rw-r----- 1 mysql mysql 954995 Jul 31 16:45 relay-bin.000103
    296 -rw-r----- 1 mysql mysql    217 Jul 31 16:45 relay-bin.000104
    297 -rw-r--r-- 1 mysql mysql     38 Jul 31 16:45 relay-bin.index
    298 
    299 //Find out the end position to set util clause.
    300 [root@zlm2 16:59:43 /data/backup]
    301 #mysqlbinlog --base64-output=decode-rows relay-bin.000103 > 103.log
    302 
    303 [root@zlm2 17:01:22 /data/backup]
    304 #tail -20 103.log
    305 #180731 16:35:17 server id 1003306  end_log_pos 954724 CRC32 0xe3e63622     Delete_rows: table id 114 flags: STMT_END_F
    306 # at 954724
    307 #180731 16:35:17 server id 1003306  end_log_pos 954755 CRC32 0x84cf5136     Xid = 31
    308 COMMIT/*!*/;
    309 # at 954755 //This position is which I need to set until to.
    310 #180731 16:35:29 server id 1003306  end_log_pos 954820 CRC32 0xef03ef84     GTID    last_committed=2    sequence_number=3    rbr_only=no
    311 SET @@SESSION.GTID_NEXT= '2a4b3562-2ab6-11e8-be7a-080027de0e0e:12715699'/*!*/;
    312 # at 954820
    313 #180731 16:35:29 server id 1003306  end_log_pos 954948 CRC32 0x0309b10f     Query    thread_id=2    exec_time=0    error_code=0
    314 use `sysbench`/*!*/;
    315 SET TIMESTAMP=1533047729/*!*/;
    316 DROP TABLE `sbtest5` /* generated by server */
    317 /*!*/;
    318 # at 954948
    319 #180731 16:35:37 server id 1003306  end_log_pos 954995 CRC32 0xc1ca182a     Rotate to mysql-bin.000104  pos: 4
    320 SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
    321 DELIMITER ;
    322 # End of log file
    323 /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
    324 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
    325 
    326 zlm@192.168.56.101:3306 [(none)]>start slave sql_thread until relay_log_file='relay-bin.000103',relay_log_pos=954755;
    327 Query OK, 0 rows affected (0.00 sec)
    328 
    329 zlm@192.168.56.101:3306 [(none)]>show slave statusG
    330 *************************** 1. row ***************************
    331                Slave_IO_State: 
    332                   Master_Host: xxx
    333                   Master_User: 
    334                   Master_Port: 3306
    335                 Connect_Retry: 60
    336               Master_Log_File: 
    337           Read_Master_Log_Pos: 4
    338                Relay_Log_File: relay-bin.000103
    339                 Relay_Log_Pos: 954755
    340         Relay_Master_Log_File: 
    341              Slave_IO_Running: No
    342             Slave_SQL_Running: No
    343               Replicate_Do_DB: 
    344           Replicate_Ignore_DB: 
    345            Replicate_Do_Table: 
    346        Replicate_Ignore_Table: 
    347       Replicate_Wild_Do_Table: 
    348   Replicate_Wild_Ignore_Table: 
    349                    Last_Errno: 0
    350                    Last_Error: 
    351                  Skip_Counter: 0
    352           Exec_Master_Log_Pos: 954755 //This is the terminal relay log position I set just now.
    353               Relay_Log_Space: 955366
    354               Until_Condition: Relay
    355                Until_Log_File: relay-bin.000103
    356                 Until_Log_Pos: 954755
    357            Master_SSL_Allowed: No
    358            Master_SSL_CA_File: 
    359            Master_SSL_CA_Path: 
    360               Master_SSL_Cert: 
    361             Master_SSL_Cipher: 
    362                Master_SSL_Key: 
    363         Seconds_Behind_Master: NULL
    364 Master_SSL_Verify_Server_Cert: No
    365                 Last_IO_Errno: 0
    366                 Last_IO_Error: 
    367                Last_SQL_Errno: 0
    368                Last_SQL_Error: 
    369   Replicate_Ignore_Server_Ids: 
    370              Master_Server_Id: 0
    371                   Master_UUID: 
    372              Master_Info_File: /data/mysql/mysql3306/data/master.info
    373                     SQL_Delay: 0
    374           SQL_Remaining_Delay: NULL
    375       Slave_SQL_Running_State: 
    376            Master_Retry_Count: 86400
    377                   Master_Bind: 
    378       Last_IO_Error_Timestamp: 
    379      Last_SQL_Error_Timestamp: 
    380                Master_SSL_Crl: 
    381            Master_SSL_Crlpath: 
    382            Retrieved_Gtid_Set: 
    383             Executed_Gtid_Set: 2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715698
    384                 Auto_Position: 0
    385          Replicate_Rewrite_DB: 
    386                  Channel_Name: 
    387            Master_TLS_Version: 
    388 1 row in set (0.00 sec)
    389 
    390 zlm@192.168.56.101:3306 [(none)]>select count(*) from sysbench.sbtest5;
    391 +----------+
    392 | count(*) |
    393 +----------+
    394 |     5000 |
    395 +----------+
    396 1 row in set (0.00 sec)
    

    Step 1: Create binlog server.

     

     

    3. The supplemented experiment.

    Check the position on master 

      1 //Shudown the instance.
      2 [root@zlm2 18:27:23 /data/mysql/mysql3306/data]
      3 #ps aux|grep mysqld
      4 mysql     3979  0.0 20.0 1072196 204196 pts/1  Sl   16:41   0:02 mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf
      5 root      4265  0.0  0.0 112640   960 pts/0    R+   18:27   0:00 grep --color=auto mysqld
      6 
      7 [root@zlm2 18:27:28 /data/mysql/mysql3306/data]
      8 #mysqladmin shutdown
      9 
     10 [root@zlm2 18:27:42 /data/mysql/mysql3306/data]
     11 
     12 //Copy back the data one more time(no need to executed with option "--apply-log" first this time).
     13 [root@zlm2 18:28:13 /data/backup]
     14 #innobackupex --defaults-file=/data/mysql/mysql3306/my3306.cnf -uroot -pPassw0rd --copy-back /data/backup/2018-07-31_16-31-46/
     15 
     16 //Copy the relay logs to datadir directory again.
     17 [root@zlm2 18:29:22 /data/backup]
     18 #ls -l
     19 total 1904
     20 -rw-r--r-- 1 root  root   16082 Jul 31 17:01 103.log
     21 drwxr-x--- 7 root  root    4096 Jul 31 16:38 2018-07-31_16-31-46
     22 -rw-r----- 1 root  root  954995 Jul 31 16:42 mysql-bin.000103
     23 -rw-r----- 1 root  root     217 Jul 31 16:42 mysql-bin.000104
     24 -rw-r----- 1 mysql mysql 954995 Jul 31 16:45 relay-bin.000103
     25 -rw-r----- 1 mysql mysql    217 Jul 31 16:45 relay-bin.000104
     26 -rw-r--r-- 1 mysql mysql     38 Jul 31 16:45 relay-bin.index
     27 
     28 [root@zlm2 18:29:45 /data/backup]
     29 #cp relay* /data/mysql/mysql3306/data
     30 
     31 [root@zlm2 18:30:25 /data/mysql/mysql3306/data]
     32 #ls -l
     33 total 422884
     34 -rw-r----- 1 root root       784 Jul 31 18:29 ib_buffer_pool
     35 -rw-r----- 1 root root 104857600 Jul 31 18:29 ibdata1
     36 -rw-r----- 1 root root 104857600 Jul 31 18:28 ib_logfile0
     37 -rw-r----- 1 root root 104857600 Jul 31 18:28 ib_logfile1
     38 -rw-r----- 1 root root 104857600 Jul 31 18:28 ib_logfile2
     39 -rw-r----- 1 root root  12582912 Jul 31 18:29 ibtmp1
     40 drwxr-x--- 2 root root      4096 Jul 31 18:29 mrbs
     41 drwxr-x--- 2 root root      4096 Jul 31 18:29 mysql
     42 drwxr-x--- 2 root root      8192 Jul 31 18:29 performance_schema
     43 -rw-r----- 1 root root    954995 Jul 31 18:30 relay-bin.000103
     44 -rw-r----- 1 root root       217 Jul 31 18:30 relay-bin.000104
     45 -rw-r--r-- 1 root root        38 Jul 31 18:30 relay-bin.index
     46 drwxr-x--- 2 root root      8192 Jul 31 18:29 sys
     47 drwxr-x--- 2 root root      4096 Jul 31 18:29 sysbench
     48 -rw-r----- 1 root root        21 Jul 31 18:29 xtrabackup_binlog_pos_innodb
     49 -rw-r----- 1 root root       595 Jul 31 18:29 xtrabackup_info
     50 -rw-r----- 1 root root         1 Jul 31 18:29 xtrabackup_master_key_id
     51 
     52 [root@zlm2 18:31:11 /data/mysql/mysql3306/data]
     53 #chown -R mysql.mysql *
     54 
     55 [root@zlm2 18:31:19 /data/mysql/mysql3306/data]
     56 #sh /root/mysqld.sh
     57 
     58 [root@zlm2 18:31:23 /data/mysql/mysql3306/data]
     59 #mysql
     60 Welcome to the MySQL monitor.  Commands end with ; or g.
     61 Your MySQL connection id is 2
     62 Server version: 5.7.21-log MySQL Community Server (GPL)
     63 
     64 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
     65 
     66 Oracle is a registered trademark of Oracle Corporation and/or its
     67 affiliates. Other names may be trademarks of their respective
     68 owners.
     69 
     70 Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
     71 
     72 zlm@192.168.56.101:3306 [(none)]>select count(*) from sysbench.sbtest5;
     73 +----------+
     74 | count(*) |
     75 +----------+
     76 |    10000 |
     77 +----------+
     78 1 row in set (0.04 sec)
     79 
     80 zlm@192.168.56.101:3306 [(none)]>show master status;
     81 +------------------+----------+--------------+------------------+-------------------------------------------------+
     82 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                               |
     83 +------------------+----------+--------------+------------------+-------------------------------------------------+
     84 | mysql-bin.000003 |      210 |              |                  | 2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715698 |
     85 +------------------+----------+--------------+------------------+-------------------------------------------------+
     86 1 row in set (0.00 sec)
     87 
     88 zlm@192.168.56.101:3306 [(none)]>reset master;
     89 Query OK, 0 rows affected (0.03 sec)
     90 
     91 zlm@192.168.56.101:3306 [(none)]>show master status;
     92 +------------------+----------+--------------+------------------+-------------------+
     93 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
     94 +------------------+----------+--------------+------------------+-------------------+
     95 | mysql-bin.000001 |      154 |              |                  |                   |
     96 +------------------+----------+--------------+------------------+-------------------+
     97 1 row in set (0.00 sec)
     98 
     99 zlm@192.168.56.101:3306 [(none)]>show slave statusG
    100 Empty set (0.00 sec)
    101 
    102 zlm@192.168.56.101:3306 [(none)]>set @@global.gtid_purged='2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715696';
    103 Query OK, 0 rows affected (0.00 sec)
    104 
    105 zlm@192.168.56.101:3306 [(none)]>show master status;
    106 +------------------+----------+--------------+------------------+-------------------------------------------------+
    107 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                               |
    108 +------------------+----------+--------------+------------------+-------------------------------------------------+
    109 | mysql-bin.000001 |      154 |              |                  | 2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715696 |
    110 +------------------+----------+--------------+------------------+-------------------------------------------------+
    111 1 row in set (0.00 sec)
    112 
    113 zlm@192.168.56.101:3306 [(none)]>change master to relay_log_file='relay-bin.000103',relay_log_pos=194,master_host='xxx';
    114 Query OK, 0 rows affected (0.01 sec)
    115 
    116 zlm@192.168.56.101:3306 [(none)]>show slave statusG
    117 *************************** 1. row ***************************
    118                Slave_IO_State: 
    119                   Master_Host: xxx
    120                   Master_User: 
    121                   Master_Port: 3306
    122                 Connect_Retry: 60
    123               Master_Log_File: 
    124           Read_Master_Log_Pos: 4
    125                Relay_Log_File: relay-bin.000103
    126                 Relay_Log_Pos: 194
    127         Relay_Master_Log_File: 
    128              Slave_IO_Running: No
    129             Slave_SQL_Running: No
    130               Replicate_Do_DB: 
    131           Replicate_Ignore_DB: 
    132            Replicate_Do_Table: 
    133        Replicate_Ignore_Table: 
    134       Replicate_Wild_Do_Table: 
    135   Replicate_Wild_Ignore_Table: 
    136                    Last_Errno: 0
    137                    Last_Error: 
    138                  Skip_Counter: 0
    139           Exec_Master_Log_Pos: 0
    140               Relay_Log_Space: 955366
    141               Until_Condition: None
    142                Until_Log_File: 
    143                 Until_Log_Pos: 0
    144            Master_SSL_Allowed: No
    145            Master_SSL_CA_File: 
    146            Master_SSL_CA_Path: 
    147               Master_SSL_Cert: 
    148             Master_SSL_Cipher: 
    149                Master_SSL_Key: 
    150         Seconds_Behind_Master: NULL
    151 Master_SSL_Verify_Server_Cert: No
    152                 Last_IO_Errno: 0
    153                 Last_IO_Error: 
    154                Last_SQL_Errno: 0
    155                Last_SQL_Error: 
    156   Replicate_Ignore_Server_Ids: 
    157              Master_Server_Id: 0
    158                   Master_UUID: 
    159              Master_Info_File: /data/mysql/mysql3306/data/master.info
    160                     SQL_Delay: 0
    161           SQL_Remaining_Delay: NULL
    162       Slave_SQL_Running_State: 
    163            Master_Retry_Count: 86400
    164                   Master_Bind: 
    165       Last_IO_Error_Timestamp: 
    166      Last_SQL_Error_Timestamp: 
    167                Master_SSL_Crl: 
    168            Master_SSL_Crlpath: 
    169            Retrieved_Gtid_Set: 
    170             Executed_Gtid_Set: 2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715696
    171                 Auto_Position: 0
    172          Replicate_Rewrite_DB: 
    173                  Channel_Name: 
    174            Master_TLS_Version: 
    175 1 row in set (0.00 sec)
    176 
    177 zlm@192.168.56.101:3306 [(none)]>start slave sql_thread until sql_before_gtids='2a4b3562-2ab6-11e8-be7a-080027de0e0e:12715699';
    178 Query OK, 0 rows affected (0.51 sec)
    179 
    180 zlm@192.168.56.101:3306 [(none)]>show slave statusG
    181 *************************** 1. row ***************************
    182                Slave_IO_State: 
    183                   Master_Host: xxx
    184                   Master_User: 
    185                   Master_Port: 3306
    186                 Connect_Retry: 60
    187               Master_Log_File: 
    188           Read_Master_Log_Pos: 4
    189                Relay_Log_File: relay-bin.000103
    190                 Relay_Log_Pos: 954755
    191         Relay_Master_Log_File: 
    192              Slave_IO_Running: No
    193             Slave_SQL_Running: No
    194               Replicate_Do_DB: 
    195           Replicate_Ignore_DB: 
    196            Replicate_Do_Table: 
    197        Replicate_Ignore_Table: 
    198       Replicate_Wild_Do_Table: 
    199   Replicate_Wild_Ignore_Table: 
    200                    Last_Errno: 0
    201                    Last_Error: 
    202                  Skip_Counter: 0
    203           Exec_Master_Log_Pos: 954755
    204               Relay_Log_Space: 955366
    205               Until_Condition: SQL_BEFORE_GTIDS //This time we use this option to start slave sql_thread.
    206                Until_Log_File: 
    207                 Until_Log_Pos: 0
    208            Master_SSL_Allowed: No
    209            Master_SSL_CA_File: 
    210            Master_SSL_CA_Path: 
    211               Master_SSL_Cert: 
    212             Master_SSL_Cipher: 
    213                Master_SSL_Key: 
    214         Seconds_Behind_Master: NULL
    215 Master_SSL_Verify_Server_Cert: No
    216                 Last_IO_Errno: 0
    217                 Last_IO_Error: 
    218                Last_SQL_Errno: 0
    219                Last_SQL_Error: 
    220   Replicate_Ignore_Server_Ids: 
    221              Master_Server_Id: 0
    222                   Master_UUID: 
    223              Master_Info_File: /data/mysql/mysql3306/data/master.info
    224                     SQL_Delay: 0
    225           SQL_Remaining_Delay: NULL
    226       Slave_SQL_Running_State: 
    227            Master_Retry_Count: 86400
    228                   Master_Bind: 
    229       Last_IO_Error_Timestamp: 
    230      Last_SQL_Error_Timestamp: 
    231                Master_SSL_Crl: 
    232            Master_SSL_Crlpath: 
    233            Retrieved_Gtid_Set: 
    234             Executed_Gtid_Set: 2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715698
    235                 Auto_Position: 0
    236          Replicate_Rewrite_DB: 
    237                  Channel_Name: 
    238            Master_TLS_Version: 
    239 1 row in set (0.00 sec)
    240 
    241 zlm@192.168.56.101:3306 [(none)]>select count(*) from sysbench.sbtest5;
    242 +----------+
    243 | count(*) |
    244 +----------+
    245 |     5000 |
    246 +----------+
    247 1 row in set (0.00 sec)
    
    1 zlm@192.168.56.100:3306 [sysbench]>show master status;
    2 +------------------+----------+--------------+------------------+-------------------------------------------------+
    3 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                               |
    4 +------------------+----------+--------------+------------------+-------------------------------------------------+
    5 | mysql-bin.000098 |      363 |              |                  | 2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715693 |
    6 +------------------+----------+--------------+------------------+-------------------------------------------------+
    7 1 row in set (0.00 sec)
    

     

     

    **    Ultimately,the incremental data comes back gain.The experiment was finished successfully.**

    **Make binlog server begin to receive binlog.**

     

     1 [root@zlm3 16:25:01 /data]
     2 #mysqlbinlog -R --raw -h192.168.56.100 -urepl -prepl4slave -P3306 --stop-never mysql-bin.000098 &
     3 [1] 4375
     4 mysqlbinlog: [Warning] Using a password on the command line interface can be insecure.
     5 
     6 [root@zlm3 16:26:24 /data]
     7 #ls -l
     8 total 4
     9 drwxr-xr-x 2 mysql mysql  32 Jun 10 03:41 backup
    10 drwxr-xr-x 3 mysql mysql  22 Mar 18 16:05 mysql
    11 -rw-r----- 1 root  root  363 Jul 29 16:26 mysql-bin.000098
    

     

     

    **Flush two logs on master.**

     1 zlm@192.168.56.100:3306 [sysbench]>flush logs;
     2 Query OK, 0 rows affected (0.06 sec)
     3 
     4 zlm@192.168.56.100:3306 [sysbench]>flush logs;
     5 Query OK, 0 rows affected (0.01 sec)
     6 
     7 zlm@192.168.56.100:3306 [sysbench]>show master status;
     8 +------------------+----------+--------------+------------------+-------------------------------------------------+
     9 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                               |
    10 +------------------+----------+--------------+------------------+-------------------------------------------------+
    11 | mysql-bin.000100 |      194 |              |                  | 2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715693 |
    12 +------------------+----------+--------------+------------------+-------------------------------------------------+
    13 1 row in set (0.00 sec)
    

     

    **Check whether the newly generated binlogs are successfully transmited to binlog server.**

    1 [root@zlm3 16:26:27 /data]
    2 #ls -l
    3 total 12
    4 drwxr-xr-x 2 mysql mysql  32 Jun 10 03:41 backup
    5 drwxr-xr-x 3 mysql mysql  22 Mar 18 16:05 mysql
    6 -rw-r----- 1 root  root  410 Jul 29 16:27 mysql-bin.000098
    7 -rw-r----- 1 root  root  241 Jul 29 16:27 mysql-bin.000099
    8 -rw-r----- 1 root  root  194 Jul 29 16:27 mysql-bin.000100
    

     

    Step 2: Destroy the table.

     

    Check target table on master.

     1 zlm@192.168.56.100:3306 [sysbench]>show tables;
     2 +--------------------+
     3 | Tables_in_sysbench |
     4 +--------------------+
     5 | sbtest1            |
     6 | sbtest2            |
     7 | sbtest3            |
     8 | sbtest4            |
     9 | sbtest5            |
    10 | sbtest6            |
    11 +--------------------+
    12 6 rows in set (0.00 sec)
    13 
    14 zlm@192.168.56.100:3306 [sysbench]>select count(*) from sbtest6;
    15 +----------+
    16 | count(*) |
    17 +----------+
    18 |        0 |
    19 +----------+
    20 1 row in set (0.00 sec)
    21 
    22 zlm@192.168.56.100:3306 [sysbench]>insert into sbtest6 values(1,1,'a','b');
    23 Query OK, 1 row affected (0.00 sec)
    24 
    25 zlm@192.168.56.100:3306 [sysbench]>select * from sbtest6;
    26 +----+---+---+-----+
    27 | id | k | c | pad |
    28 +----+---+---+-----+
    29 |  1 | 1 | a | b   |
    30 +----+---+---+-----+
    31 1 row in set (0.00 sec)
    

     

    Generate Xtrabackup backup set.

     1 [root@zlm1 16:32:14 ~]
     2 #innobackupex --defaults-file=/data/mysql/mysql3306/my3306.cnf -uroot -pPassw0rd /data/backup
     3 180729 16:32:20 innobackupex: Error: extra argument found -pPassw0rd
     4 180729 16:32:20 innobackupex: Error: extra argument found /data/backup
     5 
     6 [root@zlm1 16:32:20 ~]
     7 #innobackupex -v
     8 innobackupex version 2.4.4 Linux (x86_64) (revision id: df58cf2)
     9 
    10 [root@zlm1 16:32:26 ~]
    11 #innobackupex --defaults-file=/data/mysql/mysql3306/my3306.cnf --user=root --password=Passw0rd /data/backup
    12 180729 16:32:33 innobackupex: Starting the backup operation
    13 ...
    14 
    15 180729 16:32:53 Backup created in directory '/data/backup/2018-07-29_16-32-33'
    16 MySQL binlog position: filename 'mysql-bin.000100', position '476', GTID of the last change '2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715694'
    17 180729 16:32:53 [00] Writing backup-my.cnf
    18 180729 16:32:53 [00]        ...done
    19 180729 16:32:53 [00] Writing xtrabackup_info
    20 180729 16:32:53 [00]        ...done
    21 xtrabackup: Transaction log of lsn (1719676169) to (1719676178) was copied.
    22 180729 16:32:53 completed OK!
    

     

    Insert one record of "sbtest6" and drop it.

     1 zlm@192.168.56.100:3306 [sysbench]>insert into sbtest6 values(2,2,'c','d');
     2 Query OK, 1 row affected (0.00 sec)
     3 
     4 zlm@192.168.56.100:3306 [sysbench]>show master status;
     5 +------------------+----------+--------------+------------------+-------------------------------------------------+
     6 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                               |
     7 +------------------+----------+--------------+------------------+-------------------------------------------------+
     8 | mysql-bin.000100 |      758 |              |                  | 2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715695 |
     9 +------------------+----------+--------------+------------------+-------------------------------------------------+
    10 1 row in set (0.00 sec)
    11 
    12 zlm@192.168.56.100:3306 [sysbench]>drop tables sbtest6;
    13 Query OK, 0 rows affected (0.02 sec)
    14 
    15 zlm@192.168.56.100:3306 [sysbench]>show master status;
    16 +------------------+----------+--------------+------------------+-------------------------------------------------+
    17 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                               |
    18 +------------------+----------+--------------+------------------+-------------------------------------------------+
    19 | mysql-bin.000100 |      951 |              |                  | 2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715696 |
    20 +------------------+----------+--------------+------------------+-------------------------------------------------+
    21 1 row in set (0.00 sec)
    

     

    Shutdown the mysqld on master(mimic the MySQL instance down).

     1 [root@zlm1 16:56:54 /data/backup]
     2 #mysqladmin shutdown
     3 
     4 [root@zlm1 16:57:05 /data/backup]
     5 #ps aux|grep mysqld
     6 mysql     3804  0.0 20.7 1072396 210620 pts/0  Sl   15:52   0:02 mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf
     7 root      4512  0.0  0.0 112640   960 pts/2    R+   16:57   0:00 grep --color=auto mysqld
     8 
     9 [root@zlm1 16:57:10 /data/backup]
    10 #ps aux|grep mysqld
    11 mysql     3804  0.0 20.1 1038828 204780 pts/0  Sl   15:52   0:02 mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf
    12 root      4518  0.0  0.0 112640   956 pts/2    R+   16:57   0:00 grep --color=auto mysqld
    13 
    14 [root@zlm1 16:57:15 /data/backup]
    15 #mysqladmin shutdown
    16 mysqladmin: connect to server at '192.168.56.100' failed
    17 error: 'Can't connect to MySQL server on '192.168.56.100' (111)'
    18 Check that mysqld is running on 192.168.56.100 and that the port is 3306.
    19 You can check this by doing 'telnet 192.168.56.100 3306'
    20 
    21 [root@zlm1 16:57:23 /data/backup]
    22 #ps aux|grep mysqld
    23 root      4529  0.0  0.0 112640   960 pts/2    R+   16:57   0:00 grep --color=auto mysqld
    

     

    Copy backup set to zlm2.

     1 [root@zlm1 16:32:53 ~]
     2 #cd /data/backup/
     3 
     4 [root@zlm1 16:37:19 /data/backup]
     5 #ls -l
     6 total 4
     7 drwxr-x--- 7 root root 4096 Jul 29 16:32 2018-07-29_16-32-33
     8 
     9 [root@zlm1 16:37:24 /data/backup]
    10 #scp -r 2018-07-29_16-32-33 zlm2:/data/backup
    11 xtrabackup_logfile                                                                                                 100% 2560     2.5KB/s   00:00    
    12 ibdata1                                                                                                            100%  100MB  50.0MB/s   00:02
    13 ... //Omitted.
    

     

    *Step 3: Rescue data.**


     

    Prepare to restore the backup with "--apply-log" on zlm2.

     1 [root@zlm2 16:38:09 /data/backup]
     2 #ls -l
     3 total 4
     4 drwxr-x--- 7 root root 4096 Jul 29 16:37 2018-07-29_16-32-33
     5 
     6 [root@zlm2 16:38:12 /data/backup]
     7 #innobackupex -v
     8 xtrabackup: recognized server arguments: --datadir=/var/lib/mysql 
     9 innobackupex version 2.4.12 Linux (x86_64) (revision id: 170eb8c)
    10 
    11 [root@zlm2 16:39:13 /data/backup]
    12 #innobackupex --defaults-file=/data/mysql/mysql3306/my3306.cnf -uroot -pPassw0rd --apply-log /data/backup/2018-07-29_16-32-33/
    13 ... //Omitted.
    14 
    15 xtrabackup: starting shutdown with innodb_fast_shutdown = 1
    16 InnoDB: page_cleaner: 1000ms intended loop took 8812ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)
    17 InnoDB: FTS optimize thread exiting.
    18 InnoDB: Starting shutdown...
    19 InnoDB: Shutdown completed; log sequence number 1719676456
    20 180729 16:39:39 completed OK!
    

     

    本文由金沙游乐场85155发布于大数据库,转载请注明出处:恢复误删除表黑科技之relay log大法金沙游乐场8

    关键词:

上一篇:没有了

下一篇:Sybase:基本语言元素(对比sqlserver)