Mysql binlog的查看方法
介紹
簡(jiǎn)介
MySQL的二進(jìn)制日志可以說是MySQL最重要的日志了，它記錄了所有的DDL和DML(除了數(shù)據(jù)查詢語句)語句，以事件形式記錄，還包含語句所執(zhí)行的消耗的時(shí)間，MySQL的二進(jìn)制日志是事務(wù)安全型的。一般來說開啟二進(jìn)制日志大概會(huì)有1%的性能損耗(參見MySQL官方中文手冊(cè) 5.1.24版)。二進(jìn)制有兩個(gè)最重要的使用場(chǎng)景:
其一：MySQL Replication在Master端開啟binlog，Mster把它的二進(jìn)制日志傳遞給slaves來達(dá)到master-slave數(shù)據(jù)一致的目的。
其二：自然就是數(shù)據(jù)恢復(fù)了，通過使用mysqlbinlog工具來使恢復(fù)數(shù)據(jù)。
binlog日志概念
binlog全稱為:binary，翻譯就是二進(jìn)制文件，主要記錄了MySQL在進(jìn)行DML（數(shù)據(jù)操作語言）過程中的操作日志。在執(zhí)行SQL語句的過程中，作為使用者，無需關(guān)注程序執(zhí)行的過程，但是當(dāng)數(shù)據(jù)庫(kù)數(shù)據(jù)丟失，或者需要搭建數(shù)據(jù)庫(kù)主從復(fù)制時(shí)，則此時(shí)的binlog日志文件的重要性就展現(xiàn)出來了。
二進(jìn)制日志包括兩類文件：二進(jìn)制日志索引文件（文件名后綴為.index）用于記錄所有的二進(jìn)制文件，二進(jìn)制日志文件（文件名后綴為.00000*）記錄數(shù)據(jù)庫(kù)所有的DDL和DML(除了數(shù)據(jù)查詢語句)語句事件。
binlog本身是一類二進(jìn)制文件。二進(jìn)制文件更省空間，寫入速度更快，是無法直接打開來查看的。
因此mysql提供了命令mysqlbinlog進(jìn)行查看。
一般的statement格式的二進(jìn)制文件，用下面命令就可以（后面會(huì)講到什么是statement格式）
mysqlbinlog mysql-bin.000001
如果是row格式，加上-v或者-vv參數(shù)就行，如
mysqlbinlog -vv mysql-bin.000001
使用場(chǎng)景
以事件形式記錄，還包含語句所執(zhí)行的消耗的時(shí)間，MySQL的二進(jìn)制日志是事務(wù)安全型的。binlog 的主要目的是復(fù)制和恢復(fù)。
- MySQL主從復(fù)制：MySQL Replication在Master端開啟binlog，Master把它的二進(jìn)制日志傳遞給slaves來達(dá)到master-slave數(shù)據(jù)一致的目的
- 數(shù)據(jù)恢復(fù)：通過使用 mysqlbinlog工具來使恢復(fù)數(shù)據(jù)
- 查看數(shù)據(jù)更改記錄
數(shù)據(jù)庫(kù)操作類型：
在對(duì)數(shù)據(jù)庫(kù)的日常操作中，MySQL的操作語法，經(jīng)常使用的幾種MySQL指令類型如下
DQL：數(shù)據(jù)查詢語言，例如 where 、group by 、 orderby
DML：數(shù)據(jù)操作語言，例如 insert、delete、update等
DPL：事務(wù)處理語言，例如begin transaction、commit、rollback
DCL：數(shù)據(jù)控制語言，例如grant 、revoke控制用戶訪問權(quán)限等
DDL：數(shù)據(jù)定義語言，例如create、drop刪除表、索引添加類等
binlog三種模式
| format | 定義 | 優(yōu)點(diǎn) | 缺點(diǎn) |
|---|---|---|---|
| statem ent | 記錄的是修改SQL語句 | 日志文件小，節(jié)約I0，提高性能 | 準(zhǔn)確性差，對(duì)一些系統(tǒng)函數(shù)不能準(zhǔn)確復(fù)制或不能復(fù)制，如now0.uuid0等 |
| row | 記錄的是每行實(shí)際數(shù)據(jù)的變更 | 準(zhǔn)確性強(qiáng)，能準(zhǔn)確復(fù)制數(shù)據(jù)的變更 | 日志文件大，較大的網(wǎng)絡(luò)10和磁盤lO |
| mixed | statement和row模式的混合 | 準(zhǔn)確性強(qiáng)，文件大小適中 | 有可能發(fā)生主從不一致問題 |
業(yè)內(nèi)目前推薦使用的是row模式，準(zhǔn)確性高，雖然說文件大，但是現(xiàn)在有SSD和萬兆光纖網(wǎng)絡(luò)，這些磁盤IO和網(wǎng)絡(luò)IO都是可以接受的。
那么，大家一定想問，為什么不推薦使用mixed模式，理由如下
假設(shè)master有兩條記錄，而slave只有一條記錄。
master的數(shù)據(jù)為
+----+------------------------------------------------------+ | id | n | +----+------------------------------------------------------+ | 1 | d24c2c7e-430b-11e7-bf1b-00155d016710 | | 2 | ddd | +----+------------------------------------------------------+
slave的數(shù)據(jù)為
+----+-------------------------------------------------------+ | id | n | +----+-------------------------------------------------------+ | 1 | d24c2c7e-430b-11e7-bf1b-00155d016710 | +----+-------------------------------------------------------+
當(dāng)在master上更新一條從庫(kù)不存在的記錄時(shí)，也就是id=2的記錄，你會(huì)發(fā)現(xiàn)master是可以執(zhí)行成功的。而slave拿到這個(gè)SQL后，也會(huì)照常執(zhí)行，不報(bào)任何異常，只是更新操作不影響行數(shù)而已。并且你執(zhí)行命令show slave status，查看輸出，你會(huì)發(fā)現(xiàn)沒有異常。但是，如果你是row模式，由于這行根本不存在，是會(huì)報(bào)1062錯(cuò)誤的。
在 MySQL 5.7.7 之前，默認(rèn)的格式是 STATEMENT，在 MySQL 5.7.7 及更高版本中，默認(rèn)值是 ROW。
開啟binlog
一般來說開啟binlog日志大概會(huì)有1%的性能損耗。
需要先輸入mysql 進(jìn)入mysql命令行模式
mysql> show variables like 'log_%'; #注意有分號(hào)
+----------------------------------------+---------------------------------------+ | Variable_name | Value | +----------------------------------------+---------------------------------------+ | log_bin | ON | ------> ON表示已經(jīng)開啟binlog日志 | 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 | | log_error | /usr/local/mysql/data/martin.err | | log_output | FILE | | log_queries_not_using_indexes | OFF | | log_slave_updates | OFF | | log_slow_admin_statements | OFF | | log_slow_slave_statements | OFF | | log_throttle_queries_not_using_indexes | 0 | | log_warnings | 1 | +----------------------------------------+---------------------------------------+
如果沒有開啟
啟用binlog，通過配置 /etc/my.cnf 或 /etc/mysql/mysql.conf.d/mysqld.cnf 或mysql\bin\my.ini 配置文件的 log-bin 選項(xiàng)：
在配置文件中加入 log-bin 配置，表示啟用binlog，如果沒有給定值，寫成 log-bin=，則默認(rèn)名稱為主機(jī)名。（注：名稱若帶有小數(shù)點(diǎn)，則只取第一個(gè)小數(shù)點(diǎn)前的部分作為名稱）
[mysqld] log-bin=my-binlog-name
log-bin后面是binlog的文件名，譬如 log-bin=mysql-bin， 則生成的文件名為：
然后需要重啟mysql
也可以通過 SET SQL_LOG_BIN=1 命令來啟用 binlog，通過 SET SQL_LOG_BIN=0 命令停用 binlog。啟用 binlog 之后須重啟MySQL才能生效。
binlog文件介紹
binlog日志包括兩類文件:
二進(jìn)制日志索引文件（文件名后綴為.index）用于記錄所有有效的的二進(jìn)制文件
二進(jìn)制日志文件（文件名后綴為.00000*）記錄數(shù)據(jù)庫(kù)所有的DDL和DML語句事件
binlog是一個(gè)二進(jìn)制文件集合，每個(gè)binlog文件以一個(gè)4字節(jié)的魔數(shù)開頭，接著是一組Events:
魔數(shù)：0xfe62696e對(duì)應(yīng)的是0xfebin；
Event：每個(gè)Event包含header和data兩個(gè)部分；header提供了Event的創(chuàng)建時(shí)間，哪個(gè)服務(wù)器等信息，data部分提供的是針對(duì)該Event的具體信息，如具體數(shù)據(jù)的修改；
第一個(gè)Event用于描述binlog文件的格式版本，這個(gè)格式就是event寫入binlog文件的格式；
其余的Event按照第一個(gè)Event的格式版本寫入；
最后一個(gè)Event用于說明下一個(gè)binlog文件；
binlog的索引文件是一個(gè)文本文件，其中內(nèi)容為當(dāng)前的binlog文件列表
當(dāng)遇到以下3種情況時(shí)，MySQL會(huì)重新生成一個(gè)新的日志文件，文件序號(hào)遞增：
MySQL服務(wù)器停止或重啟時(shí)
使用 flush logs 命令；
當(dāng) binlog 文件大小超過 max_binlog_size 變量的值時(shí)；
max_binlog_size 的最小值是4096字節(jié)，最大值和默認(rèn)值是 1GB (1073741824字節(jié))。事務(wù)被寫入到binlog的一個(gè)塊中，所以它不會(huì)在幾個(gè)二進(jìn)制日志之間被拆分。
因此，如果你有很大的事務(wù)，為了保證事務(wù)的完整性，不可能做切換日志的動(dòng)作，只能將該事務(wù)的日志都記錄到當(dāng)前日志文件中，直到事務(wù)結(jié)束，你可能會(huì)看到binlog文件大于 max_binlog_size 的情況。
binlog日志內(nèi)容查看
binlog日志有二種查看方式，具體如下：
1、mysql查看binlog
這種方法相當(dāng)于執(zhí)行sql語句 在圖形化工具中也可直接執(zhí)行
mysql> show binlog events; #只查看第一個(gè)binlog文件的內(nèi)容（全部的 怎么只看部分呢） mysql> show binlog events ``in 'mysql-bin.000002'``;#查看指定binlog文件的內(nèi)容 mysql> show binary logs; #獲取binlog文件列表 mysql> show master status； #查看當(dāng)前正在寫入的binlog文件
mysql> show binary logs; #獲取binlog文件列表
| 參數(shù)名 | 含義 |
|---|---|
| log bin = (on | off base name) |
| sql log bin =( on | off ) |
| expire logs days | 指定自動(dòng)刪除二進(jìn)制日志的時(shí)間，即日志過期時(shí)間 |
| log bin index | 指定mysql-bin.index文件的路徑 |
| binlog format = [ mixed | row | statement ] | 指定二進(jìn)制日志基于什么模式記錄 |
| max binlog size | 指定二進(jìn)制日志文件最大值 |
| binlog cache size | 指定事務(wù)日志緩存區(qū)大小 |
| max binlog cache size | 指定二進(jìn)制日志緩存最大大小 |
| sync binlog = { 0 | n } | 指定寫緩沖多少次，刷一次盤 |
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count] 在二進(jìn)制日志中顯示事件。如果未指定，則顯示第一個(gè)二進(jìn)制日志。需要特權(quán)。'log_name'SHOW BINLOG EVENTSREPLICATION SLAVE 該LIMIT子句與該語句具有相同的語法SELECT。請(qǐng)參見“ SELECT語句”。 mysql> show binlog events limit 10; +------------------+-------+----------------+-----------+-------------+-----------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-------+----------------+-----------+-------------+-----------------------------------------------------+ | mysql-bin.000001 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.27-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 | 300 | BEGIN | | mysql-bin.000001 | 300 | Table_map | 1 | 412 | table_id: 392 (cactus_000510.360exthost_examinemgr) | | mysql-bin.000001 | 412 | Delete_rows | 1 | 8617 | table_id: 392 | | mysql-bin.000001 | 8617 | Delete_rows | 1 | 12732 | table_id: 392 flags: STMT_END_F | | mysql-bin.000001 | 12732 | Xid | 1 | 12763 | COMMIT /* xid=530 */ | | mysql-bin.000001 | 12763 | Anonymous_Gtid | 1 | 12828 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql-bin.000001 | 12828 | Query | 1 | 12917 | BEGIN | +------------------+-------+----------------+-----------+-------------+-----------------------------------------------------+ 10 rows in set (0.13 sec)
Binlog Event 簡(jiǎn)要分析
從一個(gè)最簡(jiǎn)單的實(shí)例來分析Event，包括創(chuàng)建表，插入數(shù)據(jù)，更新數(shù)據(jù)，刪除數(shù)據(jù)；
CREATE TABLE `test` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `age` int(11) DEFAULT NULL, `name` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; insert into test values(1,22,"小旋鋒"); update test set name='whirly' where id=1; delete from test where id=1;
日志格式為STATEMENT，查看所有的Event
日志格式為ROW時(shí)是下面這樣，可以發(fā)現(xiàn)又有一些不同
2、使用mysqlbinlog工具
以下為普通bash命令 非mysql模式下的了
發(fā)出SHOW BINLOG EVENTS with no LIMIT子句可能會(huì)啟動(dòng)一個(gè)非常耗時(shí)和資源消耗的過程，因?yàn)榉?wù)器將二進(jìn)制日志的完整內(nèi)容（包括服務(wù)器修改數(shù)據(jù)的所有語句）返回給客戶端。作為替代SHOW BINLOG EVENTS，使用mysqlbinlog實(shí)用程序?qū)⒍M(jìn)制日志保存到文本文件中，以供以后檢查和分析。
而且 show event命令查看了binlog的簡(jiǎn)單信息，如何查看執(zhí)行時(shí)間、執(zhí)行花費(fèi)的時(shí)間等詳細(xì)信息需要使用工具包中的mysqlbinlog命令。
mysqlbinlog是一個(gè)查看mysql二進(jìn)制日志的工具，可以把mysql上面的所有操作記錄從日志里導(dǎo)出，這個(gè)工具默認(rèn)的安裝路徑為：/usr/local/mysql/bin/mysqlbinlog
可以通過find / -name "mysqlbinlog"或者which mysqlbinlog命令查找mysqlbinlog的工具路徑。
至于日志文件，參照前面的開啟binlog章
基于開始/結(jié)束時(shí)間：
/usr/local/mysql/bin/mysqlbinlog --start-datetime=``"2013-03-01 00:00:00" --stop-datetime=``"2014-03-21 23:59:59" /usr/local/mysql/``var``/mysql-bin.000007 -r test2.sql
# mysqlbinlog 的執(zhí)行格式
mysqlbinlog [options] log_file ...
# 查看bin-log二進(jìn)制文件（shell方式）
mysqlbinlog -v --base64-output=decode-rows /var/lib/mysql/master.000003
# 查看bin-log二進(jìn)制文件（帶查詢條件）
mysqlbinlog -v --base64-output=decode-rows /var/lib/mysql/master.000003 \
--start-datetime="2019-03-01 00:00:00" \
--stop-datetime="2019-03-10 00:00:00" \
--start-position="5000" \
--stop-position="20000"
截取結(jié)果其中的一段進(jìn)行分析：
# at 21019 #190308 10:10:09 server id 1 end_log_pos 21094 CRC32 0x7a405abc Query thread_id=113 exec_time=0 error_code=0 SET TIMESTAMP=1552011009/*!*/; BEGIN /*!*/;
上面輸出包括信息：
position: 位于文件中的位置，即第一行的（# at 21019）,說明該事件記錄從文件第21019個(gè)字節(jié)開始 timestamp: 事件發(fā)生的時(shí)間戳，即第二行的（#190308 10:10:09） server id: 服務(wù)器標(biāo)識(shí)（1） end_log_pos 表示下一個(gè)事件開始的位置（即當(dāng)前事件的結(jié)束位置+1） thread_id: 執(zhí)行該事件的線程id （thread_id=113） exec_time: 事件執(zhí)行的花費(fèi)時(shí)間 error_code: 錯(cuò)誤碼，0意味著沒有發(fā)生錯(cuò)誤 type:事件類型Query
發(fā)現(xiàn)BINLOG 處的內(nèi)容我們看不明白。。。_
增加 --base64-output=decode-rows –v 選項(xiàng)解析
（base64-output，可以控制輸出語句輸出base64編碼的BINLOG語句;decode-rows：選項(xiàng)將把基于行的事件解碼成一個(gè)SQL語句）
/mysql/app/mariadb/bin/mysqlbinlog --no-defaults --database=db --base64-output=decode-rows -v --start-datetime='2019-04-11 00:00:00' --stop-datetime='2019-04-11 15:00:00' mysql-bin.000007 | more
變成人類可以讀懂的SQL語句了；
PS: 可以把binlog解析后的內(nèi)容放到一個(gè)文件后，在分析，利用如下腳本
/mysql/app/mariadb/bin/mysqlbinlog --no-defaults --database=db --base64-output=decode-rows -v --start-datetime='2019-04-11 00:00:00' --stop-datetime='2019-04-11 15:00:00' mysql-bin.000007 >/tmp/binlog007.sql
mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000002
怎么刪binlog
刪binlog的方法很多，有三種是常見的
(1) 使用reset master,該命令將會(huì)刪除所有日志，并讓日志文件重新從000001開始。
(2) 使用命令
PURGE { BINARY | MASTER } LOGS { TO ‘log_name’ | BEFORE datetime_expr }
例如
purge master logs to “binlog_name.00000X”
將會(huì)清空00000X之前的所有日志文件.
(3) 使用–expire_logs_days=N選項(xiàng)指定過了多少天日志自動(dòng)過期清空。
到此這篇關(guān)于Mysql binlog的查看實(shí)例的文章就介紹到這了,更多相關(guān)Mysql binlog內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
MySQL decimal unsigned更新負(fù)數(shù)轉(zhuǎn)化為0
這篇文章主要介紹了MySQL decimal unsigned更新負(fù)數(shù)轉(zhuǎn)化為0,文中通過示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友可以參考下2020-12-12
MySQL三大日志之binlog、redoLog、undoLog詳細(xì)講解
這篇文章主要介紹了MySQL三大日志之binlog、redoLog、undoLog的相關(guān)資料,,binlog是MySQL服務(wù)層的日志,記錄數(shù)據(jù)庫(kù)執(zhí)行的寫入性操作,redoLog是InnoDB存儲(chǔ)引擎層的日志,記錄數(shù)據(jù)頁(yè)的變更,undoLog也是InnoDB存儲(chǔ)引擎層的日志,記錄數(shù)據(jù)的邏輯變化,需要的朋友可以參考下2025-02-02
這篇文章主要介紹了Mysql查詢近30天活躍用戶數(shù),本文通過實(shí)例代碼給大家介紹的非常詳細(xì),感興趣的朋友跟隨小編一起看看吧2024-08-08
Mysql大數(shù)據(jù)量查詢優(yōu)化思路詳析
這篇文章主要介紹了Mysql大數(shù)據(jù)量查詢優(yōu)化思路，Mysql大表查詢優(yōu)化，理論上千萬級(jí)別以下的數(shù)據(jù)量Mysql單表查詢性能處理都是可以的。下文我們就來看看具體得思路解析2022-01-01
本篇文章介紹了mysql中函數(shù)查詢的使用。需要的朋友參考下2013-05-05
這篇文章主要介紹了MySQL遠(yuǎn)程訪問設(shè)置終極方法,本文總結(jié)了多種設(shè)置方法和技巧,是解決遠(yuǎn)程訪問的終極解決方案,需要的朋友可以參考下2014-12-12
Mysql中count帶條件計(jì)數(shù)實(shí)例代碼
這篇文章主要給大家介紹了關(guān)于Mysql中count帶條件計(jì)數(shù)的相關(guān)資料,Mysql中count()函數(shù)的一般用法是統(tǒng)計(jì)字段非空的記錄數(shù),所以可以利用這個(gè)特點(diǎn)來進(jìn)行條件統(tǒng)計(jì),需要的朋友可以參考下2023-09-09
本文將詳細(xì)介紹mysql中IFNULL,IF,CASE的區(qū)別,需要了解的朋友可以參考下2012-11-11
這篇文章主要介紹了MySQL如何使用union all獲得并集排序,文中通過示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友可以參考下2020-03-03
mysql #1062 –Duplicate entry ''1'' for key ''PRIMARY''
Mysql進(jìn)行數(shù)據(jù)備份，還原后進(jìn)行回帖，出現(xiàn)以下錯(cuò)誤代碼,其實(shí)主要是導(dǎo)入數(shù)據(jù)重復(fù)的問題，將現(xiàn)在的數(shù)據(jù)表清空，重新導(dǎo)入即可2012-07-07