讀寫分離是什么

讀寫分離，基本的原理是讓主數(shù)據(jù)庫處理事務性增、改、刪操作（INSERT、UPDATE、DELETE），而從數(shù)據(jù)庫處理SELECT查詢操作。數(shù)據(jù)庫復制被用來把事務性操作導致的變更同步到集群中的從數(shù)據(jù)庫。

為什么要進行讀寫分離

因為數(shù)據(jù)庫的“寫”（寫10000條數(shù)據(jù)可能要3分鐘）操作是比較耗時的。

但是數(shù)據(jù)庫的“讀”（讀10000條數(shù)據(jù)可能只要5秒鐘）。

所以讀寫分離，解決的是，數(shù)據(jù)庫的寫入，影響了查詢的效率。

何時要進行讀寫分離

數(shù)據(jù)庫不一定要讀寫分離，但如果程序使用數(shù)據(jù)庫較多，而更新少，查詢多的情況下會考慮使用。利用數(shù)據(jù)庫主從同步，再通過讀寫分離可以分擔數(shù)據(jù)庫壓力，提高性能。

主從復制與讀寫分離

在實際的生產(chǎn)環(huán)境中，對數(shù)據(jù)庫的讀和寫都在同一個數(shù)據(jù)庫服務器中，是不能滿足實際需求的。無論是在安全性、高可用性還是高并發(fā)等各個方面都是完全不能滿足實際需求的。因此，通過主從復制的方式來同步數(shù)據(jù)，再通過讀寫分離來提升數(shù)據(jù)庫的并發(fā)負載能力。有點類似于rsync，但是不同的是rsync是對磁盤文件做備份，而mysql主從復制是對數(shù)據(jù)庫中的數(shù)據(jù)、語句做備份。

MySQL支持的復制類型

STATEMENT：基于語句的復制。在服務器上執(zhí)行sql語句，在從服務器上執(zhí)行同樣的語句，mysql默認采用基于語句的復制，執(zhí)行效率高。

ROW：基于行的復制。把改變的內(nèi)容復制過去，而不是把命令在從服務器上執(zhí)行一遍。

MIXED：混合類型的復制。默認采用基于語句的復制，一旦發(fā)現(xiàn)基于語句無法精確復制時，就會采用基于行的復制。

主從復制的過程

主(Master)：dump線程，二進制日志

從(Slave)：I/O線程，SQL線程，中繼日志

1、主中數(shù)據(jù)進行更新，并寫入二進制日志。

2、從開啟IO線程，并勘測主的二進制日志是否有更新，請求讀取二進制日志。

3、主為從的IO線程開啟dump線程，向從發(fā)送二進制日志

4、從保存二進制日志到中繼日志中

5、從開啟SQL線程讀取中繼日志，解析成sql語句，實現(xiàn)主從同步
?

數(shù)據(jù)庫主從數(shù)據(jù)不一致解決方案

一：忽略錯誤后，繼續(xù)同步 該方法適用于主從庫數(shù)據(jù)相差不大，或者要求數(shù)據(jù)可以不完全統(tǒng)一的情況，數(shù)據(jù)要求不嚴格的情況

二：重新做主從，完全同步 該方法適用于主從庫數(shù)據(jù)相差較大，或者要求數(shù)據(jù)完全統(tǒng)一的情況

MYSQL讀寫分離原理

讀寫分離就是只在主服務器上寫，只在從服務器上讀?；镜脑硎亲屩鲾?shù)據(jù)庫處理事務性操作，而從數(shù)據(jù)庫處理select查詢。數(shù)據(jù)庫復制被用來把主數(shù)據(jù)庫上事務性操作導致的變更同步到集群中的從數(shù)據(jù)庫。

目前較為常見的MysQL讀寫分離分為以下兩種:

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

在代碼中根據(jù)select、insert進行路由分類，這類方法也是目前生產(chǎn)環(huán)境應用最廣泛的。

優(yōu)點是性能較好，因為在程序代碼中實現(xiàn)，不需要增加額外的設(shè)備為硬件開支；缺點是需要開發(fā)人員來實現(xiàn)，運維人員無從下手。

但是并不是所有的應用都適合在程序代碼中實現(xiàn)讀寫分離，像一些大型復雜的Java應用，如果在程序代碼中實現(xiàn)讀寫分離對代碼改動就較大。

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

代理一般位于客戶端和服務器之間，代理服務器接到客戶端請求后通過判斷后轉(zhuǎn)發(fā)到后端數(shù)據(jù)庫，有以下代表性程序。

????????MySQL-Proxy

MySQL-Proxy為MysQL開源項目，通過其自帶的1ua腳本進行sQL判斷。

????????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è)務，每天承載的讀寫請求數(shù)達幾干保條。支持事物以及存儲過程。

????????Amoeba

由陳思儒開發(fā)，作者曾就職于阿里巴巴。該程序由Java語言進行開發(fā)，阿里巴巴將其用于生產(chǎn)環(huán)境。但是它不支持事務和存儲過程。

Amoeba是一個非常容易使用、可移植性非常強的軟件。因此它在生產(chǎn)環(huán)境中被廣泛應用于數(shù)據(jù)庫的代理層。

????????Mycat

是一款流行的基于Java語言編寫的數(shù)據(jù)庫中間件，是一個實現(xiàn)了MySq1協(xié)議的服務器，其核心功能是分庫分表。配合數(shù)據(jù)庫的主從模式還可以實現(xiàn)讀寫分離 由于使用MysQLProxy需要寫大量的ua腳本，這些Lua并不是現(xiàn)成的，而是需要自己去寫。這對于并不熟悉MysQLProxy 內(nèi)置變量和MySQL Protocol的人來說是非常困難的。

MySQL主從復制延遲的原因

1、master服務器高并發(fā)，形成大量事務

2、網(wǎng)絡延遲

3、主從硬件設(shè)備導致：cpu主頻、內(nèi)存io、硬盤io

4、本來就不是同步復制、而是異步復制

從庫優(yōu)化Mysql參數(shù)。比如增大innodb_buffer_pool_size，讓更多操作在Mysql內(nèi)存中完成，減少磁盤操作。

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

從庫使用SSD磁盤

網(wǎng)絡優(yōu)化，避免跨機房實現(xiàn)同步

主從復制配置

#準備Master 服務器：20.0.0.30		mysql5.7         
Slave1 服務器：20.0.0.40		mysql5.7         
Slave2 服務器：20.0.0.120		mysql5.7#關(guān)閉防火墻及安全機制systemctl stop firewalld
systemctl disable firewalld
setenforce 0

Mysql主從服務器時間同步

#主服務器設(shè)置(20.0.0.30)
#安裝ntp
yum install ntp -yvim /etc/ntp.conf
--末尾添加--
server 127.0.0.0							#設(shè)置本地是時鐘源，注意修改網(wǎng)段  127.0.0.0
fudge 127.0.0.0 stratum 8				#設(shè)置時間層級為8（限制在15內(nèi)）service ntpd start
或
systemctl start ntpd#從服務器設(shè)置(20.0.0.40 、20.0.0.120)yum install ntp ntpdate -yservice ntpd start
或
systemctl start ntpd/usr/sbin/ntpdate 20.0.0.30				#進行時間同步   20.0.0.30#可添加定時任務，實現(xiàn)自動同步
crontab -e
*/30 * * * * /usr/sbin/ntpdate 20.0.0.30

主服務器的mysql配置

vim /etc/my.cnf                          #MySQL的配置文件
server-id = 1
log-bin=master-bin					     #添加，主服務器開啟二進制日志
binlog_format = MIXED                    #設(shè)置日式格式為混合模式
log-slave-updates=true					 #添加，允許slave從master復制數(shù)據(jù)時可以寫入到自己的二進制日志#重啟MySQL服務
systemctl restart mysqldmysql -u root -p123456
GRANT REPLICATION SLAVE ON *.* TO 'myslave'@'20.0.0.%' IDENTIFIED BY '123456';			#給從服務器的網(wǎng)段授權(quán)#刷新
FLUSH PRIVILEGES;show master status;
//如顯示以下
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000001 |    447   |              |                  |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)#File 列顯示日志名，Position 列顯示偏移量

從服務器的mysql配置

vim /etc/my.cnf
server-id = 2    [server-id = 3]			#修改，注意id與Master的不同，兩個Slave的id也要不同
relay-log=relay-log-bin						#添加，開啟中繼日志，從主服務器上同步日志文件記錄到本地
relay-log-index=slave-relay-bin.index		#添加，定義中繼日志文件的位置和名稱，一般和relay-log在同一目錄
relay_log_recovery = 1                      
#relay_log_recovery = 1 選配項:當 slave 從庫宕機后，假如 relay-log 損壞了，導致一部分中繼日志沒有處理，則自動放棄所有未執(zhí)行的 relay-log，并且重新從 master 上獲取日志，這樣就保證了relay-log 的完整性。默認情況下該功能是關(guān)閉的，將 relay_log_recovery 的值設(shè)置為 1 時， 可在 slave 從庫上開啟該功能，建議開啟。systemctl restart mysqldmysql -u root -123456
CHANGE master to master_host='20.0.0.30',master_user='myslave',master_password='123456',master_log_file='master-bin.000001',master_log_pos=447;					
#配置同步，注意 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				#負責與主機的io通信
Slave_SQL_Running: Yes				#負責自己的slave mysql進程

查看從配置不正常的情況

#一般 Slave_IO_Running: No 的可能性：
1、網(wǎng)絡不通
2、my.cnf配置有問題
3、密碼、file文件名、pos偏移量不對
4、防火墻沒有關(guān)閉

驗證主從復制效果

#當主數(shù)據(jù)庫進行增添或修改時，從數(shù)據(jù)庫會同步記錄主的數(shù)據(jù)#在主數(shù)據(jù)庫增添數(shù)據(jù)(20.0.0.30)mysql> create database xxxx;
Query OK, 1 row affected (0.00 sec)#此時查看配置的兩臺從服務器數(shù)據(jù)庫#20.0.0.40
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| bbs                |
| mysql              |
| performance_schema |
| sys                |
| test               |
| test1              |
| wordpress          |
| xxxx               |
+--------------------+#20.0.0.120
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test1              |
| test2              |
| xxxx               |
+--------------------+

讀寫分離配置

基于上面的主從復制

#準備Master 服務器：20.0.0.30		mysql5.7         
Slave1 服務器：20.0.0.40		mysql5.7         
Slave2 服務器：20.0.0.120		mysql5.7Amoeba 服務器：20.0.0.41		jdk1.6、Amoeba   
客戶端 服務器：20.0.0.41		mysql#關(guān)閉防火墻及安全機制systemctl stop firewalld
systemctl disable firewalld
setenforce 0

做讀寫分離實驗之前必須有一 主 兩從 環(huán)境

#Amoeba服務器配置##安裝 Java 環(huán)境##因為 Amoeba 是基于 jdk1.5 開發(fā)的，所以官方推薦使用 jdk1.5 或 1.6 版本，高版本不建議使用。cd /opt/
cp jdk-6u14-linux-x64.bin /usr/local/
cd /usr/local/
chmod +x jdk-6u14-linux-x64
./jdk-6u14-linux-x64.bin//按yes，按enter#重命名目錄
mv jdk1.6.0_14/ /usr/local/jdk1.6#修改系統(tǒng)配置文件
vim /etc/profileexport 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
#執(zhí)行
source /etc/profile
#查看當前java環(huán)境
java -version#安裝 Amoeba軟件mkdir /usr/local/amoebatar zxvf 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說明安裝成功#配置 Amoeba讀寫分離，兩個 Slave 讀負載均衡###先在Master(20.0.0.30)、Slave1(20.0.0.40)、Slave2(20.0.0.120) 的mysql上開放權(quán)限給 Amoeba 訪問grant all on *.* to test@'20.0.0.%' identified by '123456';#再回到amoeba服務器配置amoeba服務：cd /usr/local/amoeba/conf/#備份
cp amoeba.xml amoeba.xml.bakvim amoeba.xml									#修改amoeba配置文件
--30行--
<property name="user">amoeba</property>--32行-- 
<property name="password">123456</property>--115行--
<property name="defaultPool">master</property>--117-去掉注釋-
<property name="writePool">master</property>
<property name="readPool">slaves</property>#備份
cp dbServers.xml dbServers.xml.bak
vim dbServers.xml								#修改數(shù)據(jù)庫配置文件--23行--注釋掉  作用：默認進入test庫 以防m(xù)ysql中沒有test庫時，會報錯
<!-- <property name="schema">test</property> -->--26--修改
<property name="user">test</property>--28-30--去掉注釋
<property name="password">123456</property>--45--修改，設(shè)置主服務器的名Master
<dbServer name="master"  parent="abstractServer">--48--修改，設(shè)置主服務器的地址
<property name="ipAddress">192.168.10.15</property>--52--修改，設(shè)置從服務器的名slave1
<dbServer name="slave1"  parent="abstractServer">--55--修改，設(shè)置從服務器1的地址
<property name="ipAddress">192.168.10.14</property>--58--復制上面6行粘貼，設(shè)置從服務器2的名slave2和地址
<dbServer name="slave2"  parent="abstractServer">
<property name="ipAddress">192.168.10.16</property>--65行--修改
<dbServer name="slaves" virtual="true">--71行--修改
<property name="poolNames">slave1,slave2</property>/usr/local/amoeba/bin/amoeba start&					#啟動Amoeba軟件，按ctrl+c 返回
netstat -anpt | grep java							#查看8066端口是否開啟，默認端口為TCP 8066

測試讀寫分離

yum install -y mariadb-server mariadb
systemctl start mariadb.service
———————————————————— 可選，也可以用MySQL在客戶端服務器上測試(20.0.0.41)：mysql -u amoeba -p123456 -h 20.0.0.41 -P8066#通過amoeba服務器代理訪問mysql ，在通過客戶端連接mysql后寫入的數(shù)據(jù)只有主服務會記錄，然后同步給從--從服務器#在客戶端（20.0.0.41）更新mysql> create database amoeba;
Query OK, 1 row affected (0.04 sec)#查看主(20.0.0.30)、從1(20.0.0.40)、從2(20.0.0.120)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| amoeba             |
| bbs                |
| mysql              |
| performance_schema |
| sys                |
| test               |
| test1              |
| wordpress          |
+--------------------+
9 rows in set (0.01 sec)#當關(guān)閉兩臺從服務器（20.0.0.40、20.0.0.120）stop slave;		#關(guān)閉同步use amoeba;
create table test(id int,name varchar(20),text varchar(30));//在slave1上：insert into test values('1','zhangsan','this_is_slave1');//在slave2上：
insert into test values('2','lisi','this_is_slave2');//在主服務器上：
insert into test values('3','wangwu','this_is_master');//在客戶端服務器上：
use amoeba;
select * from test;		//客戶端會分別向slave1和slave2讀取數(shù)據(jù)，顯示的只有在兩個從服務器上添加的數(shù)據(jù)，沒有在主服務器上添加的數(shù)據(jù)
mysql> select * from test;
+------+----------+----------------+
| id   | name     | text           |
+------+----------+----------------+
|    1 | zhangsan | this_is_slave1 |
+------+----------+----------------+再次執(zhí)行select * from test;	mysql> select * from test;
+------+------+----------------+
| id   | name | text           |
+------+------+----------------+
|    2 | lisi | this_is_slave2 |
+------+------+----------------+#在客戶端（20.0.0.41） 插入新條目
insert into test values('4','qianqi','this_is_client');		#只有主服務器(20.0.0.30)上有此數(shù)據(jù)
mysql> select * from test;
+------+--------+----------------+
| id   | name   | text           |
+------+--------+----------------+
|    3 | wangwu | this_is_master |
|    4 | qianqi | this_is_client |
+------+--------+----------------+//在兩個從服務器(20.0.0.40、20.0.0.120)上執(zhí)行 start slave; 即可實現(xiàn)同步在主服務器上添加的數(shù)據(jù)
start slave;
#(20.0.0.40)
mysql> select * from test;
+------+----------+----------------+
| id   | name     | text           |
+------+----------+----------------+
|    1 | zhangsan | this_is_slave1 |
|    3 | wangwu   | this_is_master |
|    4 | qianqi   | this_is_client |
+------+----------+----------------+#(20.0.0.120)
mysql> select * from test;
+------+----------+----------------+
| id   | name     | text           |
+------+----------+----------------+
|    2 | lisi     | this_is_slave2 |
|    3 | wangwu   | this_is_master |
|    4 | qianqi   | this_is_client |
+------+----------+----------------+