MySQL如何開啟binlog?binlog三種模式的分析

MySQL如何開啟binlog踢京?binlog三種模式的分析

前提誉碴,創(chuàng)建表t,并插入數(shù)據(jù)瓣距,語句如下:

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  `t_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `a` (`a`),
  KEY `t_modified`(`t_modified`)
) ENGINE=InnoDB;

insert into t values(1,1,'2018-11-13');
insert into t values(2,2,'2018-11-12');
insert into t values(3,3,'2018-11-11');
insert into t values(4,4,'2018-11-10');
insert into t values(5,5,'2018-11-09');

判斷MySQL是否已經(jīng)開啟binlog?

登錄mysql黔帕,執(zhí)行:SHOW VARIABLES LIKE 'log_bin';

  • OFF:關(guān)閉
  • ON:開啟

如何開啟binlog日志?

找到my.cnf :

select @@basedir;

如果還是找不到文件的位置蹈丸,可以執(zhí)行下面命令成黄,可以得到mysql的配置文件的默認(rèn)加載順序

mysql --help | grep 'Default options' -A 1

執(zhí)行結(jié)果:

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf

可以看到mysql優(yōu)先加載/etc/my.cnf中的配置呐芥。

所以需要在/etc/my.cnf中mysqld節(jié)添加開啟binlog的配置,如下有兩種方式:

#第一種方式:
#開啟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。執(zhí)行SHOW VARIABLES LIKE 'log_bin'; Value 值為 ON即可闻伶。

binlog的配置簡介

MySQL配置文件my.cnf文件中的mysqld節(jié)的配置:

[mysqld]
#設(shè)置日志三種格式:STATEMENT滨攻、ROW、MIXED 蓝翰。
binlog_format = mixed
#設(shè)置日志路徑光绕,注意路經(jīng)需要mysql用戶有權(quán)限寫
log-bin = /data/mysql/logs/mysql-bin.log
#設(shè)置binlog清理時間
expire_logs_days = 7
#binlog每個日志文件大小
max_binlog_size = 100m
#binlog緩存大小
binlog_cache_size = 4m
#最大binlog緩存大小
max_binlog_cache_size = 512m

重啟MySQL生效,如果不方便重啟服務(wù)畜份,也可以直接修改對應(yīng)的變量即可诞帐。

binlog的三種模式比較

binlog的格式也有三種:STATEMENTROW漂坏、MIXED景埃。

  • STATMENT模式:基于SQL語句的復(fù)制(statement-based replication, SBR)媒至,每一條會修改數(shù)據(jù)的sql語句會記錄到binlog中顶别。
    優(yōu)點:不需要記錄每一條SQL語句與每行的數(shù)據(jù)變化,這樣子binlog的日志也會比較少拒啰,減少了磁盤IO驯绎,提高性能。
    缺點:在某些情況下會導(dǎo)致master-slave中的數(shù)據(jù)不一致(比如:delete from t where a>=4 and t_modified<='2018-11-10' limit 1;在主庫執(zhí)行這個語句的時候谋旦,如果使用的是a索引剩失,會刪除(4,4,'2018-11-10')這條記錄,如果使用的是t_modified的索引則會刪除insert into t values(5,5,'2018-11-09');所以在執(zhí)行這條sql語句的時候提示:
    Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted.
    由于 statement 格式下册着,記錄到 binlog 里的是語句原文拴孤,因此可能會出現(xiàn)這樣一種情況:在主庫執(zhí)行這條 SQL 語句的時候,用的是索引 a甲捏;而在備庫執(zhí)行這條 SQL 語句的時候演熟,卻使用了索引 t_modified。因此司顿,MySQL 認(rèn)為這樣寫是有風(fēng)險的芒粹。
    sleep()函數(shù), last_insert_id()大溜,以及user-defined functions(udf)等也會出現(xiàn)問題)化漆;
  • ROW基于行的復(fù)制(row-based replication, RBR)格式:不記錄每一條SQL語句的上下文信息,僅需記錄哪條數(shù)據(jù)被修改了钦奋,修改成了什么樣子了座云。
    優(yōu)點:不會出現(xiàn)某些特定情況下的存儲過程疙赠、或function、或trigger的調(diào)用和觸發(fā)無法被正確復(fù)制的問題朦拖。
    缺點:會產(chǎn)生大量的日志棺聊,尤其是alter table的時候會讓日志暴漲。
  • MIXED混合模式復(fù)制(mixed-based replication, MBR):以上兩種模式的混合使用贞谓,一般的復(fù)制使用STATEMENT模式保存binlog限佩,對于STATEMENT模式無法復(fù)制的操作使用ROW模式保存binlog,MySQL會根據(jù)執(zhí)行的SQL語句選擇日志保存方式裸弦。

列舉幾個關(guān)于binlog常用的命令

#查看日志開啟狀態(tài) 
show variables like 'log_%';
#查看所有binlog日志列表
show master logs;
#查看最新一個binlog日志的編號名稱祟同,及其最后一個操作事件結(jié)束點 
show master status;
#刷新log日志,立刻產(chǎn)生一個新編號的binlog日志文件理疙,跟重啟一個效果 
flush logs;
#清空所有binlog日志 
reset master;

binlog中到底記錄了什么內(nèi)容晕城?如何分析binlog日志?

開啟binlog日志之后窖贤,我們在登錄mysql砖顷,在控制臺執(zhí)行一條sql語句:delete from t where a>=4 and t_modified<='2018-11-10' limit 1;
查看當(dāng)前的binlog日志文件show master status;;
筆者得到的是mysql-bin.000005,根據(jù)binlog日志文件的配置赃梧,我們可以去存儲binlog的日志文件下面看看cd /var/lib/mysql/,可以看到文件夾下面有mysql-bin.000005文件滤蝠。
執(zhí)行:cat mysql-bin.000005,結(jié)果如下:

[root@mysql]# cat mysql-bin.000005
n
 MZ]w{5.7.27-log
_               MZ]8


**4Z]#

可以看到授嘀,里面的內(nèi)容根本沒法閱讀物咳。

因為binlog日志文件:mysql-bin.000005是二進制文件,沒法用vi等打開蹄皱,這時就需要mysql的自帶的mysqlbinlog工具進行解碼览闰,執(zhí)行:mysqlbinlog mysql-bin.000005可以將二進制文件轉(zhuǎn)為可閱讀的sql語句。

分析對比binlog的ROW模式和STATEMENT模式下的日志:

ROW模式:

    /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
    /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
    DELIMITER /*!*/;
    # at 4
    #190819 15:35:46 server id 1  end_log_pos 123 CRC32 0x2bfa58f0  Start: binlog v 4, server v 5.7.27-log created 190819 15:35:46 at startup
    # Warning: this binlog is either in use or was not closed properly.
    ROLLBACK/*!*/;
    BINLOG '
    UlFaXQ8BAAAAdwAAAHsAAAABAAQANS43LjI3LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
    AAAAAAAAAAAAAAAAAABSUVpdEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
    AfBY+is=
    '/*!*/;
    # at 123
    #190819 15:35:46 server id 1  end_log_pos 154 CRC32 0xe4e91c8f  Previous-GTIDs
    # [empty]
    # at 154
    #190819 15:36:19 server id 1  end_log_pos 219 CRC32 0xcc3dc023  Anonymous_GTID  last_committed=0        sequence_number=1       rbr_only=yes
    /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
    SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
    # at 219
    #190819 15:36:19 server id 1  end_log_pos 305 CRC32 0xd39f98cf  Query   thread_id=2     exec_time=0     error_code=0
    SET TIMESTAMP=1566200179/*!*/;
    SET @@session.pseudo_thread_id=2/*!*/;
    SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
    SET @@session.sql_mode=1436549152/*!*/;
    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.time_zone='SYSTEM'/*!*/;
    SET @@session.lc_time_names=0/*!*/;
    SET @@session.collation_database=DEFAULT/*!*/;
    BEGIN
    /*!*/;
    # at 305
    #190819 15:36:19 server id 1  end_log_pos 358 CRC32 0x56a1e39d  Table_map: `mysql_test`.`t` mapped to number 108
    # at 358
    #190819 15:36:19 server id 1  end_log_pos 406 CRC32 0xc91237b0  Delete_rows: table id 108 flags: STMT_END_F
    
    BINLOG '
    c1FaXRMBAAAANQAAAGYBAAAAAGwAAAAAAAEACm15c3FsX3Rlc3QAAXQAAwMDEQEAAp3joVY=
    c1FaXSABAAAAMAAAAJYBAAAAAGwAAAAAAAEAAgAD//gEAAAABAAAAFvlrwCwNxLJ
    '/*!*/;
    ### DELETE FROM `mysql_test`.`t`
    ### WHERE
    ###   @1=4 /* INT meta=0 nullable=0 is_null=0 */
    ###   @2=4 /* INT meta=0 nullable=1 is_null=0 */
    ###   @3=1541779200 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
    # at 406
    #190819 15:36:19 server id 1  end_log_pos 437 CRC32 0x7898ebf6  Xid = 13
    COMMIT/*!*/;
    SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
    DELIMITER ;
    # End of log file
    /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
    /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

STATEMENT模式:

    /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
    /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
    DELIMITER /*!*/;
    # at 4
    #190819 16:19:48 server id 1  end_log_pos 123 CRC32 0xec3e6426  Start: binlog v 4, server v 5.7.27-log created 190819 16:19:48 at startup
    # Warning: this binlog is either in use or was not closed properly.
    ROLLBACK/*!*/;
    BINLOG '
    pFtaXQ8BAAAAdwAAAHsAAAABAAQANS43LjI3LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
    AAAAAAAAAAAAAAAAAACkW1pdEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
    ASZkPuw=
    '/*!*/;
    # at 123
    #190819 16:19:48 server id 1  end_log_pos 154 CRC32 0xc6db211a  Previous-GTIDs
    # [empty]
    # at 154
    #190819 16:20:01 server id 1  end_log_pos 219 CRC32 0x1960cba5  Anonymous_GTID  last_committed=0        sequence_number=1       rbr_only=no
    SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
    # at 219
    #190819 16:20:01 server id 1  end_log_pos 318 CRC32 0x11eecb38  Query   thread_id=2     exec_time=0     error_code=0
    SET TIMESTAMP=1566202801/*!*/;
    SET @@session.pseudo_thread_id=2/*!*/;
    SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
    SET @@session.sql_mode=1436549152/*!*/;
    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.time_zone='SYSTEM'/*!*/;
    SET @@session.lc_time_names=0/*!*/;
    SET @@session.collation_database=DEFAULT/*!*/;
    BEGIN
    /*!*/;
    # at 318
    #190819 16:20:01 server id 1  end_log_pos 473 CRC32 0x390d1f96  Query   thread_id=2     exec_time=0     error_code=0
    use `mysql_test`/*!*/;
    SET TIMESTAMP=1566202801/*!*/;
    delete from t where a>=4 and t_modified<='2018-11-10' limit 1
    /*!*/;
    # at 473
    #190819 16:20:01 server id 1  end_log_pos 504 CRC32 0x2f35c773  Xid = 13
    COMMIT/*!*/;
    SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
    DELIMITER ;
    # End of log file
    /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
    /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

可以看到ROW模式下巷折,binlog日志中的begin和commit之間并沒有sql語句压鉴。
STATEMENT模式下,binlog日志中的begin和commit之間是一條sql語句锻拘。

本文是作者根據(jù)日常業(yè)務(wù)場景油吭,寫出的一些解決問題或?qū)W習(xí)實驗的歷程。如有錯誤的地方逊拍,還請指出上鞠,相互學(xué)習(xí),共同進步芯丧。謝謝芍阎!

上一篇:如何在阿里云Linux系統(tǒng)上安裝MySQL

下一篇:MySQL如何配置主從復(fù)制,主從復(fù)制異常如何定位修復(fù)缨恒?

最后編輯于
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
  • 序言:七十年代末谴咸,一起剝皮案震驚了整個濱河市轮听,隨后出現(xiàn)的幾起案子,更是在濱河造成了極大的恐慌岭佳,老刑警劉巖血巍,帶你破解...
    沈念sama閱讀 210,978評論 6 490
  • 序言:濱河連續(xù)發(fā)生了三起死亡事件,死亡現(xiàn)場離奇詭異珊随,居然都是意外死亡述寡,警方通過查閱死者的電腦和手機,發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 89,954評論 2 384
  • 文/潘曉璐 我一進店門叶洞,熙熙樓的掌柜王于貴愁眉苦臉地迎上來鲫凶,“玉大人,你說我怎么就攤上這事衩辟±准ぃ” “怎么了汤功?”我有些...
    開封第一講書人閱讀 156,623評論 0 345
  • 文/不壞的土叔 我叫張陵诈火,是天一觀的道長魔种。 經(jīng)常有香客問我,道長封寞,這世上最難降的妖魔是什么然评? 我笑而不...
    開封第一講書人閱讀 56,324評論 1 282
  • 正文 為了忘掉前任,我火速辦了婚禮钥星,結(jié)果婚禮上沾瓦,老公的妹妹穿的比我還像新娘满着。我一直安慰自己谦炒,他們只是感情好,可當(dāng)我...
    茶點故事閱讀 65,390評論 5 384
  • 文/花漫 我一把揭開白布风喇。 她就那樣靜靜地躺著宁改,像睡著了一般。 火紅的嫁衣襯著肌膚如雪魂莫。 梳的紋絲不亂的頭發(fā)上还蹲,一...
    開封第一講書人閱讀 49,741評論 1 289
  • 那天,我揣著相機與錄音耙考,去河邊找鬼谜喊。 笑死,一個胖子當(dāng)著我的面吹牛倦始,可吹牛的內(nèi)容都是我干的斗遏。 我是一名探鬼主播,決...
    沈念sama閱讀 38,892評論 3 405
  • 文/蒼蘭香墨 我猛地睜開眼鞋邑,長吁一口氣:“原來是場噩夢啊……” “哼诵次!你這毒婦竟也來了账蓉?” 一聲冷哼從身側(cè)響起,我...
    開封第一講書人閱讀 37,655評論 0 266
  • 序言:老撾萬榮一對情侶失蹤逾一,失蹤者是張志新(化名)和其女友劉穎铸本,沒想到半個月后,有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體遵堵,經(jīng)...
    沈念sama閱讀 44,104評論 1 303
  • 正文 獨居荒郊野嶺守林人離奇死亡箱玷,尸身上長有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點故事閱讀 36,451評論 2 325
  • 正文 我和宋清朗相戀三年,在試婚紗的時候發(fā)現(xiàn)自己被綠了陌宿。 大學(xué)時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片汪茧。...
    茶點故事閱讀 38,569評論 1 340
  • 序言:一個原本活蹦亂跳的男人離奇死亡,死狀恐怖限番,靈堂內(nèi)的尸體忽然破棺而出舱污,到底是詐尸還是另有隱情,我是刑警寧澤弥虐,帶...
    沈念sama閱讀 34,254評論 4 328
  • 正文 年R本政府宣布扩灯,位于F島的核電站,受9級特大地震影響霜瘪,放射性物質(zhì)發(fā)生泄漏珠插。R本人自食惡果不足惜,卻給世界環(huán)境...
    茶點故事閱讀 39,834評論 3 312
  • 文/蒙蒙 一颖对、第九天 我趴在偏房一處隱蔽的房頂上張望捻撑。 院中可真熱鬧,春花似錦缤底、人聲如沸顾患。這莊子的主人今日做“春日...
    開封第一講書人閱讀 30,725評論 0 21
  • 文/蒼蘭香墨 我抬頭看了看天上的太陽江解。三九已至,卻和暖如春徙歼,著一層夾襖步出監(jiān)牢的瞬間犁河,已是汗流浹背。 一陣腳步聲響...
    開封第一講書人閱讀 31,950評論 1 264
  • 我被黑心中介騙來泰國打工魄梯, 沒想到剛下飛機就差點兒被人妖公主榨干…… 1. 我叫王不留桨螺,地道東北人。 一個月前我還...
    沈念sama閱讀 46,260評論 2 360
  • 正文 我出身青樓酿秸,卻偏偏與公主長得像灭翔,于是被迫代替她去往敵國和親。 傳聞我的和親對象是個殘疾皇子允扇,可洞房花燭夜當(dāng)晚...
    茶點故事閱讀 43,446評論 2 348

推薦閱讀更多精彩內(nèi)容