恢复思路
首先mysql数据库必须开启binlog日志功能,且有做全备和增量备份,如每周一次全备,每天一次增量备份。
先根据全量备份,将数据库中的数据恢复到临近的某一时间点,再找出该时间点到发生事故时段的所有增量备份的binlog文件。
1、开启binlog日志功能
参考一下mysql配置
port=3306
default-time_zone='+8:00'
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
basedir="D:/software_install/mysql-5.6.10-winx64/"
datadir="D:/software_install/mysql-5.6.10-winx64/data/"
log-error="D:/software_install/mysql-5.6.10-winx64/log/error.log"
#binlog日志格式,mysql默认采用statement,建议使用mixed
binlog_format=mixed
#binlog日志文件
log-bin="D:/software_install/mysql-5.6.10-winx64/binlog/mysql-bin.log"
#binlog过期清理时间
expire_logs_days=7
#binlog每个日志文件大小
max_binlog_size=100m
#binlog缓存大小
binlog_cache_size=4m
#最大binlog缓存大小
max_binlog_cache_size=512m
server-id=1
2、全备过程
#文件备份路径最好写绝对路径,如果只写文件名称,生成的文件在打开cmd窗口的位置
mysqldump -u 用户 -p 数据库 [表1 表2 …… 表N] > 备份文件路径
#实际上还要加一些别的参数控制
mysqldump -u root -p --single-transaction --flush-logs --master-data=2 liberty > backup_test.sql
--single-transaction,开启单一事务,整个备份过程只有一个事务,不适用该参数时,会频繁的开启事务,实现点对点的备份恢复.因为-single-transaction选项备份时涉及到的是select语句,所以dml语句是被允许的,ddl不被允许直到回滚保存点撤销select 语句
--flush-logs 为结束当前日志,生成新日志文件
--master-data=2 选项将会在输出SQL中记录下完全备份后新日志文件的名称,用于日后恢复时参考
liberty 数据库名称
若要恢复数据,执行sql脚本即可恢复到全备数据。然后要关闭数据库的binlog日志功能,再重启。接着进行增量恢复
3、增量恢复
执行完全量备份文件后,再依次执行binlog日志
# mysqlbinlog --no-defaults mysql-bin.000001 | mysql -u root -p
# mysqlbinlog --no-defaults mysql-bin.000002 | mysql -u root -p
# mysqlbinlog --no-defaults mysql-bin.000003 | mysql -u root -p
# mysqlbinlog --no-defaults mysql-bin.000004 | mysql -u root -p
最后一个发生故障的binlog文件可以有两种方式来恢复。
3.1 基于位置的恢复:跳过误操作的位置
找到误操作所在的binlog文件
show binlog events in 'mysql-bin.000002';
得到的结果:
+------------------+------+-------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+------+-------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| mysql-bin.000002 | 4 | Format_desc | 1 | 120 | Server ver: 5.6.10-log, Binlog ver: 4 |
| mysql-bin.000002 | 120 | Query | 1 | 239 | use `shop`; DROP TABLE `studet` /* generated by server */ |
| mysql-bin.000002 | 239 | Query | 1 | 369 | use `shop`; DROP TABLE IF EXISTS `student` /* generated by server */ |
| mysql-bin.000002 | 369 | Query | 1 | 644 | use `shop`; CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=latin1 |
| mysql-bin.000002 | 644 | Query | 1 | 764 | use `shop`; /*!40000 ALTER TABLE `student` DISABLE KEYS */ |
| mysql-bin.000002 | 764 | Query | 1 | 843 | BEGIN |
| mysql-bin.000002 | 843 | Query | 1 | 1272 | use `shop`; INSERT INTO `student` VALUES (1,'寮犱笁'),(2,'鏉庡洓'),(3,'鐜嬩簲'),(4,'璧垫煶'),(5,'璧垫煶'),(6,'璧垫煶'),(7,'璧垫煶'),(8,'璧垫煶'),(9,'璧垫煶'),(10,'璧垫煶'),(11,'璧垫煶'),(12,'璧垫煶'),(13,'璧垫煶'),(14,'璧垫煶'),(15,'璧垫煶'),(16,'璧垫煶'),(17,'璧垫煶'),(18,'璧垫煶'),(19,'璧 垫煶'),(20,'璧垫煶'),(21,'璧垫煶'),(22,'璧垫煶'),(23,'璧垫煶'),(24,'璧垫煶') |
| mysql-bin.000002 | 1272 | Xid | 1 | 1303 | COMMIT /* xid=571 */ |
| mysql-bin.000002 | 1303 | Query | 1 | 1422 | use `shop`; /*!40000 ALTER TABLE `student` ENABLE KEYS */ |
| mysql-bin.000002 | 1422 | Query | 1 | 1501 | BEGIN |
| mysql-bin.000002 | 1501 | Intvar | 1 | 1533 | INSERT_ID=25 |
| mysql-bin.000002 | 1533 | Query | 1 | 1649 | use `test`; INSERT into student(name) values('鐢颁竷') |
| mysql-bin.000002 | 1649 | Xid | 1 | 1680 | COMMIT /* xid=612 */ |
| mysql-bin.000002 | 1680 | Query | 1 | 1759 | BEGIN |
| mysql-bin.000002 | 1759 | Intvar | 1 | 1791 | INSERT_ID=26 |
| mysql-bin.000002 | 1791 | Query | 1 | 1907 | use `test`; INSERT into student(name) values('鐜嬪叓') |
| mysql-bin.000002 | 1907 | Xid | 1 | 1938 | COMMIT /* xid=619 */ |
| mysql-bin.000002 | 1938 | Query | 1 | 2017 | BEGIN |
| mysql-bin.000002 | 2017 | Query | 1 | 2130 | use `test`; delete from student where name='璧垫煶' |
| mysql-bin.000002 | 2130 | Xid | 1 | 2161 | COMMIT /* xid=626 */ |
+------------------+------+-------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
20 rows in set (0.00 sec)
故障的sql语句为最后一条delete语句,所以最终的位置是1938。
执行最后一条恢复语句即可恢复到故障之前的所有数据。
mysqlbinlog --start-position=4 --stop-position=1938 D:\software_install\mysql-5.6.10-winx64\binlog\mysql-bin.000002 | mysql -u root -p
3.2 基于时间的恢复:跳过误操作的时间
# mysqlbinlog --no-defaults --stop-datetime='2018-07-03 18:19:49' /usr/local/mysql/data/mysql-bin.00000x | mysql -u root -p
# mysqlbinlog --no-defaults --start-datetime='2018-07-03 18:22:21' /usr/local/mysql/data/mysql-bin.00000x | mysql -u root -p
测试了没有成功!
注意点
mysql 5.6版本以上新加了gtid 功能,gtid开启之后,默认不执行已经执行过的事务。直接还原是还原不了的,所以将二进制文件倒成sql文件时或二进制直接还原时:要加–skip-gtids参数或是要重新建个实例。
其实就是 将二进制文件倒成sql文件例: mysqlbinlog --skip-gtids mysql-bin-0000001 > test.sql (gtid开启之后,默认不执行已经执行过的事务所以要加–skip-gtids参数)