暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / MySQL binlog日志解析

MySQL binlog日志解析

潍鲸 2020-05-26
956

   MySQL binlog是什么   

binlog 是一个二进制格式的文件，用于记录用户对数据库 更新的SQL语句 信息，例如更改数据库表和更改内容的SQL语句都会记录到binlog里，但是对库表等内容的查询不会记录。binlog 日志是二进制格式的，通过使用 mysqlbinlog 命令解析查看。binlog主要用于复制（Master-Slave 主从同步）、恢复和审计。


   binlog 三种模式   

  • Row Level 模式（RBR）

不记录每条sql语句的上下文信息，只需记录哪条数据被修改，修改了哪些信息。


优势：
* 准确性强，能准确复制数据的变更
* 减少数据库锁的使用


劣势：
* 会产生大量的日志,导致较大的网络IO和磁盘IO
* 要求主从数据库表结构完全一致
* 无法在从数据库上单独执行触发器


  • tatement Level 模式（SBR）

每一条修改数据的 sql 都会记录到 master 的 bin_log 中，slave 在复制的时候 sql 进程会解析成 master 端执行过的相同的 sql 在 slave 库上再次执行。


优势：
* 不需要记录每一条sql语句和每一行的数据变化，减少了binlog日志量，节约IO，提高性能
* 并不强制要求主从数据库的表结构定义完全相同


劣势：
* 在某些情况下会导致 master-slave 中的数据不一致(如sleep()函数， last_insert_id()，以及user-defined functions(udf)等会出现问题)
* 相比于 RBR 模式，在执行复制时需要更多的行锁


  • MIXED模式（MBR）

Row Level模式和tatement Level 模式的混合使用，一般的复制使用 STATEMENT 模式保存 binlog，对于 STATEMENT 模式无法复制的操作使用 ROW 模式保存 binlog，MySQL会根据执行的 SQL 语句选择日志保存方式。


优势：
准确性强，文件大小适中。


劣势：
有可能发生主从不一致问题。

  • binlog模式的选择

不用存储过程、触发器、函数，选择默认的 Statement level 。
用到MySQL的特殊功能（存储过程、触发器、函数）选择Mixed模式。
用到MySQL的特殊功能（存储过程、触发器、函数），又希望数据最大化一直则选择Row模式。


   binlog 常用参数   


  • 管理Binlog相关的SQL命令

SQL命令
命令含义
show master logs;
查看所有Binlog的日志列表
show master status;查看最后一个Binlog日志的编号名称，及最后一个事件结束的位置（pos）
flush logs;刷新Binlog，此刻开始产生一个新编号的Binlog日志文件.
reset master;刷清空所有的Binlog日志


  • 查看binlog日志命令

show binlog events [IN ‘log_name’] [FROM pos] [LIMIT [offset,] row_cout]

SQL命令
命令含义
show binlog events;查看第一个Binlog日志
show binlog events in ‘binlog.000030’;查看指定的Binlog日志
show binlog events in ‘binlog.000030’ from 931;从指定的位置开始，查看指定的Binlog日志
show binlog events in ‘binlog.000030’ from 931 limit 2;从指定的位置开始，查看指定的Binlog日志，限制查询的条数
show binlog events in ‘binlog.000030’ from 931 limit 1,2;从指定的位置开始，带有偏移，查看指定的Binlog日志，限制查询的条数


  • Binlog 中的常用的Event_type

QUERY_EVENT：与数据无关的操作，begin、drop table、truncate table等
TABLE_MAP_EVENT：记录下一个操作所对应的表信息，存储了数据库名和表名
XID_EVNET：标记事物提交
WRITE_ROWS_EVENT：插入数据，即insert操作
UPDATE_ROWS_EVENT：更新数据，即update操作
DELETE_ROWS_EVENT：删除数据，即delete操作


  • Binlog 常用参数配置


参数名
含义
log_bin = {on | off | base_name}指定是否启用记录二进制日志或者指定一个日志路径。
sql_log_bin ={ on | off }指定是否启用记录二进制日志
expire_logs_days指定自动删除二进制日志的时间，即日志过期时间
log_bin_index指定mysql-bin.index文件的路径
binlog_format = { mixed | row | statement }指定二进制日志基于什么模式记录
max_binlog_size指定二进制日志文件最大值
binlog_cache_size指定事务日志缓存区大小
max_binlog_cache_size指定二进制日志缓存最大大小
sync_binlog = { 0 | n }指定写缓冲多少次，刷新一次磁盘


   MySQL binlog日志开启和查看方式   


  • binlog日志开启配置


检查 binlog 功能是否开启，默认关闭。

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | OFF   |
+---------------+-------+
1 row in set (0.00 sec)


开启 binlog 功能

[root@VM_0_15_centos log]# vim /etc/my.cnf
#在 [mysqld] 标签下编辑如下内容：
# binlog 存放路径
log_bin=/var/log/mysql/mysql-bin
# 服务Id,保持唯一
server_id=1


# 修改完配置重启MySQL服务。

重新检查是否开启日志功能
mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.00 sec)

查看 binlog 模式，默认binlog模式是ROW
mysql> show variables like '%binlog_format%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.00 sec)


修改 binlog 模式

[root@VM_0_15_centos log]# vim /etc/my.cnf
[mysqld]
binlog_format=mixed

查看 binlog 模式
mysql> show variables like '%binlog_format%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | MIXED |
+---------------+-------+
1 row in set (0.00 sec)


删除 binlog 文件方式

reset master
# 此命令将会删除所有日志，并让日志文件重新从000001开始。


# 将会清空 00000X 之前的所有日志文件
PURGE { BINARY | MASTER } LOGS { TO ‘log_name’ | BEFORE datetime_expr }
purge master logs to “binlog_name.00000X”


# 修改 my.cnf 配置
expire_logs_days = 3 #保留最近3天的binlog;默认值是0,表示不自动删除.

  • 登录mysql方式查看binlog


只查看第一个binlog文件的内容

show binlog events;


查看指定binlog文件的内容

show binlog events in 'mysql-bin.000002';


查看当前正在写入的binlog文件

show master status\G


获取binlog文件列表

show binary logs;


用mysqlbinlog工具查看


不要查看当前正在写入的binlog文件
不要加–force参数强制访问
如果binlog格式是行模式的,请加 -vv参数


  • 本地查看binlog


基于开始/结束时间
mysqlbinlog --start-datetime='2013-09-10 00:00:00' --stop-datetime='2013-09-10 01:01:01' -d 库名 二进制文件


基于pos值
mysqlbinlog --start-postion=107 --stop-position=1000 -d 库名 二进制文件


转换为可读文本。
mysqlbinlog –base64-output=DECODE-ROWS -v -d 库名 二进制文件


提取指定的binlog日志。
# mysqlbinlog /opt/data/APP01bin.000001
# mysqlbinlog /opt/data/APP01bin.000001|grep insert
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
insert into tb values(2,'WEIJING')


提取指定position位置的binlog日志。
mysqlbinlog --start-position="120" --stop-position="332" /opt/data/APP01bin.000001


提取指定position位置的binlog日志并输出到压缩文件。
mysqlbinlog --start-position="120" --stop-position="332" /opt/data/APP01bin.000001 |gzip >extra_01.sql.gz


提取指定position位置的binlog日志导入数据库。
mysqlbinlog --start-position="120" --stop-position="332" /opt/data/APP01bin.000001 | mysql -uroot -p


提取指定开始时间的binlog并输出到日志文件。
mysqlbinlog --start-datetime="2014-12-15 20:15:23" /opt/data/APP01bin.000002 --result-file=extra02.sql

提取指定位置的多个binlog日志文件。
mysqlbinlog --start-position="120" --stop-position="332" /opt/data/APP01bin.000001 /opt/data/APP01bin.000002|more

提取指定数据库binlog并转换字符集到UTF8。
mysqlbinlog --database=test --set-charset=utf8 /opt/data/APP01bin.000001 /opt/data/APP01bin.000002 >test.sql


  • 远程查看binlog


指定开始/结束时间,并把结果重定向到本地t.binlog文件中。
mysqlbinlog -u username -p password -h192.168.1.116 -P3306 --read-from-remote-server --start-datetime='2018-09-10 20:00:00' --stop-datetime='2018-09-10 21:00:00' mysql-bin.000001 > t.binlog


远程提取日志，指定结束时间。
mysqlbinlog -urobin -p -h192.168.1.116 -P3306 --stop-datetime="2014-12-15 20:30:23" --read-from-remote-server mysql-bin.000033 |more


远程提取使用row格式的binlog日志并输出到本地文件。
mysqlbinlog -uroot -p -P3606 -h192.168.1.177 --read-from-remote-server -vv inst3606bin.000005 >row.sql
mysql
文章转载自潍鲸，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论