數(shù)據(jù)庫架構(gòu)之【MySQL8+Replication】RDBMS 主從庫讀寫分離方案

MySQL8 數(shù)據(jù)庫系統(tǒng)內(nèi)置 Replication （復制）功能绞旅，通過 Replication 功能可以快速便利的實現(xiàn)數(shù)據(jù)庫實時備份和讀寫分離淹遵。

本方案基于CentOS8系統(tǒng)設(shè)計杖剪，建議在RedHat/CentOS系統(tǒng)中使用啰劲。

1. Replication 拓撲結(jié)構(gòu)

Replication 由主庫節(jié)點和從庫節(jié)點組成损搬，其中：

1起惕、主庫節(jié)點只能有1個崎淳，節(jié)點上部署的數(shù)據(jù)庫實例即能讀也能寫堪夭；

2、從庫節(jié)點至少有1個拣凹，根據(jù)應用場景可以部署≥2個森爽，節(jié)點上部署的數(shù)據(jù)庫實例只能讀；

3嚣镜、當數(shù)據(jù)寫入主庫節(jié)點后爬迟，主要節(jié)點將數(shù)據(jù)插入/更新/刪除事務的腳本分別傳輸?shù)剿械膹膸旃?jié)點上執(zhí)行，使從屬節(jié)點的數(shù)據(jù)與主要節(jié)點保持一致菊匿。

因此：

各個從屬節(jié)點實現(xiàn)了數(shù)據(jù)庫的實時備份付呕，當主要節(jié)點服務器的磁盤或文件故障時，能夠避免數(shù)據(jù)損失并為主要節(jié)點提供恢復副本跌捆，提高數(shù)據(jù)安全性徽职。

應用系統(tǒng)事務性的插入/更新/刪除/查詢類操作可以通過主要節(jié)點完成，而較高性能損耗和高并發(fā)的查詢/統(tǒng)計/分析/導出類操作可以通過多個從屬節(jié)點完成佩厚，實現(xiàn)讀寫分離姆钉，提高應用系統(tǒng)性能。

2.Replication 應用場景

假設(shè)有一個“人事管理系統(tǒng)”抄瓦，主要實現(xiàn)了員工信息的員工信息的錄入潮瓶、查看、瀏覽闺鲸、綜合查詢筋讨、信息導出、統(tǒng)計分析模塊摸恍。那在使用 Replication 時悉罕，錄入、查看立镶、瀏覽模塊使用主庫節(jié)點壁袄，綜合查詢、信息導出和統(tǒng)計分析可以共同或者分別使用一個或多個從庫節(jié)點媚媒。

在選用 Replication 之前嗜逻，應當考量以下技術(shù)要點：

1、從數(shù)據(jù)庫高可用角度分析缭召， Replication 雖然由多個數(shù)據(jù)庫節(jié)點組成栈顷，但本質(zhì)來講仍然是“單點數(shù)據(jù)庫”逆日，因為它不具備“集群數(shù)據(jù)庫”典型的故障轉(zhuǎn)移和負載均衡功能。

首先萄凤，當主要或從庫節(jié)點發(fā)生故障時室抽，本身并不存在故障轉(zhuǎn)移機制，因此仍會造成應用系統(tǒng)的局部故障靡努，比如：當主庫節(jié)點故障時坪圾，應用系統(tǒng)的事務處理和信息管理模塊也會連帶故障，但復雜查詢惑朦、數(shù)據(jù)導出兽泄、統(tǒng)計分析模塊仍然可用。

其次漾月，當主要或從庫節(jié)點負載過高時病梢，本身并不存在請求分發(fā)或分布式處理的機制，因此仍會造成應用系統(tǒng)的局部性能低下栅屏，比如：當業(yè)務系統(tǒng)產(chǎn)生大量事務性并發(fā)時飘千，主庫節(jié)點負載所有流量，并不會分流到從屬節(jié)點上栈雳，但同樣也不會影響復雜查詢护奈、數(shù)據(jù)導出、統(tǒng)計分析模塊的性能哥纫。

因此霉旗， Replication 僅實現(xiàn)了在同一個應用系統(tǒng)中，為不同類型的模塊按計劃分配獨立數(shù)據(jù)庫節(jié)點蛀骇，達到提升整體性能的目的厌秒。

2、只有一個主庫節(jié)點擅憔，對于事務處理和信息管理模塊的性能優(yōu)化是有限的鸵闪。

在 Replication 中，有且只有一個主庫節(jié)點可以進行讀寫操作暑诸，因此對于事務處理和信息管理模塊的性能優(yōu)化是有限的蚌讼，或者說仍然是單點數(shù)據(jù)庫服務器支持的。

3个榕、并不是所有讀請求都適合使用從庫節(jié)點篡石。

在 Replication 中，各個從庫節(jié)點的數(shù)據(jù)來源主要節(jié)點西采，并且不是與主要節(jié)點保持事務一致性的凰萨，在一些特別情況下（如：網(wǎng)絡(luò)不穩(wěn)定），有可能在極短的時間內(nèi)，主要節(jié)點寫入的新數(shù)據(jù)沒有同步到從屬節(jié)點中胖眷。比如：

事務模塊在一個表單頁面完成信息錄入后武通，單擊“保存”按鈕將數(shù)據(jù)寫入到主要節(jié)點后立即跳轉(zhuǎn)到該信息的查看頁面。首先數(shù)據(jù)寫入使用主要節(jié)點無疑珊搀，類似于查看頁面讀取數(shù)據(jù)的功能厅须，就不適合使用從屬節(jié)點，應該仍然使用主要節(jié)點食棕。因為從保存數(shù)據(jù)到查看數(shù)據(jù)的過程很短（幾乎是瞬間的），所以不能夠期待從屬節(jié)點每次都能在讀取數(shù)據(jù)之前從主要節(jié)點同步到數(shù)據(jù)错沽，即便大多數(shù)時候可以簿晓。

綜合來看，使用 Replication 的應用系統(tǒng)應當具備以下特點：

1千埃、應用系統(tǒng)的連續(xù)運行要求一般憔儿，可以接受一定時間的宕機；

2放可、應用系統(tǒng)的整體并發(fā)量一般谒臼，但個別的查詢、統(tǒng)計耀里、導出類功能相對于事務處理和業(yè)務管理類功能有較大的并發(fā)或性能損耗蜈缤。

3. Replication 安裝部署

3.1 網(wǎng)絡(luò)資源規(guī)劃

1、主庫節(jié)點：

1）操作系統(tǒng)：CentOS8

2）IP地址和端口號：192.168.216.20:3306

3）主機名：DB-M

4）數(shù)據(jù)庫：MySQL8

2冯挎、從庫節(jié)點1

1）操作系統(tǒng)：CentOS8

2）IP地址和端口號：192.168.216.21:3306

3）主機名：DB-S1

4）數(shù)據(jù)庫：MySQL8

3底哥、從庫節(jié)點-≥2（可擴充）

1）操作系統(tǒng)：CentOS8

2）IP地址和端口號：192.168.216.22:3306

3）主機名：DB-S2

4）數(shù)據(jù)庫：MySQL8

3.2. 安裝和配置

3.2.1.主從節(jié)點數(shù)據(jù)庫安裝

在各個服務器上安裝 MySQL8 數(shù)據(jù)庫。 有關(guān)如何安裝 MySQL8 數(shù)據(jù)庫房官，請閱讀文章《RedHat/CentOS8【MySQL8】安裝趾徽、配置和管理》，文章地址【http://www.reibang.com/p/b68e2120a068】翰守。

3.2.2.主庫節(jié)點配置

1孵奶、設(shè)置主庫配置文件參數(shù)。

使用文本編輯器打開配置文件：

[centos@DB-M ~]$ sudo gedit /etc/my.cnf

追加或驗證文件中的以下參數(shù)并保存：

[mysqld]

# 表啟用 binlog （MySQL 的 Replication 通過 binlog 實現(xiàn)）功能蜡峰，并指定日志文件的路徑名稱了袁。
log-bin=binlog

# 表示二進制日志索引文件的路徑與名稱，一般不需設(shè)置事示。
# log_bin_index=mysql-bin.index

# 表示日志寫入磁盤的模式早像。
# 當設(shè)置為0，每隔一秒更新一次到日志文件肖爵，并刷新到磁盤中卢鹦。該模式下在事務提交的時候，不會主動觸發(fā)寫入磁盤的操作。速度最快冀自，但當節(jié)點實例故障時會丟失上一秒鐘所有事務揉稚。
# 當設(shè)置為1，默認模式熬粗，每次提交事務時更新一次到日志文件搀玖，并刷新到磁盤中。速度最慢但最安全驻呐，但當節(jié)點實例故障時會最多一個語句或者一個事務灌诅。
# 當設(shè)置為2，每次提交事務時更新一次到日志文件含末，但每隔一秒刷新一次到磁盤中猜拾。
innodb_flush_log_at_trx_commit=1

# 表示從當日志文件緩存的事務數(shù)達到設(shè)置值后，刷新到磁盤中佣盒。
# 當設(shè)置為0挎袜，表示由文件系統(tǒng)自動刷新到磁盤中。設(shè)置為0時最高效肥惭。
# 當職位為≥1盯仪，表示日志文件緩存到該事務數(shù)量時，刷新到磁盤中蜜葱。設(shè)置為1時最安全全景。
sync_binlog=1

# 表示日志的格式∏６冢可選項包括：Row蚪燕，Statement，Mixed奔浅。
# 當設(shè)置為 Row馆纳，主節(jié)點在日志中會記錄每一行數(shù)據(jù)被修改的形式，然后在備節(jié)點中對相同的數(shù)據(jù)進行修改汹桦。優(yōu)點是直接記錄最終的數(shù)據(jù)狀態(tài)鲁驶，不受存儲過程或觸發(fā)器的影響；缺點是日志記錄的數(shù)據(jù)量非常大舞骆。
# 當設(shè)置為 Statement钥弯，默認值，主節(jié)點在日志中會記錄每一條 SQL 腳本督禽，優(yōu)點是日志數(shù)據(jù)量小脆霎，性能高；缺點是需要記錄SQL的上下文狈惫，如果主備環(huán)境有差異睛蛛，在備節(jié)點執(zhí)行時會產(chǎn)生BUG。
# 當設(shè)置為 Mixed，主節(jié)點在日志中會根據(jù)執(zhí)行的 SQL 腳本來自動確定使用 Row 或 Statement 模式忆肾。
# binlog-format=Mixed

# 表示節(jié)點實例唯一ID（正整數(shù)）荸频，設(shè)置為 1 表示主節(jié)點，設(shè)置＞1表示備節(jié)點客冈。
server_id=1

# 表示啟用 binlog 的數(shù)據(jù)庫旭从，多個數(shù)據(jù)庫重復設(shè)置此參數(shù)，每個數(shù)據(jù)庫一行场仲；不設(shè)置時表示所有數(shù)據(jù)庫都啟用 binlog 和悦。
# binlog_do_db=binlogdb

# 表示不啟用 binlog 的數(shù)據(jù)庫，多個數(shù)據(jù)庫重復設(shè)置此參數(shù)渠缕，每個數(shù)據(jù)庫一行摹闽；不設(shè)置時表示所有數(shù)據(jù)庫都啟用 binlog 。
# binlog-ignore-db=ignoredb

2褐健、重新啟動數(shù)據(jù)庫服務。

[centos@DB-M ~]$ sudo systemctl restart mysqld.service

3澜汤、創(chuàng)建 Replication 用戶并授予數(shù)據(jù)庫權(quán)限蚜迅，記錄主庫狀態(tài)。

[centos@DB-M ~]$ mysql -u root -p
Enter password:

# 創(chuàng)建 Replication 賬號遠程登錄的策略和口令俊抵。
# 格式：create user '<賬號>'@'<客戶端IP策略>' identified by '<登錄密碼>';
# 說明：
# <客戶端IP策略>可以使用通配符”%”谁不，也可以指定具體IP地址或地址段，如：
# 允許全部遠程地址訪問徽诲，設(shè)置為：”%”;
# 允許IP地址訪問刹帕，設(shè)置為：”192.168.0.1”;
# 允許IP地址段訪問，設(shè)置為：”192.168.0.%”谎替。
# with mysql_native_password 表示設(shè)置的口令可同時用于遠程訪問（不指定時用于本地訪問）偷溺。
mysql> create user 'repl'@'%' identified with mysql_native_password by 'password';

# 為 Replication 賬號授予數(shù)據(jù)庫 Replication 權(quán)限。
mysql> grant replication slave on *.* to 'repl'@'%';

# 刷新數(shù)據(jù)庫表钱贯。
mysql> flush tables with read lock;

# 查看主節(jié)點狀態(tài)挫掏。
mysql>show master status\G;

# 響應信息。其中 "File" 和 "Position" 字段的數(shù)據(jù)值在配置從庫節(jié)點時需要使用秩命。
*************************** 1. row ***************************
             File: binlog.000001
         Position: 950
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

4尉共、創(chuàng)建數(shù)據(jù)庫歷史快照。如果在搭建主備模式前弃锐，主庫已經(jīng)具有存量的數(shù)據(jù)庫袄友，則需要為主庫建立快照霹菊。并將快照文件上傳到從庫服務器上。（可選步驟）

[centos@DB-M ~]$ mysqldump -u root -p --all-databases --master-data > snapshot.sql
[centos@DB-M ~]$ scp snapshot.sql centos@192.168.0.21:~
[centos@DB-M ~]$ scp snapshot.sql centos@192.168.0.22:~

3.2.3.從庫節(jié)點配置

以"從庫節(jié)點1"節(jié)點為例：

1待诅、導入主庫歷史快照募书。如果在搭建主備模塊前，如果主庫已經(jīng)具有存量的數(shù)據(jù)庫。那從庫需要將主庫的快照文件導入启泣，以保持當前狀態(tài)與主庫完全一致矾麻。（可選步驟）

[centos@DB-S1 ~]$ mysql -u root -p < snapshot.sql
Enter password:

2、設(shè)置從庫配置文件參數(shù)限次。

使用文本編輯器打開配置文件：

[centos@DB-S1 ~]$ sudo gedit /etc/my.cnf

追加或驗證文件中的以下參數(shù)并保存：

[mysqld]

# 表示節(jié)點實例唯一ID（正整數(shù)）赠群，設(shè)置＞1表示備節(jié)點柏卤，必須設(shè)置＞1的數(shù)值敌蚜。
# 各個備節(jié)點的標識必須保持唯一性，不能重復。
server_id=2

# 表示開啟超級用戶（如 root 用戶）的數(shù)據(jù)庫只讀模式贫奠。
super_read_only=on

# 表示開啟普通用戶的數(shù)據(jù)庫只讀模式唬血。
read_only=on

3、重新啟動數(shù)據(jù)庫服務唤崭。

[centos@DB-S1 ~]$ sudo systemctl restart mysqld.service

4拷恨、配置從庫同步參數(shù)，并啟動主備同步浩姥。

[centos@DB-M ~]$ mysql -u root -p
Enter password:

# master_host：主節(jié)點IP地址；
# master_port：主節(jié)點服務端口號状您；
# master_user：主節(jié)點創(chuàng)建的具有 Replication 權(quán)限的用戶賬號勒叠；
# master_password：主節(jié)點創(chuàng)建的具有 Replication 權(quán)限的用戶口令；
# master_log_file：主節(jié)點創(chuàng)建的 binlog 日志文件名膏孟；
# master_log_pos：主節(jié)點創(chuàng)建的 binlog 日志文件位置表示眯分。
mysql>change master to master_host='192.168.0.20',master_port=3306,master_user='repl',master_password='password',master_log_file='binlog.000001',master_log_pos=950;

# 啟動從庫同步。
mysql>start slave;

# 查看從庫狀態(tài)柒桑。
mysql>show slave status\G;

# 響應信息弊决。正常情況下 "Slave_IO_Running" 和 "Slave_SQL_Running" 都應為 Yes 。
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.20
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000001
          Read_Master_Log_Pos: 950
               Relay_Log_File: Slave-1-relay-bin.000006
                Relay_Log_Pos: 322
        Relay_Master_Log_File: binlog.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
......

注意：其他"從庫"節(jié)點上全部需要按照以上步驟配置魁淳。

5飘诗、如果主庫的參數(shù)發(fā)生變化或者從庫的同步配置錯誤，從庫重設(shè)同步參數(shù)方法如下：

[centos@DB-S1 ~]$ mysql -u root -p
Enter password:

# 停止從庫界逛。
mysql>stop slave;

# 修改參數(shù)昆稿。
mysql>change master to master_log_file='binlog.000001',master_log_pos=950;

# 重新啟動從庫。
mysql>start slave;

# 查看從庫狀態(tài)息拜。
mysql>show slave status\G;

6溉潭、從庫的主備模式管理：

[centos@DB-S1 ~]$ mysql -u root -p
Enter password:

# 停止從庫净响。
mysql>stop slave;

# 修改主庫參數(shù)。
mysql>change master to master_log_file='binlog.000001',master_log_pos=950;

# 啟動從庫喳瓣。
mysql>start slave;

# 重置從庫狀態(tài)馋贤。
mysql>reset slave;

# 查看從庫狀態(tài)。
mysql>show slave status\G;
最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
  • 人面猴
    序言：七十年代末畏陕，一起剝皮案震驚了整個濱河市配乓，隨后出現(xiàn)的幾起案子，更是在濱河造成了極大的恐慌蹭秋，老刑警劉巖扰付，帶你破解...
    沈念sama閱讀 217,277評論 6贊 503
  • 死咒
    序言：濱河連續(xù)發(fā)生了三起死亡事件，死亡現(xiàn)場離奇詭異仁讨，居然都是意外死亡羽莺，警方通過查閱死者的電腦和手機，發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 92,689評論 3贊 393
  • 救了他兩次的神仙讓他今天三更去死
    文/潘曉璐 我一進店門洞豁，熙熙樓的掌柜王于貴愁眉苦臉地迎上來盐固，“玉大人，你說我怎么就攤上這事丈挟〉蟛罚” “怎么了？”我有些...
    開封第一講書人閱讀 163,624評論 0贊 353
  • 道士緝兇錄：失蹤的賣姜人
    文/不壞的土叔 我叫張陵曙咽，是天一觀的道長蛔趴。 經(jīng)常有香客問我，道長例朱，這世上最難降的妖魔是什么孝情？ 我笑而不...
    開封第一講書人閱讀 58,356評論 1贊 293
  • ?港島之戀（遺憾婚禮）
    正文 為了忘掉前任，我火速辦了婚禮洒嗤，結(jié)果婚禮上箫荡，老公的妹妹穿的比我還像新娘。我一直安慰自己渔隶，他們只是感情好羔挡，可當我...
    茶點故事閱讀 67,402評論 6贊 392
  • 惡毒庶女頂嫁案：這布局不是一般人想出來的
    文/花漫 我一把揭開白布。 她就那樣靜靜地躺著间唉，像睡著了一般绞灼。 火紅的嫁衣襯著肌膚如雪。 梳的紋絲不亂的頭發(fā)上呈野，一...
    開封第一講書人閱讀 51,292評論 1贊 301
  • 城市分裂傳說
    那天镀赌，我揣著相機與錄音，去河邊找鬼际跪。 笑死商佛，一個胖子當著我的面吹牛喉钢，可吹牛的內(nèi)容都是我干的。 我是一名探鬼主播良姆，決...
    沈念sama閱讀 40,135評論 3贊 418
  • 雙鴛鴦連環(huán)套：你想象不到人心有多黑
    文/蒼蘭香墨 我猛地睜開眼肠虽，長吁一口氣：“原來是場噩夢啊……” “哼！你這毒婦竟也來了玛追？” 一聲冷哼從身側(cè)響起税课，我...
    開封第一講書人閱讀 38,992評論 0贊 275
  • 萬榮殺人案實錄
    序言：老撾萬榮一對情侶失蹤，失蹤者是張志新（化名）和其女友劉穎痊剖，沒想到半個月后韩玩，有當?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體，經(jīng)...
    沈念sama閱讀 45,429評論 1贊 314
  • ?護林員之死
    正文 獨居荒郊野嶺守林人離奇死亡陆馁，尸身上長有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點故事閱讀 37,636評論 3贊 334
  • ?白月光啟示錄
    正文 我和宋清朗相戀三年找颓，在試婚紗的時候發(fā)現(xiàn)自己被綠了。 大學時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片叮贩。...
    茶點故事閱讀 39,785評論 1贊 348
  • 活死人
    序言：一個原本活蹦亂跳的男人離奇死亡击狮，死狀恐怖，靈堂內(nèi)的尸體忽然破棺而出益老，到底是詐尸還是另有隱情彪蓬，我是刑警寧澤，帶...
    沈念sama閱讀 35,492評論 5贊 345
  • ?日本核電站爆炸內(nèi)幕
    正文 年R本政府宣布捺萌，位于F島的核電站档冬，受9級特大地震影響，放射性物質(zhì)發(fā)生泄漏桃纯。R本人自食惡果不足惜酷誓，卻給世界環(huán)境...
    茶點故事閱讀 41,092評論 3贊 328
  • 男人毒藥：我在死后第九天來索命
    文/蒙蒙 一、第九天 我趴在偏房一處隱蔽的房頂上張望慈参。 院中可真熱鬧呛牲，春花似錦刮萌、人聲如沸驮配。這莊子的主人今日做“春日...
    開封第一講書人閱讀 31,723評論 0贊 22
  • 一樁弒父案着茸，背后竟有這般陰謀
    文/蒼蘭香墨 我抬頭看了看天上的太陽壮锻。三九已至，卻和暖如春涮阔，著一層夾襖步出監(jiān)牢的瞬間猜绣，已是汗流浹背。 一陣腳步聲響...
    開封第一講書人閱讀 32,858評論 1贊 269
  • 情欲美人皮
    我被黑心中介騙來泰國打工敬特， 沒想到剛下飛機就差點兒被人妖公主榨干…… 1. 我叫王不留掰邢，地道東北人牺陶。 一個月前我還...
    沈念sama閱讀 47,891評論 2贊 370
  • 代替公主和親
    正文 我出身青樓，卻偏偏與公主長得像辣之，于是被迫代替她去往敵國和親掰伸。 傳聞我的和親對象是個殘疾皇子，可洞房花燭夜當晚...
    茶點故事閱讀 44,713評論 2贊 354