三、 mysqlbinlog使用简介

最新推荐文章于 2025-09-26 17:29:43 发布

原创最新推荐文章于 2025-09-26 17:29:43 发布 · 1.2k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql

数据库专栏收录该内容

4 篇文章

订阅专栏

本文介绍MySQL二进制日志的配置与使用方法，包括日志文件的生成、mysqlbinlog工具的应用以及如何利用日志进行数据恢复。

部署运行你感兴趣的模型镜像

日志是把数据库的每一个变化都记载到一个专用的文件里，这种文件就叫做日志文件。二进制日志由配置文件的log-bin选项负责启用，Mysql服务器将在数据根目录创建两个新文件XXX-bin.001和xxx-bin.index，若配置选项没有给出文件名，Mysql将使用主机名称命名这两个文件，其中.index文件包含一份全体日志文件的清单。Mysql会把用户对所有数据库的内容和结构的修改情况记入XXX-bin.n文件，而 不会记录SELECT和没有实际更新的UPDATE语句。

mysqlbinlog是mysql的二进制日志工具，用于解析二进制日志文件。以下实验均在windows环境下测试。

1、打开bin-log选项，启动二进制日志功能

有两种打开方式，一种在启动mysql服务的时候指定 --log-bin[=base_name] 选项，服务启动之后，会在[base_name]目录下生成日志文件和日志索引文件（如果有指定base_name的话，否则默认在mysql主目录下的data目录生成相应文件）；另一种启动方式是修改my.ini，在[mysqld]节点下添加log-bin选项，如若无多余路径，则直接在在data目录下生成前缀为“选项名”的文件，保存后重启mysql服务。比如我在配置文件中添加：

则重启mysql服务之后，会在data目录下生成两个文件，分别为二进制日志文件 mysqlLogBin.000001 和索引文件 mysqlLogBin.index。下面两个截图是修改my.ini并重启mysql前后log_bin的状态：

启动log-bin前

启动log-bin后

2、操作数据库

建立数据库 -> 建表 -> 插入数据-> 修改数据-> 删除表格-> 删除数据库，具体如控制台日志所示：

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| test |

+--------------------+

4 rows in set (0.00 sec)

mysql> create database jayz_test;

Query OK, 1 row affected (0.00 sec)

mysql> use jayz_test;

Database changed

mysql> show tables;

Empty set (0.00 sec)

mysql> source F:\\pet.sql;

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.19 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 9 rows affected (0.05 sec)

Records: 9 Duplicates: 0 Warnings: 0

Query OK, 0 rows affected (0.00 sec)

mysql> select * from pet;

+----------+--------+---------+------+------------+------------+

+----------+--------+---------+------+------------+------------+

| Fluffy | Harold | cat | f | 1993-02-04 | NULL |

| Claws | Gwen | cat | m | 1994-03-17 | NULL |

| Buffy | Harold | dog | f | 1989-05-13 | NULL |

| Fang | Benny | dog | m | 1990-08-27 | NULL |

| Browser | Diane | dog | m | 1979-08-31 | 1995-07-29 |

+----------+--------+---------+------+------------+------------+

9 rows in set (0.00 sec)

mysql> delete from pet where sex IS NULL;

Query OK, 1 row affected (0.03 sec)

mysql> select * from pet;

+----------+--------+---------+------+------------+------------+

+----------+--------+---------+------+------------+------------+

| Fluffy | Harold | cat | f | 1993-02-04 | NULL |

| Claws | Gwen | cat | m | 1994-03-17 | NULL |

| Buffy | Harold | dog | f | 1989-05-13 | NULL |

| Fang | Benny | dog | m | 1990-08-27 | NULL |

| Browser | Diane | dog | m | 1979-08-31 | 1995-07-29 |

+----------+--------+---------+------+------------+------------+

8 rows in set (0.00 sec)

mysql> update pet set birth='1989-08-31' where death IS NOT NULL;

Query OK, 1 row affected (0.03 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from pet;

+----------+--------+---------+------+------------+------------+

+----------+--------+---------+------+------------+------------+

| Fluffy | Harold | cat | f | 1993-02-04 | NULL |

| Claws | Gwen | cat | m | 1994-03-17 | NULL |

| Buffy | Harold | dog | f | 1989-05-13 | NULL |

| Fang | Benny | dog | m | 1990-08-27 | NULL |

| Browser | Diane | dog | m | 1989-08-31 | 1995-07-29 |

+----------+--------+---------+------+------------+------------+

8 rows in set (0.00 sec)

mysql> drop table pet;

Query OK, 0 rows affected (0.06 sec)

mysql> drop database jayz_test;

Query OK, 0 rows affected (0.03 sec)

mysql>

3、确定恢复内容

在步骤2中，我们已经删除了整个jayz_test数据库及其数据，因此在恢复过程中要将drop table和drop database前的所有过程redo一遍。那么如何确定不要执行drop table及以后的sql语句呢？

在mysqlbinlog工具中，提供了--start-position和--stop-position两个选项，前者指明从哪个位置开始重做，后者指明重做至那个位置结束，默认的，两者分别是二进制日志文件的开头和末尾。那么，上述的“位置”该如何确定？使用mysqlbinlog命令解析二进制日志文件，并重定向保存为.txt文件，便于查看：

mysqlbinlog F:\mysql-5.6.31-winx64\data\mysqlLogBin.000001 > F:\mysqlLogBin.txt

可以看到，从position 2117开始，数据库开始执行drop操作，因此，我们重做至position=2117的位置就可以了。

注意：mysqlbinlog会因为client设置字符集的缘故而运行失败，如下所示：

而由于mysqlbinlog每次运行时是重新加载my.ini配置文件，因此使用mysqlbinlog命令时可以：1）将my.ini中[client]节点下的字符集设置注释，保存； 2）运行mysqlbinlog工具。也即，修改后的my.ini不需要重启mysql服务也会被mysqlbinlog所识别。（记得修改回来！）

4、mysqlbinlog恢复

mysqlbinlog恢复命令

恢复效果如下，可以看到，除了两句删除命令，其余建表，插入数据，修改数据的sql语句都被重做：

注意：

在使用mysqlbinlog进行恢复的过程中，假如有两份二进制文件，可以通过如下做法（1）执行：

mysqlbinlog binlog.000001 binlog.000002 | mysql -u root -p

而不推荐以下做法（2）：

mysqlbinlog binlog.000001 | mysql -u root -p # DANGER!!

mysqlbinlog binlog.000002 | mysql -u root -p # DANGER!!

官方给出的解释如下：

Processing binary logs this way using different connections to the server causes problems if the first log file contains a CREATE TEMPORARY TABLE statement and the second log contains a statement that uses the temporary table. When the first mysql process terminates, the server drops the temporary table. When the second mysql process attempts to use the table, the server reports “unknown table.”

简单大意就是：做法（2）会分别建立两个mysql连接，如果第一个连接有生成临时表，这些临时表在该连接结束后被清除。而第二个连接binlog.2中有sql需要用到binglog1中的临时表结果时，此时就会执行失败，因此做法（1）更值得推荐。

参考：

官方手册mysqlbinlog小节 http://dev.mysql.com/doc/refman/5.6/en/point-in-time-recovery.html

您可能感兴趣的与本文相关的镜像

AutoGPT

AI应用

AutoGPT于2023年3月30日由游戏公司Significant Gravitas Ltd.的创始人Toran Bruce Richards发布,AutoGPT是一个AI agent（智能体），也是开源的应用程序，结合了GPT-4和GPT-3.5技术，给定自然语言的目标，它将尝试通过将其分解成子任务，并在自动循环中使用互联网和其他工具来实现这一目标