mysqlbinlog解析出的SQL语句被批注是怎么回事,MySQL禁止binlog日志中的BINLOG部分

MySQL mysqlbinlog解析出的SQL语句被注释是怎么回事

MySQL抑制binlog日志中的BINLOG部分

MySQL通过binlog来记录整个数据的变更过程,因此我们只要有MySQL的binlog日志即可完整的还原数据库。MySQL binlog日志记录有3种不同的方式,即:STATEMENT,MIXED,ROW。对于不同的日志模式,生成的binlog有不同的记录方式。对于MIXED(部分SQL语句)和ROW模式是以base-64方式记录,会以BINLOG开头,是一段伪SQL,我们可以用使用base64-output参数来抑制其显示。本文对此给出了描述及演示。

有关mysqlbinlog的用法,请参考:使用mysqlbinlog提取二进制日志

 

1、mysqlbinlog之base64-output参数

--base64-output=value

This option determines when events should be displayed encoded as base-64 strings using BINLOG statements. The option has these permissible values (not case sensitive):

AUTO ("automatic") or UNSPEC ("unspecified") displays BINLOG statements automatically when necessary (that is, for format description events and row events). If no --base64-output option is given, the effect is the same as --base64-output=AUTO. NEVER causes BINLOG statements not to be displayed. mysqlbinlog exits with an error if a row event is found that must be displayed using BINLOG.DECODE-ROWS specifies to mysqlbinlog that you intend for row events to be decoded and displayed as commented SQL statements by also specifying the --verbose option. Like NEVER, DECODE-ROWS suppresses display of BINLOG statements, but unlike NEVER, it does not exit with an error if a row event is found.

以上描述对于binlog日志中的BINLOG部分,如果要过虑掉需要指定DECODE-ROWS 以及--verbose选项。

 

The SQL statements produced by --verbose for row events are much more readable than the corresponding BINLOG statements. However, they do not correspond exactly to the original SQL statements that generated the events. The following limitations apply:

--verbose选项可以获取更多的可读信息,但是并不是一个原始的SQL语句(类似的)。

· The original column names are lost and replaced by @N, where N is a column number.

· Character set information is not available in the binary log, which affects string column display:

There is no distinction made between corresponding binary and nonbinary string types (BINARY and CHAR,VARBINARY and VARCHAR, BLOB and TEXT). The output uses a data type of STRING for fixed-length strings andVARSTRING for variable-length strings.For multibyte character sets, the maximum number of bytes per character is not present in the binary log, so the length for string types is displayed in bytes rather than in characters. For example, STRING(4) will be used as the data type for values from either of these column types:

CHAR(4) CHARACTER SET latin1

CHAR(2) CHARACTER SET ucs2

Due to the storage format for events of type UPDATE_ROWS_EVENT, UPDATE statements are displayed with theWHERE clause preceding the SET clause.

Proper interpretation of row events requires the information from the format description event at the beginning of the binary log. Because mysqlbinlog does not know in advance whether the rest of the log contains row events, by default it displays the format description event using a BINLOG statement in the initial part of the output.

If the binary log is known not to contain any events requiring a BINLOG statement (that is, no row events), the --base64-output=NEVER option can be used to prevent this header from being written.

 

2、演示生成binlog日志

--环境
mysql> show variables like 'version';
+---------------+------------+
| Variable_name | Value      |
+---------------+------------+
| version       | 5.6.12-log |
+---------------+------------+

--如下查询binlog为row记录模式
mysql> show variables like 'binlog_for%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+

mysql> reset master;
Query OK, 0 rows affected (0.01 sec)

mysql> show master status;
+-----------------+----------+--------------+------------------+-------------------+
| File            | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+-------------------+
| APP01bin.000001 |      120 |              |                  |                   |
+-----------------+----------+--------------+------------------+-------------------+

mysql> use test;
Database changed

--创建表t1
mysql> create table t1(id smallint,val varchar(20));
Query OK, 0 rows affected (0.01 sec)

--插入单条记录
mysql> insert into t1 values(1,'robin');
Query OK, 1 row affected (0.00 sec)

--清空表
mysql> truncate table t1;
Query OK, 0 rows affected (0.01 sec)

--查看binlog events
mysql> show binlog events;
+-----------------+-----+-------------+-----------+-------------+----------------------------------------------------------+
| Log_name        | Pos | Event_type  | Server_id | End_log_pos | Info                                                     |
+-----------------+-----+-------------+-----------+-------------+----------------------------------------------------------+
| APP01bin.000001 |   4 | Format_desc |        11 |         120 | Server ver: 5.6.12-log, Binlog ver: 4                    |
| APP01bin.000001 | 120 | Query       |        11 |         238 | use `test`; create table t1(id smallint,val varchar(20)) |
| APP01bin.000001 | 238 | Query       |        11 |         310 | BEGIN                                                    |
| APP01bin.000001 | 310 | Table_map   |        11 |         358 | table_id: 74 (test.t1)                                   |
| APP01bin.000001 | 358 | Write_rows  |        11 |         402 | table_id: 74 flags: STMT_END_F                           |
| APP01bin.000001 | 402 | Xid         |        11 |         433 | COMMIT /* xid=30 */                                      |
| APP01bin.000001 | 433 | Query       |        11 |         517 | use `test`; truncate table t1                            |
+-----------------+-----+-------------+-----------+-------------+----------------------------------------------------------+
7 rows in set (0.00 sec)

--获取binlog位置
mysql> show variables like 'log_bin_basename';
+------------------+--------------------+
| Variable_name    | Value              |
+------------------+--------------------+
| log_bin_basename | /opt/data/APP01bin |
+------------------+--------------------+

3、演示提取binlog日志

BINLOG ' #这个BINLOG部分是真实的SQL语句,无法看到具体内容<br >fzcsvbmlaaaamaaaagybaaaaaeoaaaaaaaeabhrlc3qaanqxaaicdwi8aameualg<br="" >fzcsvb4laaaalaaaajibaaaaaeoaaaaaaaeaagac="" wbaavyb2jpbv7cujq="<br" >'="" *!*="" ;

#使用-v参数的情形,可以看到我们操作生成的SQL语句了,为insert into [email protected]之类的形式,如果-vv则输出列的描述信息

BINLOG '<br >fzcsvbmlaaaamaaaagybaaaaaeoaaaaaaaeabhrlc3qaanqxaaicdwi8aameualg<br="" >fzcsvb4laaaalaaaajibaaaaaeoaaaaaaaeaagac="" wbaavyb2jpbv7cujq="<br" >'="" *!*="" ;

#添加--base64-output=DECODE-ROWS选项来抑制BINLOG的显示,如下我们看不到了BINLOG部分

#此时使用mysqlbinlog做一个不完全恢复

#查看恢复后的结果

 

MySQL通过binlog来记录整个数据的变更过程,因此我们只要有MySQL的binlog日志即可完整的还原数据库。MySQL bi...

认识MySQL日志

  • 错误日志
    • --log-error
    • host_name.err
    • error.log
    • 默认在datadir下
  • 慢查询日志
    • --slow_query_log
    • --long_query_time
    • host_name-slow.log
    • slow_log
    • mysqldumpslow
    • pt-query-digest
  • 常规日志
    • --general_log
    • host_name.log
    • general_log
  • 二进制日志
    • --log_bin
    • --expire-log-days
    • host_name-bin.000001
    • mysqlbinlog
    • binlog2sql
    • binary log什么时间刷新
      • 达到max_binlog_size
      • 执行flush logs;命令
      • 服务器重启,会扫描binlog.seq,并生成一个最后的binlog.seq+1的binlog文件
    • 二进制日志记录的格式
      • 建议binlog_format=row
    • 查询二进制日志文件
      • show binary logs 列出当前的日志文件及大小
      • show master status;显示MySQL当前的日志及状态(需要super,replication,client权限)
      • show binlog events in 'mysql-bin.000010';
        • mysql的二进制日志是以’事件‘(event)为单位存储到日志中,一个insert,update,....有多个事件组成
        • 专业名词:日志文件:mysql-bin.000010,字节偏移量(位置)
      • 一个dml语句基本事物包括哪些events
        • GTID
        • query
        • table_map
        • write_rows
        • xid
      • 一个ddl语句基本事物包括哪些events
        • GTID
        • query
    • 查看二进制日志
      • 二进制格式,无法用文本查看
      • 日志以紧凑二进制格式存储,以事件组合
      • 使用mysqlbinlog查看
        • mysqlbinlog -v --base64-out-put=decode-rows mysql-bin.000010|more
        • mysqlbinlog -v --base64-out-put=decode-rows mysql-bin.000010 mysql-bin.000011
        • --start-positon 在哪个位置开始
        • --stop-position 在那个位置结束
        • 可以将这个命令的参数好好看一下
    • 二进制日志维护
      • 删除二进制日志
      • 基于时间删除日志
        • set global expire_logs_days=7;
        • purge binary logs before now() -interval 3 days;
      • 根据文件名删除
        • purge binary logs to 'mysql-bin.000010';
      • 注意:主库purge时要确保日志已经传到从库上了,否则复制会出现问题,还有这个命令不会写到binlog中,所以从库是不会删除任何东西的
  • 中继日志
    • hostname-relay.log
    • 这里如果使用默认配置,主机名修改后主从复制会出问题,因为sql线程找不到relay.log了,需要注意
    • relay_log_purge relay-log消费完了就清理掉
    • relay_log_space_limit reloy-log超过多少就不去master拉取日志了,可能会导致日志没同步过来的情况,特殊有需求才开
  • 审计日志
    • audit_log
    • audit_log_file
    • audit.log
    • 官方收费组件,需要购买企业版
    • 基于策略的日志记录
    • 通过audit_log_policy选项设置
    • 提供日志记录选项ALL,NONE,LOGINS或QUERIES
    • 默认为ALL
    • 在日志文件中生成一个服务器活动审计记录
    • 内容取决于策略,可能包括:
      • 在系统上发生的错误的记录
      • 客户机连接和断开连接的时间
      • 客户机在连接期间执行的操作
      • 客户机访问的数据库和表

 

MySQL DBA日常管理命令

  • 总览
    • 5.7新增加的sys schema,可以去官方手册上面学习,叶老师博客上有翻译
    • 认识information_schema数据库,这个数据库是memory引擎的,在ibdata1中
    • 学习利用information_schena的字典信息生成语句
      • ps:information_schema相当于MySQL的中央信息库模式和模式对象,服务器的统计信息(状态变量,设置,连接),该库不持久化,'虚拟数据库'可以通过select访问
  • information_schema
    • 常见用法
      • select table_name , engine from information_schema.tables where table_schema = 'xxx';
      • select character_set_name,collation_name from information_schema.collation where id_default='Yes';
      • select table_schema,count(*) from information_schema.tables group by table_schema;
      • ps:information_schema是只读库

一网友反馈使用mysqlbinlog解析出的二进制日志中的内容中,有些SQL语句有#注释的情况,这个是怎么回事呢?我们通过实验来了解一下具体细节情况,如下所示,实验环境为5.6.20-enterprise-commercial-advanced-log

show语句及相关字典查询

  • 核心语句
    • show databases;
    • show tables;
    • show processlist;
    • show create table table_name;
    • show index from table_name;
    • show open tables; #查看打开了哪些表
    • show table status;
    • show grants for user;
    • help show; #忘了看帮助

 

MySQL目录结构

  • 数据目录
    • data目录
      • auto.cnf #server-uuid
      • error.log #错误日志
      • ib_buffer_pool #insert_buffer
      • ibdata1 #系统表空间
      • ib_logfile0 #redolog
      • ibtmp1 #临时表文件
    • logs目录 #binlog目录
    • tmp目录
  • MySQL server目录
    • bin #管理命令目录
      • mysqld
      • mysql
      • mysqldump
      • mysqlbinlog
      • mysqladmin
      • mysql_config_edior
      • perror
      • mysqlslap #做性能测试
      • resolveip #ip解析,这个解析不对可能会连接不上数据库,可能发生的场景是将数据库目录拷贝到别的机器上启动后连接不上,可以用这个命令看一下解析是否出问题了
    • docs #文档文件
    • include #c语言头文件目录
      • /usr/local/mysql/bin/mysql_config文件内有c语言include目录地址,有些软件会用到这个文件去找到include目录和lib目录以及plugin目录等
    • lib #引用的库文件
      • 在lib目录中有个plugin目录,里面有一些MySQL的插件在里边,比如innodb引擎,半同步插件等
    • man #帮助手册目录,如果man命令找不到,可以执行以下命令:cp man/man* /usr/local/share/man/ -r
    • share #字符集和初始化sql
    • support_files #脚本目录
      • mysql.server #守护进程启动前启动
      • mysql-log-rotate #log切换脚本
      • mysql_multi.server #多实例脚本

 

# whereis mysqlbinlog

mysqlbinlog: /usr/bin/mysqlbinlog /usr/share/man/man1/mysqlbinlog.1.gz

 

我们先在参数文件my.cnf里面设置binlog_format=ROW ,然后重启一下MySQL服务

 

 

mysql> show variables like 'binlog_format';

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

| Variable_name | Value |

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

| binlog_format | ROW   |

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

1 row in set (0.00 sec)

 

mysql> show master status;

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

| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

| DB-Server-bin.000005 |      512 |              |                  |                   |

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

1 row in set (0.00 sec)

 

mysql> 

 

 

 

mysql> drop table kkk;

Query OK, 0 rows affected (0.01 sec)

 

mysql> create table kkk (id int ,name varchar(32));

Query OK, 0 rows affected (0.02 sec)

 

mysql> insert into kkk

    -> select 100, 'name' union all

    -> select 200, 'kerry' union all

    -> select 300, 'k3';

Query OK, 3 rows affected (0.02 sec)

Records: 3  Duplicates: 0  Warnings: 0

 

mysql> 

 

默认情况下只能看到一些经过base-64编码的信息,如下所示:

 

[root@DB-Server ~]# /usr/bin/mysqlbinlog  /data/mysql/DB-Server-bin.000005

 

 

 

use `MyDB`/*!*/;

SET TIMESTAMP=1530288296/*!*/;

DROP TABLE `kkk` /* generated by server */

/*!*/;

# at 628

#180629 12:05:14 server id 1  end_log_pos 745 CRC32 0xc6037a3f  Query   thread_id=3     exec_time=0     error_code=0

SET TIMESTAMP=1530288314/*!*/;

create table kkk (id int ,name varchar(32))

/*!*/;

# at 745

#180629 12:06:07 server id 1  end_log_pos 817 CRC32 0x74fd4efb  Query   thread_id=3     exec_time=0     error_code=0

SET TIMESTAMP=1530288367/*!*/;

BEGIN

/*!*/;

# at 817

#180629 12:06:07 server id 1  end_log_pos 866 CRC32 0xfb1391dd  Table_map: `MyDB`.`kkk` mapped to number 73

# at 866

#180629 12:06:07 server id 1  end_log_pos 930 CRC32 0xecb4e812  Write_rows: table id 73 flags: STMT_END_F

 

BINLOG '

71g2WxMBAAAAMQAAAGIDAAAAAEkAAAAAAAEABE15REIAA2trawACAw8CYAAD3ZET+w==

71g2Wx4BAAAAQAAAAKIDAAAAAEkAAAAAAAEAAgAC//xkAAAABG5hbWX8yAAAAAVrZXJyefwsAQAA

AmszEui07A==

'/*!*/;

# at 930

#180629 12:06:07 server id 1  end_log_pos 961 CRC32 0x7ca988e3  Xid = 37

COMMIT/*!*/;

DELIMITER ;

# End of log file

ROLLBACK /* added by mysqlbinlog */;

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

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

 

mysqlbinlog有一个参数--verbose(或-v),将自动生成带注释的SQL语句(在行事件中重构伪SQL语句),其实这个并非原始SQL语句,而是伪SQL,如果使用这个参数两次(如-v -v),则输出列的描述信息,会生成字段的类型、长度、是否为null等属性信息:

 

-v, --verbose       Reconstruct pseudo-SQL statements out of row events. -v  -v adds comments on column data types.

 

[root@DB-Server ~]# /usr/bin/mysqlbinlog -v /data/mysql/DB-Server-bin.000005

 

图片 1

 

如上所示,其实这里的SQL语句不是原始SQL语句,那么能否看到原始SQL语句呢?答案是可以,但是必须设置系统变量binlog_rows_query_log_events

 

mysql> show variables like 'binlog_rows_query_log_events';

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

| Variable_name                | Value |

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

| binlog_rows_query_log_events | OFF   |

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

1 row in set (0.00 sec)

 

mysql> set binlog_rows_query_log_events=1;

Query OK, 0 rows affected (0.00 sec)

 

mysql> flush logs;

Query OK, 0 rows affected (0.01 sec)

 

mysql>  show master status;

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

| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

| DB-Server-bin.000026 |      120 |              |                  |                   |

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

1 row in set (0.00 sec)

 

mysql>

mysql> insert into kkk select 600, 'k600';

Query OK, 1 row affected (0.00 sec)

Records: 1  Duplicates: 0  Warnings: 0

 

[root@DB-Server ~]# /usr/bin/mysqlbinlog  --base64-output=DECODE-ROWS  -v -v /data/mysql/DB-Server-bin.000026

 

图片 2

 

 

 

 

在二进制日志格式为MIXED模式下,简单测试没有发现SQL被注释的情况,记录的都是原始的SQL语句。不清楚是否存在某些特殊情况也会出现这种情况。网友反馈腾讯云的MySQL在MIXED模式下,也会出现这种情况,不过没有使用过腾讯的MySQL,手头也没有测试环境,只能作罢!

本文由澳门威斯尼人平台登录发布于澳门威斯尼人平台登录,转载请注明出处:mysqlbinlog解析出的SQL语句被批注是怎么回事,MySQL禁止binlog日志中的BINLOG部分

相关阅读