當前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

mysql binlog sql统计_mysql的binlog详解

發布時間：2023/12/10 数据库 32 豆豆

生活随笔收集整理的這篇文章主要介紹了 mysql binlog sql统计_mysql的binlog详解小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

binlog是mysql記錄操作的二進制日志文件,有三種格式可選,但是老舊的SBR已經不適合現在大多數業務需求,所以大多數都建議用MBR和RBR,即mixed或row,而解析他的原因,幾乎都只有一個,就是恢復數據庫,或者是反編譯來恢復數據庫,目的都是一樣.

以下是基于mysql5.6的mysqlbinlog來描述的,請各位知照.

binlog使用方法

直接來看看例子:#可以在數據庫界面操作一些命令

#只查看第一個binlog文件的內容(不建議)

mysql>?show?binlog?events;

#查看指定binlog文件的內容(不建議)

mysql>?show?binlog?events?in?'mysql-bin.000002';

#查看當前正在寫入的binlog文件

mysql>?show?master?status\G

#獲取binlog文件列表

mysql>?show?binary?logs;

#直接用mysqlbinlog命令就更全面一些

#可以直接打開,可能不夠直觀,而且會不停刷屏,慎用

mysqlbinlog?mysql-bin.000002

#查看row格式下的二進制日志

mysqlbinlog?-vv?--base64-output=decode-rows?filename

#基于開始/結束時間的解析

mysqlbinlog?--start-datetime='2013-09-10?00:00:00'?--stop-datetime='2013-09-10?01:01:01'?-d?庫名?二進制文件

#基于pos值的解析

mysqlbinlog?--start-position=107?--stop-position=1000?-d?庫名?二進制文件

#恢復單一數據庫的binlog

mysqlbinlog?-d?dbname??binlog.00002

#常用示例1:將一個row格式的binlog文件按特定時間段,特定庫解析出來,導出到文件,-vv意思是把注釋也打出來

mysqlbinlog?-vv?--base64-output=decode-rows?--start-datetime='2016-06-15?20:00:00'?--stop-datetime='2016-06-15?21:00:00'?-d?baiyang??mysql-bin.000011?>?t.txt

#常用示例2:將解析出來的binlog文件進行增刪改查統計

mysqlbinlog?-vv?--base64-output=decode-rows?filename|awk?'/###/?{if($0~/UPDATE|INSERT|SELECT/)count[$2"?"$NF]++}END{for(i?in?count)?print?i,"\t",count[i]}'?|?column?-t?|?sort?-k3nr

參數說明:

--base64-output=value????使用base-64編碼格式顯示二進制日志內容,AUTO ("automatic") 或 UNSPEC ("unspecified") 參數自動顯示二進制語句的內容，如果沒有指定 --base64-output參數，則效果就相當于--base64-output=AUTO；NEVER參數會導致二進制語句不會顯示；DECODE-ROWS解碼處理二進制日志。比較常用的是--base64-output=DECODE-ROWS：會顯示出row模式帶來的sql變更

--bind-address=name????綁定IP地址(用于一些異常IP訪問的情況)

--character-sets-dir=name????字符集文件的目錄路徑(非正常字符集使用)

-C, --compress????啟用壓縮模式(減少帶寬使用)

-d db_name,--database=db_name????只列出該數據庫的條目(只用本地日志)。

--debug-check????檢查內存和打開的文件使用情況并退出。

--debug-info????打印一些調試信息并退出。

--default-auth=name????默認的客戶端身份驗證插件路徑

-D,disable-logs-bin????禁用二進制日志。如果使用--to-last-logs選項將輸出發送給同一臺MySQL服務器，可以避免無限循環。該選項在崩潰恢復時也很有用，可以避免復制已經記錄的語句。注釋：該選項要求有SUPER權限。

-F, --force-if-open????強制打開沒有正常關閉的二進制日志文件,(默認開啟,通過--skip-force-if-open關閉)

-f, --force-read????強制打開不能識別的二進制日志事件,如果使用該選項,mysqlbinlog預到不能識別的二進制日志事件，它會打印警告，忽略該事件并繼續。沒有該選項，mysqlbinlog讀到此類事件則停止。

-H, --hexdump????在注釋中顯示日志的十六進制轉儲。該輸出可以幫助復制過程中的調試。

-h, --host=name????獲取給定主機上的MySQL服務器的二進制日志。

-l, --local-load=name????為指定目錄中的LOAD DATA INFILE預處理本地臨時文件。

-o, --offset=#????跳過前 N個條目。

-p, --password[=name]????連接服務器時使用密碼。如果使用短選項形式(-p)，選項和密碼之間不能有空格。如果在命令行中–password或-p選項后面沒有密碼值，則提示輸入一個密碼。

--plugin-dir=name????客戶端插件目錄

-P, --port=#????用于連接遠程服務器的TCP/IP端口號。

--protocol=name????使用那種連接協議,可以是:tcp, socket, pipe,memory

-R, --read-from-remote-server????從MySQL服務器讀二進制日志。如果沒有加入該選項，任何連接服務器的參數選項將無效。這些選項是–host、–password、–port、–protocol、–socket和–user。可代替read-from-remote-master=BINLOG-DUMP-NON-GTIDS.

--read-from-remote-master=name????從MySQL服務器讀二進制日志.通過設置COM_BINLOG_DUMP或COM_BINLOG_DUMP_GTID命令來設定參數BINLOG-DUMP-NON-GTIDS或BINLOG-DUMP-GTIDS,如果設置--read-from-remote-master=BINLOG-DUMP-GTIDS并結合--exclude-gtids,可以省一些網絡流量

--raw????需要配合-R使用。輸出原始二進制日志數據,而不是SQL,并輸出到日志文件。

-r, --result-file=name????將輸出指向給定的文件。和--raw一起使用的話,這是一個文件前序.

--secure-auth????如果客戶端低于pre-4.1.1,將拒絕連接,默認開啟,可以通過--skip-secure-auth來關閉

--server-id=#????只提取給定服務器ID的二進制日志,指my.cnf的server-id的ID值

--server-id-bits=#????把server-id設置在顯著位置

--set-charset=name????輸出時添加SET NAMES character_set提示設定字符集

-s, --short-form????只顯示日志中包含的語句，不顯示其它信息。這僅用于測試,不應被使用在生產系統中。如果要禁止base64-output的輸出,可以考慮使用--base64-output=never?instead.

-S, --socket=name????使用套接字socket來連接,通常是本機

--ssl????使用ssl協議來連接(一般不會用)

--ssl-ca=name????使用ca證書方式的SLL來連接,同理,下面幾個選項是各種證書和密鑰驗證方式的SSL登陸,就不一一介紹了.

--ssl-capath=name,--ssl-cert=name,--ssl-cipher=name,--ssl-key=name,--ssl-crl=name,--ssl-crlpath=name,--ssl-verify-server-cert

--start-datetime=datetime????從二進制日志中第1個日期時間等于或晚于datetime參量的事件開始讀取。datetime值相對于運行mysqlbinlog的機器上的本地時區。該值格式應符合DATETIME或TIMESTAMP數據類型。例如：mysqlbinlog –start-datetime="2004-12-2511:25:56" binlog.000003

-j, --start-position=#????從二進制日志中第1個位置等于N參量時的事件開始讀。

--stop-datetime=datetime????從二進制日志中第1個日期時間等于或晚于datetime參量的事件起停止讀。和--start-datetime選項配合使用設定導出特定的時間段信息,有助于減少結果輸出。

--stop-never????等待更多的日志傳輸而不是退出當前命令,直到斷開服務器連接為止,隱式設置--to-last-log

--stop-never-slave-server-id=#????當前命令的從庫ID值,當用到--read-from-remote-server或--stop-never時,命令就會像從庫一樣拉取binlog數據,所以需要一個唯一的server-id值來標識他.

--stop-position=#????從二進制日志中第1個位置等于和大于N參量時的事件起停止讀。和--start-position選項配合使用設定導出特定的position間隔的binlog信息,有助于減少結果輸出.

-t, --to-last-log????在MySQL服務器中請求的二進制日志的結尾處不停止，而是繼續打印直到最后一個二進制日志的結尾。如果將輸出發送給同一臺MySQL服務器，會導致無限循環。該選項要求–read-from-remote-server。

-u, --user=name????連接遠程服務器時使用的MySQL用戶名。

-v, --verbose????詳細模式,顯示statement模式帶來的sql語句,-vv表示增加列類型的注釋.

-V, --version????顯示版本信息并退出

--open-files-limit=#????指定要保留的打開的文件描述符的數量

-c, --verify-binlog-checksum????驗證校驗binlog日志

--binlog-row-event-max-size=#????最大binlog日志文件容量大小,此值必須是256的倍數。

--skip-gtids????不打印全局事務標識符信息(GTID),例如:SET GTID_NEXT=... etc

--include-gtids=name????打印給定值的GTID的信息

--exclude-gtids=name????打印給定值以外的GTID的信息

--rewrite-db=name????更新數據庫時給出一個和原來不一樣的數據庫名,例如:rewrite-db='from->to'.

—help，-？????顯示幫助消息并退出。

使用binlog恢復數據

由于binlog可以直接解析成sql語句的,一般來說直接執行是可行的.

但是有時候我們不想做的那么復雜,加上一個參數,直接執行,通過管道符就可以了.mysqlbinlog?--start-position='530050688'?--stop-position='601156037'?-d?test?/opt/mysql-bin.000019?|mysql?-uroot?-p123?--binary-mode

binlog server的創建:

用過mysql的都知道mysql支持主從架構,有些時候我們單純只想異地備份binlog日志,但是特地搭一個從庫貌似不科學,而且mysql機制上也并不是把binlog拉下來就算,還要寫進數據庫占用一定空間,以前會引入blackhold引擎,而現在就方便多了,因為mysql5.6后引入了mysql的binlog server,相當方便,相當簡單,binlog就被拉過來了,而且只要進程不掉,就一直拉.

因為方法很簡單,就是一條命令過去,所以不用很緊張,至于參數的意思,請看上面

命令如下,記得加后臺運行命令符噢:#先看看當前的master狀態,可以忽略GTID,因為可以不用設置GTID

mysql>?show?master?status\G

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

File:?mysql-bin.000007

Position:?56789

Binlog_Do_DB:

Binlog_Ignore_DB:

Executed_Gtid_Set:?3edae34c-6299-11e6-8999-8038bc0c67be:1-6754,

4cdc2a74-6299-7555-95ce-008cfaf595bc:1-7941248

1?row?in?set?(0.00?sec)

#開啟binlog?server

mysqlbinlog?-R?--raw?--host=*.*.*.*?--user=root?--password=x?xxxxxxxxx?--stop-never?--stop-never-slave-server-id=56789?mysql-bin.000007?&

#binlog就過來了,看看目錄

ll?./*

-rw-rw----?1?root?root?1073741921?5月??29?19:15?mysql-bin.000007

-rw-rw----?1?root?root?1073741954?6月???2?12:14?mysql-bin.000008

-rw-rw----?1?root?root?1073743557?6月???8?10:03?mysql-bin.000009

-rw-rw----?1?root?root?1073742035?6月??12?16:33?mysql-bin.000010

-rw-rw----?1?root?root?1073742000?6月??16?10:38?mysql-bin.000011

-rw-rw----?1?root?root?1073742053?6月??20?09:18?mysql-bin.000012

#看看進程,正在運行,這就完成了

ps?aux?|grep?mysql

root?????19683??0.0??0.2??59836??4764?pts/1????S????14:39???0:00?mysqlbinlog?-R?--raw?--host=x.x.x.x?-uroot?-P3306?-px?xxxxxxxxxxxxxx?--stop-never?--stop-never-slave-server-id=21305198?mysql-bin.000007

root?????19697??0.0??0.0?112648???976?pts/1????S+???14:45???0:00?grep?--color=auto?mysql

原來我已經跑那么多了,呵呵~!

再看看數據庫狀態show?processlist;

|?14277762?|?tencentroot?|?127.0.0.1:53878?????|?NULL????|?Sleep???????|??????25?|???????????????????????????????????????????????????????????????????????|?NULL?????????????|

有兩個進程在拉binlog,毫無疑問,其中一個是真正的從庫,另一個就是我的binlogserver了.

完畢.

總結

以上是生活随笔為你收集整理的mysql binlog sql统计_mysql的binlog详解的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。

上一篇：微信小程序 CSS filter（滤镜）
下一篇： css标签总结大全