MySQL 讀寫分離的概述

MySQL 作為目前世界上使用最廣泛的免費資料庫，相信所有從事系統運維的工程師都一定接觸過，但在實際的生產環境中，由單臺 MySQL 作為獨立的資料庫是完全不能滿足實際需求的，無論是在安全性，高可用性以及高并發等各個方面，

因此，一般來說都是通過主從復制（Master-Slave）的方式來同步資料，再通過讀寫分離（MySQL-Proxy/Amoeba）來提升資料庫的并發負載能力，這樣的方案來進行部署與實施的，

讀寫分離作業原理

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

資料內部交換程序：

為什么要讀寫分離

1. 面對越來越大的訪問壓力，單臺的服務器的性能成為瓶頸，需要分擔負載
2. 主從只負責各自的寫和讀，極大程度的緩解 X（寫）鎖和 S（讀）鎖爭用
3. 從庫可配置 myisam 引擎，提升查詢性能以及節約系統開銷
4. 增加冗余，提高可用性

實作讀寫分離的方式

一般有兩種方式實作

1. 應用程式層實作，網站的程式實作
   應用程式層實作指的是在應用程式內部及連接器中實作讀寫分離
   
   優點：
   應用程式內部實作讀寫分離，安裝既可以使用；
   減少一定部署難度；
   訪問壓力在一定級別以下，性能很好，
   缺點：
   架構一旦調整，代碼要跟著變；
   難以實作高級應用，如自動分庫，分表；
   無法適用大型應用場景，

2. 中間件層實作：
   中間件層實作是指在外部中間件程式實作讀寫分離

常見的中間件程式

1. Cobar：
   阿里巴巴 B2B 開發的關系型分布式系統，管理將近 3000 個 MySQL 實體， 在阿里經受住了考驗，后面由于作者的走開的原因 cobar 沒有人維護 了，阿里也開發了 tddl 替代 cobar，
2. MyCAT：
   社區愛好者在阿里 cobar 基礎上進行二次開發，解決了 cobar 當時存在的一些問題，并且加入了許多新的功能在其中，目前 MyCAT 社區活躍度很高，已經有一些公司在使用 MyCAT，總體來說支持度比
   較高，也會一直維護下去，
3. OneProxy：
   資料庫界大牛，前支付寶資料庫團隊領導樓總開發，基于 mysql 官方 的 proxy 思想利用 c 進行開發的，OneProxy 是一款商業收費的中間件，樓總舍去了一些功能點，專注在性能和穩定性上，有人測驗過說在高并發下很穩定，
4. Vitess：
   這個中間件是 Youtube 生產在使用的，但是架構很復雜， 與以往中間件不同，使用 Vitess 應用改動比較大，要使用他提供語言的 API 介面，我們可以借鑒他其中的一些設計思想，
5. Kingshard：
   Kingshard 是前 360Atlas 中間件開發團隊的陳菲利用業余時間 用 go 語言開發的，目前參與開發的人員有 3 個左右， 目前來看還不是成熟可以使用的產品，需要在不斷完善，
6. Atlas：
   360 團隊基于 mysql proxy 把 lua 用 C 改寫，原有版本是支持分表， 目前已經放出了分庫分表版本，在網上看到一些朋友經常說在高并發下會經常掛掉，如果大家要使用需要提前做好測驗，
7. MaxScale 與 MySQL Route：
   這兩個中間件都算是官方的，MaxScale 是 mariadb (MySQL 原作者維護的一個版本)研發的，目前版本不支持分庫分表，MySQL Route 是現在 MySQL 官方 Oracle 公司發布出來的一個中間件，

優點：
架構設計更靈活
可以在程式上實作一些高級控制，如：透明化水平拆分，failover，監控
可以依靠技術手段提高 mysql 性能
對業務代碼的影響小，同時也安全

缺點：
需要一定的開發運維團隊的支持，

什么是 MyCAT

一個徹底開源的，面向企業應用開發的大資料庫集群；
支持事務、ACID、可以替代 MySQL 的加強版資料庫；
一個可以視為 MySQL 集群的企業級資料庫，用來替代昂貴的 Oracle 集群；
一個融合記憶體快取技術、NoSQL 技術、HDFS 大資料的新型 SQL Server；
結合傳統資料庫和新型分布式資料倉庫的新一代企業級資料庫產品；
一個新穎的資料庫中間件產品，

MyCat 服務安裝與配置

MyCat 有提供編譯好的安裝包，支持 Windows、Linux、Mac、Solaris 等系統上安裝與運行，官方下載主頁 http://www.mycat.org.cn/

架構

192.168.1.63 mycat
192.168.1.64 主
192.168.1.65 從

Mycat 需要安裝 JDK 1.7 或者以上版：
第一步：下載 jdk-8u191-linux-x64.tar.gz 檔案

[root@xuegod63 local]# wget http://download.oracle.com/otn-pub/java/jdk/8u191-
b12/2787e4a523244c269598db4e85c51e0c/jdk-8u191-linux-x64.tar.gz 
#如果在Linux 上下載的程式包有問題，需要把鏈接復制到 Windows 上下載再上傳到 Linux，

第二步：新建/usr/java 檔案夾，將 jdk-8u191-linux-x64.tar.gz 解壓到該目錄中

[root@xuegod63 local]# mkdir /usr/java
[root@xuegod63 local]# tar xf jdk-8u191-linux-x64.tar.gz -C /usr/java/

第三步：配置環境變數 在/etc/profile 底部加入如下內容：

[root@xuegod63 local]# vim /etc/profile.d/java.sh #/etc/profile.d/目錄下創建
java.sh 檔案并定入如下內容
JAVA_HOME=/usr/java/jdk1.8.0_191
PATH=$JAVA_HOME/bin:$PATH 
CLASSPATH=$JAVA_HOME/jre/lib/ext:$JAVA_HOME/lib/tools.jar
export PATH JAVA_HOME CLASSPATH
[root@xuegod63 local]# source /etc/profile.d/java.sh #使環境變數生效
[root@xuegod63 local]# java -version#查看 java 版本
java version "1.8.0_191"
Java(TM) SE Runtime Environment (build 1.8.0_191-b12)
Java HotSpot(TM) 64-Bit Server VM (build 25.191-b12, mixed mode)

安裝 mycat
Linux 下可以下載 Mycat-server-xxxxx.linux.tar.gz 解壓在某個目錄下，注意目錄不能有空格，在 Linux(Unix)下，建議放在 usr/local/Mycat 目錄下，如下：

[root@xuegod63 ~]# tar -xf Mycat-server-1.5-RELEASE-0301083012-linux.tar.gz -C 
/usr/local/
[root@xuegod63 local]# ls /usr/local/mycat/

下面修改 MyCAT 用戶密碼(僅供參考)：

[root@xuegod63 ~]# useradd mycat
[root@xuegod63 ~]# passwd mycat #修改時輸入兩次密碼 成功

[root@xuegod63 ~]# chown -R mycat.mycat /usr/local/mycat #修改權限

目錄解釋如下：

bin 程式目錄，存放了 window 版本和 linux 版本，除了提供封裝成服務的版本之外，也提供了nowrap 的 shell 腳本命令，方便大家選擇和修改，進入到 bin 目錄：

Linux 下運行：./mycat console,首先要 chmod +x *

注：mycat 支持的命令{ console | start | stop | restart | status | dump }

conf 目錄下存放組態檔，server.xml 是 Mycat 服務器引數調整和用戶授權的組態檔，schema.xml 是邏輯庫定義和表以及分片定義的組態檔，rule.xml 是分片規則的組態檔，分片規則的具體一些引數資訊單獨存放為檔案，也在這個目錄下，組態檔修改，需要重啟 Mycat 生效，

lib 目錄下主要存放 mycat 依賴的一些 jar 檔案，

日志存放在 logs/mycat.log 中，每天一個檔案，日志的配置是在 conf/log4j.xml 中，根據自己的需要，可以調整輸出級別為 debug，在 debug 級別下，會輸出更多的資訊，方便排查問題，

MyCat 服務啟動與啟動設定

MyCAT 在 Linux 中部署啟動時，首先需要在 Linux 系統的環境變數中配置 MYCAT_HOME，操作方式如下：

[root@xuegod63 local]# vim /etc/profile.d/mycat.sh #在/etc/profile.d 目錄下創建
mycat.sh 檔案，并寫入如下，
MYCAT_HOME=/usr/local/mycat PATH=$MYCAT_HOME/bin:$PATH
[root@xuegod63 local]# source /etc/profile.d/mycat.sh #使環境變數生效

如果是在多臺 Linux 系統中組建的 MyCAT 集群，那需要在 MyCAT Server 所在的服務器上配置對其他 IP 和主機名的映射，配置方式如下：

vi /etc/hosts 

例如：我有 3 臺機器，配置如下：
IP 主機名：

192.168.1.63 xuegod63.cn 
192.168.1.64 xuegod64.cn 
192.168.1.65 xuegod65.cn

編輯完后，保存檔案，

mycat 的用戶賬號和授權資訊是在 conf/server.xml 檔案中配置

[root@xuegod63 local]# vim /usr/local/mycat/conf/server.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://org.opencloudb/">
 <system>
 <property name="defaultSqlParser">druidparser</property>
 </system>
 <!--以下設定為應用訪問帳號權限 -->
 34 行 <user name="root"> #定義管理員用戶，也就是連接 Mycat 的用戶名
 <property name="password">123456</property> #密碼
 <property name="schemas">ha</property> #定義一個邏輯庫，與schema 組態檔對應
 </user>
 <!--以下設定為應用只讀帳號權限 -->
 <user name="user">
 <property name="password">user</property>
 <property name="schemas">ha</property>
 <property name="readOnly">true</property>
 </user>
</mycat:server>

編輯 MyCAT 的組態檔 schema.xml，關于 dataHost 的配置資訊如下：

[root@xuegod63 local]# mv /usr/local/mycat/conf/schema.xml 
/usr/local/mycat/conf/schema.xml.bak #備份原檔案，
[root@xuegod63 local]# vim /usr/local/mycat/conf/schema.xml 新建
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://org.opencloudb/">
 <schema name="ha" checkSQLschema="false" sqlMaxLimit="100" 
dataNode='dn1'>
 </schema>
 <dataNode name="dn1" dataHost="dthost" database="ha"/>
 <dataHost name="dthost" maxCon="500" minCon="10" balance="1" 
writeType="0" dbType="mysql" dbDriver="native" switchType="-1" 
slaveThreshold="100">
 <heartbeat>select user()</heartbeat>
 <writeHost host="xuegod64.cn" url="192.168.1.64:3306" user="mycat" 
password="123456">
 </writeHost>
 <writeHost host="xuegod65.cn" url="192.168.1.65:3306" user="mycat" 
password="123456">
 </writeHost>
 </dataHost>
</mycat:schema>

綠色 ha 為物理存在的資料庫名
注解：
schema 標簽用于定義 MyCat 實體中的邏輯庫，name:后面就是邏輯庫名 MyCat 可以有多個邏輯庫，每個邏輯庫都有自己的相關配置，可以使用 schema 標簽來劃分這些不同的邏輯庫，

checkSQLschema 這個屬性默認就是 false，官方檔案的意思就是是否去掉表前面的資料庫的名稱，”select * from db1.testtable” ，設定為 true 就會去掉 db1，但是如果 db1 的名稱不是schema 的名稱，那么也不會被去掉，因此官方建議不要使用這種語法，同時默認設定為 false，

sqlMaxLimit 當該值設定為某個數值時，每條執行的 SQL 陳述句，如果沒有加上 limit 陳述句，MyCat 也會自動的加上所對應的值，例如設定值為 100，執行”select * from test_table”,則效果為
“selelct * from test_table limit 100”.

dataNode 標簽定義了 MyCat 中的資料節點，也就是我們通常說所的資料分片，一個dataNode 標簽就是一個獨立的資料分片.

dataHost 該標簽定義了具體的資料庫實體、讀寫分離配置和心跳陳述句

heartbeat 標簽指明用于和后端資料庫進行心跳檢查的陳述句，

writeHost /readHost 這兩個標簽都指定后端資料庫的相關配置，用于實體化后端連接池，唯一不同的是，writeHost 指定寫實體、readHost 指定讀實體， 在一個 dataHost 內可以定義多個writeHost 和 readHost，但是，如果 writeHost 指定的后端資料庫宕機，那么這個 writeHost 系結的所有 readHost 都將不可用，另一方面，由于這個 writeHost 宕機，系統會自動的檢測到，并切換到備用的 writeHost 上去，這兩個標簽的屬性相同

有兩個引數需要注意，balance 和 switchType，其中，balance 指的負載均衡型別，目前的取值
有 4 種：

1. balance=“0”, 不開啟讀寫分離機制，所有讀操作都發送到當前可用的 writeHost 上；
2. balance=“1”，全部的 readHost 與 stand by writeHost 參與 select 陳述句的負載均衡，簡單的
   說，當雙主雙從模式(M1->S1，M2->S2，并且 M1 與 M2 互為主備)，正常情況下，M2、S1、S2 都
   參與 select 陳述句的負載均衡；
3. balance=“2”，所有讀操作都隨機的在 writeHost、readhost 上分發；
4. balance=“3”，所有讀請求隨機的分發到 wiriterHost 對應的 readhost 執行，writerHost 不
   負擔讀壓力，

switchType 指的是切換的模式，目前的取值也有 4 種：

1. switchType=’-1’ 表示不自動切換；
2. switchType=‘1’ 默認值，表示自動切換；
3. switchType=‘2’ 基于 MySQL 主從同步的狀態決定是否切換,心跳陳述句為 show slave status；
4. switchType='3’基于 MySQL galary cluster 的切換機制（適合集群）（1.4.1），心跳陳述句為show status like ‘wsrep%’，

經過以上兩個步驟的配置，就可以到/usr/local/mycat/bin 目錄下執行 ./mycat start，即可啟動mycat 服務！

[root@xuegod63 local]# /usr/local/mycat/bin/mycat start
[root@xuegod63 ~]# cat /usr/local/mycat/logs/wrapper.log #查看日志，啟動成功

配置 MySQL 主從

安裝 mysql
上傳 mysql-5.7.tar.gz 到 xuegod64

[root@xuegod64 ~]# scp mysql-5.7.tar.gz root@192.168.1.65:/root/
[root@xuegod64 ~]# tar xf mysql-5.7.tar.gz
[root@xuegod64 ~]# yum install -y ./mysql*.rpm

關閉密碼強度審計插件

[root@xuegod64 ~]# systemctl start mysqld
[root@xuegod64 ~]# echo "validate-password=OFF">> /etc/my.cnf
[root@xuegod64 ~]# systemctl restart mysqld

修改 root 用戶密碼

[root@xuegod64 ~]# grep "password" /var/log/mysqld.log
[root@xuegod64 ~]# mysql -uroot -p'-NEjo1gbPllh'
mysql> set password for root@localhost = password('123456');
mysql> exit

另一臺機器

[root@xuegod65 ~]# tar xf mysql-5.7.tar.gz
[root@xuegod65 ~]# yum install -y ./mysql*.rpm

關閉密碼強度審計插件

[root@xuegod65 ~]# systemctl start mysqld
[root@xuegod65 ~]# echo "validate-password=OFF">> /etc/my.cnf
[root@xuegod65 ~]# systemctl restart mysqld

修改 root 用戶密碼

[root@xuegod65~]# grep "password" /var/log/mysqld.log
[root@xuegod65 ~]# mysql -uroot -p'%OrrfGwyM6tS'
mysql> set password for root@localhost = password('123456');
mysql> exit

創建資料庫和測驗資料

[root@xuegod64 ~]# mysql -uroot -p123456
mysql> create database ha;
mysql> use ha;
mysql> create table test(id int,name varchar(20));
mysql> insert into test values(1,'man');
[root@xuegod64 ~]# mysqldump -uroot -p123456 -B ha >HA.sql #可以匯出資料庫

將匯出的資料庫傳給從服務器

[root@xuegod64 ~]# scp HA.sql root@192.168.1.65:/root/

xuegod65 從服務上操作：
匯入資料庫和表，用于實作讀操作：

[root@xuegod65 ~]# mysql -uroot -p123456<HA.sql
[root@xuegod64 ~]# vim /etc/my.cnf
log-bin=mysql-bin-master #啟用二進制日志
server-id=1 #本機資料庫 ID 標示
binlog-do-db=ha #可以被從服務器復制的庫, 二進制需要同步的資料庫名
binlog-ignore-db=mysql #不可以被從服務器復制的庫
[root@xuegod64 ~]# systemctl restart mysqld
[root@xuegod64 ~]# mysql -uroot -p'123456'

授權給 mycat 登陸資料庫使用的帳號以及主從復制賬號

mysql> GRANT ALL PRIVILEGES ON *.* TO 'mycat'@"%" IDENTIFIED BY "123456"; 
mysql> grant replication slave on *.* to slave@"192.168.1.%" identified by "123456";
mysql> exit
[root@xuegod65 ~]# vim /etc/my.cnf
log-bin=mysql-bin-slave #啟用二進制日志
server-id=2 #本機資料庫 ID 標示
binlog-do-db=ha #可以被從服務器復制的庫, 二進制需要同步的資料庫名
binlog-ignore-db=mysql #不可以被從服務器復制的庫
[root@xuegod65 ~]# systemctl restart mysqld
[root@xuegod65 ~]# mysql -uroot -p'123456'

授權給 mycat 登陸資料庫使用的帳號以及主從復制賬號

mysql> GRANT ALL PRIVILEGES ON *.* TO 'mycat'@"%" IDENTIFIED BY "123456";
mysql> grant replication slave on *.* to slave@"192.168.1.%" identified by "123456";
mysql> change master to 
master_host='192.168.1.64',master_user='slave',master_password='123456';
mysql> start slave; #啟動 slave
mysql> show slave status\G #查看狀態 ，有兩個 yes 主從同步成功！
mysql> exit

模擬 slave 故障：從服務器掛掉了

[root@xuegod65 ~]# systemctl stop mysqld

在客戶端上測驗讀寫

[root@xuegod63 ~]# mysql -uroot -p123456 -h 192.168.1.63 -P8066 
mysql> use ha;
mysql> select * from test;

說明讀操作的路由切換到 master 上了，對外沒有任何影響！

[root@xuegod65 ~]# systemctl start mysqld

模擬 master 故障：主服務器掛掉了

[root@xuegod64 ~]# systemctl stop mysqld

在客戶端上測驗讀寫

[root@xuegod63 ~]# mysql -uroot -p123456 -h 192.168.1.63 -P8066
mysql> use ha;
mysql> create table test1(id int);
ERROR 1184 (HY000): 拒絕連接 #主資料庫停止了，是無法寫操作的，但不影響讀.
mysql> select * from test;

[root@xuegod64 ~]# systemctl start mysqld

只讀用戶登錄測驗

mysql -u user -puser -h 192.168.1.201 -P8066
mysql> insert into test values(5,'feng');
ERROR 1495 (HY000): User readonly
mysql> select * from ha.test;
+------+--------+
| id | name |
+------+--------+
| 1 | cd |

實戰節點宕機后自動切換 Slave 節點

配置 MM 模式主從復制環境
啟用 65 為 64 的主

[root@xuegod64 ~]# mysql -uroot -p'123456'
mysql> change master to 
master_host='192.168.1.65',master_user='slave',master_password='123456';
mysql> start slave; #啟動 slave
mysql> show slave status\G #查看狀態 ，有兩個 yes 主從同步成功！
mysql> exit

mycat 組態檔優化調整，

[root@xuegod63 ~]# vim /usr/local/mycat/conf/schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://org.opencloudb/">
 <schema name="ha" checkSQLschema="false" sqlMaxLimit="100" 
dataNode='dn1'>
 </schema>
 <dataNode name="dn1" dataHost="dthost" database="ha"/>
 <dataHost name="dthost" maxCon="500" minCon="10" balance="1" 
writeType="0" dbType="mysql" dbDriver="native" switchType="1"
slaveThreshold="100">
 <heartbeat>select user()</heartbeat>
 <writeHost host="xuegod64.cn" url="192.168.1.64:3306" user="mycat" 
password="123456">
 </writeHost>
 <writeHost host="xuegod65.cn" url="192.168.1.65:3306" user="mycat" 
password="123456">
 </writeHost>
 </dataHost>
</mycat:schema>

我們前面配置 switchType="-1"關閉了自動切換，但是我們在主從環境下應該避免在從節點寫入資料，否則會造成主從資料庫資料不一致， 如果我們使用的是 MM 模式則主節點發生故障后會自動切換至備用節點，此時不影響資料寫入，

[root@xuegod63 ~]# /usr/local/mycat/bin/mycat restart 
[root@xuegod64 ~]# systemctl stop mysqld
[root@xuegod63 ~]# mysql -uroot -p123456 -h 192.168.1.63 -P8066
MySQL [(none)]> insert into ha.test values(666,'mk');
MySQL [(none)]> select * from ha.test;
+------+---------+
| id | name |
+------+---------+
| 1 | man |
| 666 | mk |

停主節點：

[root@xuegod64 ~]# systemctl stop mysqld
Mycat 端繼續插入
mysql> insert into test values(5,'feng');

恢復主節點

[root@xuegod64 ~]# systemctl start mysqld
mysql -uroot -p123456
select * from ha.test;

關于使用方面的建議，在一些 CMS（內容管理系統）系統中底層的資料庫代碼已經經過封裝，此時使用 mycat 是不可取的，因為封裝后的 sql 陳述句會和 mycat 不兼容，建議是程式開發時應考慮連接mysql 的方式和 mycat 是相互兼容的，或者選擇其他中間件比如 mysql-proxy，

                                             想要獲取原檔案和學習視頻加筆記
                                               添加我們小姐姐一 一獲取哦！