mysql 二进制日志大小_mysql二进制日志相关参数

1，binlog_cache_size和max_binlog_cache_size

表示的为每个session的事物分配的缓存

当插入或者修改数据的时候，不会立刻写入磁盘，而是会缓存起来，缓存的大小由binlog_cache_size 来控制

mysql> show variables like '%binlog_cache%';

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

| Variable_name         | Value                |

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

| binlog_cache_size     | 32768                |

| max_binlog_cache_size | 18446744073709547520 |

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

2 rows in set (0.00 sec)

2 binlog_cache_use

表示的是当前事物的数量

mysql> show status like '%binlog_cache_use%';

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

| Variable_name    | Value |

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

| Binlog_cache_use | 7     |

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

1 row in set (0.00 sec)

mysql> show table status like 'emp'\G;

*************************** 1. row ***************************

Name: emp

Engine: InnoDB

Version: 10

Row_format: Compact

Rows: 10

Avg_row_length: 1638

Data_length: 16384

Max_data_length: 0

Index_length: 16384

Data_free: 141557760

Auto_increment: NULL

Create_time: 2015-08-16 21:39:30

Update_time: NULL

Check_time: NULL

Collation: latin1_swedish_ci

Checksum: NULL

Create_options:

Comment:

1 row in set (0.05 sec)

ERROR:

No query specified

mysql> insert into emp values(1000,'lsq');

Query OK, 1 row affected (0.03 sec)

mysql> show status like '%binlog_cache_use%';

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

| Variable_name    | Value |

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

| Binlog_cache_use | 8     |

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

1 row in set (0.00 sec)

mysql> insert into emp values(1001,'lsq');

Query OK, 1 row affected (0.00 sec)

mysql> insert into emp values(1002,'lsq');

Query OK, 1 row affected (0.04 sec)

mysql> insert into emp values(1003,'lsq');

Query OK, 1 row affected (0.02 sec)

mysql> show status like '%binlog_cache_use%';

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

| Variable_name    | Value |

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

| Binlog_cache_use | 11    |

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

1 row in set (0.00 sec)

mysql> show variables like 'autocommit%';

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

| Variable_name | Value |

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

| autocommit    | ON    |

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

1 row in set (0.00 sec)

mysql> set autocommit=0;

Query OK, 0 rows affected (0.02 sec)

mysql> show variables like 'autocommit%';

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

| Variable_name | Value |

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

| autocommit    | OFF   |

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

1 row in set (0.00 sec)

mysql> insert into emp values(999,'lsq');

Query OK, 1 row affected (0.00 sec)

mysql>

mysql>

mysql> show status like '%binlog_cache_use%';

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

| Variable_name    | Value |

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

| Binlog_cache_use | 11    |

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

1 row in set (0.00 sec)

mysql> insert into emp values(998,'lsq');

Query OK, 1 row affected (0.00 sec)

mysql> show status like '%binlog_cache_use%';

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

| Variable_name    | Value |

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

| Binlog_cache_use | 11    |

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

1 row in set (0.00 sec)

mysql> commit;

Query OK, 0 rows affected (0.02 sec)

mysql> show status like '%binlog_cache_use%';

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

| Variable_name    | Value |

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

| Binlog_cache_use | 12    |

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

1 row in set (0.00 sec)

3，max_binlog_size 二进制日志文件的大小

4，sync_binlog

当前的参数是0，代表由系统来控制何时同步

mysql> show variables like"%sync_binlog%";

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

| Variable_name | Value |

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

| sync_binlog| 0|

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

1 row in set (0.00 sec)

如果这个参数是1，那么每次提交一个事物都会与磁盘同步一次数据

如果这个参数是2，那么每次提交二个事物都会与磁盘同步一次数据