MySQL-Binlog二进制日志介绍20200106

阅读量：4172 次

发布时间：2019-05-26

本文共 15742 字，大约阅读时间需要 52 分钟。

MySQL-Binlog二进制日志介绍

概述

MySQL 的二进制日志 binlog 可以说是 MySQL 最重要的日志，它记录了所有的 DDL 和 DML 语句（除了数据查询语句select、show等），以事件形式记录，还包含语句所执行的消耗的时间，MySQL的二进制日志是事务安全型的。binlog 的主要目的是复制和恢复。

这个文件记录了mysq1数据库所有的dml , ddl语句事件(不包括select)，记录增删改操作，也可以记录SQL语句，也可以记录行变化记录，还可以记录这些操作的时间。

比如: update itpux表set name=' itpux' where id between 1 and 5;

Binlog日志的两个最重要的使用场景

MySQL主从复制：MySQL Replication在Master端开启binlog，Master把它的二进制日志传递给slaves来达到master-slave数据一致的目的

数据恢复：通过使用 mysqlbinlog工具来使恢复数据，如宕机恢复，异常操作的恢复。

不好的就是:大概损朱1%左右的性能，并且占用空间。

一 MySQL Binlog的三种模式

1.1 row level (默认级别: mysql5.7.6之后+8.0)

日志中记录每--行数据修改的情况。

优点:可以更方便查看每一条数据修改的细节。

缺点:数据量比较大。

使用：希望数据最安全，复制强一致。

1.2 statement Level (默认级别: mysql5.7.6之前)

记录每一条修改的SQL.

优点:解决了数据量比较大的问题。

缺点:容易出现主从复制不一致。

使用场景:使用mysql的功能比较少，又不使用存储过程/触发器/函数

1.3 mixed (混合模式)

结合row level 与statement level 的优点。

二配置binlog二进制日志

show variables like '%log_bin%';

show variables like '%binlog%';

show variables like '%server_id%';

2.1 必须配置参数

2.1.1 log_bin

## 默认为off /mysql/log/3306/itpuxdb-binlog --(这个名字会自动加后缀:.00000*) 设置完路径以后会变成on

2.1.2 log_bin_index

## /mysql/log/3306/itpuxdb-binlog.index 名称

2.1.3 binlog_format

## binlog模式 row mixed statement Level

2.1.4 binlog_rows_query_log_events

## 将设置binlog_rows_query_log_events=on，在binlog格式为row的模式下，也可通过SHOW BINLOG EVENTS in 'binlog.000001';查看完整的sql语句。

## 在row模式下..开启该参数,将把sql语句打印到binlog日志里面.默认是0(off)。

## 虽然将语句放入了binlog,但不会执行这个sql,就相当于注释一样.但对于dba来说,在查看binlog的时候,很有用处。

2.1.5 server_id

## 主从复制，机器的名称(数字:IP+端口: 513306) 只能是存数字真垃圾

2.1.6 binlog_row_image

binlog_row_image这个参数是MySQL5.6新增的参数，默认值是FULL，在5.7版本默认值也是FULL，

binlog_row_image参数可以设置三个合法值: FULL、MINIMAL、NOBLOB

三个不同值的作用如下：

FULL: Log all columns in both the before image and the after image.

binlog日志记录所有前镜像和后镜像。

节省磁盘空间: 低

数据安全性: 高

MINIMAL: Log only those columns in the before image that are required to identify the row to be changed; log only those columns in the after image where a value was specified by the SQL statement, or generated by auto-increment.

binlog日志的前镜像只记录唯一识别列(唯一索引列、主键列)，后镜像只记录修改列。

节省磁盘空间: 高

数据安全性: 低

noblob: Log all columns (same as full), except for BLOB and TEXT columns that are not required to identify rows, or that have not changed。inlog记录所有的列，就像full格式一样。但对于BLOB或TEXT格式的列，如果他不是唯一识别列(唯一索引列、主键列)，或者没有修改，那就不记录。

节省磁盘空间: 中

数据安全性: 中

2.2 文件大小

max_binlog_size 1073741824

## binlog文件大小，范围4K-1G，默认1G。但如果一个SQL事务太大，比如这个SQL产生5G日志，binlog也会达到5G。

2.3 缓存大小

show variables like '%binlog%cache%';

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

| Variable_name | Value |

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

| binlog_cache_size | 32768 |

| binlog_stmt_cache_size | 32768 |

| max_binlog_cache_size | 18446744073709547520 |

| max_binlog_stmt_cache_size | 18446744073709547520 |

2.3.1 binlog_cache_size

show variables like '%binlog%cache%';

| Variable_name | Value |

| binlog_cache_size | 32768 |

## 32K 二进制日志写缓存区大小数据操作buffer pool 》 binlog buffer 》 file system buffer 》 commit 》 binlog file

show status like 'binlog_cache%';

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

| Variable_name | Value |

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

| Binlog_cache_disk_use | 0 |

| Binlog_cache_use | 0 |

Binlog_cache_disk_use ##表示用到binlog缓存冲的次数。

Binlog_cache_use ## 表示使用临时文件来存放binlog缓存的次数,这个比较大，需要增加binlog_cache_size大小。

2.3.2 max_binlog_cache_size

| max_binlog_cache_size | 18446744073709547520 |

## max_binlog_cache_size 表示的是binlog 能够使用的最大cache 内存大小，防止事务因为binlog_cache_size太小，无法进行下去。

2.3.3 binlog_stmt_cache_size

| binlog_stmt_cache_size | 32768 |

## 非事务语句缓存大小 32k

2.3.4 max_binlog_stmt_cache_size

| max_binlog_stmt_cache_size | 18446744073709547520 |

## 非事务语句最大缓存大小，超过就会报错。

2.3.5 binlog_row_image

show variables like '%binlog%row%';

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

| Variable_name | Value |

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

| binlog_row_image | FULL |

针对binlog_format= 'row'格式来设置记录的日志范围:默认是full，还可以是minimal, noblob

2.3.6 binlog_gtid_simple_recovery

show variables like '%binlog%recovery%';

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

| Variable_name | Value |

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

| binlog_gtid_simple_recovery | ON |

## 在主从复制时有影响，现在是on，用于重启/清理时数据库只打开最老和最新的两个binlog计算gtid_purged和gtid_executed,不需要打开所有文件。

2.4 flush disk相关的

2.4.1 sync_binlog:

写binlog的流程:数据操作buffer pool》binlog buffer 》file system buffer 》commit 》binlog file

show variables like '%sync%binlog%';

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

| Variable_name | Value |

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

| sync_binlog | 1 |

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

在写binlog file之前commit有3种模式，分别是:0，1，N

sync_binlog=0: mysql不会主动同步binlog内容到磁盘文件中，而是依赖操作系统刷新文件的机会刷binlog file，一般是1秒刷一次。

sync_ binlog=1: 默认值，mysql主动刷新file system buff到binlog file中，每1次commit，就主动fsync一次。

sync_binlog=N (不是0，也不是1)：mysql主动刷新file system buff到binlog file中，每N次commit，就主动fsync一次。

sync_binlog配合另一个参数innodb_flush_log_at_trx_commit;如果都是1， 数据库挂了以后，最多只丢一条语句或一个事务的数据。.

三 MySQL Binlog statement二进制格式详解

3.1 设置参数

show variables like '%binlog_format%';

| Variable_name | Value |

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

| binlog_format | ROW |

mysql> set global binlog_format='statement';

Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%binlog_format%';

| Variable_name | Value |

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

| binlog_format | ROW |

mysql> set binlog_format='statement';

Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%binlog_format%';

| Variable_name | Value |

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

| binlog_format | STATEMENT |

show variables like '%isolation%';

| Variable_name | Value |

| transaction_isolation | READ-COMMITTED |

| tx_isolation | READ-COMMITTED |

mysql> set transaction_isolation='repeatable-read';

Query OK, 0 rows affected (0.00 sec)

mysql> set global transaction_isolation='repeatable-read';

Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%isolation%';

| Variable_name | Value |

| transaction_isolation | REPEATABLE-READ |

| tx_isolation | REPEATABLE-READ |

select @@global.transaction_isolation,@@global.binlog_format,@@global.transaction_isolation,@@global.binlog_format

| REPEATABLE-READ | STATEMENT |

show master status;

flush logs;

show master status;

3.2 模拟DDL与 DML操作

create table itpuxb1 (

id int(11) primary key not null auto_increment,

rowformat varchar(50) not null,

uuids varchar(50) not null,

timepoint datetime not null default current_timestamp,

CurrentVersion timestamp not null default current_timestamp on update current_timestamp) engine=innodb;

insert into itpuxb1(rowformat, uuids) select 'itpuxb1' ,UUID();

select * from itpuxb1;

1 itpuxb1 313a0e56-5355-11eb-a619-000c29e0c627 2021-01-10 23:04:52 2021-01-10 23:04:52

2 itpuxb1 31d20145-5355-11eb-a619-000c29e0c627 2021-01-10 23:04:53 2021-01-10 23:04:53

3 itpuxb1 32212574-5355-11eb-a619-000c29e0c627 2021-01-10 23:04:54 2021-01-10 23:04:54

4 itpuxb1 32417614-5355-11eb-a619-000c29e0c627 2021-01-10 23:04:54 2021-01-10 23:04:54

update itpuxb1 set rowformat='jjj_update' where id<4;

show master status;

4618

3.3 读取binlog日志

开始 3000 结束4618

mysqlbinlog --start-position=1 --stop-position=4618 /mysql/log/3306/itpuxdb-binlog.000003 | more

开始时间#210110 23:04:53 server id 1313306 end_log_pos 1305 CRC32 0xdad5f2f2 Query thread_id=5 exec_time=0 err

or_code=0

SET TIMESTAMP=1610291093/*!*/;

insert into itpuxb1(rowformat, uuids) select 'itpuxb1' ,UUID()

/*!*/;

# at 1305

结束时间，提交#210110 23:04:53 server id 1313306 end_log_pos 1336 CRC32 0x4fb435b4 Xid = 74

COMMIT/*!*/;

show master status;

| itpuxdb-binlog.000003 | 5303 | |

show databases;

use itpuxdb;

delete from itpuxb1 where id>4;

Query OK, 7 rows affected (0.00 sec)

mysql> show master status;

| itpuxdb-binlog.000003 | 5594 | |

mysqlbinlog --start-position=5303 --stop-position=5600 /mysql/log/3306/itpuxdb-binlog.000003 | more

BEGIN

/*!*/;

开始的点 # at 5453

#210110 23:38:03 server id 1313306 end_log_pos 5563 CRC32 0xc1506367 Query thread_id=7 exec_time=0 err

or_code=0

use `itpuxdb`/*!*/;

SET TIMESTAMP=1610293083/*!*/;

delete from itpuxb1 where id>4

/*!*/;

结束的点 # at 5563

#210110 23:38:03 server id 1313306 end_log_pos 5594 CRC32 0x96252860 Xid = 399

COMMIT/*!*/;

3.4 总结

binlog_format='statement'的时候，DDL及DML都是明文按SQL记录存储。对主从复制的影响:

1）对有些参数，在不同的服务器和不同的时间，执行结果不一样，会导致主从不一致。

2)特别是一些函数: uuid(),user(),时间函数now()

3)性能问题

4）数据异常

四 MySQL Binlog row二进制格式详解

4.1 设置参数

show variables like '%binlog_format%';

| Variable_name | Value |

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

| binlog_format | ROW |

mysql> set global binlog_format='row';

Query OK, 0 rows affected (0.00 sec)

mysql> set binlog_format='row';

Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%binlog_format%';

| Variable_name | Value |

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

| binlog_format | row |

show variables like '%isolation%';

| Variable_name | Value |

| transaction_isolation | READ-COMMITTED |

| tx_isolation | READ-COMMITTED |

mysql> set transaction_isolation='repeatable-read';

Query OK, 0 rows affected (0.00 sec)

mysql> set global transaction_isolation='repeatable-read';

Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%isolation%';

| Variable_name | Value |

| transaction_isolation | REPEATABLE-READ |

| tx_isolation | REPEATABLE-READ |

select @@global.transaction_isolation,@@transaction_isolation,@@global.binlog_format,@@binlog_format;

| REPEATABLE-READ | REPEATABLE-READ | ROW | ROW |

show master status;

flush logs;

show master status;

flush logs;

Query OK, 0 rows affected (0.01 sec)

show master status;

| itpuxdb-binlog.000006 | 154 | |

4.2 模拟DDL与 DML操作

create table itpuxb2 (

id int(11) primary key not null auto_increment,

rowformat varchar(50) not null,

uuids varchar(50) not null,

timepoint datetime not null default current_timestamp,

CurrentVersion timestamp not null default current_timestamp on update current_timestamp) engine=innodb;

insert into itpuxb2(rowformat, uuids) select 'itpuxb1' ,UUID();

select * from itpuxb2 ;

update itpuxb2 set rowformat='jjj_update' where id<3;

show master status;

| itpuxdb-binlog.000006 | 5612 | |

4.3 读取binlog日志

开始 154 结束5612的日志

mysqlbinlog --base64-output=decode-rows -v -v /mysql/log/3306/itpuxdb-binlog.000002 | more

重点说明binlog在row模式下是加密的，必须要加-v ，如果加两个V格式更好一点,--ddl操作是明文的,而dml操作是加密的。

## 开始时间 # at 5303

#210111 21:51:10 server id 1313306 end_log_pos 5581 CRC32 0x87c5917d Update_rows: table id 130 flags: STMT_END_F

### UPDATE `itpuxdb`.`itpuxb2`

### WHERE

### @1=1 /* INT meta=0 nullable=0 is_null=0 */

### @2='itpuxb1' /* VARSTRING(150) meta=150 nullable=0 is_null=0 */

### @3='e7263ace-5413-11eb-ab0e-000c29e0c627' /* VARSTRING(150) meta=150 nullable=0 is_null=0 */

### @4='2021-01-11 21:50:02' /* DATETIME(0) meta=0 nullable=0 is_null=0 */

### @5=1610373002 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */

### SET

### @1=1 /* INT meta=0 nullable=0 is_null=0 */

### @2='jjj_update' /* VARSTRING(150) meta=150 nullable=0 is_null=0 */

### @3='e7263ace-5413-11eb-ab0e-000c29e0c627' /* VARSTRING(150) meta=150 nullable=0 is_null=0 */

### @4='2021-01-11 21:50:02' /* DATETIME(0) meta=0 nullable=0 is_null=0 */

### @5=1610373070 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */

### UPDATE `itpuxdb`.`itpuxb2`

##where 旧事物

### WHERE

### @1=2 /* INT meta=0 nullable=0 is_null=0 */

### @2='itpuxb1' /* VARSTRING(150) meta=150 nullable=0 is_null=0 */

### @3='efacf3ca-5413-11eb-ab0e-000c29e0c627' /* VARSTRING(150) meta=150 nullable=0 is_null=0 */

### @4='2021-01-11 21:50:16' /* DATETIME(0) meta=0 nullable=0 is_null=0 */

### @5=1610373016 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */

### SET

##SET 新事物

### @1=2 /* INT meta=0 nullable=0 is_null=0 */

### @2='jjj_update' /* VARSTRING(150) meta=150 nullable=0 is_null=0 */

### @3='efacf3ca-5413-11eb-ab0e-000c29e0c627' /* VARSTRING(150) meta=150 nullable=0 is_null=0 */

### @4='2021-01-11 21:50:16' /* DATETIME(0) meta=0 nullable=0 is_null=0 */

### @5=1610373070 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */

##结束时间

# at 5581

#210111 21:51:10 server id 1313306 end_log_pos 5612 CRC32 0xaf87500a Xid = 176

COMMIT/*!*/;

4.4 总结

当binlog_format=row，其它参数默认，ddl操作是明文的，而dml操作是加密的,但是只记录行的操作，不记录SQL语句。

对主从复制的影响:

1)同步最安全。

2)不管是更新还是删除，或者批量录数据，都是按行进行，依次处理所有行的记录，而不是整条SQL

show binlog events in '/mysql/log/3306/itpuxdb-binlog.000006'

4.5 binlog_row_image总结

binlog_row_image=full，insert/update语句的set部分是全部的新记录，delete/update的where部分是全部的旧记录。

binlog_row_image=minimal，则update语句只有修改的列内容，delete及update的where部分都没有记录

五 MySQL binlog清理方法

1不建议随便去修改binlog格式（教据库级别)

2 binlog日志的清理

3expire_logs_days:过期时间

show variables like '%expire_logs_days%';

| Variable_name | Value |

| expire_logs_days | 90 |

4 flush logs;切换日志（(oracle:alter system switch logfile;>

5.1 purge手工清理

show master status;

| itpuxdb-binlog.000008 | 154 | |

## 可以查看master数据库当前正在使用的二进制日志及当前执行二进制日志位置

purge binary logs to ' itpuxdb-binlog.000008';手工清理日志。

show binary logs;

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

| Log_name | File_size |

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

| itpuxdb-binlog.000001 | 177 |

| itpuxdb-binlog.000002 | 177 |

| itpuxdb-binlog.000003 | 5617 |

| itpuxdb-binlog.000004 | 206 |

| itpuxdb-binlog.000005 | 206 |

| itpuxdb-binlog.000006 | 5635 |

| itpuxdb-binlog.000007 | 177 |

| itpuxdb-binlog.000008 | 154 |

purge binary logs to 'itpuxdb-binlog.000005';

Query OK, 0 rows affected (0.01 sec)

mysql> show binary logs;

| Log_name | File_size |

| itpuxdb-binlog.000005 | 206 |

| itpuxdb-binlog.000006 | 5635 |

| itpuxdb-binlog.000007 | 177 |

| itpuxdb-binlog.000008 | 154 |

5.2 全部清除

reset master;

全清(一夜回到解决前)。

reset master;

Query OK, 0 rows affected (0.00 sec)

mysql> show binary logs;

| Log_name | File_size |

| itpuxdb-binlog.000001 | 154 |

六 MySQLbinlog

MySQL的二进制日志可以说是MySQL最重要的日志了，它记录了所有的DDL和DML(除了数据查询语句)语句,主要用于MySQL主从复制,恢复的时候，用于闪回。

6.1 show binlog

show master status;

show binary logs;

show binlog events;

| itpuxdb-binlog.000001 | 4 | Format_desc | 1313306 | 123 | Server ver: 5.7.32-log, Binlog ver: 4 |

| itpuxdb-binlog.000001 | 123 | Previous_gtids | 1313306 | 154

6.2参数: in

in '日志文件'，指定要查询的binlog文件名，不指定就默认看第一个。show binlog events in 'itpuxdb-binlog.000001';

show binlog events in 'itpuxdb-binlog.000004';

delete from itpuxb2 where id>5;

show binlog events in 'itpuxdb-binlog.000004';

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

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

| itpuxdb-binlog.000004 | 4 | Format_desc | 1313306 | 123 | Server ver: 5.7.32-log, Binlog ver: 4 |

| itpuxdb-binlog.000004 | 123 | Previous_gtids | 1313306 | 154 | |

| itpuxdb-binlog.000004 | 154 | Anonymous_Gtid | 1313306 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |

| itpuxdb-binlog.000004 | 219 | Query | 1313306 | 294 | BEGIN |

| itpuxdb-binlog.000004 | 294 | Rows_query | 1313306 | 348 | # delete from itpuxb2 where id>5 |

| itpuxdb-binlog.000004 | 348 | Table_map | 1313306 | 411 | table_id: 109 (itpuxdb.itpuxb2) |

| itpuxdb-binlog.000004 | 411 | Delete_rows | 1313306 | 8588 | table_id: 109 |

| itpuxdb-binlog.000004 | 8588 | Delete_rows | 1313306 | 9331 | table_id: 109 flags: STMT_END_F |

| itpuxdb-binlog.000004 | 9331 | Xid | 1313306 | 9362 | COMMIT /* xid=99 */

6.3POS指定位置查询

指定从哪个pos起始点开始查起。

show binlog events in 'itpuxdb-binlog.000004' from 219;

6.4limit查询总条数

show binlog events in 'itpuxdb-binlog.000004' from 219 limit 3;

show master status;

| itpuxdb-binlog.000004| 154 | |

insert into syjname select * from itpux_m1 where AGE>50;

show master status;

| itpuxdb-binlog.000004 | 864071 | |

转载地址：http://nabai.baihongyu.com/

你可能感兴趣的文章

android数据存储与访问之使用pull解析器

查看>>

Android 短信模块分析(七) MMS数据库定义及结构整理

查看>>

Android 短信模块分析(八) MMS数据库表关系

查看>>

Android 图标上面添加提醒(二)使用开源UI类库 Viewbadger

查看>>

Android 图标上面添加提醒(一)使用Canvas绘制

查看>>

Android WebView加载Html右边空白问题的解决方案

查看>>

Android 仿网易新闻v3.5:上下滑动的引导页

查看>>

Android 天气预报图文字幕垂直滚动效果

查看>>

Android硬件加速

查看>>

智慧平安社区系统开发解决方案，智慧小区大数据分析平台建设

查看>>

NQI国家质量技术基础系统开发，国家质量基础设施平台建设

MySQL-Binlog二进制日志介绍

概述

一 MySQL Binlog的三种模式

1.1 row level (默认级别: mysql5.7.6之后+8.0)

1.2 statement Level (默认 级别: mysql5.7.6之前)

1.3 mixed (混合模式)

二 配置binlog二进制日志

2.1 必须配置参数

2.1.1 log_bin

2.1.2 log_bin_index

2.1.3 binlog_format

2.1.4 binlog_rows_query_log_events

2.1.5 server_id

2.1.6 binlog_row_image

2.2 文件大小

2.3 缓存大小

2.3.1 binlog_cache_size

2.3.2 max_binlog_cache_size

2.3.3 binlog_stmt_cache_size

2.3.4 max_binlog_stmt_cache_size

2.3.5 binlog_row_image

2.3.6 binlog_gtid_simple_recovery

2.4 flush disk相关的

2.4.1 sync_binlog:

三 MySQL Binlog statement二进制格式详解

3.1 设置参数

3.2 模拟DDL与 DML操作

3.3 读取binlog日志

3.4 总结

四 MySQL Binlog row二进制格式详解

4.1 设置参数

4.2 模拟DDL与 DML操作

4.3 读取binlog日志

4.4 总结

4.5 binlog_row_image总结

五 MySQL binlog清理方法

5.1 purge手工清理

5.2 全部清除

六 MySQLbinlog

6.1 show binlog

6.2参数: in

6.3POS指定位置查询

6.4limit查询总条数

1.2 statement Level (默认级别: mysql5.7.6之前)

二配置binlog二进制日志