mysql二進位制日誌相關引數

lsq_008發表於2015-08-18
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，那麼每次提交二個事物都會與磁碟同步一次資料

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/10972173/viewspace-1774931/，如需轉載，請註明出處，否則將追究法律責任。

相關文章