mysql dba系统学习（7）二进制日志之三

mingk

二进制日志的相关系统变量

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,binlog_cache_use
表示的是当前事物的数量
当前没有事物
mysql> showstatus like "%binlog_cache_use%";
+------------------+-------+
| Variable_name| Value |
+------------------+-------+
| Binlog_cache_use | 0|
+------------------+-------+

创建事物
mysql>show table status like 'tt'\G;
*************************** 1. row***************************
Name: tt
Engine: MyISAM（这种引擎不支持事物）
Version: 10
Row_format: Fixed
Rows: 1
Avg_row_length: 8
Data_length: 8
Max_data_length: 2251799813685247
Index_length: 1024
Data_free: 0
Auto_increment: NULL
Create_time: 2013-09-03 11:42:18
Update_time: 2013-09-03 15:24:13
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
mysql>drop table tt;
Query OK, 0 rows affected (0.01 sec)

mysql> create table tt(id int)engine=innodb; 设置成innodb的引擎
Query OK, 0 rows affected, 2 warnings (0.02sec)

mysql> insert into tt values(1);
Query OK, 1 row affected (0.00 sec)

mysql> show variables like"autocommit";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit| ON|
+---------------+-------+
1 row in set (0.00 sec)

mysql> setautocommit=0;关闭自动提交事物
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like"autocommit";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit| OFF|
+---------------+-------+
1 row in set (0.01 sec)

mysql> insert into tt values(2);
Query OK, 1 row affected (0.01 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql>showstatus like"%binlog_cache_use%";
+------------------+-------+
| Variable_name| Value |
+------------------+-------+
| Binlog_cache_use | 2|
+------------------+-------+
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，那么每次提交二个事物都会与磁盘同步一次数据

dog1888

下雨了，别忘了打伞，湿身是小，淋病就麻烦啦*^_^*

lakers009

活在这个腐败的社会.何必把自己装的那么清高