MySQL不完全恢复

不完全恢复

通过做Slave恢复

找到问题语句的位置,这里是1315

1
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 master

1
2
3
4
5
6
7
8
9
10
change 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
14
mysql-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
6
change master to
master_host='10.4.3.200',
master_user='repl',
master_password='repl',
master_port=3306;
不要加master_auto_position

关库
1
shutdown

主库binlog发过来,改名字为relay

1
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_thread

1
2
3
4
5
6
7
8
root@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 |
+------------------+----------+--------------+------------------+---------------------------------------------+

Powered by Hexo and Hexo-theme-hiker

Copyright © 2013 - 2022 Fan() All Rights Reserved.

访客数 : | 访问量 :