[筆記] mysql binlog

2018110208:31

mysql binlog
用來記錄 mysqld 資料增刪改等 SQL 命令

主要用於 主從的複製 Replication
Master 的 binlog 會傳給 Slave
讓 slave 裡的資料也會如同 Master



主要設定
/etc/my.cnf

log-bin=mysql-bin    #binlog 的檔名
   如果要將 binlog 放在不同的目錄，可以這樣寫
   log-bin= /mnt/sdb1/data/mysql-bin

binlog-format=statement           
   有三種模式，statement / row / mixed



binlog 相關的查詢
 

 

mysql> show variables like '%log_bin%';

+---------------------------------+---------------------------------------+
| Variable_name                   | Value                                 |
+---------------------------------+---------------------------------------+
| log_bin                         | ON                                    |
| log_bin_basename                | /usr/local/mysql/data/mysql-bin       |
| log_bin_index                   | /usr/local/mysql/data/mysql-bin.index |
| log_bin_trust_function_creators | OFF                                   |
| log_bin_use_v1_row_events       | OFF                                   |
| sql_log_bin                     | ON                                    |
+---------------------------------+---------------------------------------+
6 rows in set (0.00 sec)





mysql> select @@binlog_format;

+-----------------+
| @@binlog_format |
+-----------------+
| ROW             |
+-----------------+
1 row in set (0.00 sec)

 

 

 
查詢目前已產生的 binglog 檔案有哪些
**內定檔案滿 1G (或 mysqld 重啟) 就會再以新檔名儲存 
mysql> show binary logs;

+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |    115815 |
| mysql-bin.000002 |     42619 |
| mysql-bin.000003 |    119162 |
| mysql-bin.000004 |     27393 |
| mysql-bin.000005 |      4022 |
| mysql-bin.000006 |    197681 |
| mysql-bin.000007 |    234293 |
| mysql-bin.000008 |     55320 |


**就其實是 /usr/local/mysql/data/mysql-bin.index  檔案的內容




mysql> select @@max_binlog_size ;

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

| @@max_binlog_size |

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

|        1073741824 |

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

1 row in set (0.00 sec)

 

  
查詢 binlog 檔案的內容



命令格式

SHOW BINLOG EVENTS

    [IN 'log_name']

    [FROM pos]

    [LIMIT [offset,] row_count]



mysql> SHOW BINLOG EVENTS in 'mysql-bin.000062' from 0 limit 10;
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                  |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| mysql-bin.000062 |   4 | Format_desc |         1 |         120 | Server ver: 5.6.40-log, Binlog ver: 4 |
| mysql-bin.000062 | 120 | Query       |         1 |         191 | BEGIN                                 |
| mysql-bin.000062 | 191 | Table_map   |         1 |         257 | table_id: 72 (ntt.admin_userlog)  |
| mysql-bin.000062 | 257 | Write_rows  |         1 |         344 | table_id: 72 flags: STMT_END_F        |
| mysql-bin.000062 | 344 | Query       |         1 |         416 | COMMIT                                |
| mysql-bin.000062 | 416 | Query       |         1 |         487 | BEGIN                                 |
| mysql-bin.000062 | 487 | Table_map   |         1 |         553 | table_id: 72 (ntt.admin_userlog)  |
| mysql-bin.000062 | 553 | Write_rows  |         1 |         640 | table_id: 72 flags: STMT_END_F        |
| mysql-bin.000062 | 640 | Query       |         1 |         712 | COMMIT                                |
| mysql-bin.000062 | 712 | Query       |         1 |         783 | BEGIN                                 |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
10 rows in set (0.00 sec)

 


線上修改 binlog 格式

  
mysql> set global binlog_format= ROW



mysql> set global binlog_format= STATEMENT



mysql> set global binlog_format= MIXED

 



有關三種 binlog 格式，這篇講得很清楚
   关于binary log那些事



statment 格式
   最早的 binlog 格式
 

 

# bin/mysqlbinlog data/mysql-bin.000069

:::  ::

# at 87276
#181102 15:03:36 server id 1  end_log_pos 87366 CRC32 0xf95c948d        Query   thread_id=15    exec_time=0     error_code=0
SET TIMESTAMP=1541142216/*!*/;
COMMIT
/*!*/;
# at 87366
#181102 15:03:36 server id 1  end_log_pos 87455 CRC32 0x635a01bb        Query   thread_id=15    exec_time=0     error_code=0
SET TIMESTAMP=1541142216/*!*/;
BEGIN
/*!*/;
# at 87455
#181102 15:03:36 server id 1  end_log_pos 87678 CRC32 0x16bc6620        Query   thread_id=15    exec_time=0     error_code=0
SET TIMESTAMP=1541142216/*!*/;
UPDATE `wp_postmeta` SET `meta_value` = '1541142071:1' WHERE `post_id` = 32 AND `meta_key` = '_edit_lock' AND `meta_value` = '1541141999:1'
/*!*/;
# at 87678
#181102 15:03:36 server id 1  end_log_pos 87768 CRC32 0x75950080        Query   thread_id=15    exec_time=0     error_code=0
SET TIMESTAMP=1541142216/*!*/;
COMMIT
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

 


row 格式
 
  
# bin/mysqlbinlog data/mysql-bin.000062

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#181101 17:32:47 server id 1  end_log_pos 120 CRC32 0x0f9545e1  Start: binlog v 4, server v 5.6.40-log created 18110            1 17:32:47 at startup
ROLLBACK/*!*/;
BINLOG '
P8jaWw8BAABBdAAHHgAAAAAAAQANS42LjQwLWxvZwAAAAAAAAAAAAAACCCCAAAACCAAAAAAAAAA
AAEEAAAAAAAAADDAAA/yNpbEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCCCCACgoKGRkCAeFF
lQ8=
'/*!*/;
# at 120

 

  
# bin/mysqlbinlog data/mysql-bin.000062 -v --base64-output=DECODE-ROWS

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#181101 17:32:47 server id 1  end_log_pos 120 CRC32 0x0f9545e1  Start: binlog v 4, server v 5.6.40-log created 181101 17:32:47 at startup
ROLLBACK/*!*/;
# at 120
#181101 22:09:31 server id 1  end_log_pos 191 CRC32 0xa00f75af  Query   thread_id=4     exec_time=0     error_code=0
SET TIMESTAMP=1541081371/*!*/;
SET @@session.pseudo_thread_id=4/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 191
#181101 22:09:31 server id 1  end_log_pos 257 CRC32 0xcb059a16  Table_map: `ntt`.`admin_userlog` mapped to number 72
# at 257
#181101 22:09:31 server id 1  end_log_pos 344 CRC32 0x884c72fb  Write_rows: table id 72 flags: STMT_END_F
### INSERT INTO `ntt`.`admin_userlog`
### SET
###   @1=1022
###   @2=6
###   @3=0
###   @4=0
###   @5='登入 ip=192.168.0.153'
###   @6=2018-11-01 22:09:30
# at 344
#181101 22:09:31 server id 1  end_log_pos 416 CRC32 0x0ea8f74f  Query   thread_id=4     exec_time=0     error_code=0
SET TIMESTAMP=1541081371/*!*/;
COMMIT

 

其它常用參數
指定時間 
bin/mysqlbinlog data/mysql-bin.000062 -v --base64-output=DECODE-ROWS \
 --start-datetime="2010-04-01 10:40:00" \
 --stop-datetime="2010-04-01 10:55:00" > 285.sql





mixed 格式
5.1.8 起新增
大致綜合 STATEMENT + ROW
大部分情況下是以 STATEMENT 格式在做紀錄，
當改為 READ-COMMITTED 時，會改以 ROW 模式紀錄






特別注意

當 資料庫交易隔離等級 (mysql transaction isolation level)  為 READ-COMMITTED 時
binglog_format 不可設為 STATEMENT 不然會有錯誤  (僅可設定 ROW or MIXED)
 
  **當 REPEATABLE-READ 時，建議設為 ROW  (參考


例如 
mysql> select @@binlog_format;

+-----------------+
| @@binlog_format |
+-----------------+
| STATEMENT       |
+-----------------+

1 row in set (0.00 sec)





mysql> select @@tx_isolation;   //查詢目前是何種 isolation 

+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)



mysql> set session transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)



mysql> select @@tx_isolation;

+----------------+
| @@tx_isolation |
+----------------+

| READ-COMMITTED |

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

1 row in set (0.00 sec)





mysql> insert into testdb values (10,1000);
ERROR 1665 (HY000): Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED.


mysql> set session binlog_format= MIXED;
Query OK, 0 rows affected (0.00 sec)


mysql> insert into testdb values (10,1000);
Query OK, 1 row affected (0.01 sec)

 

 

 


參考

MySQL Innodb 事务隔离级别

Why base64-output=DECODE-ROWS does not print row events in MySQL binary logs

資料庫交易的 Isolation





紀錄所有 SQL 命令 (包含 SELECT 之類的查詢命令)

/etc/my.cnf

log-output=FILE   #若選擇 TABLE 則會記錄在  mysql.slow_log 中
general-log=1
general_log_file="/tmp/general_log.log"

 
線上動態設定：
SET global log_output = 'FILE';
SET global general_log_file = '/tmp/a.txt';
SET global general_log = 1;   // 1 啟用, 0 關閉

Selecting General Query Log and Slow Query Log Output Destinations

 
上一篇

[Swift] status bar 的高度計算

下一篇

2017 全球手機/APP 產業數據

相關文章

我要留言