不完全恢复
通过做Slave恢复
找到问题语句的位置,这里是13151
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16#170911 14:42:19 server id 330641 end_log_pos 2508 CRC32 0xf697b7bb Xid = 215
COMMIT/*!*/;
# at 2508
#170911 14:50:56 server id 330641 end_log_pos 2573 CRC32 0x8309dea0 GTID last_committed=9 sequence_number=10
SET @@SESSION.GTID_NEXT= '5c351518-78ec-11e7-8e7a-005056a610c3:1315'/*!*/;
# at 2573
#170911 14:50:56 server id 330641 end_log_pos 2693 CRC32 0xb06b9074 Query thread_id=25 exec_time=1 error_code=0
use `fandb`/*!*/;
SET TIMESTAMP=1505112656/*!*/;
DROP TABLE `users` /* generated by server */
/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
那一份备份,恢复出来,启动,reset slave all;然后change master1
2
3
4
5
6
7
8
9
10change master to
master_host='10.4.3.100',
master_user='repl',
master_password='repl',
master_port=3306,
master_auto_position=1;
重点
start slave sql_thread until SQL_BEFORE_GTIDS='5c351518-78ec-11e7-8e7a-005056a610c3:1315';
start slave io_thread;
伪Master恢复
找一个备份,恢复,启动1
2
3
4
5
6
7
8
9
10
11
12
13
14mysql-bin.000005 194 5c351518-78ec-11e7-8e7a-005056a610c3:1-1298
innobackupex --copy-back .
[root@uz22199 full]# mysqld_safe &
root@mysqldb 17:41: [(none)]> reset slave all;
Query OK, 0 rows affected (0.03 sec)
root@mysqldb 17:43: [(none)]> show master status;
+------------------+----------+--------------+------------------+---------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+---------------------------------------------+
| mysql-bin.000001 | 154 | | | 5c351518-78ec-11e7-8e7a-005056a610c3:1-1298 |
+------------------+----------+--------------+------------------+---------------------------------------------+
1 row in set (0.00 sec)
随便change master一下,目的让它知道自己是从库(要不没有relay log info)1
2
3
4
5
6change master to
master_host='10.4.3.200',
master_user='repl',
master_password='repl',
master_port=3306;
不要加master_auto_position
关库1
shutdown
主库binlog发过来,改名字为relay1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29[root@test43100 bak]# scp relay.0000* 10.4.1.45:/data/mysqldata/3306/data/
root@10.4.1.45's password:
relay.000005 100% 495 0.5KB/s 00:00
relay.000006 100% 476 0.5KB/s 00:00
relay.000007 100% 241 0.2KB/s 00:00
relay.000008 100% 1003 1.0KB/s 00:00
relay.000009 100% 241 0.2KB/s 00:00
relay.000010 100% 495 0.5KB/s 00:00
relay.000011 100% 495 0.5KB/s 00:00
relay.000012 100% 2693 2.6KB/s 00:00
You have mail in /var/spool/mail/root
for i in `ls relay.0*` ; do echo "./"$i>>relay.index; done
[root@uz22199 data]# >relay.index
[root@uz22199 data]# for i in `ls relay.0*` ; do echo "./"$i>>relay.index; done
[root@uz22199 data]# more relay.index
./relay.000001
./relay.000005
./relay.000006
./relay.000007
./relay.000008
./relay.000009
./relay.000010
./relay.000011
./relay.000012
[root@uz22199 data]# chown mysql:mysql relay.*
启动,注意–relay-log-recovery=0
否则报错
Error during –relay-log-recovery: Could not locate rotate event from the master.
1 | [root@uz22199 full]# mysqld_safe --relay-log-recovery=0 & |
再change master一次,目的告诉他relay文件和位置1
change master to relay_log_file='relay.000001' , relay_log_pos=4;
启动sql_thread1
2
3
4
5
6
7
8root@mysqldb 17:52: [(none)]> start slave sql_thread until SQL_BEFORE_GTIDS='5c351518-78ec-11e7-8e7a-005056a610c3:1315';
root@mysqldb 17:52: [(none)]> show master status;
+------------------+----------+--------------+------------------+---------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+---------------------------------------------+
| mysql-bin.000003 | 4121 | | | 5c351518-78ec-11e7-8e7a-005056a610c3:1-1314 |
+------------------+----------+--------------+------------------+---------------------------------------------+