一、mysql日志简介
mysql中日志主要分为四类:
- 通用查询日志:记录建立的客户端连接和执行的语句。
- 慢查询日志:记录所有执行时间超过long_query_time的所有查询或者不使用索引的查询。
- 错误日志:记录mysql服务启动、运行或停止时出现的问题。
- 二进制日志(binlog日志):记录所有更改数据的语句,可以用于数据复制。
二、二进制日志
二进制日志主要记录mysql数据库的变化,日志中包含了所有更新了数据或者潜在更新了数据(没有匹配任何行的delete)的语句,语句以“事件”的形式保存,描述数据更改。
日志记录了每个更新数据库语句的执行时间信息,使用二进制日志的主要目的时最大可能的恢复数据库,因为二进制日志包含备份后进行的所有更新。
三、数据环境准备
实际生产环境中,一般都会配合备份库来执行数据恢复操作。这里我们先建一个库和表,并备份。
3.1、建库、建表、准备数据
CREATE DATABASE `mydb`;
CREATE TABLE `table1` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `table1` VALUES (1,'刘一'),(2,'陈二'),(3,'张三'),(4,'李四'),(5,'王五'),(6,'赵六'),(7,'孙七'),(8,'周八'),(9,'吴九'),(10,'郑十');
3.2、备份数据
进入mysql客户端的bin目录,或者将mysqldump命令配置为系统环境变量
mysqldump -uroot -p -h localhost -P3306 -B mydb >D:\mysqlbackup\mydb_bak_20210219.sql
3.3、操作数据
这里我们修改id为2的数据,并且删除修改后id为2的数据,然后再更新id为5的数据
UPDATE `mydb`.`table1` SET `name` = '陈二2' WHERE `id` = 2;
DELETE from `mydb`.`table1` where `id` =2;
UPDATE `mydb`.`table1` SET `name` = '王五5' WHERE `id` = 5;
此时的数据结果为
此时我发现我的删除操作不应该执行,我想要的只是保留着两个更新。这种情况下模拟的就是实际生产过程中的异常删除。
此时我们的恢复思路是:
第一步:查看binlog日志,得到我们想要恢复的操作在日志记录中的position
第二步:使用备份库将数据恢复到上次备份时的状态;
第三步:使用mysqlbinlog命令恢复数据
四、恢复数据
当我们发现数据被异常删除时,建议执行刷新日志的操作,生成一个新的日志。
这样做的目的是,线上正在执行的操作的后续日志会写入到这个新的日志文件中,我们可以在上一个日志中查看我们的异常操作,当然这一步刷新生成新日志不是必须的。
4.1、查看日志
这里我现有的日志是:huangtclog.000002,
连接MySQL服务器,在命令行界面执行刷新日志命令
flush logs;
刷新之后生产了一个新的日志huangtclog.000003,此时我在上一个日志文件huangtclog.000002中查看我的操作记录
连接MySQL服务器,在命令行界面执行下面命令
show binlog events in 'huangtclog.000002';
这里我们可以看到,当position为377时,我们执行了更新,当position为683时执行了delete操作,当position为984的时候执行了更新操作。
当然我们也可以退出mysql连接,直接通过命令行查看详细日志
mysqlbinlog D:\developSoft\mysql-8.0.20-winx64\data\log\huangtclog.000002
或者输入到文件中单独查看
mysqlbinlog D:\developSoft\mysql-8.0.20-winx64\data\log\huangtclog.000002 >D:\mysqlbackup\huangtc000002.log
这时,可以看到D:\mysqlbackup目录下生成了一个huangtc000002.log文件。可以使用文本工具打开该log文件。
4.2、使用备份库恢复数据
将现有的库删除或者备份,然后使用备份库恢复数据。
root用户连接MySQL服务器,使用以下命令导入备份文件
source D:\mysqlbackup\mydb_bak_20210219.sql;
4.3、使用binlog日志恢复操作
我们需要把position为377的update操作和position为984的update操作在恢复之后的新库上执行,因此执行以下命令
mysqlbinlog D:\developSoft\mysql-8.0.20-winx64\data\log\huangtclog.000002 --start-position=319 --stop-position=440 | mysql -uroot -p
mysqlbinlog D:\developSoft\mysql-8.0.20-winx64\data\log\huangtclog.000002 --start-position=926 --stop-position=1047 | mysql -uroot -p
此时数据结果为:
至此,我们已经使用binlog日志恢复了两次update操作的数据结果。
五、总结
理解下来,我们就可以发现,binlog日志之所以能够用来恢复数据,是因为它记录了每个更新数据库语句的执行信息,它不包含没有修改数据的语句,我们恢复数据的过程,就是在binlog日志中找到我们需要恢复的操作的节点,然后将这个操作在备份库中重新执行的过程。