mysql bin日志_MySQL binlog日志的作用、配置参数、SQL语句、日志内容查看

最新推荐文章于 2024-07-22 03:55:15 发布

原创最新推荐文章于 2024-07-22 03:55:15 发布 · 459 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql bin日志

本文详细介绍了MySQL的binlog日志，包括其作用（如数据恢复和主从复制）、相关配置参数（如sync_binlog、binlog_format等）、SQL操作（如RESET MASTER、SHOW MASTER STATUS等）以及如何查看binlog日志内容。通过实例展示了如何使用mysqlbinlog工具解析binlog事件，强调了在主从复制中正确设置binlog位置的重要性。

主要介绍binlog日志基本操作。内容包括：

binlog日志的作用

binlog日志相关的配置参数

binlog相关的SQL语句

binlog日志内容查看

以下是本博客的正文：

binlog日志的作用

binlog日志记录了一段时间内数据库上的所有写数据操作的完整数据。因此在数据不一致或者误操作删除数据后，可以用于某个时间段或者某个binlog位置范围内的操作数据的恢复工作。

binlog日志的另外一个工作能用于MySQL主从复制体系。主机上产生binlog日志数据，并且发送到从机上。从机上的IO线程接收到事件数据后保存到本地的relay event日志中，从机的SQL线程随后将这些relay event日志中的事件数据在从机上执行，从而达到主从一致的状态。

binlog日志相关的配置参数

比较重要的几个参数：

log_bin=mysql_binlog

binlog日志文件的文件名前缀

sync_binlog=1

执行多个事务后同步binlog日志文件。

0：不同步，仅仅写入binlog缓存。

1：1个事务后同步。

N(N>1)：N个事务后同步。

max_binlog_size=10M

一个binlog日志文件的最大大小。最小值为4096字节。

binlog_format=row

binlog事件的格式。

ROW：记录行数据，默认不记录用户SQL，对于DDL语句，仍然记录SQL。

STATEMENT：记录用户sql语句。当执行一些函数时导致复制机制产生问题，数据不一致。

MIXED：主要记录sql语句，当用到了特定的函数时记录行数据。

在ROW模式下，如果delete语句实际没有删除任何记录，则不会记录到binlog日志中。

对于一个UPDATE语句修改了多条记录，则每一条记录会作为一个binlog事件记录到binlog日志中。

binlog_row_image=full

记录行数据(ROW模式)时，是否记录没有使用到的字段的值。

FULL：记录所有字段，包括没有使用的字段。

MINIMAL：只记录被更改的字段和能够唯一识别记录的字段的值。

NOBLOB：基本跟FULL相同，但是没有使用到的TEXT和BLOB字段除外。

binlog_rows_query_log_events=1

是否记录用户的原始SQL，用于ROW模式。

0：不记录。

1：记录。

全部的参数如下所示：

mysql> show variables like '%binlog%'

-> ;

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

| Variable_name | Value |

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

| binlog_cache_size | 32768 |

| binlog_checksum | CRC32 |

| binlog_direct_non_transactional_updates | OFF |

| binlog_error_action | ABORT_SERVER |

| binlog_format | ROW |

| binlog_group_commit_sync_delay | 0 |

| binlog_group_commit_sync_no_delay_count | 0 |

| binlog_gtid_simple_recovery | ON |

| binlog_max_flush_queue_time | 0 |

| binlog_order_commits | ON |

| binlog_row_image | FULL |

| binlog_rows_query_log_events | ON |

| binlog_stmt_cache_size | 32768 |

| binlog_transaction_dependency_history_size | 25000 |

| binlog_transaction_dependency_tracking | COMMIT_ORDER |

| innodb_api_enable_binlog | OFF |

| innodb_locks_unsafe_for_binlog | OFF |

| log_statements_unsafe_for_binlog | ON |

| max_binlog_cache_size | 18446744073709547520 |

| max_binlog_size | 10485760 |

| max_binlog_stmt_cache_size | 18446744073709547520 |

| sync_binlog | 1 |

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

22 rows in set (0.01 sec)

binlog相关的SQL语句

清除全部的binlog文件。

o RESET MASTER

Deletes all binary logs listed in the index file, resets the binary

log index file to be empty, and creates a new binary log file.

mysql> reset master;

Query OK, 0 rows affected (0.01 sec)

查看当前全部binlog文件的名字和大小。

mysql> show binary logs;

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

| Log_name | File_size |

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

| mysql_binlog.000001 | 154 |

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

1 row in set (0.00 sec)

File_size列出的就是binlog文件的字节数大小。

[root@101 data]# ls -l mysql_binlog.*

-rw-r-----. 1 mysql mysql 154 Sep 20 20:46 mysql_binlog.000001

-rw-r-----. 1 mysql mysql 22 Sep 20 20:46 mysql_binlog.index

查看binlog的最新位置，也是154，就是这个位置(Position)实际上是文件的字节偏移量。

mysql> help show master status

Name: 'SHOW MASTER STATUS'

Description:

Syntax:

SHOW MASTER STATUS

This statement provides status information about the binary log files

of the master. It requires either the SUPER or REPLICATION CLIENT

privilege.

mysql> show master status;

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

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

| mysql_binlog.000001 | 154 | | | |

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

1 row in set (0.00 sec)

mysql> show binlog events in 'mysql_binlog.000001';

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

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

| mysql_binlog.000001 | 4 | Format_desc | 101 | 123 | Server ver: 5.7.22-log, Binlog ver: 4 |

| mysql_binlog.000001 | 123 | Previous_gtids | 101 | 154 | |

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

2 rows in set (0.00 sec)

刷新binlog文件，即新建立一个binlog日志文件。

mysql> show binary logs;

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

| Log_name | File_size |

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

| mysql_binlog.000001 | 154 |

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

1 row in set (0.00 sec)

mysql> flush binary logs;

Query OK, 0 rows affected (0.00 sec)

mysql> flush binary logs;

Query OK, 0 rows affected (0.00 sec)

mysql> show binary logs;

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

| Log_name | File_size |

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

| mysql_binlog.000001 | 204 |

| mysql_binlog.000002 | 204 |

| mysql_binlog.000003 | 154 |

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

3 rows in set (0.00 sec)

flush logs, flush tables with read , flush tables for export这些命令不会写binlog日志，因为如果写了binlog，可能会在主从复制时产生问题。

FLUSH LOGS, FLUSH TABLES WITH READ LOCK (with or without a table list),

and FLUSH TABLES tbl_name ... FOR EXPORT are not written to the binary

slave.

show binlog events 语句用于查看指定的binlog日志文件中记录的binlog事件。

binlog日志内容查看

假定目前binlog_format=row。

先用flush logs创建一个新的binlog文件。

mysql> flush logs;

Query OK, 0 rows affected (0.01 sec)

mysql> show master status;

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

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

| mysql_binlog.000046 | 154 | | | |

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

1 row in set (0.00 sec)

使用mysqlbinlog程序查看文件内容，DECODE-ROWS选项表示解析原始输出信息中的BASE64数据块为SQL语句，否则将看到BASE64数据块。

[root@101 data]# mysqlbinlog -v --base64-output=DECODE-ROWS mysql_binlog.000046

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;

/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER /*!*/;

# at 4

#180920 0:09:37 server id 101 end_log_pos 123 CRC32 0xc5c14f6f Start: binlog v 4, server v 5.7.22-log created 180920 0:09:37

# Warning: this binlog is either in use or was not closed properly.

# at 123

#180920 0:09:37 server id 101 end_log_pos 154 CRC32 0xa91b6a91 Previous-GTIDs

# [empty]

SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;

DELIMITER ;

# End of log file

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

使用下面的命令去掉mysqlbinlog输出中的注释语句，使得输出的日志信息更容易观察。

root@101 data]# mysqlbinlog -v --base64-output=DECODE-ROWS mysql_binlog.000046 |grep -E -v "^/*"

DELIMITER /*!*/;

# at 4

#180920 0:09:37 server id 101 end_log_pos 123 CRC32 0xc5c14f6f Start: binlog v 4, server v 5.7.22-log created 180920 0:09:37

# Warning: this binlog is either in use or was not closed properly.

# at 123

#180920 0:09:37 server id 101 end_log_pos 154 CRC32 0xa91b6a91 Previous-GTIDs

# [empty]

SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;

DELIMITER ;

# End of log file

mysql_binlog.000046文件的日志从position=4的位置开始，到end_log_pos=154结束，这个文件是mysql当前产生的最新的一个binlog文件，end_log_pos与show master status的输出信息的position的值相同。由此可见，show master status输出的position是最新的binlog文件的末尾位置。

在搭建主从复制架构时，从机的change master to语句中的master_log_position=xxxxx，这个xxxxx值不能简单的赋值为show master status的position值。应该考虑实际情况再决定使用什么数值。

如果从机是按照主机的最新的备份制作的数据库，那么可以使用show master status的position值,此时master_log_file也应该是这里输出的file的值。

如果从机不是按照最新的备份制作的数据库，那么需要仔细分析后根据位置信息和文件信息以及具体时间点进行设置。

执行一个SQL语句。

mysql> show master status;

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

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

| mysql_binlog.000046 | 154 | | | |

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

1 row in set (0.00 sec)

mysql> insert into t2 (name) values ('A1'),('B1');

Query OK, 2 rows affected (0.01 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql> show master status;

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

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

| mysql_binlog.000046 | 421 | | | |

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

1 row in set (0.00 sec)

binlog Position从154变成了421。

查看新的binlog日志：

root@101 data]# mysqlbinlog -v --base64-output=DECODE-ROWS mysql_binlog.000046 |grep -E -v "^/*"

DELIMITER /*!*/;

# at 4

#180920 0:09:37 server id 101 end_log_pos 123 CRC32 0xc5c14f6f Start: binlog v 4, server v 5.7.22-log created 180920 0:09:37

# Warning: this binlog is either in use or was not closed properly.

# at 123

#180920 0:09:37 server id 101 end_log_pos 154 CRC32 0xa91b6a91 Previous-GTIDs

# [empty]

# at 154

#180920 0:23:30 server id 101 end_log_pos 219 CRC32 0xc8b9a40f Anonymous_GTIDlast_committed=0 sequence_number=1 rbr_only=yes

SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;

# at 219

#180920 0:23:30 server id 101 end_log_pos 291 CRC32 0xb2d919c9 Query thread_id=2 exec_time=0 error_code=0

SET TIMESTAMP=1537374210/*!*/;

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=1436549152/*!*/;

SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;

SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;

SET @@session.lc_time_names=0/*!*/;

SET @@session.collation_database=DEFAULT/*!*/;

BEGIN

# at 291

#180920 0:23:30 server id 101 end_log_pos 339 CRC32 0x097d51d0 Table_map: `test`.`t2` mapped to number 110

# at 339

#180920 0:23:30 server id 101 end_log_pos 390 CRC32 0x025373f6 Write_rows: table id 110 flags: STMT_END_F

### INSERT INTO `test`.`t2`

### SET

### @1=1

### @2='A1'

### INSERT INTO `test`.`t2`

### SET

### @1=2

### @2='B1'

# at 390

#180920 0:23:30 server id 101 end_log_pos 421 CRC32 0x077fb982 Xid = 26

COMMIT/*!*/;

SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;

DELIMITER ;

# End of log file

在position=339的地方写入一个事务的2条新记录，然后执行了提交操作，end_log_pos=421。

执行2条update语句：

mysql> update t2 set name='AAAA1' where id=1;

Query OK, 1 row affected (0.01 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> update t2 set name='AAAA2' where id=2;

Query OK, 1 row affected (0.01 sec)

Rows matched: 1 Changed: 1 Warnings: 0

继续查看binlog日志信息，使用--start-position选项指定从position=421(之前的binlog日志前面已经查看过了)开始查看binlog日志。

[root@101 data]# mysqlbinlog --start-position=421 -v --base64-output=DECODE-ROWS mysql_binlog.000046 |grep -E -v "^/*"

DELIMITER /*!*/;

# at 421

#180920 0:30:38 server id 101 end_log_pos 486 CRC32 0x45344b55 Anonymous_GTIDlast_committed=1 sequence_number=2 rbr_only=yes

SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;

# at 486

#180920 0:30:38 server id 101 end_log_pos 558 CRC32 0xdb968326 Query thread_id=2 exec_time=0 error_code=0

SET TIMESTAMP=1537374638/*!*/;

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=1436549152/*!*/;

SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;

SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;

SET @@session.lc_time_names=0/*!*/;

SET @@session.collation_database=DEFAULT/*!*/;

BEGIN

# at 558

#180920 0:30:38 server id 101 end_log_pos 606 CRC32 0xe179cc2d Table_map: `test`.`t2` mapped to number 110

# at 606

#180920 0:30:38 server id 101 end_log_pos 661 CRC32 0x26f63b69 Update_rows: table id 110 flags: STMT_END_F

### UPDATE `test`.`t2`

### WHERE

### @1=1

### @2='A1'

### SET

### @1=1

### @2='AAAA1'

# at 661

#180920 0:30:38 server id 101 end_log_pos 692 CRC32 0x2dcc1cd6 Xid = 29

COMMIT/*!*/;

# at 692

#180920 0:30:53 server id 101 end_log_pos 757 CRC32 0x07b40b05 Anonymous_GTIDlast_committed=2 sequence_number=3 rbr_only=yes

SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;

# at 757

#180920 0:30:53 server id 101 end_log_pos 829 CRC32 0xbbb5f160 Query thread_id=2 exec_time=0 error_code=0

SET TIMESTAMP=1537374653/*!*/;

BEGIN

# at 829

#180920 0:30:53 server id 101 end_log_pos 877 CRC32 0x96ab4362 Table_map: `test`.`t2` mapped to number 110

# at 877

#180920 0:30:53 server id 101 end_log_pos 932 CRC32 0x9b9c8a78 Update_rows: table id 110 flags: STMT_END_F

### UPDATE `test`.`t2`

### WHERE

### @1=2

### @2='B1'

### SET

### @1=2

### @2='AAAA2'

# at 932

#180920 0:30:53 server id 101 end_log_pos 963 CRC32 0x1c975da3 Xid = 30

COMMIT/*!*/;

SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;

DELIMITER ;

# End of log file

mysqlbinlog程序输出的日志信息中，可以看到数据库中执行的写操作的SQL语句的基本原型,不包含字段名称。字段名全部都使用@1,@2等代替了，这里@1表示第1个字段，@2表示第2个字段，依次类推。由于这些SQL中已经包含了表名，可以很容易取到字段的顺序以及名称。因此，可以根据这些SQL语句还原出真实的包含了字段名的SQL语句。

mysql> desc t2;

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

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

| id | int(11) | NO | PRI | NULL | auto_increment |

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

2 rows in set (0.00 sec)

### UPDATE `test`.`t2`

### WHERE

### @1=2

### @2='B1'

### SET

### @1=2