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

恢复误删除表黑科技之relay log大法

发布时间:2019-10-09 11:58编辑:大数据库浏览(186)

     

     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!
    
     1 [root@zlm3 06:47:52 ~]
     2 #innobackupex --defaults-file=/data/mysql/mysql3306/my.cnf --apply-log /data/backup/2018-07-31_06-29-03/
     3 ... //Omitted.
     4 
     5 [root@zlm3 06:46:39 ~]
     6 #cd /data/mysql/mysql3306/data/
     7 
     8 [root@zlm3 06:46:44 /data/mysql/mysql3306/data]
     9 #ls -l
    10 total 409716
    11 -rw-r----- 1 mysql mysql        56 Jul 27 11:15 auto.cnf
    12 -rw-r----- 1 mysql mysql     19677 Jul 27 11:25 error.log
    13 -rw-r----- 1 mysql mysql      2005 Jul 27 11:25 ib_buffer_pool
    14 -rw-r----- 1 mysql mysql 104857600 Jul 27 11:25 ibdata1
    15 -rw-r----- 1 mysql mysql 104857600 Jul 27 11:25 ib_logfile0
    16 -rw-r----- 1 mysql mysql 104857600 Jul 27 11:14 ib_logfile1
    17 -rw-r----- 1 mysql mysql 104857600 Jul 27 11:14 ib_logfile2
    18 drwxr-x--- 2 mysql mysql      4096 Jul 27 11:15 mysql
    19 drwxr-x--- 2 mysql mysql      8192 Jul 27 11:15 performance_schema
    20 -rw-r----- 1 mysql mysql       276 Jul 27 11:18 relay-bin.000003
    21 -rw-r----- 1 mysql mysql      2771 Jul 27 11:25 relay-bin.000004
    22 -rw-r----- 1 mysql mysql       292 Jul 27 11:25 relay-bin.000005
    23 -rw-r----- 1 mysql mysql       454 Jul 27 11:25 relay-bin.000006
    24 -rw-r----- 1 mysql mysql       344 Jul 27 11:25 relay-bin.000007
    25 -rw-r----- 1 mysql mysql       169 Jul 27 11:25 relay-bin-group_replication_applier.000001
    26 -rw-r----- 1 mysql mysql        45 Jul 27 11:15 relay-bin-group_replication_applier.index
    27 -rw-r----- 1 mysql mysql       169 Jul 27 11:25 relay-bin-group_replication_recovery.000001
    28 -rw-r----- 1 mysql mysql        46 Jul 27 11:15 relay-bin-group_replication_recovery.index
    29 -rw-r----- 1 mysql mysql        95 Jul 27 11:25 relay-bin.index
    30 -rw-r----- 1 mysql mysql       334 Jul 27 11:25 slow.log
    31 drwxr-x--- 2 mysql mysql      8192 Jul 27 11:15 sys
    32 drwxr-x--- 2 mysql mysql      4096 Jul 27 11:15 sysbench
    33 -rw-r----- 1 mysql mysql        24 Jul 27 11:15 xtrabackup_binlog_pos_innodb
    34 -rw-r----- 1 mysql mysql       587 Jul 27 11:15 xtrabackup_info
    35 -rw-r----- 1 mysql mysql         1 Jul 27 11:15 xtrabackup_master_key_id
    36 
    37 [root@zlm3 06:46:45 /data/mysql/mysql3306/data]
    38 #rm -rf *
    39 
    40 [root@zlm3 06:46:50 /data/mysql/mysql3306/data]
    41 #ps aux|grep mysqld
    42 root      3913  0.0  0.0 112640   960 pts/1    R+   06:50   0:00 grep --color=auto mysqld
    43 
    44 [root@zlm3 06:51:00 ~]
    45 #innobackupex --defaults-file=/data/mysql/mysql3306/my.cnf --copy-back /data/backup/2018-07-31_06-29-03/
    46 ... //Omitted.
    47 
    48 180731 06:51:36 [01] Copying ./ib_buffer_pool to /data/mysql/mysql3306/data/ib_buffer_pool
    49 180731 06:51:36 [01]        ...done
    50 180731 06:51:36 [01] Copying ./xtrabackup_info to /data/mysql/mysql3306/data/xtrabackup_info
    51 180731 06:51:36 [01]        ...done
    52 180731 06:51:36 [01] Copying ./xtrabackup_binlog_pos_innodb to /data/mysql/mysql3306/data/xtrabackup_binlog_pos_innodb
    53 180731 06:51:36 [01]        ...done
    54 180731 06:51:36 [01] Copying ./xtrabackup_master_key_id to /data/mysql/mysql3306/data/xtrabackup_master_key_id
    55 180731 06:51:36 [01]        ...done
    56 180731 06:51:36 [01] Copying ./ibtmp1 to /data/mysql/mysql3306/data/ibtmp1
    57 180731 06:51:36 [01]        ...done
    58 180731 06:51:36 completed OK!
    59 
    60 [root@zlm3 06:50:14 /data/mysql/mysql3306/data]
    61 #ls -l
    62 total 421936
    63 -rw-r----- 1 root root      1017 Jul 31 06:51 ib_buffer_pool
    64 -rw-r----- 1 root root 104857600 Jul 31 06:51 ibdata1
    65 -rw-r----- 1 root root 104857600 Jul 31 06:51 ib_logfile0
    66 -rw-r----- 1 root root 104857600 Jul 31 06:51 ib_logfile1
    67 -rw-r----- 1 root root 104857600 Jul 31 06:51 ib_logfile2
    68 -rw-r----- 1 root root  12582912 Jul 31 06:51 ibtmp1
    69 drwxr-x--- 2 root root      4096 Jul 31 06:51 mysql
    70 drwxr-x--- 2 root root      8192 Jul 31 06:51 performance_schema
    71 drwxr-x--- 2 root root      8192 Jul 31 06:51 sys
    72 drwxr-x--- 2 root root      4096 Jul 31 06:51 sysbench
    73 -rw-r----- 1 root root        22 Jul 31 06:51 xtrabackup_binlog_pos_innodb
    74 -rw-r----- 1 root root       600 Jul 31 06:51 xtrabackup_info
    75 -rw-r----- 1 root root         1 Jul 31 06:51 xtrabackup_master_key_id
    76 drwxr-x--- 2 root root       120 Jul 31 06:51 zlm
    77 
    78 [root@zlm3 06:53:49 /data/mysql/mysql3306/data]
    79 #chown -R mysql.mysql *
    

    Check target table on master.

    **Check out the first consistent position we need.**

     

     

     

    The precondition is that the master has a up-to-date full backup.(Xtrabackup or mysqldump)

     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 a full Xtrabackup of master.**

    Step 1: Create binlog server.

        Even thought the mysqld process is down on master.I still can get the binlog files on it.How about change the master binlog files into relay log files and apply them on zlm3?Let's have a try.

     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.
    

        The new procedure of demonstration was shown below:

    Preface

    Change the server-uuid of binlog server to be equal with master.

     

     

     

    Step 2: Destroy the table.

    Check the table on master.

    Shutdown instance and copy back.

    The simply description of rescuing data with relay log method is shown below:

     

    4. Reconfigure those binlog files with "relay-bin.xxxxxx" format together with relay-bin.index file.

     1 [root@zlm2 17:12:39 /data/backup/2018-07-29_16-32-33]
     2 #cat xtrabackup_binlog_info
     3 mysql-bin.000100    476    2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715694
     4 
     5 zlm@192.168.56.101:3306 [sysbench]>reset master;
     6 Query OK, 0 rows affected (0.00 sec)
     7 
     8 zlm@192.168.56.101:3306 [sysbench]>set @@global.gtid_purged='2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715694';
     9 Query OK, 0 rows affected (0.00 sec)
    10 
    11 zlm@192.168.56.101:3306 [sysbench]>start slave io_thread;
    12 Query OK, 0 rows affected (0.00 sec)
    13 
    14 zlm@192.168.56.101:3306 [sysbench]>show slave statusG
    15 *************************** 1. row ***************************
    16                Slave_IO_State: 
    17                   Master_Host: 192.168.56.102
    18                   Master_User: repl
    19                   Master_Port: 3306
    20                 Connect_Retry: 60
    21               Master_Log_File: 
    22           Read_Master_Log_Pos: 4
    23                Relay_Log_File: relay-bin.000001
    24                 Relay_Log_Pos: 4
    25         Relay_Master_Log_File: 
    26              Slave_IO_Running: No
    27             Slave_SQL_Running: No
    28               Replicate_Do_DB: 
    29           Replicate_Ignore_DB: 
    30            Replicate_Do_Table: 
    31        Replicate_Ignore_Table: 
    32       Replicate_Wild_Do_Table: 
    33   Replicate_Wild_Ignore_Table: 
    34                    Last_Errno: 0
    35                    Last_Error: 
    36                  Skip_Counter: 0
    37           Exec_Master_Log_Pos: 0
    38               Relay_Log_Space: 154
    39               Until_Condition: None
    40                Until_Log_File: 
    41                 Until_Log_Pos: 0
    42            Master_SSL_Allowed: No
    43            Master_SSL_CA_File: 
    44            Master_SSL_CA_Path: 
    45               Master_SSL_Cert: 
    46             Master_SSL_Cipher: 
    47                Master_SSL_Key: 
    48         Seconds_Behind_Master: NULL
    49 Master_SSL_Verify_Server_Cert: No
    50                 Last_IO_Errno: 1236
    51                 Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.'
    52                Last_SQL_Errno: 0
    53                Last_SQL_Error: 
    54   Replicate_Ignore_Server_Ids: 
    55              Master_Server_Id: 1023306
    56                   Master_UUID: e00ef9f5-6c4b-11e8-8389-080027de0e0e
    57              Master_Info_File: /data/mysql/mysql3306/data/master.info
    58                     SQL_Delay: 0
    59           SQL_Remaining_Delay: NULL
    60       Slave_SQL_Running_State: 
    61            Master_Retry_Count: 86400
    62                   Master_Bind: 
    63       Last_IO_Error_Timestamp: 180729 18:00:08
    64      Last_SQL_Error_Timestamp: 
    65                Master_SSL_Crl: 
    66            Master_SSL_Crlpath: 
    67            Retrieved_Gtid_Set: 
    68             Executed_Gtid_Set: 2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715694
    69                 Auto_Position: 1
    70          Replicate_Rewrite_DB: 
    71                  Channel_Name: 
    72            Master_TLS_Version: 
    73 1 row in set (0.00 sec)
    

     

    1 set @@global.gtid_purged='27af30ca-6800-11e8-ad7e-080027de0e0e:1,
    2 2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715694,
    3 e00ef9f5-6c4b-11e8-8389-080027de0e0e:1';
    

     

     

    Restore the backup on zlm3.

     

     

     

     

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

     

    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)
    
    1 [root@zlm3 08:34:15 /data/backup/2018-07-31_06-29-03]
    2 #cat xtrabackup_binlog_info
    3 mysql-bin.000043    190    1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730229
    

     

    **Copy these relay-bin files to the proper directory.**

    Framework

     

     

     

    Insert one record of "sbtest6" and drop it.

    *5. Copy **those relay logs** to the proper datadir and c*hange the ownership and group of them.**

    Copy backup set to zlm2.

        I was stuck again,faint!T_T...

     

    **Step 2. Rescue***

     1 [root@zlm3 16:50:00 /data/mysql/mysql3306/logs]
     2 #ps aux|grep mysqld
     3 root      4405  0.0  0.0 112640   960 pts/0    R+   16:50   0:00 grep --color=auto mysqld
     4 
     5 [root@zlm3 16:50:08 /data/mysql/mysql3306/logs]
     6 #sh /root/mysqld.sh
     7 
     8 [root@zlm3 16:50:13 /data/mysql/mysql3306/logs]
     9 #ps aux|grep mysqld
    10 mysql     4411 16.5 15.6 498232 159408 pts/0   Rl   16:50   0:00 mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf
    11 root      4429  0.0  0.0 112640   960 pts/0    R+   16:50   0:00 grep --color=auto mysqld
    12 
    13 [root@zlm3 16:51:03 /data/mysql/mysql3306/logs]
    14 #ls -l
    15 total 11216
    16 -rw-r----- 1 root  root      4128 Jul 29 08:42 mysql-bin.000095
    17 -rw-r----- 1 root  root       241 Jul 29 08:42 mysql-bin.000096
    18 -rw-r----- 1 root  root  11461562 Jul 29 09:07 mysql-bin.000097
    19 -rw-r----- 1 mysql mysql      154 Jul 29 16:50 mysql-bin.000098
    20 -rw-r----- 1 mysql mysql       44 Jul 29 16:50 mysql-bin.index
    21 
    22 [root@zlm3 16:51:04 /data/mysql/mysql3306/logs]
    23 #cat mysql-bin.index
    24 /data/mysql/mysql3306/logs/mysql-bin.000098
    

     

        The previous fault of 1236 was due to the wrong configuration of variable "gtid_purged".There were two individual gtids generated by the other masters with different uuid as bellow:

     

    Preface

     1 (zlm@192.168.1.102 3306)[(none)]>change master to relay_log_file='relay-bin.000043',relay_log_pos=190,master_host='xxx';
     2 Query OK, 0 rows affected (0.01 sec)
     3 
     4 (zlm@192.168.1.102 3306)[(none)]>show slave statusG
     5 *************************** 1. row ***************************
     6                Slave_IO_State: 
     7                   Master_Host: xxx
     8                   Master_User: 
     9                   Master_Port: 3306
    10                 Connect_Retry: 60
    11               Master_Log_File: 
    12           Read_Master_Log_Pos: 4
    13                Relay_Log_File: relay-bin.000043
    14                 Relay_Log_Pos: 190
    15         Relay_Master_Log_File: 
    16              Slave_IO_Running: No
    17             Slave_SQL_Running: No
    18               Replicate_Do_DB: 
    19           Replicate_Ignore_DB: 
    20            Replicate_Do_Table: 
    21        Replicate_Ignore_Table: 
    22       Replicate_Wild_Do_Table: 
    23   Replicate_Wild_Ignore_Table: 
    24                    Last_Errno: 0
    25                    Last_Error: 
    26                  Skip_Counter: 0
    27           Exec_Master_Log_Pos: 0
    28               Relay_Log_Space: 954838
    29               Until_Condition: None
    30                Until_Log_File: 
    31                 Until_Log_Pos: 0
    32            Master_SSL_Allowed: No
    33            Master_SSL_CA_File: 
    34            Master_SSL_CA_Path: 
    35               Master_SSL_Cert: 
    36             Master_SSL_Cipher: 
    37                Master_SSL_Key: 
    38         Seconds_Behind_Master: NULL
    39 Master_SSL_Verify_Server_Cert: No
    40                 Last_IO_Errno: 0
    41                 Last_IO_Error: 
    42                Last_SQL_Errno: 0
    43                Last_SQL_Error: 
    44   Replicate_Ignore_Server_Ids: 
    45              Master_Server_Id: 0
    46                   Master_UUID: 
    47              Master_Info_File: mysql.slave_master_info
    48                     SQL_Delay: 0
    49           SQL_Remaining_Delay: NULL
    50       Slave_SQL_Running_State: 
    51            Master_Retry_Count: 86400
    52                   Master_Bind: 
    53       Last_IO_Error_Timestamp: 
    54      Last_SQL_Error_Timestamp: 
    55                Master_SSL_Crl: 
    56            Master_SSL_Crlpath: 
    57            Retrieved_Gtid_Set: 
    58             Executed_Gtid_Set: 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730229
    59                 Auto_Position: 0
    60          Replicate_Rewrite_DB: 
    61                  Channel_Name: 
    62            Master_TLS_Version: 
    63 1 row in set (0.00 sec)
    

     

        Les't see another way to achieve the goal more simply.

     

     

     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
    

     

    Supplemented on July 31:

    Execute start slave sql_thread util clause.

    Generate Xtrabackup backup set.

     1 [root@zlm3 04:11:50 /data/mysql/mysql3306/data]
     2 #cd /data/backup/
     3 
     4 [root@zlm3 04:12:17 /data/backup]
     5 #mysqlbinlog --base64-output=decode-rows relay-bin.000043 > 43.log
     6 
     7 [root@zlm3 04:12:47 /data/backup]
     8 #tail -20 43.log
     9 #180731  6:34:54 server id 1013306  end_log_pos 954224     Delete_rows: table id 222 flags: STMT_END_F
    10 # at 954224
    11 #180731  6:34:54 server id 1013306  end_log_pos 954251     Xid = 58
    12 COMMIT/*!*/;
    13 # at 954251
    14 #180731  6:35:09 server id 1013306  end_log_pos 954312     GTID    last_committed=2    sequence_number=3    rbr_only=no
    15 SET @@SESSION.GTID_NEXT= '1b7181ee-6eaf-11e8-998e-080027de0e0e:3730232'/*!*/;
    16 # at 954312
    17 #180731  6:35:09 server id 1013306  end_log_pos 954436     Query    thread_id=13    exec_time=0    error_code=0
    18 use `sysbench`/*!*/;
    19 SET TIMESTAMP=1533011709/*!*/;
    20 DROP TABLE `sbtest5` /* generated by server */ //Here's the dropping operation.Therefore,the util position we need is "954251" which just below the "COMMIT/*!*/;"
    21 /*!*/;
    22 # at 954436
    23 #180731  6:35:29 server id 1013306  end_log_pos 954479     Rotate to mysql-bin.000044  pos: 4
    24 SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
    25 DELIMITER ;
    26 # End of log file
    27 /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
    28 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
    

     

     

    *Step 4: **Implement a slave with binlog server.***

    6. Execute "change master to ... " with "relay_log_file" and "relay_log_pos".

     

        After discussing with my classmate Shuaibing Zhang,I found out that the reason why I got failure above was due to not executing "reset slave all;".Therefore,it meantioned that "Could not find the first log..." when I Executed "change master to ... ".

        Therefore,the configuration command should be executed like this:

     

    Restart instance and check table.

    **Transfer the mysql-bin files into relay-bin files.**

     

    **    The data in Xtrabackup of master has been restored on zlm3. *Notice,it doesn't contain the operations of deletion 5000 rows. Firstly,I supposed that the mysqld has crashed and it can never start again. Secondly,I don't have binlog server any more this time.Is *there any other way to restore the dropping table and guarantee the change will not lose on it?**How can we restore the data safely and simply?Surely there is.**

    Check the position on master 

    •     full mysqldump backup + binlog on master(master was normally running)
    •     full Xtrabackup backup + binlog on master(master was normally running)**
    •     full Xtrabackup backup + binlog on binlog server( binlog server had acquired binlogs before master crashed.)
     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!
    

     

     

    7. Execute "change replication filter ... " if you're supposed to merely restore a single table.(optional)

    • Binlog server act as a master in this experiment.It can prevent data loss such as miss opeartion of dropping tables whenever the master crashes.
    • We can implement as many binlog servers as possible to continuously acquire the binlogs on different masters.
    • If the only purpose is to get back the dropping data,replication filter of "replicate_do_table" is not necessary.
    • The modification of "sever-uuid" is also neglectable operation.Each gtid contains a differten uuid is completely acceptable.It won't influence our replication except for a little bit complex in output.
    1. Destroy***

     

     

     

    **Scp the Xtrabackup backup to another server zlm3 with newly initialized instance**

    Precedure

    3. Execute "reset slave all;" to clear the original replication information(restored from master).**

     

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

    关键词:

上一篇:《mysql必知必会》学习_第六章_20180730_欢

下一篇:没有了