Mysql數(shù)據(jù)庫的主從復(fù)制與讀寫分離怎么實(shí)現(xiàn)

這篇文章主要介紹“Mysql數(shù)據(jù)庫的主從復(fù)制與讀寫分離怎么實(shí)現(xiàn)”，在日常操作中，相信很多人在Mysql數(shù)據(jù)庫的主從復(fù)制與讀寫分離怎么實(shí)現(xiàn)問題上存在疑惑，小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”Mysql數(shù)據(jù)庫的主從復(fù)制與讀寫分離怎么實(shí)現(xiàn)”的疑惑有所幫助！接下來，請跟著小編一起來學(xué)習(xí)吧！

前言

在實(shí)際的生產(chǎn)環(huán)境中，如果對MySQL數(shù)據(jù)庫的讀和寫都在一臺數(shù)據(jù)庫服務(wù)中操作，無論在安全性、高可用性，還是高并發(fā)性等各個方面都是完全不能滿足實(shí)際需求的，一般來說都是通過主從復(fù)制（Master-Slave)的方式來同步數(shù)據(jù)，再通過讀寫分離來提升數(shù)據(jù)庫的并發(fā)負(fù)載能力這樣的方案進(jìn)行部署與實(shí)施

一、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二進(jìn)制日志

slave中繼日志

3.請求方式

I/O線程

dump線程

SQL線程

4.主從復(fù)制的原理

①M(fèi)aster節(jié)點(diǎn)將數(shù)據(jù)的改變記錄成二進(jìn)制日志（bin log），當(dāng)Master上的數(shù)據(jù)發(fā)生改變時，則將其改變寫入二進(jìn)制日志中

②Slave節(jié)點(diǎn)會在一定時間間隔內(nèi)對Master的二進(jìn)制日志進(jìn)行探測其是否發(fā)生改變

如果發(fā)生改變，則開始一個I/O線程請求 Master的二進(jìn)制事件

③同時Master節(jié)點(diǎn)為每個I/O線程啟動一個dump線程，用于向其發(fā)送二進(jìn)制事件，并保存至Slave節(jié)點(diǎn)本地的中繼日志（Relay log）中

④Slave節(jié)點(diǎn)將啟動SQL線程從中繼日志中讀取二進(jìn)制日志，在本地重放，即解析成 sql 語句逐一執(zhí)行，使得其數(shù)據(jù)和 Master節(jié)點(diǎn)的保持一致，最后I/O線程和SQL線程將進(jìn)入睡眠狀態(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è)是單機(jī)，讀寫都在一臺MySQL上面完成，性能肯定不高。如果有三臺MySQL，一臺mater只負(fù)責(zé)寫操作，兩臺salve只負(fù)責(zé)讀操作，性能不就能大大提高了嗎？

所以主從復(fù)制、讀寫分離就是為了數(shù)據(jù)庫能支持更大的并發(fā)

隨著業(yè)務(wù)量的擴(kuò)展、如果是單機(jī)部署的MySQL，會導(dǎo)致I/O頻率過高。采用主從復(fù)制、讀寫分離可以提高數(shù)據(jù)庫的可用性

7.用途及條件

mysql主從復(fù)制用途：

實(shí)時災(zāi)備，用于故障切換

讀寫分離，提供查詢服務(wù)

備份，避免影響服務(wù)

必要條件：

主庫開啟binlog日志（設(shè)置log-bin參數(shù)）

主從server-id不同

從庫服務(wù)器能連通主庫

8.mysql主從復(fù)制存在的問題

主庫宕機(jī)后，數(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)存中完成，減少磁盤操作

• 從庫使用高性能主機(jī)，包括cpu強(qiáng)悍、內(nèi)存加大。避免使用虛擬云主機(jī)，使用物理主機(jī)，這樣提升了i/o方面性

• 從庫使用SSD磁盤

• 網(wǎng)絡(luò)優(yōu)化，避免跨機(jī)房實(shí)現(xiàn)同步

二、主從復(fù)制的形式

三、讀寫分離

1.原理

①只在主服務(wù)器上寫，只在從服務(wù)器上讀

②主數(shù)據(jù)庫處理事務(wù)性查詢，從數(shù)據(jù)庫處理select查詢

③數(shù)據(jù)庫復(fù)制用于將事務(wù)性查詢導(dǎo)致的變更同步到集群中的從數(shù)據(jù)庫

2.為什么要讀寫分離呢？

因?yàn)閿?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ù)制與讀寫分離 在實(shí)際的生產(chǎn)環(huán)境中，對數(shù)據(jù)庫的讀和寫都在同一個數(shù)據(jù)庫服務(wù)器中，是不能滿足實(shí)際需求的

5.目前較為常見的MySQL讀寫分離

分為以下兩種

①基于程序代碼內(nèi)部實(shí)現(xiàn)

在代碼中根據(jù) select、insert 進(jìn)行路由分類，這類方法也是目前生產(chǎn)環(huán)境應(yīng)用最廣泛的優(yōu)點(diǎn)是性能較好，因?yàn)樵诔绦虼a中實(shí)現(xiàn)，不需要增加額外的設(shè)備為硬件開支；缺點(diǎn)是需要開發(fā)人員來實(shí)現(xiàn)，運(yùn)維人員無從下手但是并不是所有的應(yīng)用都適合在程序代碼中實(shí)現(xiàn)讀寫分離，像一些大型復(fù)雜的Java應(yīng)用，如果在程序代碼中實(shí)現(xiàn)讀寫分離對代碼改動就較大

②基于中間代理層實(shí)現(xiàn)

代理一般位于客戶端和服務(wù)器之間，代理服務(wù)器接到客戶端請求后通過判斷后轉(zhuǎn)發(fā)到后端數(shù)據(jù)庫，有以下代表性程序
（1）MySQL-Proxy，MySQL-Proxy 為 MySQL 開源項(xiàng)目，通過其自帶的 lua 腳本進(jìn)行SQL 判斷。
（2）Atlas，是由奇虎360的Web平臺部基礎(chǔ)架構(gòu)團(tuán)隊(duì)開發(fā)維護(hù)的一個基于MySQL協(xié)議的數(shù)據(jù)中間層項(xiàng)目，它是在mysql-proxy 0.8.2版本的基礎(chǔ)上，對其進(jìn)行了優(yōu)化，增加了一些新的功能特性。360內(nèi)部使用Atlas運(yùn)行的mysql業(yè)務(wù)，每天承載的讀寫請求數(shù)達(dá)幾十億條。支持事物以及存儲過程
（3）Amoeba，由陳思儒開發(fā)，作者曾就職于阿里巴巴。該程序由Java語言進(jìn)行開發(fā)，阿里巴巴將其用于生產(chǎn)環(huán)境。但是它不支持事務(wù)和存儲過程。

由于使用MySQL Proxy 需要寫大量的Lua腳本，這些Lua并不是現(xiàn)成的，而是需要自己去寫，這對于并不熟悉MySQL Proxy 內(nèi)置變量和MySQL Protocol 的人來說是非常困難的Amoeba是一個非常容易使用、可移植性非常強(qiáng)的軟件。因此它在生產(chǎn)環(huán)境中被廣泛應(yīng)用于數(shù)據(jù)庫的代理層

四、案例實(shí)施

1.案例環(huán)境

本案例環(huán)境使用舞臺服務(wù)器磨你搭建，拓?fù)鋱D如下

2.實(shí)驗(yàn)思路（解決需求）

客戶端訪問代理服務(wù)器

代理服務(wù)器寫入到主服務(wù)器

主服務(wù)器將增刪改寫入自己二進(jìn)制日志

從服務(wù)器將主服務(wù)器的二進(jìn)制日志同步至自己中繼日志

從服務(wù)器重放中繼日志到數(shù)據(jù)庫中

客戶端讀，則代理服務(wù)器直接訪問從服務(wù)器

降低負(fù)載，起到負(fù)載均衡作用

3.準(zhǔn)備

• 除了客戶端，都需要先源碼編譯安裝好MySQL

• 都需關(guān)閉防火墻及控制訪問機(jī)制

systemctl stop firewalld
systemctl disable firewalld
#關(guān)閉防火墻（及開機(jī)禁用）
setenforce 0
#關(guān)閉安全訪問控制機(jī)制

4.搭建MySQL主從復(fù)制

①M(fèi)ysql主從服務(wù)器時間同步

主服務(wù)器設(shè)置

#安裝 NTP
yum -y install ntp
#配置 NTP
vim /etc/ntp.conf
#末行添加以下內(nèi)容
server 127.127.126.0
fudge 127.127.126.0 stratum 8
#設(shè)置本地是時鐘源，注意修改網(wǎng)段
#設(shè)置時間層級為8（限制在15內(nèi)）
#重啟服務(wù)
service ntpd restart

從服務(wù)器設(shè)置

yum -y install ntp ntpdate
#安裝服務(wù)，ntpdate用于同步時間
service ntpd start
#開啟服務(wù)
/usr/sbin/ntpdate 192.168.126.11
#進(jìn)行時間同步，指向Master服務(wù)器IP
crontab -e
#寫入計(jì)劃性任務(wù)，每半小時進(jìn)行一次時間同步
*/30 * * * * /usr/sbin/ntpdate 192.168.126.11

②配置MySQL Master主服務(wù)器

vim /etc/my.cnf
#配置以下內(nèi)容
server-id = 1
log-bin=master-bin
#添加，主服務(wù)器開啟二進(jìn)制日志
log-slave-updates=true
#添加，允許從服務(wù)器更新二進(jìn)制日志

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 列顯示日志名，F(xiàn)osition 列顯示偏移量

③配置從服務(wù)器

vim /etc/my.cnf
server-id = 2
#修改，注意id與Master的不同，兩個Slave的id也要不同
relay-log=relay-log-bin
#添加，開啟中繼日志，從主服務(wù)器上同步日志文件記錄到本地
relay-log-index=slave-relay-bin.index
#添加，定義中繼日志文件的位置和名稱

systemctl restart mysqld
mysql -uroot -p123123
change 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é)與主機(jī)的io通信
Slave_SQL_Running: Yes
#負(fù)責(zé)自己的slave mysql進(jìn)程

slave1：

slave2: 和slave1配置一樣，id不能相同

一般 Slave_IO_Running: No 有這幾種可能性：

• 網(wǎng)絡(luò)不通

• my.cnf 配置有問題

• 密碼、file 文件名、pos 偏移量不對

• 防火墻沒有關(guān)閉

④驗(yà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ù)切片的功能

①在主機(jī)Amoeba上安裝Java環(huán)境 因?yàn)?Amoeba 基于是 jdk1.5 開發(fā)的，所以官方推薦使用 jdk1.5 或 1.6 版本，高版本不建議使用

cd /opt/
#在FinalShell中，把軟件包拖進(jìn)來
amoeba-mysql-binary-2.2.0.tar.gz
jdk-6u14-linux-x64.bin
cp 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.6
export CLASSPATH=$CLASSPATH:$JAVA_HOME/lib:$JAVA_HOME/jre/lib
export PATH=$JAVA_HOME/lib:$JAVA_HOME/jre/bin/:$PATH:$HOME/bin
export AMOEBA_HOME=/usr/local/amoeba
export PATH=$PATH:$AMOEBA_HOME/bin

#輸出定義Java的工作目錄
#輸出指定的java類型
#將java加入路徑環(huán)境變量
#輸出定義amoeba工作目錄
#加入路徑環(huán)境變量

source /etc/profile
#執(zhí)行修改后的全局配置文件
java -version
#查看java版本信息以檢查是否安裝成功

②安裝并配置Amoeba

mkdir /usr/local/amoeba
tar 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訪問

④在主機(jī)amoeba中編輯amoeba.xml配置文件

cd /usr/local/amoeba/conf/
cp amoeba.xml amoeba.xml.bak
vim 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 配置文件

cp dbServers.xml dbServers.xml.bak
vim dbServers.xml
#修改數(shù)據(jù)庫配置文件

#23行注釋掉
作用：默認(rèn)進(jì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

/usr/local/amoeba/bin/amoeba start&
#啟動Amoeba軟件，按ctrl+c 返回
netstat -anpt | grep java
#查看8066端口是否開啟，默認(rèn)端口為TCP 8066

⑦測試

1.前往客戶端快速裝好 MySQL 虛擬客戶端，然后通過代理訪問 MySQL

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ù)器上

use club;
create table puxin (id int(10),name varchar(10),address varchar(20));

3.然后關(guān)閉從服務(wù)器的 Slave 功能，從主服務(wù)器上同步了表，手動插入數(shù)據(jù)內(nèi)容

stop slave;
#關(guān)閉同步
use club;
insert into puxin values('1','wangyi','this_is_slave1');
#slave2
insert into puxin values('2','wanger','this_is_slave2');

4.再回到主服務(wù)器上插入其他內(nèi)容

insert into pucin values('3','wangwu','this_is_master');

5.測試讀操作，前往客戶端主機(jī)查詢結(jié)果

use club;
select * from puxin;

6.在客戶端上插入一條語句，但是在客戶端上查詢不到，最終只有在 Master 上才上查看到這條語句內(nèi)容，說明寫操作在 Master 服務(wù)器上

insert into puxin values('4','liuliu','this_is_client');

7.再在兩個從服務(wù)器上執(zhí)行 start slave; 即可實(shí)現(xiàn)同步在主服務(wù)器上添加的數(shù)據(jù)

總結(jié)

由此驗(yàn)證，已經(jīng)實(shí)現(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，查看主從信息進(jìn)行查看，里面有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ù)器通信

• 再者查看防火墻和核心防護(hù)是否關(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ò)延遲

到此，關(guān)于“Mysql數(shù)據(jù)庫的主從復(fù)制與讀寫分離怎么實(shí)現(xiàn)”的學(xué)習(xí)就結(jié)束了，希望能夠解決大家的疑惑。理論與實(shí)踐的搭配能更好的幫助大家學(xué)習(xí)，快去試試吧！若想繼續(xù)學(xué)習(xí)更多相關(guān)知識，請繼續(xù)關(guān)注億速云網(wǎng)站，小編會繼續(xù)努力為大家?guī)砀鄬?shí)用的文章！