使用伪master+binlog恢复数据

实验描述：在3317实例下通过伪master（3318实例）恢复master（3316实例）数据。

1.3317恢复最近一次全备并记录全备位置：

[root@bogon data]# innobackupex --apply-log /tmp/backup/

xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 369765910
160728 18:19:55 completed OK!

[root@bogon data]# innobackupex --defaults-file=/usr/local/mysql/my3317.cnf --move-back /tmp/backup/

[root@bogon backup]# cat xtrabackup_binlog_info
mysql-bin.000001 120

2.3317实例恢复之前数据：

root@localhost:mysql3317.sock  18:39:16 [zhangshuo]>select * from zs;
+------+-----------+
| id   | name      |
+------+-----------+
|    1 | zhangshuo |
+------+-----------+
1 row in set (0.04 sec)

3.3316实例写入数据：

root@localhost:mysql3316.sock  18:42:07 [zhangshuo]>show create table zs;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    16
Current database: zhangshuo

+-------+--------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                             |
+-------+--------------------------------------------------------------------------------------------------------------------------+
| zs    | CREATE TABLE `zs` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.06 sec)

root@localhost:mysql3316.sock  18:42:33 [zhangshuo]>insert into zs(id,name) values(2,'zhangjie');
Query OK, 1 row affected (0.10 sec)

root@localhost:mysql3316.sock  18:43:07 [zhangshuo]>select * from zs;
+------+-----------+
| id   | name      |
+------+-----------+
|    1 | zhangshuo |
|    2 | zhangjie  |
+------+-----------+
2 rows in set (0.00 sec)

4.将3316实例下的所有binlog拷贝到伪master3318，并创建复制账号：

[root@bogon /]# cd /data/mysql/mysql3316/logs/
[root@bogon logs]# cp -p mysql-bin.* /data/mysql/mysql3318/logs/

root@localhost:mysql3318.sock  19:13:55 [(none)]>grant replication slave on *.* to 'repl'@'%' identified by 'repl';

5.修改binlog.index文件，因为mysql会根据binlog.index索引文件读取binary log。

[root@bogon logs]# cat mysql-bin.index 
/data/mysql/mysql3318/logs/mysql-bin.000001

[root@bogon logs]# chown mysql:mysql *

6.编写change master to 伪master3318。

root@localhost:mysql3317.sock  19:19:20 [zhangshuo]>change master to master_host='192.168.1.113',master_user='repl',master_password='repl',master_port=3318,master_log_file='mysql-bin.000001',master_log_pos=120;
Query OK, 0 rows affected, 2 warnings (0.08 sec)

root@localhost:mysql3317.sock  19:20:09 [zhangshuo]>start slave;
Query OK, 0 rows affected (0.06 sec)

root@localhost:mysql3317.sock  19:21:38 [zhangshuo]>show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.113
                  Master_User: repl
                  Master_Port: 3318
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 318
               Relay_Log_File: mysql-relay-bin.000003
                Relay_Log_Pos: 481
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 318
              Relay_Log_Space: 1028
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1283318
                  Master_UUID: eaf59638-54b2-11e6-bb2a-000c29111e54
             Master_Info_File: /data/mysql/mysql3317/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
1 row in set (0.00 sec)

7.验证数据是否恢复：

root@localhost:mysql3317.sock  19:22:01 [zhangshuo]>select * from zs;
+------+-----------+
| id   | name      |
+------+-----------+
|    1 | zhangshuo |
|    2 | zhangjie  |
+------+-----------+
2 rows in set (0.00 sec)

至此，数据恢复成功。