重慶分公司
重慶分公司，新征程啟航
為企業(yè)提供網(wǎng)站建設(shè)、域名注冊(cè)、服務(wù)器等服務(wù)
MySQL主從復(fù)制與讀寫分離詳解
系統(tǒng)運(yùn)維
在實(shí)際生產(chǎn)環(huán)境中，如果對(duì)數(shù)據(jù)庫的讀、寫都在同一個(gè)數(shù)據(jù)庫服務(wù)器中操作，無論是在安全性、高可用性還是高并發(fā)等各個(gè)方面都是完全不能滿足實(shí)際需求的。那么在實(shí)際生產(chǎn)環(huán)境中是如何解決這個(gè)問題？這就需要提到兩個(gè)對(duì)于mysql數(shù)據(jù)庫特別重要的技術(shù)——主從復(fù)制和讀寫分離。
實(shí)際環(huán)境中，一臺(tái)Mysql主服務(wù)器帶兩臺(tái)MYSQL從服務(wù)器做數(shù)據(jù)復(fù)制，前端應(yīng)用在進(jìn)行數(shù)據(jù)庫寫操作時(shí)，對(duì)主服務(wù)器進(jìn)行操作；在進(jìn)行數(shù)據(jù)庫讀操作時(shí)，對(duì)兩臺(tái)從服務(wù)器進(jìn)行操作，這樣大大減輕了主服務(wù)器的壓力。如圖所示：
一、MySQL主從復(fù)制原理MySQL的主從復(fù)制和MySQL的讀寫分離兩者有必然的聯(lián)系，首先要部署主從復(fù)制，只有主從復(fù)制完成了，才能在此基礎(chǔ)上進(jìn)行數(shù)據(jù)的讀寫分離
1）MySQL支持的復(fù)制類型（1）基于語句的復(fù)制：在主服務(wù)器上執(zhí)行的SQL語句，在從服務(wù)器上執(zhí)行同樣的語句。MySQL默認(rèn)采用基于語句的復(fù)制，效率比較高。但是必須開啟二進(jìn)制日志功能；
（2）基于行的復(fù)制：把改變的內(nèi)容復(fù)制過去，而不是把命令在從服務(wù)器上執(zhí)行一遍；
（3）混合類型的復(fù)制：默認(rèn)采用基于語句的復(fù)制，一旦發(fā)現(xiàn)基于語句無法精確復(fù)制時(shí)，就會(huì)采用基于行的復(fù)制；
MySQL復(fù)制的工作過程如圖所示：
MySQL復(fù)制的工作過程詳細(xì)介紹：
1.在每個(gè)事物更新數(shù)據(jù)完成之前，Master在二進(jìn)制日志記錄這些變化。寫入二進(jìn)制日志完成后，Master通知存儲(chǔ)引擎提交事務(wù)；
2.Slave將Master的Binary log（二進(jìn)制日志）復(fù)制到其Relay log（中繼日志）。首先Slave開始一個(gè)工作進(jìn)程——I/O線程，I/O線程在Master上打開一個(gè)普通的連接，然后開始Binlog dump process（二進(jìn)制日志轉(zhuǎn)儲(chǔ)過程）。Binlog dump process從Master的二進(jìn)制日志中讀取事件，如果已經(jīng)跟上Master，它就會(huì)睡眠并等待Master產(chǎn)生新的事件。I/O線程將這些時(shí)間寫入中繼日志；
3.SQL slave thread（SQL從線程）處理該過程的最后一步。SQL線程從中繼日志中讀取事件，并重放其中的事件而更新Slave的數(shù)據(jù)，使其與Master中的數(shù)據(jù)一致，只要該線程與I/O線程保持一致，中繼日志通常會(huì)位于OS的緩存中，所以中繼日志的開銷很小；
復(fù)制過程有一個(gè)很重要的限制，即復(fù)制在Slave上是串行化的，也就是說Master上的并行更新操作不能在Slave上并行操作。
二、MySQL讀寫分離原理簡(jiǎn)單來說，讀寫分離就是只在主服務(wù)器上寫、只在從服務(wù)器上讀?；镜脑硎亲屩鲾?shù)據(jù)庫處理事務(wù)性查詢，而從數(shù)據(jù)庫處理select查詢。數(shù)據(jù)庫復(fù)制被用來把事務(wù)性查詢導(dǎo)致的變更同步到群集中的從數(shù)據(jù)庫。如圖所示：
目前常見的MySQL讀寫分離分為兩種：
在代碼中根據(jù)select、insert進(jìn)行路由分類，這類方法也是目前生產(chǎn)環(huán)境應(yīng)用最廣泛的。優(yōu)點(diǎn)是性能較好，因?yàn)樵诔绦虼a中實(shí)現(xiàn)，不需要增加額外的設(shè)備作為硬件開發(fā)，缺點(diǎn)是需要開發(fā)人員來實(shí)現(xiàn)，運(yùn)維人員無從下手；
2）基于中間代理層實(shí)現(xiàn)代理一般位于客戶端和服務(wù)器之間，代理服務(wù)器接到客戶端的請(qǐng)求后通過判斷后轉(zhuǎn)發(fā)到后端數(shù)據(jù)庫，有兩個(gè)代表性程序：
（1）MySQL—Proxy：為MySQL開源項(xiàng)目，通過其自帶的lua腳本進(jìn)行SQL判斷，雖然是MySQL官方產(chǎn)品。但是MySQL官方并不建議將MySQL—Proxy用到生產(chǎn)環(huán)境中；
（2）Amoeba（變形蟲）：由陳思儒開發(fā)，其層就職于阿里巴巴。該程序用Java語言進(jìn)行開發(fā)，阿里巴巴將其用于生產(chǎn)環(huán)境。它不支持事務(wù)和存儲(chǔ)過程；
經(jīng)過上述簡(jiǎn)單的比較，通過程序代碼實(shí)現(xiàn)MySQL讀寫分離自然是一個(gè)不錯(cuò)的選擇，但是并不是所有的應(yīng)用都適合在程序代碼中實(shí)現(xiàn)讀寫分離。例如：一些大型復(fù)雜Java應(yīng)用，如果在程序代碼中實(shí)現(xiàn)分離對(duì)代碼改動(dòng)較大。所以，像這種代行復(fù)雜的應(yīng)用一般會(huì)考慮使用代理層來實(shí)現(xiàn)。
三、搭建MySQL主從復(fù)制實(shí)驗(yàn)拓補(bǔ)如下：
（1）MySQL主服務(wù)器建立時(shí)間同步大致步驟分為：
（1）MySQL主服務(wù)器建立時(shí)間同步；
（2）MySQL從服務(wù)器進(jìn)行時(shí)間同步；
（3）關(guān)閉防火墻；
（4）安裝MySQL數(shù)據(jù)庫；
（5）配置MySQL主服務(wù)器；
（6）配置MySQL從服務(wù)器；
（7）驗(yàn)證主從復(fù)制效果；
[root@localhost ~]# yum -y install ntp
//安裝NTP
[root@localhost ~]# vim /etc/ntp.conf
//編寫ntp服務(wù)的配置文件
…………………… //省略部分內(nèi)容，填寫以下內(nèi)容
server 127.127.1.0
//需要同步上層服務(wù)器（本身）
fudge 127.127.1.0 stratum 8
//同步上層服務(wù)器的層數(shù)（大小不能超過或等于16）
[root@localhost ~]# systemctl restart ntpd
//重啟ntp服務(wù)兩臺(tái)從服務(wù)器上都要進(jìn)行時(shí)間同步！
[root@localhost ~]# yum -y install ntpdate
[root@localhost ~]# ntpdate 192.168.1.1
//安裝ntpdate工具，并指定ntp服務(wù)器實(shí)現(xiàn)同步由于實(shí)驗(yàn)環(huán)境，這里就采用強(qiáng)制關(guān)閉防火墻了。每臺(tái)服務(wù)器上都關(guān)閉防火墻！
[root@localhost ~]# systemctl stop firewalld
//關(guān)閉防火墻安裝MySQL數(shù)據(jù)庫可以參考博文：編譯安裝MySQL數(shù)據(jù)庫系統(tǒng)
安裝完成之后，啟動(dòng)MySQL數(shù)據(jù)庫
[root@localhost ~]# systemctl start mysqld
//啟動(dòng)MySQL數(shù)據(jù)庫
[root@localhost ~]# netstat -anpt | grep mysqld
tcp6 0 0 :::3306 :::* LISTEN 3263/mysqld
//mysql數(shù)據(jù)庫的監(jiān)聽端口是TCP/3306，確認(rèn)一下！
[root@localhost ~]# mysqladmin -u root password \'123456\'
//給MySQL數(shù)據(jù)庫管理員“root”設(shè)置密碼主、從服務(wù)器都需要安裝MySQL數(shù)據(jù)庫、并啟動(dòng)、設(shè)置密碼 ?。。?/p>（5）配置MySQL主服務(wù)器
[root@localhost ~]# vim /etc/my.cnf
//編寫MySQL數(shù)據(jù)庫的配置文件
…………………… //省略部分內(nèi)容，填寫以下內(nèi)容
server_id = 11
//服務(wù)器ID號(hào)，自定義但不可以與別的MySQL服務(wù)器沖突
log_bin = master-bin
//定義bin-log的文件名（默認(rèn)保存在數(shù)據(jù)目錄下）
log-slave-updates = true
//開啟從服務(wù)器自動(dòng)備份
[root@localhost ~]# systemctl restart mysqld
//重啟MySQL數(shù)據(jù)庫
[root@localhost ~]# mysql -u root -p
Enter password:
//登錄MySQL數(shù)據(jù)庫
mysql> grant replication slave on *.* to \'myslave\'@\'192.168.1.%\' identified by \'123456\';
Query OK, 0 rows affected (0.00 sec)
//授權(quán)用戶myslave，密碼是123456，在192.168.1.0網(wǎng)段的所有MySQL服務(wù)器上對(duì)所有數(shù)據(jù)庫、表具有復(fù)制的權(quán)限
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
//刷新權(quán)限
mysql> show master status;
//查看主服務(wù)器的狀態(tài)
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000001 | 410 | | | |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
//File列顯示日志名，Position列顯示偏移量兩臺(tái)從服務(wù)器的配置幾乎一樣！
[root@localhost ~]# vim /etc/my.cnf
//編寫MySQL數(shù)據(jù)庫的配置文件
…………………… //省略部分內(nèi)容，填寫以下內(nèi)容
server_id = 22
//服務(wù)器ID號(hào)，自定義但不可以與別的MySQL服務(wù)器沖突
relay-log = relay-log-bin
//定義中繼日志文件名稱和路徑（默認(rèn)保存在數(shù)據(jù)目錄下）
relay-log-index = slave-relay-bin.index
//定義中繼日志索引文件名（記錄很多的中繼日志文件）
//默認(rèn)情況，中繼日志使用host_name-relay-bin.nnnnnn形式的文件名，其中host_name是從服務(wù)器主機(jī)名，nnnnnn是序 列號(hào)。
//用連續(xù)序列號(hào)來創(chuàng)建連續(xù)中繼日志文件，從000001開始。從服務(wù)器跟蹤索引文件中目前正使用的中繼日志。 默認(rèn)中繼日志索引文件名為host_name-relay-bin.index。
//默認(rèn)情況，在從服務(wù)器的數(shù)據(jù)目錄中創(chuàng)建這些文件。
[root@localhost ~]# systemctl restart mysqld
//重啟MySQL數(shù)據(jù)庫
[root@localhost ~]# mysql -u root -p
Enter password:
//登錄MySQL數(shù)據(jù)庫
mysql> change master to master_host=\'192.168.1.1\',master_user=\'myslave\',
-> master_password=\'123456\',master_log_file=\'master-bin.000001\',master_log_pos=410;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
//指定主服務(wù)的IP地址、登錄主服務(wù)器的用戶名、密碼、指定同步的日志、偏移量
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
//啟動(dòng)同步
mysql> show slave status\\G;
//查看Slave狀態(tài)，確保以下兩項(xiàng)為“Yes”
…………………… //省略部分內(nèi)容，填寫以下內(nèi)容
Slave_IO_Running: Yes //I/O線程啟動(dòng)
Slave_SQL_Running: Yes //SQL線程啟動(dòng) …………………… //省略部分內(nèi)容，填寫以下內(nèi)容兩臺(tái)從服務(wù)器配置完畢后！即可進(jìn)行測(cè)試！
（7）驗(yàn)證主從復(fù)制效果①在主、從服務(wù)器上登錄MySQL數(shù)據(jù)庫，查看數(shù)據(jù)庫；
[root@localhost ~]# mysql -u root -p
Enter password:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.10 sec)②在主服務(wù)器上創(chuàng)建新的數(shù)據(jù)庫；
mysql> create database qq;
Query OK, 1 row affected (0.00 sec)③在主、從服務(wù)器上分別查看數(shù)據(jù)庫
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| qq |
| test |
+--------------------+
5 rows in set (0.00 sec)顯示數(shù)據(jù)庫相同，則表示主從復(fù)制成功！
四、搭建MySQL讀寫分離搭建MySQL讀寫分離的基礎(chǔ)就是MySQL主從復(fù)制，以下所有操作都是基于mysql主從復(fù)制的基礎(chǔ)上。
搭建MySQL讀寫分離使用的軟件是——Amoeba軟件。簡(jiǎn)單介紹一下：
Amoeba項(xiàng)目開源框架于2008年發(fā)布一款A(yù)moeba for MySQL軟件。這個(gè)軟件致力于MySQL的分布式數(shù)據(jù)庫的前端代理曾，它主要為應(yīng)用層訪問MySQL的時(shí)候充當(dāng)SQL路由功能，并且具有負(fù)載均衡、高可用性、SQL過濾、讀寫分離、可路由相關(guān)到目標(biāo)數(shù)據(jù)庫，可并發(fā)請(qǐng)求多臺(tái)數(shù)據(jù)庫。通過Amoeba能夠完成多數(shù)據(jù)源的高可用、負(fù)載均衡、數(shù)據(jù)切片的功能，目前Amoeba已在很多企業(yè)的生產(chǎn)線上使用，其版本可在官網(wǎng)上自行下載。
（1）在Amoeba服務(wù)器上安裝Java環(huán)境注意：Amoeba軟件千萬不要安裝在任意mysql服務(wù)器上！
因?yàn)锳moeba是基于jdk1.5開發(fā)的，所以官方推薦使用jdk1.5或1.6版本。高版本不建議使用！查看Centos 7系統(tǒng)默認(rèn)的Java環(huán)境：
[root@localhost ~]# java -version
openjdk version 1.8.0_102
OpenJDK Runtime Environment (build 1.8.0_102-b14)
OpenJDK 64-Bit Server VM (build 25.102-b14, mixed mode)所以，還需要重新部署一下java環(huán)境，使用的軟件包網(wǎng)盤鏈接：https://pan.baidu.com/s/1sHwbvlIZ2VDLb_qw-b_z4w
提取碼：rs1x
操作如下：
[root@localhost ~]# cp jdk-6u14-linux-x64.bin /usr/local
[root@localhost ~]# chmod +x /usr/local/jdk-6u14-linux-x64.bin
[root@localhost ~]# cd /usr/local
[root@localhost local]# ./jdk-6u14-linux-x64.bin //根據(jù)提示輸入yes和回車鍵
[root@localhost local]# mv jdk1.6.0_14/ jdk1.6 //對(duì)jdk的安裝目錄進(jìn)行重命名
[root@localhost ~]# vim /etc/profile //編寫全局環(huán)境變量配置文件
…………………… //省略部分內(nèi)容，填寫以下內(nèi)容
export JAVA_HOME=/usr/local/jdk1.6
export 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
[root@localhost ~]# source /etc/profile
[root@localhost ~]# java -version
java version 1.6.0_14
Java(TM) SE Runtime Environment (build 1.6.0_14-b08)
Java HotSpot(TM) 64-Bit Server VM (build 14.0-b16, mixed mode)
//目前系統(tǒng)的Java環(huán)境是1.6版本的，可以使用！Amoeba軟件的網(wǎng)盤鏈接：https://pan.baidu.com/s/1m-gBbCW4SxTt4b5NvQngQQ
提取碼：w93m
操作如下：
[root@localhost ~]# mkdir /usr/local/amoeba
[root@localhost ~]# tar zxf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/
[root@localhost ~]# chmod -R 755 /usr/local/amoeba/
[root@localhost ~]# /usr/local/amoeba/bin/amoeba
amoeba start|stop
//出現(xiàn)這些內(nèi)容，則表示安裝amoeba安裝成功①M(fèi)aster、Slave1、Slave2中都要開放權(quán)限給Amoeba訪問，命令如下：
mysql> grant all on *.* to \'test\'@\'192.168.1.%\' identified by \'123.com\';
Query OK, 0 rows affected (0.00 sec)②編輯amoeba.xml配置文件。此配置文件主要定義主從服務(wù)器地址池。
[root@localhost ~]# vim /usr/local/amoeba/conf/amoeba.xml
…………………… //由于內(nèi)容較多，編輯的每條內(nèi)容都添加了實(shí)際的行號(hào)
30 amoeba
31
32 123456
//指定客戶機(jī)訪問amoeba服務(wù)器時(shí)的賬號(hào)、密碼！
115 master
116
117 master
118 slaves
//注意取消原本的注釋信息 ③編輯dbServer.xml配置文件
[root@localhost ~]# vim /usr/local/amoeba/conf/dbServers.xml
…………………… //由于內(nèi)容較多，編輯的每條內(nèi)容都添加了實(shí)際的行號(hào)
26 test
27
28 123.com
//指定amoeba服務(wù)器訪問mysql數(shù)據(jù)庫使用的賬號(hào)密碼
43
44
45
46 i192.168.1.1
47
48
49
50
51
52
53 192.168.1.2
54
55
56
57
58
59 192.168.1.3
60
61
//slave2配置需要復(fù)制salve1的配置信息（行號(hào)可能與實(shí)際不符）
62
63
64
65
66 1
67
68
69 slave1,slave2
70
71 ④確認(rèn)無誤后，啟動(dòng)Amoeba軟件，其默認(rèn)端口為TCP 8066
[root@localhost ~]# /usr/local/amoeba/bin/amoeba start &
[root@localhost ~]# netstat -anpt | grep 8066
tcp6 0 0 :::8066 :::* LISTEN 3799/java 由于實(shí)驗(yàn)環(huán)境，我們測(cè)試一下讀寫分離的效果，實(shí)際環(huán)境，輕易不要測(cè)試?。?！
①在客戶端安裝mysql工具，并通過代理訪問mysql，命令如下：
[root@localhost ~]# yum -y install mariadb
//注意，Centos 7系統(tǒng)開始，自帶的數(shù)據(jù)庫叫mariadb，不再是mysql
[root@localhost ~]# mysql -u amoeba -h 192.168.1.4 -P 8066 -p
Enter password:
MySQL [(none)]>
//表示訪問成功②在MySQL主服務(wù)器上創(chuàng)建一個(gè)表同步到從服務(wù)器上
mysql> use qq;
Database changed
mysql> create table q1 (id int);
Query OK, 0 rows affected (0.10 sec)分別在兩臺(tái)從服務(wù)器上停止同步，執(zhí)行如下命令：
mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)在主服務(wù)器上插入數(shù)據(jù)：
mysql> insert into q1 values (\'1\');
Query OK, 1 row affected (0.00 sec)③兩臺(tái)從服務(wù)器上同步了表，手動(dòng)分別插入一條數(shù)據(jù)
第一臺(tái)從服務(wù)器
mysql> use qq;
Database changed
mysql> insert into q1 values (\'2\');
Query OK, 1 row affected (0.00 sec)第二臺(tái)從服務(wù)器
mysql> use qq;
Database changed
mysql> insert into q1 values (\'3\');
Query OK, 1 row affected (0.00 sec)④測(cè)試讀操作
在客戶機(jī)上第一次查詢結(jié)果：
MySQL [(none)]> select * from qq.q1;
+------+
| id |
+------+
| 3 |
+------+
1 row in set (0.00 sec)在客戶機(jī)上第二次查詢結(jié)果：
MySQL [(none)]> select * from qq.q1;
+------+
| id |
+------+
| 2 |
+------+
1 row in set (0.00 sec)永遠(yuǎn)是這兩種結(jié)果?。。?br/>這樣實(shí)現(xiàn)了讀數(shù)據(jù)的負(fù)載均衡，而且效果很明顯！
⑤測(cè)試寫操作
在客戶端上插入一條數(shù)據(jù)，但是在客戶端查詢不到！
MySQL [qq]> insert into q1 values (\'4\');
Query OK, 1 row affected (0.00 sec)但在主服務(wù)器上可以查詢到！
mysql> select * from q1;
+------+
| id |
+------+
| 1 |
| 4 |
+------+
2 rows in set (0.00 sec)由此驗(yàn)證，已經(jīng)實(shí)現(xiàn)了MySQL讀寫分離，目前所有的寫在主服務(wù)器上，避免了數(shù)據(jù)的不同步；所有的讀操作都分?jǐn)偨o了從服務(wù)器，用來分擔(dān)數(shù)據(jù)庫的壓力！
———————— 本文至此結(jié)束，感謝閱讀 ————————
當(dāng)前名稱：MySQL主從復(fù)制與讀寫分離詳解
URL地址：http://www.xueling.net.cn/article/cpcsdp.html