MySQL基于位置的恢复

mydb库中有四张表分别是load_test_A, load_test_B, load_test_C, load_test_D且表中的数据一致

mysql> select * from load_test_A;
+------------+------+------+------+------+------+------+------+
| col1       | col2 | col3 | col4 | col5 | col6 | col7 | col8 |
+------------+------+------+------+------+------+------+------+
| 2019-03-12 |    1 |    1 |    1 |    1 |    1 |  1.0 | 1    |
| 2019-03-12 |    2 |    2 |    2 |    2 |    2 |  2.0 | 2    |
| 2019-03-12 |    3 |    3 |    3 |    3 |    3 |  3.0 | 3    |
| 2019-03-12 |    4 |    4 |    4 |    4 |    4 |  4.0 | 4    |
+------------+------+------+------+------+------+------+------+

事件模拟

00:00点备份(完全逻辑备份)
###>>>
mysqldump mydb > mydb.sql
mysql -e 'FLUSH TABLES WITH READ LOCK;SHOW MASTER STATUS;UNLOCK TABLES'
mysql-bin.000015    154        #加读锁,记录bin-log位置(也可以使用FLUSH LOGS生成新得bin-log),解锁                    

            

01:00点发生事件
更新表C的一条记录 UPDATE
###>>>
UPDATE mydb.load_test_C SET col8='更新' WHERE col8=1;
mysql> select * from load_test_C;
+------------+------+------+------+------+------+------+--------+
| col1       | col2 | col3 | col4 | col5 | col6 | col7 | col8   |
+------------+------+------+------+------+------+------+--------+
| 2019-03-12 |    1 |    1 |    1 |    1 |    1 |  1.0 | 更新   |
| 2019-03-12 |    2 |    2 |    2 |    2 |    2 |  2.0 | 2      |
| 2019-03-12 |    3 |    3 |    3 |    3 |    3 |  3.0 | 3      |
| 2019-03-12 |    4 |    4 |    4 |    4 |    4 |  4.0 | 4      |
+------------+------+------+------+------+------+------+--------+

02:00点发生事件
误删表DROP TABLE 表A
###>>>
DROP TABLE mydb.load_test_A;

03:00点发生事件
向表B插入了1条数据 INSERT
###>>>
INSERT INTO mydb.load_test_B VALUES('2019-03-12',5,5,5,5,5,5,'更新');
mysql> select * from load_test_B;
+------------+------+------+------+------+------+------+--------+
| col1       | col2 | col3 | col4 | col5 | col6 | col7 | col8   |
+------------+------+------+------+------+------+------+--------+
| 2019-03-12 |    1 |    1 |    1 |    1 |    1 |  1.0 | 1      |
| 2019-03-12 |    2 |    2 |    2 |    2 |    2 |  2.0 | 2      |
| 2019-03-12 |    3 |    3 |    3 |    3 |    3 |  3.0 | 3      |
| 2019-03-12 |    4 |    4 |    4 |    4 |    4 |  4.0 | 4      |
| 2019-03-12 |    5 |    5 |    5 |    5 |    5 |  5.0 | 更新   |
+------------+------+------+------+------+------+------+--------+

04:00点发生事件
更新表D的一条记录 UPDATE
###>>>
UPDATE mydb.load_test_D SET col8='更新D' WHERE col8=1;
mysql> select * from load_test_D;
+------------+------+------+------+------+------+------+---------+
| col1       | col2 | col3 | col4 | col5 | col6 | col7 | col8    |
+------------+------+------+------+------+------+------+---------+
| 2019-03-12 |    1 |    1 |    1 |    1 |    1 |  1.0 | 更新D   |
| 2019-03-12 |    2 |    2 |    2 |    2 |    2 |  2.0 | 2       |
| 2019-03-12 |    3 |    3 |    3 |    3 |    3 |  3.0 | 3       |
| 2019-03-12 |    4 |    4 |    4 |    4 |    4 |  4.0 | 4       |
+------------+------+------+------+------+------+------+---------+

06:00点时发现误删了表A,但是又要对其他更新的数据保留

开始恢复：

先禁止所有连接,并且关闭bin-log
(如果开启bin-log,那么在全量恢复时也会被bin-log记录,然后在记录时也会记录全量恢复的bin-log,重放bin-log时也会重放这部分,从而导致失败
可能还有一种办法,就是在全量恢复前记录bin-log位置,然后重复时,重放至这个位置)

vim /etc/my.cnf
skip-networking=1
#log-bin = mysql-bin

全量恢复
mysql mydb < mydb.sql

查看bin-log
[root@bogon ~]# mysqlbinlog --database=mydb /usr/local/mysql/data/mysql-bin.000015 | grep -i -B10 'DROP TABLE `load_test_A`'
WARNING: The option --database has been used. It may filter parts of transactions, but will include the GTIDs in any case. If you want to exclude or include transactions, you should use the options --exclude-gtids or --include-gtids, respectively, instead.
# at 443
#190313  9:15:39 server id 140  end_log_pos 474 CRC32 0x435f8f35     Xid = 241
COMMIT/*!*/;
# at 474
#190313  9:15:47 server id 140  end_log_pos 539 CRC32 0x2763ed8b     Anonymous_GTID    last_committed=1    sequence_number=2    rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 539
#190313  9:15:47 server id 140  end_log_pos 663 CRC32 0x47cd717e     Query    thread_id=10    exec_time=0    error_code=0
use `mydb`/*!*/;
SET TIMESTAMP=1552439747/*!*/;
DROP TABLE `load_test_A` /* generated by server */

因为我们想要忽略删除表A的那段bin-log,其他的仍然正常执行,所以从上次完全备份开始重放,到删除表A的那段停止重复,然后在重放剩下的

mysqlbinlog --database=mydb /usr/local/mysql/data/mysql-bin.000015 --start-position=154 --stop-position=539 | mysql 
mysqlbinlog --database=mydb /usr/local/mysql/data/mysql-bin.000015 --start-position=663 | mysql

重放完成后,检查数据是否恢复,然后还原对my.cnf配置的修改并重启

mysql> select * from load_test_C;
+------------+------+------+------+------+------+------+--------+
| col1       | col2 | col3 | col4 | col5 | col6 | col7 | col8   |
+------------+------+------+------+------+------+------+--------+
| 2019-03-12 |    1 |    1 |    1 |    1 |    1 |  1.0 | 更新   |
| 2019-03-12 |    2 |    2 |    2 |    2 |    2 |  2.0 | 2      |
| 2019-03-12 |    3 |    3 |    3 |    3 |    3 |  3.0 | 3      |
| 2019-03-12 |    4 |    4 |    4 |    4 |    4 |  4.0 | 4      |
+------------+------+------+------+------+------+------+--------+
4 rows in set (0.00 sec)

mysql> select * from load_test_A;
+------------+------+------+------+------+------+------+------+
| col1       | col2 | col3 | col4 | col5 | col6 | col7 | col8 |
+------------+------+------+------+------+------+------+------+
| 2019-03-12 |    1 |    1 |    1 |    1 |    1 |  1.0 | 1    |
| 2019-03-12 |    2 |    2 |    2 |    2 |    2 |  2.0 | 2    |
| 2019-03-12 |    3 |    3 |    3 |    3 |    3 |  3.0 | 3    |
| 2019-03-12 |    4 |    4 |    4 |    4 |    4 |  4.0 | 4    |
+------------+------+------+------+------+------+------+------+
4 rows in set (0.00 sec)

mysql> select * from load_test_B;
+------------+------+------+------+------+------+------+--------+
| col1       | col2 | col3 | col4 | col5 | col6 | col7 | col8   |
+------------+------+------+------+------+------+------+--------+
| 2019-03-12 |    1 |    1 |    1 |    1 |    1 |  1.0 | 1      |
| 2019-03-12 |    2 |    2 |    2 |    2 |    2 |  2.0 | 2      |
| 2019-03-12 |    3 |    3 |    3 |    3 |    3 |  3.0 | 3      |
| 2019-03-12 |    4 |    4 |    4 |    4 |    4 |  4.0 | 4      |
| 2019-03-12 |    5 |    5 |    5 |    5 |    5 |  5.0 | 更新   |
+------------+------+------+------+------+------+------+--------+
5 rows in set (0.00 sec)

mysql> select * from load_test_D;
+------------+------+------+------+------+------+------+---------+
| col1       | col2 | col3 | col4 | col5 | col6 | col7 | col8    |
+------------+------+------+------+------+------+------+---------+
| 2019-03-12 |    1 |    1 |    1 |    1 |    1 |  1.0 | 更新D   |
| 2019-03-12 |    2 |    2 |    2 |    2 |    2 |  2.0 | 2       |
| 2019-03-12 |    3 |    3 |    3 |    3 |    3 |  3.0 | 3       |
| 2019-03-12 |    4 |    4 |    4 |    4 |    4 |  4.0 | 4       |
+------------+------+------+------+------+------+------+---------+
4 rows in set (0.00 sec)