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

業(yè)務(wù)場(chǎng)景

小公司業(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)問(wèn)題。中間使用keepalived監(jiān)聽。下面先從簡(jiǎn)單的mysql主從搞起。下面按照老方式，what->why->how ,是什么，為什么，怎么做來(lái)講解一波。

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

① 簡(jiǎn)介

指一臺(tái)服務(wù)器充當(dāng)主數(shù)據(jù)庫(kù)服務(wù)器，另一臺(tái)或多臺(tái)服務(wù)器充當(dāng)從數(shù)據(jù)庫(kù)服務(wù)器，主服務(wù)器中的數(shù)據(jù)自動(dòng)復(fù)制到從服務(wù)器之中。對(duì)于多級(jí)復(fù)制，數(shù)據(jù)庫(kù)服務(wù)器即可充當(dāng)主機(jī)，也可充當(dāng)從機(jī)。MySQL主從復(fù)制的基礎(chǔ)是主服務(wù)器對(duì)數(shù)據(jù)庫(kù)修改記錄二進(jìn)制日志，從服務(wù)器通過(guò)主服務(wù)器的二進(jìn)制日志自動(dòng)執(zhí)行更新。

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

② mysql復(fù)制的類型

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

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

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

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

1.STATEMENT模式（SBR）

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

ROW模式（RBR）

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

MIXED模式（MBR）

以上兩種模式的混合使用，一般的復(fù)制使用STATEMENT模式保存binlog，對(duì)于STATEMENT模式無(wú)法復(fù)制的操作使用ROW模式保存binlog，MySQL會(huì)根據(jù)執(zhí)行的SQL語(yǔ)句選擇日志保存方式。

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

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

2.Slave數(shù)據(jù)庫(kù)啟動(dòng)一個(gè)I/O thread連接Master數(shù)據(jù)庫(kù)，請(qǐng)求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ù)載均衡

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

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

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

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

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

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

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

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

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

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

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

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

http://www.itdecent.cn/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ù)庫(kù)的binlog刷到磁盤上去 , 0 不控制，性能最好，1每次事物提交都會(huì)刷到日志文件中，性能最差，最安全binlog_format = mixed? #binlog日志格式，mysql默認(rèn)采用statement，建議使用mixedexpire_logs_days = 7? ? ? ? ? ? ? ? ? ? ? ? ? #binlog過(guò)期清理時(shí)間max_binlog_size = 100m? ? ? ? ? ? ? ? ? ? #binlog每個(gè)日志文件大小binlog_cache_size = 4m? ? ? ? ? ? ? ? ? ? ? ? #binlog緩存大小max_binlog_cache_size= 512m? ? ? ? ? ? ? #最大binlog緩存大binlog-ignore-db=mysql #不生成日志文件的數(shù)據(jù)庫(kù)，多個(gè)忽略數(shù)據(jù)庫(kù)可以用逗號(hào)拼接，或者 復(fù)制這句話，寫多行auto-increment-offset = 1? ? # 自增值的偏移量auto-increment-increment = 1? # 自增值的自增量slave-skip-errors = all #跳過(guò)從庫(kù)錯(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ù)庫(kù)，創(chuàng)建復(fù)制用戶并授權(quán)

1.進(jìn)入master的數(shù)據(jù)庫(kù)，為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,配置從庫(kù)

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',#與主庫(kù)File 保持一致# MASTER_LOG_POS=120 , #與主庫(kù)Position 保持一致

啟動(dòng)從庫(kù)slave進(jìn)程

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

查看是否配置成功


作者：shiva_s_guard

鏈接：http://www.itdecent.cn/p/19cb0f16dea4

來(lái)源：簡(jiǎn)書

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

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請(qǐng)結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡(jiǎn)書系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

友情鏈接更多精彩內(nèi)容