一、MySQL 主從復制原理

1、MySQL 支持的復制型別

基于陳述句的復制（STATEMENT）：在主服務器上執行的 SQL 陳述句，在從服務器上執行同樣的陳述句，MySQL 默認采用基于陳述句的復制，效率比較高，
基于行的復制（ROW）：把改變的內容復制過去，而不是把命令在從服務器上執行一遍，
混合型別的復制（MIXED）：默認采用基于陳述句的復制，一旦發現基于陳述句無法精確復制時，就會采用基于行的復制，

2、MySQL主從復制的作業程序

主MySQL服務器：Master
從MySQL服務器：Slave

1）Master 服務器保存記錄到二進制日志
在每個事務更新資料完成之前，Master 服務器在二進制日志（Binary log）記錄這些改變，寫入二進制日志完成后，Master 服務器通知存盤引擎提交事務，
2）Slave 服務器復制 Master 服務器的日志
Slave 服務器將 Master 服務器的二進制日志復制到其中繼日志（Relay log），
首先 Slave 服務器開始一個作業執行緒（I/O），I/O執行緒在 Master 服務器上打開一個普通的連接，然后開始 Binlog dump process，
Binlog dump process 從 Master 服務器的二進制日志中讀取事件，如果 Slave 服務器已經跟上 Master 服務器，它會睡眠并等待 Master 服務器產生新的事件，I/O執行緒將這些事件寫入中繼日志，
3）Slave 服務器重放復制過來的日志
SQL slave thread（SQL從執行緒）處理該程序的最后一步，SQL執行緒從中繼日志讀取事件，并重放其中的事件而更新 Slave 服務器的資料，使其與 Master 服務器中的資料一致，只要該執行緒與 I/O 執行緒保持一致，中繼日志通常會位于 OS 快取中，所以中繼日志的開銷很小，

二、MySQL讀寫分離

1、讀寫分離的概念

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

2、讀寫分離存在意義

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

3、什么時候要讀寫分離

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

4、MySQL 讀寫分離原理

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

5、常見的 MySQL 讀寫分離

1）基于程式代碼內部實作
在代碼中根據 select、insert 進行路由分類，這類方法也是目前生產環境應用最廣泛的，
優缺點：
優點是性能較好，因為在程式代碼中實作，不需要增加額外的設備為硬體開支；
缺點是需要開發人員來實作，運維人員無從下手，
并不是所有的應用都適合在程式代碼中實作讀寫分離，像一些大型復雜的Java應用，如果在程式代碼中實作讀寫分離對代碼改動就較大，
2）基于中間代理層實作
代理一般位于客戶端和服務器之間，代理服務器接到客戶端請求后通過判斷后轉發到后端資料庫，有以下代表性程式：
MySQL-Proxy：MySQL-Proxy 為 MySQL 開源專案，通過其自帶的 lua 腳本進行SQL 判斷，
Atlas：是由奇虎360的Web平臺部基礎架構團隊開發維護的一個基于MySQL協議的資料中間層專案，它是在mysql-proxy 0.8.2版本的基礎上，對其進行了優化，增加了一些新的功能特性，360內部使用Atlas運行的mysql業務，每天承載的讀寫請求數達幾十億條，支持事物以及存盤程序，
Amoeba：由陳思儒開發，作者曾就職于阿里巴巴，該程式由Java語言進行開發，阿里巴巴將其用于生產環境，但是它不支持事務和存盤程序，
由于使用MySQL Proxy需要寫大量的Lua腳本，這些Lua腳本不是現成的，而需要自己撰寫，這對于并不熟悉MySQL Proxy內置變數和MySQL Protocol的人來說是非常困難的，
Amoeba是一個非常容易使用，可移植性非常強的軟體，因此它在生產環境中被廣泛用于資料庫的代理層，

三、MySQL主從復制和讀寫分離實驗部署

實驗思路
第一步：客戶端client訪問代理服務器amoeba
第二步：代理服務器讀寫判斷
寫操作：寫入到主服務器
第三步：主服務器將增刪改寫入自己二進制日志
第四步：從服務器將主服務器的二進制日志同步至自己中繼日志
第五步：從服務器重放中繼日志到資料庫中
讀操作：直接訪問從服務器
最終結果：降低負載，起到負載均衡作用

# 將所有主機的防火墻和安全機制關閉

systemctl stop firewalld
systemctl disable firewalld
setenforce 0

1、搭建 MySQL 主從復制

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

Master服務器：192.168.109.12

yum -y install ntp

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

service ntpd start

Slave1服務器：192.168.109.22
Slave2服務器：192.168.109.33

yum -y install ntp ntpdate

service ntpd start

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

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

2）主服務器的mysql配置

Master服務器：192.168.109.12

vim /etc/my.cnf
server-id = 1
log-bin=master-bin      #添加，主服務器開啟二進制日志
log-slave-updates=true  #添加，允許從服務器更新二進制日志

systemctl restart mysqld

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

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

3）從服務器的mysql配置

Slave1服務器：192.168.109.22
Slave2服務器：192.168.109.33

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

systemctl restart mysqld

mysql -u root -p
#配置同步，注意 master_log_file 和 master_log_pos 的值要與Master查詢的一致，
change master to master_host='192.168.109.12' , master_user='myslave',master_password='123123',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行程

一般 Slave_IO_Running: No 的可能性：

• 網路不通
• my.cnf配置有問題
• 密碼、file檔案名、pos偏移量不對
• 防火墻沒有關閉

4）驗證主從復制效果

create database test;
show databases;

2、搭建 MySQL 讀寫分離

Amoeba服務器：192.168.109.3

安裝 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

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

在主從服務器的mysql上授權

Master服務器：192.168.109.12
Slave1服務器：192.168.109.22
Slave2服務器：192.168.109.33

# 先在Master、Slave1、Slave2 的mysql上開放權限給 Amoeba 訪問

grant all on *.* to 'test'@'192.168.109.%' identified by '123123';

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

Amoeba服務器：192.168.109.3

（1）修改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>

（2）修改資料庫組態檔

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">123123</property>
#---------45修改，設定主服務器的名Master------------------
<dbServer name="master"  parent="abstractServer">
#---------48修改，設定主服務器的地址----------------------
<property name="ipAddress">192.168.109.12</property>
#---------52修改，設定從服務器的名slave1-----------------
<dbServer name="slave1"  parent="abstractServer">
#---------55修改，設定從服務器1的地址---------------------
<property name="ipAddress">192.168.109.22</property>
#---------58復制上面6行粘貼，設定從服務器2的名slave2和地址---
<dbServer name="slave2"  parent="abstractServer">
<property name="ipAddress">192.168.109.33</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

測驗讀寫分離

客戶端：客戶端：192.168.109.5

# 使用yum快速安裝MySQL虛擬客戶端
yum install -y mysql mysql-server

mysql -u amoeba -p123456 -h 192.168.109.3 -P8066

通過amoeba服務器代理訪問mysql ，在通過客戶端連接mysql后寫入的資料只有主服務會記錄，然后同步給從服務器
在主服務器上

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

兩臺從服務器上

stop slave;			#關閉同步
use test;

從服務器slave1：192.168.109.22

# 添加資料

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

從資料庫slave2：192.168.109.33

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

在主服務器上

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

在客戶端服務器上

use db_test;
select * from test;		//客戶端會分別向slave1和slave2讀取資料，顯示的只有在兩個從服務器上添加的資料，沒有在主服務器上添加的資料

insert into test values('4','qianqi','this_is_client');		//只有主服務器上有此資料