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，那么每次提交二个事物都会与磁盘同步一次数据

ITPub博客

mysql二进制日志相关参数

最新文章