MySQL中binlog日志文件的详细介绍

作者：匿名字体：[增加减小] 来源：互联网时间：2018-12-05

匿名通过本文主要向大家介绍了binlog,mysql等相关知识,希望本文的分享对您有所帮助

本篇文章给大家带来的内容是关于MySQL中binlog日志文件的详细介绍，有一定的参考价值，有需要的朋友可以参考一下，希望对你有所帮助。

MySQL 的 binlog日志文件，记录了数据库表的全部修改操作。本文简单整理 MySQL binlog 相关知识，以及如何使用 binlog 恢复或闪回数据库数据。

STATEMENT 格式的 binlog

要想开启 binlog，需要在启动 MySQL 时传入 --log-bin 参数。或者也可以在 MySQL 配置文件 /etc/my.cnf，设置 log_bin 开启 binlog。MySQL 5.7 开始，开启 binlog 后，--server-id 参数也必须指定，否则 MySQL 服务器会启动失败。

binlog_format 支持 STATEMENT, ROW, MIXED 三种格式，MySQL 5.5 和 5.6 默认为 STATEMENT，MySQL 5.7.7 开始默认为 ROW。若 SQL 使用 UUID(), RAND(), VERSION() 等函数，或者使用存储过程、自定义函数，基于 STATEMENT 的主从复时，是不安全的（很多人可能会认为 NOW(), CURRENT_TIMESTAMP 这些函数也是不安全的，事实上是安全的） [doc1, doc2 ]。基于 ROW 的主从复制，是最安全的复制方式。

现在先来看下 STATEMENT 格式的 binlog，/etc/my.cnf 文件修改的内容如下：

server_id = 1
log_bin = mysql-bin
binlog_format = STATEMENT
binlog_row_image=FULL

重启 MySQL 后，在数据目录 datadir 下，比如 /var/lib/mysql/，将会生成相应的 binlog 文件，mysql-bin.index 和 mysql-bin.000001。.index 后缀的文件保存全部 binlog 文件名。mysql-bin.000001 文件记录 binlog 内容。每次 MySQL 启动或者 flush 日志，都将按序号创建一个新的日志文件。另外，当日志文件大小超过 max_binlog_size 时，也会创建一个新的日志文件。

现在来试一试 binlog 功能。假设在 testdb 库在有 hello 表，并对其中某行做修改操作：

mysql> select * from hello;
+----+-------+
| id | name  |
+----+-------+
|  1 | Andy  |
|  2 | Bill  |
|  3 | Candy |
+----+-------+
4 rows in set (0.00 sec)

mysql> update hello set name = 'Will' where id = 3;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

binlog 为二进制文件，需要使用 mysqlbinlog（doc, man）命令查看：

$ sudo mysqlbinlog /var/lib/mysql/mysql-bin.000001  # 直接在 mysql 服务器上读取 binlog 文件
$ mysqlbinlog -R -h192.168.2.107 -uroot -p123456 mysql-bin.000001  # 或者，远程读取 binlog 文件

执行 update 后相应新增的 binlog 文件内容：

# at 154
#180617 22:47:49 server id 1  end_log_pos 219 CRC32 0x4bd9d69b     Anonymous_GTID    last_committed=0    sequence_number=1    rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#180617 22:47:49 server id 1  end_log_pos 302 CRC32 0x476fafc9     Query    thread_id=2    exec_time=0    error_code=0
SET TIMESTAMP=1529246869/*!*/;
SET @@session.pseudo_thread_id=2/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 302
#180617 22:47:49 server id 1  end_log_pos 423 CRC32 0x7f2c2c7a     Query    thread_id=2    exec_time=0    error_code=0
use `testdb`/*!*/;
SET TIMESTAMP=1529246869/*!*/;
update hello set name = 'Will' where id = 3
/*!*/;
# at 423
#180617 22:47:49 server id 1  end_log_pos 454 CRC32 0x68da744a     Xid = 12
COMMIT/*!*/;

ROW 格式的 binlog

修改 /etc/my.cnf 的 binlog_format 为 ROW，再重启 MySQL。格式修改后，会生成一个新的 binlog 文件 mysql-bin.000002。

mysql> show create table hello;
+-------+-------------------------------------------------------------------------+
| Table | Create Table
+-------+-------------------------------------------------------------------------+
| hello | CREATE TABLE `hello` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 |
+-------+-------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from hello where id;
+----+------+
| id | name |
+----+------+
|  1 | Andy |
|  2 | Lily |
|  3 | Will |
+----+------+
1 row in set (0.00 sec)

mysql> update hello set name = 'David' where id = 3;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

查看 ROW 格式的 binlog，需要使用 sudo mysqlbinlog -v --base64-output=DECODE-ROWS /var/lib/mysql/mysql-bin.000002 命令。执行 update 后相应新增的 binlog 内容：

# at 154
#180617 22:54:13 server id 1  end_log_pos 219 CRC32 0x2ce70d4d     Anonymous_GTID    last_committed=0    sequence_number=1    rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#180617 22:54:13 server id 1  end_log_pos 293 CRC32 0x8183fddf     Query    thread_id=2    exec_time=0    error_code=0
SET TIMESTAMP=1529247253/*!*/;
SET @@session.pseudo_thread_id=2/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 293
#180617 22:54:13 server id 1  end_log_pos 346 CRC32 0x0fc7e1a4     Table_map: `testdb`.`hello` mapped to number 110
# at 346
#180617 22:54:13 server id 1  end_log_pos 411 CRC32 0xb58e729d     Update_rows: table id 110 flags: STMT_END_F
### UPDATE `testdb`.`hello`
### WHERE
###   @1=3
###   @2='Will'
### SET
###   @1=3
###   @2='David'
# at 411
#180617 22:54:13 server id 1  end_log_pos 442 CRC32 0xef964db8     Xid = 13
COMMIT/*!*/;

若执行如下 SQL：

mysql> insert hello (name) values ('Frank');
Query OK, 1 row affected (0.02 sec)

相应生成的 binlog 内容：

# at 442
#180617 22:55:47 server id 1  end_log_pos 507 CRC32 0x79de08a7     Anonymous_GTID    last_committed=1    sequence_number=2    rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 507
#180617 22:55:47 server id 1  end_log_pos 581 CRC32 0x56f9eb6a     Query    thread_id=2    exec_time=0    error_code=0
SET TIMESTAMP=1529247347/*!*/;
BEGIN
/*!*/;
# at 581
#180617 22:55:47 server id 1  end_log_pos 634 CRC32 0xedb73620     Table_map: `testdb`.`hello` mapped to number 110
# at 634
#180617 22:55:47 server id 1  end_log_pos 684 CRC32 0x525a6a70     Write_rows: table id 110 flags: STMT_END_F
### INSERT INTO `testdb`.`hello`
### SET
###   @1=4
###   @2='Frank'
# at 684
#180617 22:55:47 server id 1  end_log_pos 715 CRC32 0x09a0d4de     Xid = 14
COMMIT/*!*/;

若执行如下 SQL：

mysql> delete from hello where id = 2;
Query OK, 1 row affected (0.02 sec)

相应生成的 binlog 内容：

# at 715
#180617 22:56:44 server id 1  end_log_pos 780 CRC32 0x9f52450e     Anonymous_GTID    last_committed=2    sequence_number=3    rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 780
#180617 22:56:44 server id 1  end_log_pos 854 CRC32 0x0959bc8d     Query    thread_id=2    exec_time=0    error_code=0
SET TIMESTAMP=1529247404/*!*/;
BEGIN
/*!*/;
# at 854
#180617 22:56:44 server id 1  end_log_pos 907 CRC32 0x2945260f     Table_map: `testdb`.`hello` mapped to number 110
# at 907
#180617 22:56:

分享到：QQ空间新浪微博腾讯微博微信百度贴吧 QQ好友复制网址打印

您可能想查找下面的文章:

2017-05-11mysql 存储过程的问题
2018-12-05详解MySQL存储过程参数有三种类型(in、out、inout)
2017-05-11MySQL 存储过程和"Cursor"的使用方法
2018-12-05SQL Server连接失败错误及解决第1/5页
2018-12-05总结Sql Server临时表和游标的使用方法
2018-12-05MySQL_实现组内排序-Oracle中的rank()函数的功能
2018-12-05清空所有表中的数据的存储过程
2018-12-05MySQL中Union子句不支持order by的解决方法_MySQL
2018-12-05mysql数据库导出xml的实现方法
2018-12-05对MySQL配置参数 my.ini/my.cnf的详细解析

MySQL中binlog日志文件的详细介绍

您可能想查找下面的文章:

相关文章

文章分类

最近更新的内容