【MySQL】MySQL之恢复

本篇演示MySQL的恢复场景。

完全恢复

1、备份数据库test；

mysql> system mysqldump -uroot -p -l -F test > /usr/local/mysql/backup/test.sql
Enter password: 
mysql> select *from t_emp;
+----+--------+---------------------+------+
| id | name   | cdate               | sex  |
+----+--------+---------------------+------+
|  1 | 张三   | 2018-03-13 18:53:44 |    1 |
|  3 | 李四   | 2018-03-13 18:53:44 |    1 |
|  5 | Alen   | 2018-03-13 17:37:34 |    1 |
|  7 | Lucy   | 2018-03-13 17:39:45 |    0 |
|  9 | Hanmei | 2018-03-14 17:19:07 |    0 |
| 11 | 李磊   | 2018-03-14 17:19:07 |    1 |
| 13 | James  | 2018-03-14 17:19:07 |    1 |
+----+--------+---------------------+------+
7 rows in set (0.00 sec)

mysql> 

2、备份完毕后，过段时间插入新数据；

mysql> insert into t_emp(name,sex)values('王五',1),('Kim',1),('Eile',0);
Query OK, 3 rows affected (0.35 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> 

3、删除数据文件，模拟数据库突然故障，数据无法访问，需要数据恢复；

mysql> system mysql -uroot -p test < /usr/local/mysql/backup/test.sql
Enter password: 
mysql> select *from t_emp;
+----+--------+---------------------+------+
| id | name   | cdate               | sex  |
+----+--------+---------------------+------+
|  1 | 张三   | 2018-03-13 18:53:44 |    1 |
|  3 | 李四   | 2018-03-13 18:53:44 |    1 |
|  5 | Alen   | 2018-03-13 17:37:34 |    1 |
|  7 | Lucy   | 2018-03-13 17:39:45 |    0 |
|  9 | Hanmei | 2018-03-14 17:19:07 |    0 |
| 11 | 李磊   | 2018-03-14 17:19:07 |    1 |
| 13 | James  | 2018-03-14 17:19:07 |    1 |
+----+--------+---------------------+------+
7 rows in set (0.01 sec)

mysql> 

4、使用mysqlbinlog恢复自备份以来的Binlog；

mysql> system mysqlbinlog /usr/local/mysql/binlog/mysql-bin.000006 | mysql -uroot -p test
Enter password: 
mysql> select *from t_emp;
+----+--------+---------------------+------+
| id | name   | cdate               | sex  |
+----+--------+---------------------+------+
|  1 | 张三   | 2018-03-13 18:53:44 |    1 |
|  3 | 李四   | 2018-03-13 18:53:44 |    1 |
|  5 | Alen   | 2018-03-13 17:37:34 |    1 |
|  7 | Lucy   | 2018-03-13 17:39:45 |    0 |
|  9 | Hanmei | 2018-03-14 17:19:07 |    0 |
| 11 | 李磊   | 2018-03-14 17:19:07 |    1 |
| 13 | James  | 2018-03-14 17:19:07 |    1 |
| 15 | 王五   | 2018-03-14 22:13:26 |    1 |
| 17 | Kim    | 2018-03-14 22:13:26 |    1 |
| 19 | Eile   | 2018-03-14 22:13:26 |    0 |
+----+--------+---------------------+------+
10 rows in set (0.00 sec)

mysql> 

至此，数据库完全恢复。

不完全恢复

由于误操作，比如误删除了一张表，此时完全恢复是没用的，因为Binlog里还存在误操作的语句，我们需要的是恢复到误操作之前的状态，然后跳过误操作的语句，再恢复后面执行的语句，完成恢复，这种恢复叫不完全恢复，MySQL中，不完全恢复分为基于时间点的恢复和基于位置的恢复，但基于位置的恢复更精确。

基于时间点恢复

基于时间点恢复原理是找出错误发生时Binlog的开始时间和结束时间，然后应用Binlog。对应的mysqlbinlog的参数为--start-datetime和--stop-datetime，在此不做演示，具体演示基于位置的恢复。

基于位置的恢复

1、查看现有数据；

mysql> use test;
Database changed
mysql> select *from t_emp;
+----+--------+---------------------+------+
| id | name   | cdate               | sex  |
+----+--------+---------------------+------+
|  1 | 张三   | 2018-03-13 18:53:44 |    1 |
|  3 | 李四   | 2018-03-13 18:53:44 |    1 |
|  5 | Alen   | 2018-03-13 17:37:34 |    1 |
|  7 | Lucy   | 2018-03-13 17:39:45 |    0 |
|  9 | Hanmei | 2018-03-14 17:19:07 |    0 |
| 11 | 李磊   | 2018-03-14 17:19:07 |    1 |
| 13 | James  | 2018-03-14 17:19:07 |    1 |
| 15 | 王五   | 2018-03-14 22:13:26 |    1 |
| 17 | Kim    | 2018-03-14 22:13:26 |    1 |
| 19 | Eile   | 2018-03-14 22:13:26 |    0 |
+----+--------+---------------------+------+
10 rows in set (0.00 sec)

mysql> 

2、假设此时有人误删除了表t_emp；

mysql> drop table t_emp;
Query OK, 0 rows affected (0.32 sec)

mysql> select *from t_emp;
ERROR 1146 (42S02): Table 'test.t_emp' doesn't exist
mysql> 

3、进行基于位置的恢复；

--分析Binlog找到删除语句前后的日志位置
[root@strong mysql]# mysqlbinlog --base64-output='decode-rows' -v  /usr/local/mysql/binlog/mysql-bin.000007 | grep -B 10 -i 'drop table `t_emp`'
# at 3120
#180314 22:13:26 server id 181  end_log_pos 3151 CRC32 0x2e1f05c8 	Xid = 103
COMMIT/*!*/;
# at 3151
#180315  9:17:28 server id 181  end_log_pos 3216 CRC32 0x8d454f59 	Anonymous_GTID	last_committed=11	sequence_number=12	rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 3216
#180315  9:17:28 server id 181  end_log_pos 3339 CRC32 0x40a53ccc 	Query	thread_id=10	exec_time=1	error_code=0
SET TIMESTAMP=1521076648/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
DROP TABLE `t_emp` /* generated by server */
[root@strong mysql]# 
--可以发现，我们想忽略的语句在日志文件的位置是 3216，下一个位置是 3339
[root@strong mysql]# mysqlbinlog  /usr/local/mysql/binlog/mysql-bin.000007 --stop-position=3216 |mysql -uroot -p 
Enter password: 
[root@strong mysql]# mysqlbinlog  /usr/local/mysql/binlog/mysql-bin.000007 --start-position=3339 |mysql -uroot -p 
Enter password: 
[root@strong mysql]# mysql -uroot -p test -e 'select *from t_emp';
Enter password: 
+----+--------+---------------------+------+
| id | name   | cdate               | sex  |
+----+--------+---------------------+------+
|  1 | 张三   | 2018-03-13 18:53:44 |    1 |
|  3 | 李四   | 2018-03-13 18:53:44 |    1 |
|  5 | Alen   | 2018-03-13 17:37:34 |    1 |
|  7 | Lucy   | 2018-03-13 17:39:45 |    0 |
|  9 | Hanmei | 2018-03-14 17:19:07 |    0 |
| 11 | 李磊   | 2018-03-14 17:19:07 |    1 |
| 13 | James  | 2018-03-14 17:19:07 |    1 |
| 15 | 王五   | 2018-03-14 22:13:26 |    1 |
| 17 | Kim    | 2018-03-14 22:13:26 |    1 |
| 19 | Eile   | 2018-03-14 22:13:26 |    0 |
+----+--------+---------------------+------+
[root@strong mysql]# 

至此，基于位置的恢复完成；