前言
在實際的生產(chǎn)環(huán)境中，如果對mysql數(shù)據(jù)庫的讀和寫都在一臺數(shù)據(jù)庫服務(wù)中操作，無論在安全性、高可用性，還是高并發(fā)性等各個方面都是完全不能滿足實際需求的，一般來說都是通過主從復(fù)制（master-slave)的方式來同步數(shù)據(jù)，再通過讀寫分離來提升數(shù)據(jù)庫的并發(fā)負(fù)載能力這樣的方案進行部署與實施
一、mysql主從復(fù)制
1.支持的復(fù)制類型
基于語句的復(fù)制（statement）:在服務(wù)器上執(zhí)行sql語句，在從服務(wù)器上執(zhí)行同樣的語句，mysql默認(rèn)采用基于語句的復(fù)制，執(zhí)行效率高基于行的復(fù)制（row): 把改變的內(nèi)容復(fù)制過去，而不是把命令在從服務(wù)器上執(zhí)行一遍混合類型的復(fù)制(mixed): 在服務(wù)器上執(zhí)行sql語句，在從服務(wù)器上執(zhí)行同樣的語句，mysql默認(rèn)采用基于語句的復(fù)制，執(zhí)行效率高
2.主從復(fù)制的工作過程是基于日志
master二進制日志
slave中繼日志
3.請求方式
i/o線程
dump線程
sql線程
4.主從復(fù)制的原理
①master節(jié)點將數(shù)據(jù)的改變記錄成二進制日志（bin log），當(dāng)master上的數(shù)據(jù)發(fā)生改變時，則將其改變寫入二進制日志中
②slave節(jié)點會在一定時間間隔內(nèi)對master的二進制日志進行探測其是否發(fā)生改變
如果發(fā)生改變，則開始一個i/o線程請求 master的二進制事件
③同時master節(jié)點為每個i/o線程啟動一個dump線程，用于向其發(fā)送二進制事件，并保存至slave節(jié)點本地的中繼日志（relay log）中
④slave節(jié)點將啟動sql線程從中繼日志中讀取二進制日志，在本地重放，即解析成 sql 語句逐一執(zhí)行，使得其數(shù)據(jù)和 master節(jié)點的保持一致，最后i/o線程和sql線程將進入睡眠狀態(tài)，等待下一次被喚醒
復(fù)制過程有一個很重要的限制，即復(fù)制在 slave 上是串行化的，也就是說 master 上的并行更新操作不能在 slave 上并行操作
中繼日志通常會位于os緩存中，所以中繼日志的開銷很小
5.mysql集群和主從復(fù)制分別適合在什么場景下使用
集群和主從復(fù)制是為了應(yīng)對高并發(fā)、大訪問量的情況,如果網(wǎng)站訪問量和并發(fā)量太大了，少量的數(shù)據(jù)庫服務(wù)器是處理不過來的，會造成網(wǎng)站訪問慢,數(shù)據(jù)寫入會造成數(shù)據(jù)表或記錄被鎖住，鎖住的意思就是其他訪問線程暫時不能讀寫要等寫入完成才能繼續(xù)，這樣會影響其他用戶讀取速度,采用主從復(fù)制可以讓一些服務(wù)器專門讀，一些專門寫可以解決這個問題
6.為什么使用主從復(fù)制、讀寫分離
主從復(fù)制、讀寫分離一般是一起使用的，目的很簡單，就是為了提高數(shù)據(jù)庫的并發(fā)性能。你想，假設(shè)是單機，讀寫都在一臺mysql上面完成，性能肯定不高。如果有三臺mysql，一臺mater只負(fù)責(zé)寫操作，兩臺salve只負(fù)責(zé)讀操作，性能不就能大大提高了嗎？
所以主從復(fù)制、讀寫分離就是為了數(shù)據(jù)庫能支持更大的并發(fā)
隨著業(yè)務(wù)量的擴展、如果是單機部署的mysql，會導(dǎo)致i/o頻率過高。采用主從復(fù)制、讀寫分離可以提高數(shù)據(jù)庫的可用性
7.用途及條件
mysql主從復(fù)制用途：
實時災(zāi)備，用于故障切換
讀寫分離，提供查詢服務(wù)
備份，避免影響服務(wù)
必要條件：
主庫開啟binlog日志（設(shè)置log-bin參數(shù)）
主從server-id不同
從庫服務(wù)器能連通主庫
8.mysql主從復(fù)制存在的問題
主庫宕機后，數(shù)據(jù)可能丟失
從庫只有一個sql thread,主庫寫壓力大，復(fù)制很可能延時
解決辦法
半同步復(fù)制——解決數(shù)據(jù)丟失的問題
并行復(fù)制——解決從庫復(fù)制延遲的問題
9.mysql主從復(fù)制延遲
①master服務(wù)器高并發(fā)，形成大量事務(wù)
②網(wǎng)絡(luò)延遲
③主從硬件設(shè)備導(dǎo)致——cpu主頻、內(nèi)存io、硬盤io
④本來就不是同步復(fù)制、而是異步復(fù)制
- 從庫優(yōu)化mysql參數(shù)。比如增大innodb_buffer_pool_size，讓更多操作在mysql內(nèi)存中完成，減少磁盤操作
- 從庫使用高性能主機，包括cpu強悍、內(nèi)存加大。避免使用虛擬云主機，使用物理主機，這樣提升了i/o方面性
- 從庫使用ssd磁盤
- 網(wǎng)絡(luò)優(yōu)化，避免跨機房實現(xiàn)同步
二、主從復(fù)制的形式
三、讀寫分離
1.原理
①只在主服務(wù)器上寫，只在從服務(wù)器上讀
②主數(shù)據(jù)庫處理事務(wù)性查詢，從數(shù)據(jù)庫處理select查詢
③數(shù)據(jù)庫復(fù)制用于將事務(wù)性查詢導(dǎo)致的變更同步到集群中的從數(shù)據(jù)庫
2.為什么要讀寫分離呢？
因為數(shù)據(jù)庫的“寫”（寫10000條數(shù)據(jù)可能要3分鐘）操作是比較耗時的但是數(shù)據(jù)庫的“讀”（讀10000條數(shù)據(jù)可能只要5秒鐘）所以讀寫分離，解決的是，數(shù)據(jù)庫的寫入，影響了查詢的效率
3.什么時候要讀寫分離？
數(shù)據(jù)庫不一定要讀寫分離，如果程序使用數(shù)據(jù)庫較多時，而更新少，查詢多的情況下會考慮使用利用數(shù)據(jù)庫主從同步，再通過讀寫分離可以分擔(dān)數(shù)據(jù)庫壓力，提高性能 4.主從復(fù)制與讀寫分離 在實際的生產(chǎn)環(huán)境中，對數(shù)據(jù)庫的讀和寫都在同一個數(shù)據(jù)庫服務(wù)器中，是不能滿足實際需求的
5.目前較為常見的mysql讀寫分離
分為以下兩種
①基于程序代碼內(nèi)部實現(xiàn)
在代碼中根據(jù) select、insert 進行路由分類，這類方法也是目前生產(chǎn)環(huán)境應(yīng)用最廣泛的優(yōu)點是性能較好，因為在程序代碼中實現(xiàn)，不需要增加額外的設(shè)備為硬件開支；缺點是需要開發(fā)人員來實現(xiàn)，運維人員無從下手但是并不是所有的應(yīng)用都適合在程序代碼中實現(xiàn)讀寫分離，像一些大型復(fù)雜的java應(yīng)用，如果在程序代碼中實現(xiàn)讀寫分離對代碼改動就較大
②基于中間代理層實現(xiàn)
代理一般位于客戶端和服務(wù)器之間，代理服務(wù)器接到客戶端請求后通過判斷后轉(zhuǎn)發(fā)到后端數(shù)據(jù)庫，有以下代表性程序
（1）mysql-proxy，mysql-proxy 為 mysql 開源項目，通過其自帶的 lua 腳本進行sql 判斷。
（2）atlas，是由奇虎360的web平臺部基礎(chǔ)架構(gòu)團隊開發(fā)維護的一個基于mysql協(xié)議的數(shù)據(jù)中間層項目，它是在mysql-proxy 0.8.2版本的基礎(chǔ)上，對其進行了優(yōu)化，增加了一些新的功能特性。360內(nèi)部使用atlas運行的mysql業(yè)務(wù)，每天承載的讀寫請求數(shù)達幾十億條。支持事物以及存儲過程
（3）amoeba，由陳思儒開發(fā)，作者曾就職于阿里巴巴。該程序由java語言進行開發(fā)，阿里巴巴將其用于生產(chǎn)環(huán)境。但是它不支持事務(wù)和存儲過程。
由于使用mysql proxy 需要寫大量的lua腳本，這些lua并不是現(xiàn)成的，而是需要自己去寫，這對于并不熟悉mysql proxy 內(nèi)置變量和mysql protocol 的人來說是非常困難的amoeba是一個非常容易使用、可移植性非常強的軟件。因此它在生產(chǎn)環(huán)境中被廣泛應(yīng)用于數(shù)據(jù)庫的代理層
四、案例實施
1.案例環(huán)境
本案例環(huán)境使用舞臺服務(wù)器磨你搭建，拓?fù)鋱D如下
| 主機名 | 主機 | 操作系統(tǒng) | ip 地址 | 主要軟件 |
|---|---|---|---|---|
| centos 7-1 | master | centos 7 | 192.168.126.11 | ntp 、 mysql-boost-5.7.17.tar.gz |
| centos 7-2 | amoeba | centos 7 | 192.168.126.12 | jdk-6u14-linux-x64.bin、amoeba-mysql-binary-2.2.0.tar.gz |
| centos 7-3 | slave1 | centos 7 | 192.168.126.13 | ntp 、ntpdate 、 mysql-boost-5.7.20.tar.gz |
| cengos 7-4 | slave2 | centos 7 | 192.168.126.14 | ntp 、ntpdate 、mysql-boost-5.7.17.tar.gz |
| centos 7-5 | 客戶端 | centos 7-5 | 192.168.126.15 | mysql5.7 |
2.實驗思路（解決需求）
客戶端訪問代理服務(wù)器
代理服務(wù)器寫入到主服務(wù)器
主服務(wù)器將增刪改寫入自己二進制日志
從服務(wù)器將主服務(wù)器的二進制日志同步至自己中繼日志
從服務(wù)器重放中繼日志到數(shù)據(jù)庫中
客戶端讀，則代理服務(wù)器直接訪問從服務(wù)器
降低負(fù)載，起到負(fù)載均衡作用
3.準(zhǔn)備
- 除了客戶端，都需要先源碼編譯安裝好mysql
- 都需關(guān)閉防火墻及控制訪問機制
|
1
2
3
4
5
|
systemctl stop firewalldsystemctl disable firewalld#關(guān)閉防火墻（及開機禁用）setenforce 0#關(guān)閉安全訪問控制機制 |
4.搭建mysql主從復(fù)制
①mysql主從服務(wù)器時間同步
主服務(wù)器設(shè)置
|
1
2
3
4
5
6
7
8
9
10
11
|
#安裝 ntpyum -y install ntp#配置 ntpvim /etc/ntp.conf#末行添加以下內(nèi)容server 127.127.126.0fudge 127.127.126.0 stratum 8#設(shè)置本地是時鐘源，注意修改網(wǎng)段#設(shè)置時間層級為8（限制在15內(nèi)）#重啟服務(wù)service ntpd restart |
從服務(wù)器設(shè)置
|
1
2
3
4
5
6
7
8
9
|
yum -y install ntp ntpdate#安裝服務(wù)，ntpdate用于同步時間service ntpd start#開啟服務(wù)/usr/sbin/ntpdate 192.168.126.11#進行時間同步，指向master服務(wù)器ipcrontab -e#寫入計劃性任務(wù)，每半小時進行一次時間同步*/30 * * * * /usr/sbin/ntpdate 192.168.126.11 |
②配置mysql master主服務(wù)器
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
vim /etc/my.cnf#配置以下內(nèi)容server-id = 1log-bin=master-bin#添加，主服務(wù)器開啟二進制日志log-slave-updates=true#添加，允許從服務(wù)器更新二進制日志systemctl restart mysqld#重啟服務(wù)使配置生效mysql -uroot -p123123#登錄數(shù)據(jù)庫程序，給從服務(wù)器授權(quán)grant replication slave on *.* to 'myslave'@'192.168.126.%' identified by '123123';flush privileges;show master status;quit#file 列顯示日志名，fosition 列顯示偏移量 |
③配置從服務(wù)器
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
vim /etc/my.cnfserver-id = 2#修改，注意id與master的不同，兩個slave的id也要不同relay-log=relay-log-bin#添加，開啟中繼日志，從主服務(wù)器上同步日志文件記錄到本地relay-log-index=slave-relay-bin.index#添加，定義中繼日志文件的位置和名稱systemctl restart mysqldmysql -uroot -p123123change master to master_host='192.168.126.11' , master_user='myslave',master_password='123123',master_log_file='master-bin.000001',master_log_pos=604;#配置同步，注意 master_log_file 和 master_log_pos 的值要與master的一致start slave;#啟動同步，如有報錯執(zhí)行 reset slave;show slave status\g#查看 slave 狀態(tài)//確保 io 和 sql 線程都是 yes，代表同步正常slave_io_running: yes#負(fù)責(zé)與主機的io通信slave_sql_running: yes#負(fù)責(zé)自己的slave mysql進程 |
slave1：
slave2: 和slave1配置一樣，id不能相同
一般 slave_io_running: no 有這幾種可能性：
- 網(wǎng)絡(luò)不通
- my.cnf 配置有問題
- 密碼、file 文件名、pos 偏移量不對
- 防火墻沒有關(guān)閉
④驗證主從復(fù)制效果
5.搭建mysql讀寫分離
- 這個軟件致力于 mysql 的分布式數(shù)據(jù)庫前端代理層，它主要為應(yīng)用層訪問 mysql 時充當(dāng) sql 路由，并具有負(fù)載均衡、高可用性、sql 過濾、讀寫分離、可路由相關(guān)到目標(biāo)數(shù)據(jù)庫、可并發(fā)請求多臺數(shù)據(jù)庫
- 通過 amoeba 能夠完成多數(shù)據(jù)源的高可用、負(fù)載均衡、數(shù)據(jù)切片的功能
①在主機amoeba上安裝java環(huán)境 因為 amoeba 基于是 jdk1.5 開發(fā)的，所以官方推薦使用 jdk1.5 或 1.6 版本，高版本不建議使用
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
|
cd /opt/#在finalshell中，把軟件包拖進來amoeba-mysql-binary-2.2.0.tar.gzjdk-6u14-linux-x64.bincp jdk-6u14-linux-x64.bin /usr/local/cd /usr/local/chmod +x jdk-6u14-linux-x64.bin./jdk-6u14-linux-x64.bin#按住enter鍵不動一直到最下面，有提示輸入yes+回車即可mv jdk1.6.0_14/ /usr/local/jdk1.6#改名vim /etc/profile#編輯全局配置文件，在最后一行添加以下配置export java_home=/usr/local/jdk1.6export classpath=$classpath:$java_home/lib:$java_home/jre/libexport path=$java_home/lib:$java_home/jre/bin/:$path:$home/binexport amoeba_home=/usr/local/amoebaexport path=$path:$amoeba_home/bin#輸出定義java的工作目錄#輸出指定的java類型#將java加入路徑環(huán)境變量#輸出定義amoeba工作目錄#加入路徑環(huán)境變量source /etc/profile#執(zhí)行修改后的全局配置文件java -version#查看java版本信息以檢查是否安裝成功 |
②安裝并配置amoeba
|
1
2
3
4
5
|
mkdir /usr/local/amoebatar zxvf /opt/amoeba-mysql-binary-2.2.0.tar.gz -c /usr/local/amoeba/chmod -r 755 /usr/local/amoeba//usr/local/amoeba/bin/amoeba#如顯示 amoeba start|stop 說明安裝成功 |
③配置amowba讀寫分離，兩個slave讀寫負(fù)載均衡 在master、slave1、slave2的mysql上開放權(quán)限給amoeba訪問
④在主機amoeba中編輯amoeba.xml配置文件
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
cd /usr/local/amoeba/conf/cp amoeba.xml amoeba.xml.bakvim amoeba.xml#修改amoeba配置文件#30行修改<property name="user">amoeba</property>#32行修改<property name="password">123123</property>#115行修改<property name="defaultpool">master</property>#117去掉注釋<property name="writepool">master</property><property name="readpool">slaves</property> |
⑤編輯dbservers.xml 配置文件
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
|
cp dbservers.xml dbservers.xml.bakvim dbservers.xml#修改數(shù)據(jù)庫配置文件#23行注釋掉作用：默認(rèn)進入test庫 以防m(xù)ysql中沒有test庫時，會報錯<!-- <property name="schema">test</property> -->#26行修改<property name="user">test</property>#28-30行去掉注釋<property name="password">123.com</property>#45行修改，設(shè)置主服務(wù)器的名master<dbserver name="master" parent="abstractserver">#48行修改，設(shè)置主服務(wù)器的地址<property name="ipaddress">192.168.126.11</property>#52行修改，設(shè)置從服務(wù)器的名slave1<dbserver name="slave1" parent="abstractserver">#55行修改，設(shè)置從服務(wù)器1的地址<property name="ipaddress">192.168.126.13</property>#58行復(fù)制上面6行粘貼，設(shè)置從服務(wù)器2的名slave2和地址<dbserver name="slave2" parent="abstractserver"><property name="ipaddress">192.168.184.14</property>#65行修改<dbserver name="slaves" virtual="true">#71修改<property name="poolnames">slave1,slave2</property> |
⑥確定配置無誤后，可以啟動 amoeba 軟件，其默認(rèn)端口為 tcp 8066
|
1
2
3
4
|
/usr/local/amoeba/bin/amoeba start&#啟動amoeba軟件，按ctrl+c 返回netstat -anpt | grep java#查看8066端口是否開啟，默認(rèn)端口為tcp 8066 |
⑦測試
1.前往客戶端快速裝好 mysql 虛擬客戶端，然后通過代理訪問 mysql
|
1
2
3
4
5
6
|
yum -y install mysql#用yum快速安裝mysql虛擬客戶端mysql -u amoeba -p123123 -h 192.168.126.12 -p8066#通過代理訪問mysql，ip地址指向amoba#在通過客戶端連接mysql后寫入的數(shù)據(jù)只有主服務(wù)會記錄，然后同步給從服務(wù)器 |
2.在 master 上創(chuàng)建一個表，同步到兩個從服務(wù)器上
|
1
2
|
use club;create table puxin (id int(10),name varchar(10),address varchar(20)); |
3.然后關(guān)閉從服務(wù)器的 slave 功能，從主服務(wù)器上同步了表，手動插入數(shù)據(jù)內(nèi)容
|
1
2
3
4
5
6
|
stop slave;#關(guān)閉同步use club;insert into puxin values('1','wangyi','this_is_slave1');#slave2insert into puxin values('2','wanger','this_is_slave2'); |
4.再回到主服務(wù)器上插入其他內(nèi)容
|
1
|
insert into pucin values('3','wangwu','this_is_master'); |
5.測試讀操作，前往客戶端主機查詢結(jié)果
|
1
2
|
use club;select * from puxin; |
6.在客戶端上插入一條語句，但是在客戶端上查詢不到，最終只有在 master 上才上查看到這條語句內(nèi)容，說明寫操作在 master 服務(wù)器上
|
1
|
insert into puxin values('4','liuliu','this_is_client'); |
7.再在兩個從服務(wù)器上執(zhí)行 start slave; 即可實現(xiàn)同步在主服務(wù)器上添加的數(shù)據(jù)
總結(jié)
由此驗證，已經(jīng)實現(xiàn)了 mysql 讀寫分離，目前所有的寫操作全部在 master 主服務(wù)器上，用來避免數(shù)據(jù)的不同步
而所有的讀操作都分?jǐn)偨o了 slave 從服務(wù)器，用來分擔(dān)數(shù)據(jù)庫的壓力
1.如何查看主從同步狀態(tài)是否成功
在從服務(wù)器內(nèi)輸入命令 show slave status\g，查看主從信息進行查看，里面有io線程的狀態(tài)信息，還有master服務(wù)器的ip地址、端口、事務(wù)開始號
當(dāng) slave_io_running 和 slave_sql_running 都顯示為yes時，表示主從同步狀態(tài)成功
2.如果i/o和sql不是yes呢,你是如何排查的
- 首先排除網(wǎng)絡(luò)問題，使用ping命令查看從服務(wù)是否能與主服務(wù)器通信
- 再者查看防火墻和核心防護是否關(guān)閉
- 接著查看從服務(wù)器內(nèi)的slave是否開啟
- 兩個從服務(wù)器的 server-id 是否相同導(dǎo)致只能連上一臺
- master_log_file 和 master_log_pos 的值要是否與master查詢的一致
3.show slave status能看到哪些信息(比較重要的)
- io線程的狀態(tài)信息
- master服務(wù)器的ip地址、端口、事務(wù)開始位置
- 最近一次的報錯信息和報錯位置等
4.主從復(fù)制慢(延遲)有哪些可能
- 主服務(wù)器的負(fù)載過大，被多個睡眠或者僵尸線程占用，導(dǎo)致系統(tǒng)負(fù)載過大
- 從庫硬件比主庫差，導(dǎo)致復(fù)制延遲
- 主從復(fù)制單線程，如果主庫寫并發(fā)太大，來不及傳送到從庫，就會導(dǎo)致延遲
- 慢sql語句過多
- 網(wǎng)絡(luò)延遲
以上就是mysql數(shù)據(jù)庫的主從復(fù)制與讀寫分離精講教程的詳細(xì)內(nèi)容，更多關(guān)于mysql數(shù)據(jù)庫主從復(fù)制與讀寫分離的資料請關(guān)注服務(wù)器之家其它相關(guān)文章！
原文鏈接：https://blog.csdn.net/weixin_53560205/article/details/121073207
