前言

在企業應用中，成熟的業務通常資料量都比較大
單台Mysql在安全性、高可用性和高并發方面都無法滿足實際的需求
配置多台主從資料庫伺服器以實作讀寫分離

一、MySQL主從複制與讀寫分離

1）主從分離和讀寫分離的相關概述

讀寫分離基本的原理是讓主資料庫處理事務性增、改、删操作（INSERT、UPDATE、DELETE），而從資料庫處理SELECT查詢操作。資料庫複制被用來把事務性操作導緻的變更同步到叢集中的從資料庫。

MySQL主從複制與讀寫分離解析和圖文詳細步驟前言一、MySQL主從複制與讀寫分離二、MySQL主從複制搭建過程和讀寫設定MySQL主從複制的幾個同步模式:總結

2）為什麼要讀寫分離

因為資料庫的“寫”（寫10000條資料可能要3分鐘）操作是比較耗時的。
但是資料庫的“讀”（讀10000條資料可能隻要5秒鐘）。
是以讀寫分離，解決的是，資料庫的寫入，影響了查詢的效率

3）什麼時候要讀寫分離

資料庫不一定要讀寫分離，如果程式使用資料庫較多時，而更新少，查詢多的情況下會考慮使用。利用資料庫主從同步，再通過讀寫分離可以分擔資料庫壓力，提高性能。

4）MySQL 讀寫分離原理

讀寫分離就是隻在主伺服器上寫，隻在從伺服器上讀。
基本的原理是讓主資料庫處理事務性查詢，而從資料庫處理 select 查詢。
資料庫複制被用來把主資料庫上事務性查詢導緻的變更同步到叢集中的從資料庫。

MySQL主從複制與讀寫分離解析和圖文詳細步驟前言一、MySQL主從複制與讀寫分離二、MySQL主從複制搭建過程和讀寫設定MySQL主從複制的幾個同步模式:總結

5）MySQL支援的複制類型

1. STATEMENT: 基于語句的複制。在伺服器上執行sql語句，在從伺服器上執行同樣的語句，mysql預設采用基于語句的複制，執行效率高。

2. ROW: 基于行的複制。把改變的内容複制過去，而不是把指令在從伺服器上執行一遍。

3. MIXED: 混合類型的複制。預設采用基于語句的複制，一旦發現基于語句無法精确複制時，就會采用基于行的複制。

6）主從複制的工作過程（主寫從複制）（重點）

在每個事務更新資料完成之前，Master在二進制日志(Binary log) 記錄這些改變。寫入二進制日志完成後，Master通知存儲引擎送出事務。

Slave 将Master複制到其中繼日志(Relay log)。首先slave開始一個工作線程(I/O)，I/0線程在Master上打開一個普通的連接配接，然後開始Binlog dump process。Binlog dump process從Master的二進制日志中讀取事件，如果已經跟上Master,它會睡眠并等待Master産生新的事件，I/O線程将這些事件寫入中繼日志（緩存中）。

SQL slave thread (SQL從線程) 處理該過程的最後一步，SQL線程從中繼日志讀取事件，并重放其中的事件而更新Slave資料，使其與Master 中的資料一緻，隻要該線程與I/O 線程保持一緻，中繼日志通常會位于OS（系統）緩存中，是以中繼日志的開銷很小。複制過程有一個很重要的限制，即複制在 Slave 上是串行化的，也就是說Master 上的并行更新操作不能在Slave上并行操作。

注意：

中繼日志通常會位于os 緩存中，是以中繼日志的開銷很小。
複制過程有一個很重要的限制，即複制在 Slave.上是串行化的，也就是說Master上的并行更新操作不能在Slave.上并行操作.

7）MySOL主從複制延遲

1、master伺服器高并發，形成大量事務

2、網絡延遲

3、主從硬體裝置導緻 cpu主頻、記憶體io、硬碟io

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

5、從庫優化Mysql參數。比如增大innodb_buffer_pool_size，讓更多操作在Mysgl記憶體中完成，減少磁盤操作。從庫使用高性能主機。包括cpu強悍、記憶體加大。避免使用虛拟雲主機，使用實體主機，這樣提升了i/o方面性。從庫使用SSD磁盤網絡優化，避免跨機房實作同步。

8）常見的 MySQL 讀寫分離分為兩種

壹、基于程式代碼内部實作

在代碼中根據 select、insert 進行路由分類，這類方法也是目前生産環境應用最廣泛的。
優點是性能較好，因為在程式代碼中實作，不需要增加額外的裝置為硬體開支；缺點是需要開發人員來實作，運維人員無從下手。
但是并不是所有的應用都适合在程式代碼中實作讀寫分離，像一些大型複雜的Java應用，如果在程式代碼中實作讀寫分離對代碼改動就較大。

貳、基于中間代理層實作

代理一般位于用戶端和伺服器之間，代理伺服器接到用戶端請求後通過判斷後轉發到後端資料庫，有以下代表性程式：

1）MySQL-Proxy。MySQL-Proxy 為 MySQL 開源項目，通過其自帶的 lua 腳本進行SQL 判斷。

2）Atlas。是由奇虎360的Web平台部基礎架構團隊開發維護的一個基于MySQL協定的資料中間層項目。它是在mysql-proxy 0.8.2版本的基礎上，對其進行了優化，增加了一些新的功能特性。360内部使用Atlas運作的mysql業務，每天承載的讀寫請求數達幾十億條。支援事物以及存儲過程。

3）Amoeba。由陳思儒開發，作者曾就職于阿裡巴巴。該程式由Java語言進行開發，阿裡巴巴将其用于生産環境。但是它不支援事務和存儲過程。
由于使用MySQL Proxy需要寫大量的Lua腳本，這些Lua腳本不是現成的，而需要自己編寫，這對于并不熟悉MySQL Proxy内置變量和MySQL Protocol的人來說是非常困難的。
Amoeba是一個非常容易使用，可移植性非常強的軟體，是以它在生産環境中被廣泛用于資料庫的代理層。

二、MySQL主從複制搭建過程和讀寫設定

1. 搭建 MySQL 主從複制

1）Mysql主從伺服器時間同步

Master伺服器：192.168.110.135

systemctl stop firewalld
systemctl disable firewalld
setenforce 0

yum -y install ntp

vim /etc/ntp.conf
##---------末尾添加---------
server 127.127.110.0          #設定本地是時鐘源，注意修改網段
fudge 127.127.110.0 stratum 8 #設定時間層級為8（限制在15内）

service ntpd start

Slave1伺服器：192.168.110.136
Slave2伺服器：192.168.110.137

yum -y install ntp ntpdate

service ntpd start

/usr/sbin/ntpdate 192.168.110.135  #進行時間同步，指向Master伺服器IP

crontab -e
*/30 * * * * /usr/sbin/ntpdate 192.168.110.135

2）主伺服器的mysql配置

Master伺服器：192.168.163.11

vim /etc/my.cnf
server-id = 1
log-bin=master-bin      #添加，主伺服器開啟二進制日志
binlog_format=MIXED     #添加，binlog日志格式，mysql預設采用
log-slave-updates=true  #添加，允許從伺服器更新二進制日志

以下選擇填入
expire_logs_days= 7     #binlog過期清理時間    
max_binlog_size = 100m  #binlog每個日志檔案大小  
binlog_cache_size= 4m    #binlog緩存大小          max_binlog_cache_size= 512m   #最大binlog緩存大小                 

systemctl restart mysqld

mysql -u root -p
#給從伺服器授權
grant replication slave on *.* to 'myslave'@'192.168.110.%' identified by '123456';
flush privileges;

show master status;
#File 列顯示日志名，Fosition 列顯示偏移量

3）從伺服器的mysql配置

Slave1伺服器：192.168.163.12
Slave2伺服器：192.168.163.13

vim /etc/my.cnf
#修改，注意id與Master的不同，兩個Slave的id也要不同
server-id = 2       server-id = 3
#添加，開啟中繼日志，從主伺服器上同步日志檔案記錄到本地
relay-log=relay-log-bin
#添加，定義中繼日志檔案的位置和名稱
relay-log-index=slave-relay-bin.index
relay_log_recovery = 1  #選配項
#當slave從庫當機後，假如relay-log 損壞了，導緻-部分中繼日志沒有處理，則自動放棄所有未執行的relay-1og， 并且重新從master 上擷取日志，這樣就保證了relay-log 的完整性。預設情況下該功能是關閉的，将relay_1og_recovery 的值設定為1時，可在slave 從庫上開啟該功能，建議開啟。

systemctl restart mysqld

mysql -u root -p
#配置同步，注意 master_log_file 和 master_log_pos 的值要與Master查詢的一緻，
change master to master_host='192.168.163.11' , master_user='myslave',master_password='123456',master_log_file='master-bin.000001',master_log_pos=604;

start slave;					#啟動同步，如有報錯執行 reset slave;
show slave status\G				#檢視 Slave 狀态
//確定 IO 和 SQL 線程都是 Yes，代表同步正常。
Slave_IO_Running: Yes			#負責與主機的io通信
Slave_SQL_Running: Yes			#負責自己的slave mysql程序

4）驗證主從複制效果

主伺服器上進入執行 create database test;

2. 搭建 MySQL 讀寫分離

Amoeba伺服器配置： Amoeba伺服器：192.168.163.10

1）安裝 Java 環境

因為 Amoeba 基于是 jdk1.5 開發的，是以官方推薦使用 jdk1.5 或 1.6 版本，高版本不建議使用。
将jdk-6u14-linux-x64.bin 和 amoeba-mysql-binary-2.2.0.tar.gz.0 上傳到/opt目錄下。

cd /opt/
cp jdk-6u14-linux-x64.bin /usr/local/

cd /usr/local/
chmod +x jdk-6u14-linux-x64.bin
./jdk-6u14-linux-x64.bin
按空格到最後一行
按yes，按enter

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

source /etc/profile
java -version

2）安裝 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 說明安裝成功

3）在主從伺服器的mysql上授權

Master伺服器：192.168.163.11

Slave1伺服器：192.168.163.12

Slave2伺服器：192.168.163.13

先在Master、Slave1、Slave2 的mysql上開放權限給 Amoeba 通路

grant all on *.* to 'test'@'192.168.163.%' identified by '123456';

4）配置 Amoeba讀寫分離，兩個 Slave 讀負載均衡

Amoeba伺服器：192.168.163.10

①修改amoeba配置檔案

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">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
#---------23注釋掉--------------------------------------
作用：預設進入test庫 以防mysql中沒有test庫時，會報錯
<!-- mysql schema
<property name="schema">test</property>
-->
#---------26修改-----------------------------------------
<!-- mysql user -->
<property name="user">test</property>
#---------28-30去掉注釋----------------------------------
<property name="password">abc123</property>
#---------45修改，設定主伺服器的名Master------------------
<dbServer name="master"  parent="abstractServer">
#---------48修改，設定主伺服器的位址----------------------
<property name="ipAddress">192.168.163.11</property>
#---------52修改，設定從伺服器的名slave1-----------------
<dbServer name="slave1"  parent="abstractServer">
#---------55修改，設定從伺服器1的位址---------------------
<property name="ipAddress">192.168.163.12</property>
#---------58複制上面6行粘貼，設定從伺服器2的名slave2和位址---
<dbServer name="slave2"  parent="abstractServer">
<property name="ipAddress">192.168.163.13</property>
#---------修改後的65或66修改-------------------------------------
<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

5）測試讀寫分離

用戶端：192.168.163.14。在用戶端伺服器上進行測試：

使用yum快速安裝MySQL虛拟用戶端

yum install -y mysql mysql-server

mysql -u amoeba -p123456 -h 192.168.163.10 -P8066

通過amoeba伺服器代理通路mysql ，在通過用戶端連接配接mysql後寫入的資料隻有主服務會記錄，然後同步給從伺服器

在主伺服器上

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

兩台從伺服器上

stop slave;			#關閉同步
use test;

在slave1上
在slave2上

insert into test values('1','slave1','this_is_slave1');

insert into test values('2','slave2','this_is_slave2');

在主伺服器上

insert into test values('3','master','this_is_master');

在用戶端伺服器上

use test;
select * from test;		//用戶端會分别向slave1和slave2讀取資料，顯示的隻有在兩個從伺服器上添加的資料，沒有在主伺服器上添加的資料

insert into test values('4','client','this_is_client');		//隻有主伺服器上有此資料

再在兩個從伺服器上執行 start slave; 即可實作同步在主伺服器上添加的資料

start slave;

MySQL主從複制的幾個同步模式:

異步複制(Asynchronous replication)

MySQL預設的複制即是異步的，主庫在執行完用戶端送出的事務後會立即将結果返給用戶端，并不關心從庫是否已經接收并處理，這樣就會有一個問題，主如果crash掉了，此時主上已經送出的事務可能并沒有傳到從上，如果此時，強行将從提升為主，可能導緻新主上的資料不完整。

全同步複制(Fully synchronous replication)

指當主庫執行完一個事務，所有的從庫都執行了該事務才傳回給用戶端。因為需要等待所有從庫執行完該事務才能傳回，是以全同步複制的性能必然會收到嚴重的影響。

半同步複制(Semi synchronous replication)

介于異步複制和全同步複制之間，主庫在執行完用戶端送出的事務後不是立刻傳回給用戶端，而是等待至少一個從庫接收到并寫到relaylog中才傳回給用戶端。相對于異步複制，半同步複制提高了資料的安全性，同時它也造成了一定程度的延遲，這個延遲最少是一個TCP/IP往返的時間。是以，半同步複制最好在低延時的網絡中使用。

總結

1、主從同步原理

通過amoeba代理伺服器，實作隻在主伺服器上寫，隻在從服務上讀；
主資料庫處理事務性查詢，從資料庫處理 select 查詢；
資料庫複制被用來把事務性查詢導緻的變更同步到叢集中的從資料庫

2、如何檢視主從同步狀态是否成功

在從伺服器内輸入指令 show slave status\G，檢視主從資訊進行檢視，裡面有IO線程的狀态資訊，還有master伺服器的IP位址、端口、事務開始号
當 slave_io_running 和 slave_sql_running 都顯示為yes時，表示主從同步狀态成功

3、如果I/O和SQL不是yes呢,你是如何排查的

首先排除網絡問題，使用ping指令檢視從服務是否能與主伺服器通信
再者檢視防火牆和核心防護是否關閉
接着檢視從伺服器内的slave是否開啟
兩個從伺服器的 server-id 是否相同導緻隻能連上一台
master_log_file 和 master_log_pos 的值要是否與Master查詢的一緻

4、show slave status能看到哪些資訊(比較重要的)

IO線程的狀态資訊
master伺服器的IP位址、端口、事務開始位置
最近一次的報錯資訊和報錯位置等

5、主從複制慢(延遲)有哪些可能

主伺服器的負載過大，被多個睡眠或者僵屍線程占用，導緻系統負載過大
從庫硬體比主庫差，導緻複制延遲
主從複制單線程，如果主庫寫并發太大，來不及傳送到從庫，就會導緻延遲。
慢SQL語句過多
網絡延遲

MySQL主從複制與讀寫分離解析和圖文詳細步驟前言一、MySQL主從複制與讀寫分離二、MySQL主從複制搭建過程和讀寫設定MySQL主從複制的幾個同步模式:總結

前言