mysql實(shí)現(xiàn)主從復(fù)制/主從同步

業(yè)務(wù)場景

小公司業(yè)務(wù)代碼存于一個(gè)服務(wù)器上澎语，而這個(gè)服務(wù)器有的時(shí)候回宕機(jī)畸肆，導(dǎo)致業(yè)務(wù)停頓泡徙，造成影響径筏。這個(gè)時(shí)候 就需要做高可用 兩個(gè)ngix+兩個(gè)tomcat+兩個(gè)mysql實(shí)現(xiàn)高可用该肴，避免單點(diǎn)問題情竹。中間使用keepalived監(jiān)聽。下面先從簡單的mysql主從搞起匀哄。下面按照老方式秦效，what->why->how ,是什么，為什么涎嚼，怎么做來講解一波阱州。

（what）什么是mysql 的主從復(fù)制？

① 簡介

指一臺服務(wù)器充當(dāng)主數(shù)據(jù)庫服務(wù)器法梯，另一臺或多臺服務(wù)器充當(dāng)從數(shù)據(jù)庫服務(wù)器苔货，主服務(wù)器中的數(shù)據(jù)自動復(fù)制到從服務(wù)器之中。對于多級復(fù)制立哑，數(shù)據(jù)庫服務(wù)器即可充當(dāng)主機(jī)夜惭，也可充當(dāng)從機(jī)。MySQL主從復(fù)制的基礎(chǔ)是主服務(wù)器對數(shù)據(jù)庫修改記錄二進(jìn)制日志刁憋，從服務(wù)器通過主服務(wù)器的二進(jìn)制日志自動執(zhí)行更新滥嘴。

一句話表示就是，主數(shù)據(jù)庫做什么至耻，從數(shù)據(jù)庫就跟著做什么若皱。

② mysql復(fù)制的類型

1.基于語句的復(fù)制 ：主庫把sql語句寫入到bin log中镊叁，完成復(fù)制

2.基于行數(shù)據(jù)的復(fù)制：主庫把每一行數(shù)據(jù)變化的信息作為事件，寫入到bin log走触，完成復(fù)制

3.混合復(fù)制：上面兩個(gè)結(jié)合體晦譬，默認(rèn)用語句復(fù)制，出問題時(shí)候自動切換成行數(shù)據(jù)復(fù)制

tip:和上面相對應(yīng)的日志格式也有三種:STATEMENT互广，ROW敛腌，MIXED。

1.STATEMENT模式（SBR）

每一條會修改數(shù)據(jù)的sql語句會記錄到binlog中惫皱。優(yōu)點(diǎn)是并不需要記錄每一條sql語句和每一行的數(shù)據(jù)變化像樊，減少了binlog日志量，節(jié)約IO旅敷，提高性能生棍。缺點(diǎn)是在某些情況下會導(dǎo)致master-slave中的數(shù)據(jù)不一致(如sleep()函數(shù)， last_insert_id()媳谁，以及user-defined functions(udf)等會出現(xiàn)問題)

ROW模式（RBR）

不記錄每條sql語句的上下文信息涂滴，僅需記錄哪條數(shù)據(jù)被修改了，修改成什么樣了晴音。而且不會出現(xiàn)某些特定情況下的存儲過程柔纵、或function、或trigger的調(diào)用和觸發(fā)無法被正確復(fù)制的問題锤躁。缺點(diǎn)是會產(chǎn)生大量的日志搁料，尤其是alter table的時(shí)候會讓日志暴漲。

MIXED模式（MBR）

以上兩種模式的混合使用进苍，一般的復(fù)制使用STATEMENT模式保存binlog加缘，對于STATEMENT模式無法復(fù)制的操作使用ROW模式保存binlog，MySQL會根據(jù)執(zhí)行的SQL語句選擇日志保存方式觉啊。

③ 主從復(fù)制工作原理剖析

1.Master 數(shù)據(jù)庫只要發(fā)生變化，立馬記錄到Binary log 日志文件中

2.Slave數(shù)據(jù)庫啟動一個(gè)I/O thread連接Master數(shù)據(jù)庫沈贝，請求Master變化的二進(jìn)制日志

3.Slave I/O獲取到的二進(jìn)制日志杠人，保存到自己的Relay log 日志文件中。

4.Slave 有一個(gè) SQL thread定時(shí)檢查Realy log是否變化宋下，變化那么就更新數(shù)據(jù)

2140640119-58e3a97a13419_articlex.jpg

（why）為什么要用mysql 的主從

1.實(shí)現(xiàn)服務(wù)器負(fù)載均衡

即可以通過在主服務(wù)器和從服務(wù)器之間切分處理客戶查詢的負(fù)荷嗡善，從而得到更好的客戶相應(yīng)時(shí)間。通常情況下学歧，數(shù)據(jù)庫管理員會有兩種思路罩引。

???????一是在主服務(wù)器上只實(shí)現(xiàn)數(shù)據(jù)的更新操作。包括數(shù)據(jù)記錄的更新枝笨、刪除袁铐、新建等等作業(yè)揭蜒。而不關(guān)心數(shù)據(jù)的查詢作業(yè)。數(shù)據(jù)庫管理員將數(shù)據(jù)的查詢請求全部 轉(zhuǎn)發(fā)到從服務(wù)器中剔桨。這在某些應(yīng)用中會比較有用屉更。如某些應(yīng)用，像基金凈值預(yù)測的網(wǎng)站洒缀。其數(shù)據(jù)的更新都是有管理員更新的瑰谜，即更新的用戶比較少。而查詢的用戶數(shù) 量會非常的多树绩。此時(shí)就可以設(shè)置一臺主服務(wù)器萨脑，專門用來數(shù)據(jù)的更新。同時(shí)設(shè)置多臺從服務(wù)器饺饭，用來負(fù)責(zé)用戶信息的查詢

???????二是在主服務(wù)器上與從服務(wù)器切分查詢的作業(yè)渤早。在這種思路下，主服務(wù)器不單單要完成數(shù)據(jù)的更新砰奕、刪除蛛芥、插入等作業(yè)，同時(shí)也需要負(fù)擔(dān)一部分查詢作業(yè)军援。而從服務(wù)器的話仅淑，只負(fù)責(zé)數(shù)據(jù)的查詢。當(dāng)主服務(wù)器比較忙時(shí)胸哥，部分查詢請求會自動發(fā)送到從服務(wù)器重涯竟，以降低主服務(wù)器的工作負(fù)荷。

2.通過復(fù)制實(shí)現(xiàn)數(shù)據(jù)的異地備份

可以定期的將數(shù)據(jù)從主服務(wù)器上復(fù)制到從服務(wù)器上空厌，這無疑是先了數(shù)據(jù)的異地備份庐船。在傳統(tǒng)的備份體制下，是將數(shù)據(jù)備份在本地嘲更。此時(shí)備份 作業(yè)與數(shù)據(jù)庫服務(wù)器運(yùn)行在同一臺設(shè)備上筐钟，當(dāng)備份作業(yè)運(yùn)行時(shí)就會影響到服務(wù)器的正常運(yùn)行。有時(shí)候會明顯的降低服務(wù)器的性能赋朦。同時(shí)篓冲，將備份數(shù)據(jù)存放在本地，也 不是很安全宠哄。如硬盤因?yàn)殡妷旱仍虮粨p壞或者服務(wù)器被失竊壹将，此時(shí)由于備份文件仍然存放在硬盤上，數(shù)據(jù)庫管理員無法使用備份文件來恢復(fù)數(shù)據(jù)毛嫉。這顯然會給企業(yè) 帶來比較大的損失诽俯。

3.提高數(shù)據(jù)庫系統(tǒng)的可用性

???????數(shù)據(jù)庫復(fù)制功能實(shí)現(xiàn)了主服務(wù)器與從服務(wù)器之間數(shù)據(jù)的同步，增加了數(shù)據(jù)庫系統(tǒng)的可用性承粤。當(dāng)主服務(wù)器出現(xiàn)問題時(shí)暴区，數(shù)據(jù)庫管理員可以馬上讓從服務(wù)器作為主服務(wù)器闯团，用來數(shù)據(jù)的更新與查詢服務(wù)。然后回過頭來再仔細(xì)的檢查主服務(wù)器的問題颜启。此時(shí)一般數(shù)據(jù)庫管理員也會采用兩種手段偷俭。

???????一是主服務(wù)器故障之后，雖然從服務(wù)器取代了主服務(wù)器的位置缰盏，但是對于主服務(wù)器可以采取的操作仍然做了一些限制涌萤。如仍然只能夠進(jìn)行數(shù)據(jù)的查詢，而 不能夠進(jìn)行數(shù)據(jù)的更新口猜、刪除等操作负溪。這主要是從數(shù)據(jù)的安全性考慮。如現(xiàn)在一些銀行系統(tǒng)的升級济炎，在升級的過程中川抡，只能夠查詢余額而不能夠取錢。這是同樣的道 理须尚。

???????二是從服務(wù)器真正變成了主服務(wù)器崖堤。當(dāng)從服務(wù)器切換為主服務(wù)器之后，其地位完全與原先的主服務(wù)器相同耐床。此時(shí)可以實(shí)現(xiàn)對數(shù)據(jù)的查詢密幔、更新、刪除等操 作撩轰。為此就需要做好數(shù)據(jù)的安全性工作胯甩。即數(shù)據(jù)的安全策略，要與原先的主服務(wù)器完全相同堪嫂。否則的話偎箫，就可能會留下一定的安全隱患

（how）怎么配置mysql主從復(fù)制

① 環(huán)境準(zhǔn)備

本地安裝兩個(gè)mysql，或者使用虛擬機(jī)皆串，或者使用docker安裝淹办，需要準(zhǔn)備兩個(gè)mysql，本文使用docker安裝恶复，具體安裝方法詳見

http://www.reibang.com/p/10769f985516

環(huán)境

宿主機(jī) centos7

mysql:5.6

mysql1(master):? 172.17.0.3:3307

mysql2(slave): 172.17.0.2:3308

② mysql 配置文件配置

mysql1(master):? 172.17.0.3 my.cnf 配置文件設(shè)置娇唯，mysql

#mysql master1 config [mysqld]server-id = 1? ? ? ? # 節(jié)點(diǎn)ID，確保唯一# log configlog-bin = mysql-bin? ? #開啟mysql的binlog日志功能sync_binlog = 1? ? ? ? #控制數(shù)據(jù)庫的binlog刷到磁盤上去 , 0 不控制寂玲，性能最好，1每次事物提交都會刷到日志文件中梗摇，性能最差拓哟，最安全binlog_format = mixed? #binlog日志格式，mysql默認(rèn)采用statement伶授，建議使用mixedexpire_logs_days = 7? ? ? ? ? ? ? ? ? ? ? ? ? #binlog過期清理時(shí)間max_binlog_size = 100m? ? ? ? ? ? ? ? ? ? #binlog每個(gè)日志文件大小binlog_cache_size = 4m? ? ? ? ? ? ? ? ? ? ? ? #binlog緩存大小max_binlog_cache_size= 512m? ? ? ? ? ? ? #最大binlog緩存大binlog-ignore-db=mysql #不生成日志文件的數(shù)據(jù)庫断序，多個(gè)忽略數(shù)據(jù)庫可以用逗號拼接流纹，或者 復(fù)制這句話，寫多行auto-increment-offset = 1? ? # 自增值的偏移量auto-increment-increment = 1? # 自增值的自增量slave-skip-errors = all #跳過從庫錯(cuò)誤

mysql2(slave): 172.17.0.2 mysql.cnf 配置

[mysqld]server-id = 2log-bin=mysql-binrelay-log = mysql-relay-binreplicate-wild-ignore-table=mysql.%replicate-wild-ignore-table=test.%replicate-wild-ignore-table=information_schema.%

重啟兩個(gè)mysql违诗，讓配置生效

③ master數(shù)據(jù)庫漱凝，創(chuàng)建復(fù)制用戶并授權(quán)

1.進(jìn)入master的數(shù)據(jù)庫，為master創(chuàng)建復(fù)制用戶

CREATE USER repl_user IDENTIFIED BY 'repl_passwd';

2.賦予該用戶復(fù)制的權(quán)利

grant replication slave on*.*to'repl_user'@'172.17.0.2'identified by'repl_passwd';FLUSHPRIVILEGES;

3.查看master的狀態(tài)

show master status;mysql>show master status;+------------------+----------+--------------+------------------+-------------------+|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set|+------------------+----------+--------------+------------------+-------------------+|mysql-bin.000005120||mysql||+------------------+----------+--------------+------------------+-------------------+1rowinset(0.00sec)

4,配置從庫

mysql> CHANGE MASTER TO MASTER_HOST = '172.17.0.3',? MASTER_USER = 'repl_user', MASTER_PASSWORD = 'repl_passwd',MASTER_PORT = 3307,MASTER_LOG_FILE='mysql-bin.000005',MASTER_LOG_POS=120,MASTER_RETRY_COUNT = 60,MASTER_HEARTBEAT_PERIOD = 10000; # MASTER_LOG_FILE='mysql-bin.000005',#與主庫File 保持一致# MASTER_LOG_POS=120 , #與主庫Position 保持一致

啟動從庫slave進(jìn)程

mysql> slave start;Query OK,0 rows affected(0.04 sec)

查看是否配置成功


作者：shiva_s_guard

鏈接：http://www.reibang.com/p/19cb0f16dea4

來源：簡書

著作權(quán)歸作者所有诸迟。商業(yè)轉(zhuǎn)載請聯(lián)系作者獲得授權(quán)茸炒，非商業(yè)轉(zhuǎn)載請注明出處。

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
  • 人面猴
    序言：七十年代末阵苇，一起剝皮案震驚了整個(gè)濱河市壁公，隨后出現(xiàn)的幾起案子，更是在濱河造成了極大的恐慌绅项，老刑警劉巖紊册，帶你破解...
    沈念sama閱讀 219,270評論 6贊 508
  • 死咒
    序言：濱河連續(xù)發(fā)生了三起死亡事件，死亡現(xiàn)場離奇詭異快耿，居然都是意外死亡囊陡，警方通過查閱死者的電腦和手機(jī)，發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 93,489評論 3贊 395
  • 救了他兩次的神仙讓他今天三更去死
    文/潘曉璐 我一進(jìn)店門掀亥，熙熙樓的掌柜王于貴愁眉苦臉地迎上來撞反，“玉大人，你說我怎么就攤上這事铺浇×⌒螅” “怎么了？”我有些...
    開封第一講書人閱讀 165,630評論 0贊 356
  • 道士緝兇錄：失蹤的賣姜人
    文/不壞的土叔 我叫張陵鳍侣，是天一觀的道長丁稀。 經(jīng)常有香客問我，道長倚聚，這世上最難降的妖魔是什么线衫？ 我笑而不...
    開封第一講書人閱讀 58,906評論 1贊 295
  • ?港島之戀（遺憾婚禮）
    正文 為了忘掉前任，我火速辦了婚禮惑折，結(jié)果婚禮上授账，老公的妹妹穿的比我還像新娘。我一直安慰自己惨驶，他們只是感情好白热，可當(dāng)我...
    茶點(diǎn)故事閱讀 67,928評論 6贊 392
  • 惡毒庶女頂嫁案：這布局不是一般人想出來的
    文/花漫 我一把揭開白布。 她就那樣靜靜地躺著粗卜，像睡著了一般屋确。 火紅的嫁衣襯著肌膚如雪。 梳的紋絲不亂的頭發(fā)上，一...
    開封第一講書人閱讀 51,718評論 1贊 305
  • 城市分裂傳說
    那天攻臀，我揣著相機(jī)與錄音焕数，去河邊找鬼。 笑死刨啸，一個(gè)胖子當(dāng)著我的面吹牛堡赔，可吹牛的內(nèi)容都是我干的。 我是一名探鬼主播设联，決...
    沈念sama閱讀 40,442評論 3贊 420
  • 雙鴛鴦連環(huán)套：你想象不到人心有多黑
    文/蒼蘭香墨 我猛地睜開眼善已，長吁一口氣：“原來是場噩夢啊……” “哼！你這毒婦竟也來了仑荐？” 一聲冷哼從身側(cè)響起雕拼，我...
    開封第一講書人閱讀 39,345評論 0贊 276
  • 萬榮殺人案實(shí)錄
    序言：老撾萬榮一對情侶失蹤，失蹤者是張志新（化名）和其女友劉穎粘招，沒想到半個(gè)月后啥寇，有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體，經(jīng)...
    沈念sama閱讀 45,802評論 1贊 317
  • ?護(hù)林員之死
    正文 獨(dú)居荒郊野嶺守林人離奇死亡洒扎，尸身上長有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點(diǎn)故事閱讀 37,984評論 3贊 337
  • ?白月光啟示錄
    正文 我和宋清朗相戀三年辑甜，在試婚紗的時(shí)候發(fā)現(xiàn)自己被綠了。 大學(xué)時(shí)的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片袍冷。...
    茶點(diǎn)故事閱讀 40,117評論 1贊 351
  • 活死人
    序言：一個(gè)原本活蹦亂跳的男人離奇死亡磷醋，死狀恐怖，靈堂內(nèi)的尸體忽然破棺而出胡诗，到底是詐尸還是另有隱情邓线，我是刑警寧澤，帶...
    沈念sama閱讀 35,810評論 5贊 346
  • ?日本核電站爆炸內(nèi)幕
    正文 年R本政府宣布煌恢，位于F島的核電站骇陈，受9級特大地震影響，放射性物質(zhì)發(fā)生泄漏瑰抵。R本人自食惡果不足惜你雌，卻給世界環(huán)境...
    茶點(diǎn)故事閱讀 41,462評論 3贊 331
  • 男人毒藥：我在死后第九天來索命
    文/蒙蒙 一、第九天 我趴在偏房一處隱蔽的房頂上張望二汛。 院中可真熱鬧婿崭，春花似錦、人聲如沸肴颊。這莊子的主人今日做“春日...
    開封第一講書人閱讀 32,011評論 0贊 22
  • 一樁弒父案，背后竟有這般陰謀
    文/蒼蘭香墨 我抬頭看了看天上的太陽婿着。三九已至颤绕，卻和暖如春幸海，著一層夾襖步出監(jiān)牢的瞬間，已是汗流浹背奥务。 一陣腳步聲響...
    開封第一講書人閱讀 33,139評論 1贊 272
  • 情欲美人皮
    我被黑心中介騙來泰國打工， 沒想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留袜硫，地道東北人氯葬。 一個(gè)月前我還...
    沈念sama閱讀 48,377評論 3贊 373
  • 代替公主和親
    正文 我出身青樓，卻偏偏與公主長得像婉陷，于是被迫代替她去往敵國和親帚称。 傳聞我的和親對象是個(gè)殘疾皇子，可洞房花燭夜當(dāng)晚...
    茶點(diǎn)故事閱讀 45,060評論 2贊 355