怎樣理解mysql binlog

今天就跟大家聊聊有關(guān)怎樣理解mysql binlog，可能很多人都不太了解，為了讓大家更加了解，小編給大家總結(jié)了以下內(nèi)容，希望大家根據(jù)這篇文章可以有所收獲。

Mysql binlog

1、mysql binlog的類型：

mysql的binlog按照生成方式，可以分為三種，分別是：

1）基于記錄的復(fù)制RBR(Row Based Replication) 或Row：

優(yōu)點(diǎn)：binlog中可以不記錄執(zhí)行的sql語(yǔ)句的上下文相關(guān)的信息，僅需要記錄那一條記錄被修改成什么了。所以rowlevel的日志內(nèi)容會(huì)非常清楚的記錄下每一行數(shù)據(jù)修改的細(xì)節(jié)。而且不會(huì)出現(xiàn)某些特定情況下的存儲(chǔ)過(guò)程，或function，以及trigger的調(diào)用和觸發(fā)無(wú)法被正確復(fù)制的問(wèn)題。

缺點(diǎn)：所有的執(zhí)行的語(yǔ)句當(dāng)記錄到日志中的時(shí)候，都將以每行記錄的修改來(lái)記錄，這樣可能會(huì)產(chǎn)生大量的日志內(nèi)容,比如一條update語(yǔ)句，修改多條記錄，則binlog中每一條修改都會(huì)有記錄，這樣造成binlog日志量會(huì)很大，特別是當(dāng)執(zhí)行alter table之類的語(yǔ)句的時(shí)候，由于表結(jié)構(gòu)修改，每條記錄都發(fā)生改變，那么該表每一條記錄都會(huì)記錄到日志中。

2）基于語(yǔ)句的復(fù)制，簡(jiǎn)稱SBR(Statement Based Replication) 或Statement：

相比row能提高性能，減少日志量。但是這個(gè)是取決于應(yīng)用的SQL情況，正常同一條記錄修改或者插入row格式所產(chǎn)生的日志量會(huì)小于Statement產(chǎn)生的日志量，但是考慮到如果帶條件的update操作，以及整表刪除，alter表等操作，ROW格式會(huì)產(chǎn)生大量日志，因此在考慮是否使用ROW格式日志時(shí)應(yīng)該根據(jù)應(yīng)用的實(shí)際情況，考慮其所產(chǎn)生的日志量會(huì)增加多少，以及帶來(lái)的IO性能問(wèn)題。

優(yōu)點(diǎn)：可以對(duì)任何語(yǔ)句都能正確工作，不需要記錄每一行的變化，減少了binlog日志量，節(jié)約了IO，提高性能，一些語(yǔ)句的效率更高。例如，一個(gè)更新GB的數(shù)據(jù)的查詢僅需要幾十個(gè)字節(jié)的二進(jìn)制日志。

缺點(diǎn)：就是二進(jìn)制日志可能會(huì)很大，而且不直觀，所以，你不能使用mysqlbinlog來(lái)查看二進(jìn)制日志。而且由于記錄的只是執(zhí)行語(yǔ)句，為了這些語(yǔ)句能在slave上正確運(yùn)行，因此還必須記錄每條語(yǔ)句在執(zhí)行的時(shí)候的一些相關(guān)信息，以保證所有語(yǔ)句能在slave得到和在master端執(zhí)行時(shí)候相同的結(jié)果。另外mysql 的復(fù)制,像一些特定函數(shù)功能，slave可與master上要保持一致會(huì)有很多相關(guān)問(wèn)題(如sleep()函數(shù)， last_insert_id()，以及user-defined functions(udf)會(huì)出現(xiàn)問(wèn)題)。此外，存儲(chǔ)過(guò)程和觸發(fā)器也是一個(gè)問(wèn)題。另外一個(gè)問(wèn)題就是基于語(yǔ)句的復(fù)制必須是串行化的。這要求大量特殊的代碼，配置，例如InnoDB的next-key鎖等。并不是所有的存儲(chǔ)引擎都支持基于語(yǔ)句的復(fù)制。

使用以下函數(shù)的語(yǔ)句也無(wú)法被復(fù)制：

* LOAD_FILE()

* UUID()

* USER()

* FOUND_ROWS()

* SYSDATE() (除非啟動(dòng)時(shí)啟用了 --sysdate-is-now 選項(xiàng))

同時(shí)在INSERT ...SELECT 會(huì)產(chǎn)生比 RBR 更多的行級(jí)鎖

3）混合方式MBR(Mixed Based Replication)：

由于兩種方式不能對(duì)所有情況都能很好的處理，所以，MySQL 5.1以上支持在基于語(yǔ)句的復(fù)制和基于記錄的復(fù)制之前動(dòng)態(tài)交換?？梢酝ㄟ^(guò)設(shè)置session變量binlog_format來(lái)進(jìn)行控制。

2、Binlog日志格式選擇

Mysql默認(rèn)是使用Statement日志格式，推薦使用MIXED.

由于一些特殊使用，可以考慮使用ROW，如自己通過(guò)binlog日志來(lái)同步數(shù)據(jù)的修改，這樣會(huì)節(jié)省很多相關(guān)操作。對(duì)于binlog數(shù)據(jù)處理會(huì)變得非常輕松,相對(duì)mixed，解析也會(huì)很輕松(當(dāng)然前提是增加的日志量所帶來(lái)的IO開(kāi)銷在容忍的范圍內(nèi)即可)。

mysql對(duì)于日志格式的選定原則:如果是采用 INSERT，UPDATE，DELETE 等直接操作表的情況，則日志格式根據(jù) binlog_format 的設(shè)定而記錄,如果是采用 GRANT，REVOKE，SET PASSWORD 等管理語(yǔ)句來(lái)做的話，那么無(wú)論如何都采用Statement模式記錄

3、Binlog相關(guān)參數(shù)

如以下：

binlog_format = MIXED   //binlog日志格式，可以選擇為mixed，statement，row

log_bin =目錄/mysql-bin.log    //binlog日志名

expire_logs_days = 7                //binlog過(guò)期清理時(shí)間

max_binlog_size = 100m                    //binlog每個(gè)日志文件大小

binlog-do-db = 需要備份的數(shù)據(jù)庫(kù)名，如果備份多個(gè)數(shù)據(jù)庫(kù)，重復(fù)設(shè)置這個(gè)選項(xiàng)即可

binlog-ignore-db = 不需要備份的數(shù)據(jù)庫(kù)苦命，如果備份多個(gè)數(shù)據(jù)庫(kù)，重復(fù)設(shè)置這個(gè)選項(xiàng)即可

4、binlog相關(guān)文件

mysql-bin.index：

用于跟蹤磁盤上存在哪些二進(jìn)制日志文件。MySQL用它來(lái)定位二進(jìn)制日志文件。

mysql-relay-bin.index：

該文件的功能與mysql-bin.index類似，但是它是針對(duì)中繼日志，而不是二進(jìn)制日志。

master.info：

保存master的相關(guān)信息。不要?jiǎng)h除它，否則，slave重啟后不能連接master。

relay-log.info：

包含slave中當(dāng)前二進(jìn)制日志和中繼日志的信息。

5、binlog日志內(nèi)容解析

1)在mysql命令界面中查看時(shí)：

如果是statement模式：

mysql> show binlog events in 'mysql-bin.000021'\G;

截取部分查詢結(jié)果：

*************************** 20. row ***************************

Log_name: mysql-bin.000021  -----------------------> 查詢的binlog日志文件名

Pos: 11197 ------------------------------------------------------------> pos起始點(diǎn):

Event_type: Query ----------------------------------------------> 事件類型：Query

Server_id: 1 -------------------------------------------> 標(biāo)識(shí)是由哪臺(tái)服務(wù)器執(zhí)行的

End_log_pos: 11308 ------------------> pos結(jié)束點(diǎn):11308(即：下行的pos起始點(diǎn))

Info: use `zyyshop`; INSERT INTO `team2` VALUES (0,345,'asdf8er5')

---> 執(zhí)行的sql語(yǔ)句

*************************** 21. row ***************************

Log_name: mysql-bin.000021

Pos: 11308 -----------------------------> pos起始點(diǎn):11308(即：上行的pos結(jié)束點(diǎn))

Event_type: Query

Server_id: 1

End_log_pos: 11417

Info: use `zyyshop`; /*!40000 ALTER TABLE `team2` ENABLE KEYS */

*************************** 22. row ***************************

Log_name: mysql-bin.000021

如果是row模式：

mysql> show binlog events in 'mysql-bin.000005'\G;

截取部分查詢結(jié)果：

*************************** 2. row ***************************

Log_name: mysql-bin.000005

Pos: 120

Event_type: Query

Server_id: 3

End_log_pos: 191

Info: BEGIN

*************************** 3. row ***************************

Log_name: mysql-bin.000005

Pos: 191

Event_type: Table_map

Server_id: 3

End_log_pos: 234

Info: table_id: 87 (lxm.t)    ---> 這里看不到執(zhí)行的sql語(yǔ)句，只能看到表名

*************************** 4. row ***************************

Log_name: mysql-bin.000005

Pos: 234

Event_type: Update_rows

Server_id: 3

End_log_pos: 280

Info: table_id: 87 flags: STMT_END_F

*************************** 5. row ***************************

Log_name: mysql-bin.000005

Pos: 280

Event_type: Xid

Server_id: 3

End_log_pos: 311

Info: COMMIT /* xid=249 */

*************************** 6. row ***************************

Log_name: mysql-bin.000005

2)用mysqlbinlog工具查看時(shí)：

如果是statement模式：

# /usr/local/mysql/bin/mysqlbinlog /usr/local/mysql/data/mysql-bin.000013

  下面截取一個(gè)片段分析：

.............................................................................................................

# at 552

#131128 17:50:46 server id 1  end_log_pos 665   Query   thread_id=11    exec_time=0     error_code=0        ---->執(zhí)行時(shí)間:17:50:46；pos點(diǎn):665

  SET TIMESTAMP=1385632246/*!*/;

  update zyyshop.stu set name='李四' where id=4        ---->執(zhí)行的SQL

  /*!*/;

# at 665

#131128 17:50:46 server id 1  end_log_pos 692   Xid = 1454 ---->執(zhí)行時(shí)間:17:50:46；pos點(diǎn):692

.............................................................................................................

注: server id 1         數(shù)據(jù)庫(kù)主機(jī)的服務(wù)號(hào)；

    end_log_pos 665  pos點(diǎn)

    thread_id=11      線程號(hào)

如果是row模式：

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

#161022 15:59:31 server id 3  end_log_pos 120 CRC32 0x45d9e7a2  Start: binlog v 4, server v 5.6.24-log created 161022 15:59:31

BINLOG '

YxwLWA8DAAAAdAAAAHgAAAAAAAQANS42LjI0LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAaLn

2UU=

'/*!*/;

# at 120

#161022 16:00:57 server id 3  end_log_pos 191 CRC32 0x37e11f27  Query   thread_id=1     exec_time=0     error_code=0

SET TIMESTAMP=1477123257/*!*/;

SET @@session.pseudo_thread_id=1/*!*/;

SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;

SET @@session.sql_mode=1073741824/*!*/;

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

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

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

BEGIN

/*!*/;

# at 191

#161022 16:00:57 server id 3  end_log_pos 234 CRC32 0xe2ba303b  Table_map: `lxm`.`t` mapped to number 87

# at 234

#161022 16:00:57 server id 3  end_log_pos 280 CRC32 0xdae765d4  Update_rows: table id 87 flags: STMT_END_F

.............................................................................................................

是看不到明文的DML語(yǔ)句。

6、常用binlog日志操作命令

@查看所有binlog日志列表

mysql> show master logs;或者show binary logs;

@查看master狀態(tài)，即最后(最新)一個(gè)binlog日志的編號(hào)名稱，及其最后一個(gè)操作事件pos結(jié)束點(diǎn)(Position)值

mysql> show master status;

@刷新log日志，自此刻開(kāi)始產(chǎn)生一個(gè)新編號(hào)的binlog日志文件

mysql> flush logs;

注：每當(dāng)mysqld服務(wù)重啟時(shí)，會(huì)自動(dòng)執(zhí)行此命令，刷新binlog日志；在mysqldump備份數(shù)據(jù)時(shí)加 -F 選項(xiàng)也會(huì)刷新binlog日志；

@重置(清空)所有binlog日志

mysql> reset master;

7、如何查看binlog日志的內(nèi)容

1)在mysql命令界面中查看：

mysql> show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];

這條語(yǔ)句可以將指定的binlog日志文件，分成有效事件行的方式返回，并可使用limit指定pos點(diǎn)的起始偏移，查詢條數(shù)；

選項(xiàng)解析：

IN 'log_name'   指定要查詢的binlog文件名(不指定就是第一個(gè)binlog文件)

FROM pos   指定從哪個(gè)pos起始點(diǎn)開(kāi)始查起(不指定就是從整個(gè)文件首個(gè)pos點(diǎn)開(kāi)始算)

LIMIT [offset,]   偏移量(不指定就是0)

row_count       查詢總條數(shù)(不指定就是所有行)

示例：

A.查詢第一個(gè)(最早)的binlog日志：

    mysql> show binlog events\G;

B.指定查詢 mysql-bin.000021 這個(gè)文件：

    mysql> show binlog events in 'mysql-bin.000021'\G;

C.指定查詢 mysql-bin.000021 這個(gè)文件，從pos點(diǎn):8224開(kāi)始查起：

    mysql> show binlog events in 'mysql-bin.000021' from 8224\G;

D.指定查詢 mysql-bin.000021 這個(gè)文件，從pos點(diǎn):8224開(kāi)始查起，查詢10條

    mysql> show binlog events in 'mysql-bin.000021' from 8224 limit 10\G;

E.指定查詢 mysql-bin.000021 這個(gè)文件，從pos點(diǎn):8224開(kāi)始查起，偏移2行，查詢10條

    mysql> show binlog events in 'mysql-bin.000021' from 8224 limit 2,10\G;

2) 使用mysqlbinlog：

binlog是二進(jìn)制文件，普通文件查看器cat、more、vi等都無(wú)法打開(kāi)，必須使用自帶的 mysqlbinlog 命令查看。binlog日志與數(shù)據(jù)庫(kù)文件在同目錄中。

在MySQL5.5以下版本使用mysqlbinlog命令時(shí)如果報(bào)錯(cuò)，就加上 “--no-defaults”選項(xiàng)。

a)如果是ROW模式的二進(jìn)制日志文件，為了查看mysql具體執(zhí)行了什么樣的sql語(yǔ)句，需要使用-v（--verbose）選項(xiàng)，該選項(xiàng)會(huì)將行事件重構(gòu)成被注釋掉的偽SQL語(yǔ)句，如果想看到更詳細(xì)的信息可以將該選項(xiàng)給兩次如-vv，這樣可以包含一些數(shù)據(jù)類型和元信息的注釋內(nèi)容。例如：

mysqlbinlog -v mysql-bin.000001

mysqlbinlog -vv mysql-bin.000001

b)mysqlbinlog和可以通過(guò)--read-from-remote-server選項(xiàng)從遠(yuǎn)程服務(wù)器讀取二進(jìn)制日志文件，這時(shí)需要一些而外的連接參數(shù)，如--host，--password ，--port，--user，--socket，--protocol等，這些參數(shù)僅在指定了--read-from-remote-server后有效。

c)無(wú)論是本地二進(jìn)制日志文件還是遠(yuǎn)程服務(wù)器上的二進(jìn)制日志文件，無(wú)論是行模式、語(yǔ)句模式還是混合模式的二進(jìn)制日志文件，被mysqlbinlog工具解析后都可直接應(yīng)用與MySQL Server進(jìn)行基于時(shí)間點(diǎn)、位置或數(shù)據(jù)庫(kù)的恢復(fù)。

常見(jiàn)參數(shù)有:

1)   --database=db_name, -d db_name

該參數(shù)使mysqlbinlog僅從本地二進(jìn)制日志中輸出指定的db_name被use命令選作默認(rèn)數(shù)據(jù)庫(kù)時(shí)產(chǎn)生的日志事件。行為類似于mysqld的--binlog-do-db命令。若該參數(shù)指定了多次那么只有最后一次指定的內(nèi)容有效。參數(shù)具體的影響依賴于二進(jìn)制日志格式，只有在使用行模式的日志格式時(shí)該參數(shù)才能保證一致性。基于語(yǔ)句或混合模式的二進(jìn)制日志格式中因?yàn)榭赡艽嬖诳鐜?kù)的更新導(dǎo)致--database參數(shù)表現(xiàn)不同的行為，從而不能保證數(shù)據(jù)一致性。例如：

mysqlbinlog  mysql-bin.000001  -d testDB | mysql -uusername -p

2)   --force-read, -f

使用了該參數(shù)后mysqlbinlog工具在讀取到不能識(shí)別的日志事件時(shí)會(huì)打印出warning，忽略事件并繼續(xù)執(zhí)行，沒(méi)有此參數(shù)的情況下mysqlbinlog會(huì)停止。

mysqlbinlog  mysql-bin.000001  -d testDB -f | mysql -uusername -p

3)   --no-defaults

阻止mysqlbinlog工具從任何配置文件讀取參數(shù)，.mylogin.cnf除外（以便于安全的保存密碼）

mysqlbinlog mysql-bin.000001 -d testDB -f --no-defaults| mysql -uusername -p

4) --start-datetime=datetime和--stop-datetime=datetime

這兩個(gè)參數(shù)用于指定恢復(fù)開(kāi)始時(shí)間點(diǎn)和結(jié)束時(shí)間點(diǎn)，可以一起或單獨(dú)給出，也可與--start-position，--stop-position混用。

mysqlbinlog  mysql-bin.000001  -d testDB -f --no-defaults --start-datetime=datetime --stop-position=NNNNNN | mysql -uusername -p

5) --start-position=N, -j N和--stop-position=N

上邊一組參數(shù)用于指定恢復(fù)開(kāi)始位置和結(jié)束位置，可以一起或單獨(dú)給出也可與--start-datetime，--stop-datetime混用

mysqlbinlog  mysql-bin.000001  -d testDB -f --no-defaults --start-position=NNNNNN --stop-datetime=datetime | mysql -uusername -p

d)如果需要還原的二進(jìn)制日志文件不止一個(gè)，安全的方式是多個(gè)二進(jìn)制文件同時(shí)執(zhí)行。

mysqlbinlog  mysql-bin.000001  mysql-bin.000002 mysql-bin.000003 --start-position=NNNNNN --stop-datetime=datetime | mysql -uusername -p

或

mysqlbinlog  mysql-bin.00000[1-3] --start-position=NNNNNN --stop-datetime=datetime | mysql -uusername -p

當(dāng)多個(gè)二進(jìn)制日志文件同時(shí)執(zhí)行時(shí)，--start-position和--stop-position分別只應(yīng)用于第一個(gè)列出的二進(jìn)制日志文件和最后一個(gè)列出的二進(jìn)制日志文件

     當(dāng)然也可以先將多個(gè)二進(jìn)制日志文件的輸出導(dǎo)到同一個(gè).sql文件最后在執(zhí)行該.sql文件（適用于日志量不多的情況）。

8、binlog的應(yīng)用：

可以用binlog來(lái)恢復(fù)誤操作的數(shù)據(jù)。

案例：

1)全備份

mysqldump -uroot -p123456 -lF --log-error=/root/myDump.err -B zyyshop > /root/BAK.zyyshop.sql

備份時(shí)使用-F選項(xiàng)，意味著備份工作剛開(kāi)始時(shí)就會(huì)刷新log日志，產(chǎn)生新的binlog日志來(lái)記錄備份之后的數(shù)據(jù)庫(kù)的“增刪改”操作。

2)備份之后，業(yè)務(wù)對(duì)數(shù)據(jù)庫(kù)進(jìn)行了大量的增刪改查操作。然后數(shù)據(jù)庫(kù)有張表被誤刪除了。此刻立即查看最后一個(gè)binlog日志，記錄下關(guān)鍵的pos點(diǎn)，即是在哪個(gè)點(diǎn)上的操作導(dǎo)致了數(shù)據(jù)庫(kù)的破壞。然后flush logs，讓mysql重新開(kāi)始新的binlog日志記錄文件。從理論上講，此時(shí)舊的binlog日志是不會(huì)被繼續(xù)寫入了。此時(shí)，備份舊的binlog日志。

3)讀取舊的binlog日志，分析問(wèn)題。

方式一，用mysqlbinlog命令來(lái)讀取binlog日志：

     mysqlbinlog  /usr/local/mysql/data/mysql-bin.000023

方式二，在mysql服務(wù)器中查看：

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

在輸出中找到誤刪除表的確切pos點(diǎn)。

4)首先用全備份進(jìn)行恢復(fù)：

mysql -uroot -p123456 -v < /root/BAK.zyyshop.sql;

5)從binlog日志中恢復(fù)數(shù)據(jù)：

mysqlbinlog mysql-bin.0000xx | mysql -u用戶名 -p密碼數(shù)據(jù)庫(kù)名

所謂恢復(fù)，就是讓mysql將保存在binlog日志中指定段落區(qū)間的sql語(yǔ)句逐個(gè)重新執(zhí)行一次而已。

看完上述內(nèi)容，你們對(duì)怎樣理解mysql binlog有進(jìn)一步的了解嗎？如果還想了解更多知識(shí)或者相關(guān)內(nèi)容，請(qǐng)關(guān)注億速云行業(yè)資訊頻道，感謝大家的支持。