mysql5.7基于位置position的恢复

1.全备之前查前当前数据库的状态：

mysql> drop table ljgtest1;
Query OK, 0 rows affected (0.03 sec)

mysql> create table ljgtest1(id int);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into ljgtest1 values(10);
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)

2.备份数据库

mysql> flush table with read lock;
Query OK, 0 rows affected (0.00 sec)

开启另外一个ssh备份数据库，备份结束后需要相看一下当前的position，以便做为恢复的起点：

[root@qht131 backup]# mysqldump -uroot -p -R --single-transaction --master-data=2 l5m > l5m.sql
Enter password:
[root@qht131 backup]# date
Wed Apr 18 19:54:25 CST 2018

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql_bin.000007 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

3.对表t1做些修改

mysql> use l5m
Database changed
mysql> select * from ljgtest1;
+------+
| id   |
+------+
|   10 |
+------+
1 row in set (0.00 sec)

mysql> insert into ljgtest1 values(20);
Query OK, 1 row affected (0.01 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> update ljgtest1 set id=50 where id=20;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

#flush logs一下，重新产生一个binlog文件记录一条新的数据。
mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into ljgtest1 values(100);
Query OK, 1 row affected (0.00 sec)

mysql> select * from ljgtest1;
+------+
| id   |
+------+
|   10 |
|   50 |
|  100 |
+------+
3 rows in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

[root@qht131 mysql]# date
Wed Apr 18 21:32:53 CST 2018

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql_bin.000001 |      1025 |
| mysql_bin.000002 |       767 |
| mysql_bin.000003 |  90661262 |
| mysql_bin.000004 |       553 |
| mysql_bin.000005 |      4906 |
| mysql_bin.000006 |      5972 |
| mysql_bin.000007 |       721 |
| mysql_bin.000008 |       411 |
+------------------+-----------+
8 rows in set (0.00 sec)

4.删除ljgtest1,并建立ljgtest2

mysql> drop table ljgtest1;
Query OK, 0 rows affected (0.02 sec)

mysql> flush logs;  #再做一次flush logs
Query OK, 0 rows affected (0.00 sec)

mysql> create table ljgtest2 (id int);
Query OK, 0 rows affected (0.06 sec)

5.开始恢复数据库，恢复的数据库最好开启skip-networking并且关闭binlog，第一：节省恢复的时间，第二：如果恢复的数据库就是原库的话，恢复时产生的binlog会与之前生产过程中产生的binlog重复。

[root@qht131 mysql]# cat /etc/my.cnf
。。。
skip-networking = 1
#log_bin = /u01/log/mysql/mysql_bin

[root@qht131 mysql]#  service mysql restart
Shutting down MySQL..                                      [  OK  ]
Starting MySQL.                                            [  OK  ]

6.由于这次测试的是基于position的恢复，恢复时需要跳过有害的drop操作，后面的事务需要继续应用到，所以干净的恢复到全备的状态需要删除l5m数据库并重建。

mysql> drop database l5m;
Query OK, 2 rows affected (0.10 sec)

mysql> create database l5m;
Query OK, 1 row affected (0.00 sec)

[root@qht131 backup]# mysql -uroot -p l5m < /u01/backup/l5m.sql

mysql> use l5m
Database changed
mysql> select * from ljgtest1;
+------+
| id   |
+------+
|   10 |
+------+
1 row in set (0.00 sec)

mysql> show tables;
+---------------+
| Tables_in_l5m |
+---------------+
| ljgtest1      |
+---------------+
1 row in set (0.00 sec)

已恢复到全备的状态了

接着恢复全备以后的所有事务，需要先找到drop操作在哪个binlog。

[root@qht131 mysql]# mysqlbinlog mysql_bin.000007 | grep -n 'DROP TABLE'
[root@qht131 mysql]# mysqlbinlog mysql_bin.000008 | grep -n 'DROP TABLE'
50:DROP TABLE `ljgtest1` /* generated by server */

在mysql_bin_00008上面进行的drop操作，先要应用全备以来mysql_bin_00007的所有操作。

[root@qht131 mysql]# mysqlbinlog  mysql_bin.000007

# at 674
#180418 21:30:58 server id 10000  end_log_pos 721 CRC32 0xa903ce19      Rotate to mysql_bin.000008  pos: 4
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*/;

--stop-postion如果不指定的话应该默认会恢复到文件底部，这个没有测试过。

[root@qht131 mysql]# mysqlbinlog --start-position=154 --stop-position=721 /u01/log/mysql/mysql_bin.000007 > /u01/log/mysql/07.sql
[root@qht131 mysql]# mysql -uroot -p -D l5m < /u01/log/mysql/07.sql

已恢复到第一次flush logs的状态了。

mysql> use l5m;
Database changed
mysql> select * from ljgtest1;
+------+
| id   |
+------+
|   10 |
|   50 |
+------+
2 rows in set (0.00 sec)

接着恢复drop之前的状态

[root@qht131 mysql]# mysqlbinlog mysql_bin.000008 | grep -n ljgtest1
32:#180418 21:31:07 server id 10000  end_log_pos 340 CRC32 0x976ab741   Table_ma                                                             p: `l5m`.`ljgtest1` mapped to number 166

[root@qht131 mysql]# mysqlbinlog mysql_bin.000008 | sed -n '32,100p'
#180418 21:31:07 server id 10000  end_log_pos 340 CRC32 0x976ab741      Table_map: `l5m`.`ljgtest1` mapped to number 166
# at 340
#180418 21:31:07 server id 10000  end_log_pos 380 CRC32 0xda6d16b5      Write_rows: table id 166 flags: STMT_END_F


BINLOG '
m0jXWhMQJwAAMgAAAFQBAAAAAKYAAAAAAAEAA2w1bQAIbGpndGVzdDEAAQMAAUG3apc=
m0jXWh4QJwAAKAAAAHwBAAAAAKYAAAAAAAEAAgAB//5kAAAAtRZt2g==
'/*!*/;
# at 380
#180418 21:31:10 server id 10000  end_log_pos 411 CRC32 0xa635fc14      Xid = 999
COMMIT/*!*/;
# at 411
#180418 21:38:12 server id 10000  end_log_pos 476 CRC32 0x2f4c9086      Anonymous_GTID  last_committed=1        sequence_number=2       rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 476
#180418 21:38:12 server id 10000  end_log_pos 595 CRC32 0xe2b16b1c      Query   thread_id=39    exec_time=0     error_code=0
use `l5m`/*!*/;
SET TIMESTAMP=1524058692/*!*/;
DROP TABLE `ljgtest1` /* generated by server */
/*!*/;
# at 595
#180418 21:38:28 server id 10000  end_log_pos 642 CRC32 0xb7f90e76      Rotate to mysql_bin.000009  pos: 4
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file

通过mysqlbinlog发现drop操作是在#476的位置操作的，开始恢复到#476之前的操作(#411)

[root@qht131 mysql]# mysqlbinlog --start-position=4 --stop-position=411 /u01/log/mysql/mysql_bin.000008 > /u01/log/mysql/08_01.sql
[root@qht131 mysql]# mysql -uroot -p -D l5m < /u01/log/mysql/08_01.sql

mysql> select * from ljgtest1;
+------+
| id   |
+------+
|   10 |
|   50 |
|  100 |
+------+
3 rows in set (0.00 sec)

最后恢复drop操作之后的操作，由于drop之后直接flush logs了，所以后面的操作直接从mysql_bin.00009开始恢复

[root@qht131 mysql]# mysqlbinlog mysql_bin.000009 > 09.sql
[root@qht131 mysql]# vi 09.sql
[root@qht131 mysql]# mysql -uroot -p -D l5m < 09.sql

mysql> show tables;
+---------------+
| Tables_in_l5m |
+---------------+
| ljgtest1      |
| ljgtest2      |
+---------------+
2 rows in set (0.00 sec)

表ljgtest2也恢复出来了，至此所有恢复操作已完成。

最后不要忘记修改/etc/my.cnf，打开网络以及开启binlog.

[root@qht131 mysql]# cat /etc/my.cnf
。。。
#skip-networking = 1
log_bin = /u01/log/mysql/mysql_bin
[root@qht131 mysql]#  service mysql restart
Shutting down MySQL..                                      [  OK  ]
Starting MySQL.                                            [  OK  ]