1. 檢視binlog日誌是否開啟

mysql預設是不開啟binlog日誌的，需要手動開啟

mysql> show variables like 'log_%';

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

| Variable_name | Value |

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

| log_bin | OFF |

| log_bin_basename | |

| log_bin_index | |

| log_bin_trust_function_creators | OFF |

| log_bin_use_v1_row_events | OFF |

| log_builtin_as_identified_by_password | OFF |

| log_error | C:\Program Files\mysql\data\DESKTOP-7MV7HDB.err |

| log_error_verbosity | 3 |

| log_output | FILE |

| log_queries_not_using_indexes | OFF |

| log_slave_updates | OFF |

| log_slow_admin_statements | OFF |

| log_slow_slave_statements | OFF |

| log_statements_unsafe_for_binlog | ON |

| log_syslog | ON |

| log_syslog_tag | |

| log_throttle_queries_not_using_indexes | 0 |

| log_timestamps | UTC |

| log_warnings | 2 |

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

19 rows in set, 1 warning (0.02 sec)

可以看到log.bin日誌為OFF並沒有開啟

2. 開啟binlog日誌

Linux找到my.cnf配置檔案

windows找到my.ini配置檔案

修改其中的內容

#第一種方式:

#開啟binlog日誌

log_bin=ON

#binlog日誌的基本檔名

log_bin_basename=/var/lib/mysql/mysql-bin

#binlog檔案的索引檔案，管理所有binlog檔案

log_bin_index=/var/lib/mysql/mysql-bin.index

#配置serverid

server-id=1

#第二種方式:

#此一行等同於上面log_bin三行

log-bin=/var/lib/mysql/mysql-bin

#配置serverid

server-id=1

修改完後重新啟動mysql

linux：service mysqld restart

windows：服務->mysql->右鍵重新啟動

執行完後再次檢視binlog

mysql> show variables like 'log_%';

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

| Variable_name | Value |

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

| log_bin | ON |

| log_bin_basename | D:\data\mysql\mysql-bin |

| log_bin_index | D:\data\mysql\mysql-bin.index |

| log_bin_trust_function_creators | OFF |

| log_bin_use_v1_row_events | OFF |

| log_builtin_as_identified_by_password | OFF |

| log_error | C:\Program Files\mysql\data\DESKTOP-7MV7HDB.err |

| log_error_verbosity | 3 |

| log_output | FILE |

| log_queries_not_using_indexes | OFF |

| log_slave_updates | OFF |

| log_slow_admin_statements | OFF |

| log_slow_slave_statements | OFF |

| log_statements_unsafe_for_binlog | ON |

| log_syslog | ON |

| log_syslog_tag | |

| log_throttle_queries_not_using_indexes | 0 |

| log_timestamps | UTC |

| log_warnings | 2 |

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

19 rows in set, 1 warning (0.00 sec)

D:\data\mysql即是binlog日誌存放的目錄

3. 運算元據庫，檢視效果

3.1 檢視當前正在寫入的binlog檔案

mysql> show master status\G

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

File: mysql-bin.000001

Position: 382

Binlog_Do_DB:

Binlog_Ignore_DB:

Executed_Gtid_Set:

1 row in set (0.00 sec)

3.2 檢視指定binlog檔案的內容

這裡做了個測試，感覺linux展示的效果比windows的要好，不知道原因，希望有大佬不吝賜教

#linux環境

mysql> mysql> show binlog events in 'mysql-bin.000001';

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

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

| mysql-bin.000001 | 4 | Format_desc | 1 | 120 | Server ver: 5.6.45-log, Binlog ver: 4 |

| mysql-bin.000001 | 120 | Query | 1 | 211 | BEGIN |

| mysql-bin.000001 | 211 | Query | 1 | 351 | use `usercenter`; DELETE FROM `usercenter`.`sms_vercode` WHERE `id` = 36 |

| mysql-bin.000001 | 351 | Xid | 1 | 382 | COMMIT /* xid=48 */ |

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

#windows環境

mysql> show binlog events in 'mysql-bin.000001';

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

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

| mysql-bin.000001 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.23-log, Binlog ver: 4 |

| mysql-bin.000001 | 123 | Previous_gtids | 1 | 154 | |

| mysql-bin.000001 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |

| mysql-bin.000001 | 219 | Query | 1 | 291 | BEGIN |

| mysql-bin.000001 | 291 | Table_map | 1 | 358 | table_id: 109 (test.student) |

| mysql-bin.000001 | 358 | Update_rows | 1 | 532 | table_id: 109 flags: STMT_END_F |

| mysql-bin.000001 | 532 | Xid | 1 | 563 | COMMIT /* xid=29 */ |

| mysql-bin.000001 | 563 | Anonymous_Gtid | 1 | 628 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |

| mysql-bin.000001 | 628 | Query | 1 | 700 | BEGIN |

| mysql-bin.000001 | 700 | Table_map | 1 | 767 | table_id: 109 (test.student) |

| mysql-bin.000001 | 767 | Update_rows | 1 | 941 | table_id: 109 flags: STMT_END_F |

| mysql-bin.000001 | 941 | Xid | 1 | 972 | COMMIT /* xid=45 */ |

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

12 rows in set (0.01 sec)

3.3 用mysqlbinlog工具檢視

直接使用

#linux環境

/usr/bin/mysqlbinlog /var/lib/mysql/mysql-bin.000001

/*!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

#200904 15:59:30 server id 1 end_log_pos 120 CRC32 0x13ff3b26 Start: binlog v 4, server v 5.6.45-log created 200904 15:59:30 at startup

# Warning: this binlog is either in use or was not closed properly.

ROLLBACK/*!*/;

BINLOG '

4vNRXw8BAAAAdAAAAHgAAAABAAQANS42LjQ1LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAADi81FfEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAASY7

/xM=

'/*!*/;

# at 120

#200904 16:24:21 server id 1 end_log_pos 211 CRC32 0xd1be92c3 Query thread_id=4 exec_time=0 error_code=0

SET TIMESTAMP=1599207861/*!*/;

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 utf8mb4 *//*!*/;

SET @@session.character_set_client=45,@@session.collation_connection=45,@@session.collation_server=8/*!*/;

SET @@session.lc_time_names=0/*!*/;

SET @@session.collation_database=DEFAULT/*!*/;

BEGIN

/*!*/;

# at 211

#200904 16:24:21 server id 1 end_log_pos 351 CRC32 0xf46910bb Query thread_id=4 exec_time=0 error_code=0

use `usercenter`/*!*/;

SET TIMESTAMP=1599207861/*!*/;

DELETE FROM `usercenter`.`sms_vercode` WHERE `id` = 36

/*!*/;

# at 351

#200904 16:24:21 server id 1 end_log_pos 382 CRC32 0x238a94fc Xid = 48

COMMIT/*!*/;

DELIMITER ;

# End of log file

ROLLBACK /* added by mysqlbinlog */;

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

windows環境下執行這個命令報錯

C:\Program Files\mysql\bin> mysqlbinlog D:\data\mysql\mysql-bin.000001;

mysqlbinlog: [ERROR] unknown variable 'default-character-set=utf8'

原因是mysqlbinlog這個工具無法識別binlog中的配置中的default-character-set=utf8這個指令。

解決方法1：my.cnf(my.ini)中將default-character-set=utf8 修改為 character-set-server = utf8 修改完後要重啟mysql

解決方法2：用mysqlbinlog --no-defaults D:\data\mysql\mysql-bin.000001開啟

解決方法3：原來是我的my.ini檔案中有兩個設定預設字符集的，刪掉一個即可

[client]

port=3306

#幹掉這個或者下面那個

default-character-set=utf8

[mysqld]

port=3306

character_set_server=utf8

最後執行後的結果跟linux下的差不多，就不粘結果了

基於開始/結束時間檢視

/usr/bin/mysqlbinlog --start-datetime="2020-09-04 16:00:00" --stop-datetime="2020-09-04 18:00:00" /var/lib/mysql/mysql-bin.000001

3.4 解析binlog格式

BEGIN

/*!*/;

# at 211

#200904 16:24:21 server id 1 end_log_pos 351 CRC32 0xf46910bb Query thread_id=4 exec_time=0 error_code=0

use `usercenter`/*!*/;

SET TIMESTAMP=1599207861/*!*/;

DELETE FROM `usercenter`.`sms_vercode` WHERE `id` = 36

/*!*/;

# at 351

#200904 16:24:21 server id 1 end_log_pos 382 CRC32 0x238a94fc Xid = 48

COMMIT/*!*/;

DELIMITER ;

# End of log file

ROLLBACK /* added by mysqlbinlog */;

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

at 351：說明"事務"的起點，是以第211位元組開始的

end_log_pos 382：說明以第282位元組結束

200904 16:24:21：事務發生的時間

exec_time=0：事務執行的時間

error_code=0：錯誤碼

server id 1：伺服器的標識id

mysql檢視binlog日誌詳解

相關文章