注意：本案例适用于人为SQL语句造成的误操作或者没有主从复制等的热备情况宕机时的修复
思路：
- 利用全备的sql文件中记录的CHANGE MASTER语句，binlog文件及其位置点信息，找出binlog文件中增量的那部分
- 用mysqlbinlog 命令将上述的binlog 文件导出为sql文件，并剔除其中的drop语句
- 通过全备文件和增量binlog文件的导出sql文件，就可以恢复到完整的数据
详细模拟操作如下：
一. 创建数据库，表，插入模拟数据
开启 binlog日志功能
/etc/my.cnf文件里的[mysqld]区块添加：<code>log-bin=mysql-bin</code> 重启服务
use test;
CREATE TABLE IF NOT EXISTS recover_table(
id mediumint unsigned NOT NULL AUTO_INCREMENT,
name varchar(32) NOT NULL,
age int unsigned NOT NULL,
PRIMARY KEY(id)
)ENGINE=innoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 COMMENT 'recover_table';
insert into recover_table values(null, 'tom', 20);
insert into recover_table values(null, 'jack', 22);
insert into recover_table values(null, 'rose', 23);
insert into recover_table values(null, 'json', 23);
mysql> select * from recover_table;
+----+------+-----+
| id | name | age |
+----+------+-----+
| 1 | tom | 20 |
| 2 | jack | 22 |
| 3 | rose | 23 |
| 4 | json | 22 |
+----+------+-----+
4 rows in set (0.00 sec)
二. 进行全文件备份
参数说明：
-B：指定数据库
-F：刷新日志
-R：备份存储过程等
-x：锁表
--master-data：在备份语句里添加CHANGE MASTER语句以及binlog文件及位置点信息
test 为数据库名
[root@iZ95i72m2pc9 backup]# mysqldump -uroot -p -B -F -R -x --master-data=2 test|gzip >/test/backup/test_$(date +%F).sql.gz
Enter password:
mysql> select * from recover_table;
+----+------+-----+
| id | name | age |
+----+------+-----+
| 1 | tom | 20 |
| 2 | jack | 22 |
| 3 | rose | 23 |
| 4 | json | 22 |
+----+------+-----+
4 rows in set (0.00 sec)
三. 再插入模拟数据, 删除数据库
insert into recover_table values(null, 'sam', 25);
insert into recover_table values(null, 'jare', 26);
mysql> select * from recover_table;
+----+------+-----+
| id | name | age |
+----+------+-----+
| 1 | tom | 20 |
| 2 | jack | 22 |
| 3 | rose | 23 |
| 4 | json | 22 |
| 5 | sam | 25 |
| 6 | jare | 26 |
+----+------+-----+
6 rows in set (0.00 sec)
drop database test;
四. 查看全备之后新增的binlog文件
[root@iZ95i72m2pc9 backup]# ll
-rw-r--r-- 1 root root 936 Oct 17 10:40 test_2017-10-17.sql.gz
[root@iZ95i72m2pc9 backup]# gzip -d test_2017-10-17.sql.gz
[root@iZ95i72m2pc9 backup]# ll
-rw-r--r-- 1 root root 2397 Oct 17 10:40 test_2017-10-17.sql
[root@iZ95i72m2pc9 backup]# grep CHANGE test_2017-10-17.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=120;
说明：mysql-bin.000002的120行，在该文件之前的binlog文件中的数据都已经包含在这个全备的sql文件中了（增量）
五. 查看并切换到mysql 数据存放目录, 转sql 格式
[root@iZ95i72m2pc9 backup]# ps -ef|grep mysql
root 4411 4376 0 10:13 pts/0 00:00:00 mysql -uroot -p
root 4458 4421 0 10:45 pts/1 00:00:00 grep mysql
root 13046 1 0 Apr12 ? 00:00:00 /bin/sh /alidata/server/mysql/bin/mysqld_safe --datadir=/alidata/server/mysql/data --pid-file=/alidata/server/mysql/data/iZ95i72m2pc9.pid
mysql 13347 13046 0 Apr12 ? 02:03:40 /alidata/server/mysql/bin/mysqld --basedir=/alidata/server/mysql --datadir=/alidata/server/mysql/data --plugin-dir=/alidata/server/mysql/lib/plugin --user=mysql --log-error=/alidata/log/mysql/error.log --pid-file=/alidata/server/mysql/data/iZ95i72m2pc9.pid --socket=/tmp/mysql.sock --port=3306
[root@iZ95i72m2pc9 backup]# cd /alidata/server/mysql/data
备份日志数据到指定目录,避免数据混乱的问题
[root@iZ95i72m2pc9 mysql]# cp ./data/mysql-bin.000004 /opt/backup/
[root@iZ95i72m2pc9 backup]# cd /opt/backup/
[root@iZ95i72m2pc9 backup]# ll
-rw-r----- 1 root root 743 Oct 17 10:52 mysql-bin.000004
-rw-r--r-- 1 root root 2397 Oct 17 10:40 test_2017-10-17.sql
转为sql 格式
[root@iZ95i72m2pc9 backup]# mysqlbinlog -d test mysql-bin.000004 >00004bin.sql
删除里面的drop 语句（或误操作的语句）
六. 开始恢复数据，先恢复备份文件，再恢复增加文件
[root@iZ95i72m2pc9 backup]# mysql -uroot -p < test_2017-10-17.sql
Enter password:
mysql> select * from recover_table;
+----+------+-----+
| id | name | age |
+----+------+-----+
| 1 | tom | 20 |
| 2 | jack | 22 |
| 3 | rose | 23 |
| 4 | json | 22 |
+----+------+-----+
4 rows in set (0.00 sec)
[root@iZ95i72m2pc9 backup]# mysql -uroot -p < 00004bin.sql
Enter password:
mysql> select * from recover_table;
+----+------+-----+
| id | name | age |
+----+------+-----+
| 1 | tom | 20 |
| 2 | jack | 22 |
| 3 | rose | 23 |
| 4 | json | 22 |
| 5 | sam | 25 |
| 6 | jare | 26 |
+----+------+-----+
6 rows in set (0.00 sec)
总结：
- 恢复条件为mysql要开启binlog日志功能，并且要全备和增量的所有数据
- 恢复时建议对外停止更新，即禁止更新数据库
- 先恢复全量，然后把全备时刻点以后的增量日志，按顺序恢复成SQL文件，然后把文件中有问题的SQL语句删除（也可通过时间和位置点），再恢复到数据库
网友评论