MyCAT+MySQL 讀寫分離部署

單一的 MySQL 伺服器一旦當機，會造成單點故障，整個系統無法運轉，是以要有主庫(Master)和從庫(Slave備庫)兩份，主庫故障時自動切換至備庫。另外，大多企業應用是寫的少(INSERT/DELETE/UPDATE)查的多(SELECT)，是以還要實作讀寫分離，以便根據讀寫比例分别擴充讀、寫庫節點的數目。這麼複雜的切庫與資料分流，如果在應用内部實作，會很繁瑣，而且耦合度高，可采用資料庫中間件實作這些功能。本文是使用 MyCAT 實作雙主(寫)雙從(讀) MySQL 的部署。

作者：王克鋒

出處：https://kefeng.wang/2016/12/22/mycat-mysql/

版權：自由轉載-非商用-非衍生-保持署名，轉載請标明作者和出處。

1 MySQL 開源中間件比較

1.1 TDDL(alibaba)

不是獨立運作的中間件，以 JAR 包形式供給應用調用；社群已停滞。

1.2 Amoeba(alibaba，陳思儒)

獨立運作的中間件，介于應用和MySQL叢集之間，後端使用JDBC Driver，是以更通用；社群已停滞。

1.3 Cobar(alibaba)

是 Amoeba 的進化版本，後端JDBC Driver改為原生的 MySQL 協定，不通用，但更強大（如主備切換）；社群已停滞(2013年)。

1.4 MyCAT

是 Cobar 的進化版本，後端由 BIO 改為 NIO；社群活躍。

号稱是國内最活躍的、性能最好的開源資料庫中間件！

使用者包括中國電信、中國聯通。

2 MyCAT 技術要點

官網: http://mycat.io/

電子書: http://mycat.io/document/Mycat_V1.6.0.pdf

紙質書: http://blog.csdn.net/wind520/article/details/53213691

MyCAT 采用 Java 開發，實作 MySQL 公開的二進制協定，将自己僞裝成 MySQL Server；

應用隻需連接配接 MyCAT 執行 SQL，MyCAT 按照配置的政策，分發 SQL 至相應的 MySQL 節點。

2.1 MyCAT 關鍵特性

• 支援SQL92标準
• 支援MySQL、Oracle、DB2、SQL Server、PostgreSQL等DB的常見SQL文法
• 遵守Mysql原生協定，跨語言，跨平台，跨資料庫的通用中間件代理。
• 基于心跳的自動故障切換，支援讀寫分離，支援MySQL主從，以及galera cluster叢集。
• 支援資料的多片自動路由與聚合，支援sum,count,max等常用的聚合函數,支援跨庫分頁。
• 支援單庫内部任意join，支援跨庫2表join，甚至基于caltlet的多表join。
• 支援通過全局表，ER關系的分片政策，實作了高效的多表join查詢。
• 支援全局序列号，解決分布式下的主鍵生成問題。
• 分片規則豐富，插件化開發，易于擴充。
• 強大的web，指令行監控。
• 支援zookeeper協調主從切換、zk序列、配置zk化（1.6）
• 支援庫内分表（1.6）
• 叢集基于ZooKeeper管理，線上更新，擴容，智能優化，大資料處理（2.0開發版）。
• ……

2.2 資料分片（Sharding）方式分類

資料庫概念的分級：Node(host:ip), Schema(database), Table, Record

資料的分片有兩種模式（想象各 Schema/Table 從左至右排列，同表 Record 從上至下排列）：

• 垂直分片：不同 Schema/Table 拆分到不同的 Node，根據 Schema/Table 名稱指定 Node；
• 水準分片：同表的 Record 拆分到多台 Node 中，是根據列值公式結果分散到 Node。

2.3 分片帶來的問題

• 分布式事務；
• 跨節點 JOIN；
• 跨節點合并、排序、分頁；
• 多資料源管理。

2.4 典型的分片規則

• 根據 UserID 取餘: 相同使用者的資料處于同一個庫；
• 根據日期，比如同月或同日的處于同一個庫；
• 其他字段求餘，或者根據取值範圍，處于同一個庫。

3 MySQL 準備 4 個資料庫

關于 MySQL 單機多執行個體，請參照我前面的文章：MySQL 安裝及其單機多執行個體部署

将建立端口号為 33061/33062/33063/33064 的 4 個 MySQL 執行個體。

3.1 停用之前的兩個資料庫

停用 [mysqld], 端口 3306 将讓給 MyCAT 使用(僞裝成原 [mysqld])

删除 [[email protected]]，将改建為雙主雙從 4 個資料庫

sudo systemctl stop mysqld
sudo systemctl disable mysqld
## 暫保留 MySQL 安裝時的 [mysqld] 及其資料檔案
## 暫保留防火牆中的 3306 端口，讓給 MyCAT 使用

sudo systemctl stop [email protected]
sudo systemctl disable [email protected]
sudo rm -rf /var/lib/mysql-slave/ /var/log/mysqld-slave.log ## 删除資料和日志檔案
sudo vim /etc/my.cnf ## 删除 [[email protected]] 一節

sudo vim /etc/sysconfig/iptables ## 删除 --dport 33061 一行
sudo systemctl restart iptables
           

3.2 增加 4 個 [[email protected]] 配置

## sudo vim /etc/my.cnf
[mysqld@master1]
port=
datadir=/var/lib/mysql-master1
socket=/var/lib/mysql-master1/mysql.sock
log-error=/var/log/mysqld-master1.log
explicit_defaults_for_timestamp=true
log_timestamps=SYSTEM

[mysqld@master2]
port=
datadir=/var/lib/mysql-master2
socket=/var/lib/mysql-master2/mysql.sock
log-error=/var/log/mysqld-master2.log
explicit_defaults_for_timestamp=true
log_timestamps=SYSTEM

[mysqld@slave1]
port=
datadir=/var/lib/mysql-slave1
socket=/var/lib/mysql-slave1/mysql.sock
log-error=/var/log/mysqld-slave1.log
explicit_defaults_for_timestamp=true
log_timestamps=SYSTEM

[mysqld@slave2]
port=
datadir=/var/lib/mysql-slave2
socket=/var/lib/mysql-slave2/mysql.sock
log-error=/var/log/mysqld-slave2.log
explicit_defaults_for_timestamp=true
log_timestamps=SYSTEM
           

3.3 啟動 4 個服務

如果建立過一次，現在要重建，則要先删除：

sudo systemctl stop [email protected]{master1,master2,slave1,slave2}
sudo systemctl disable [email protected]{master1,master2,slave1,slave2}
sudo rm -rf /var/lib/mysql-{master1,master2,slave1,slave2}
sudo rm -rf /var/log/mysqld-{master1,master2,slave1,slave2}.log
           

設定為開機自動啟動，并立即啟動：

sudo systemctl enable [email protected]{master1,master2,slave1,slave2}
sudo systemctl start [email protected]{master1,master2,slave1,slave2}
           

3.4 修改 root 密碼

sudo grep "temporary password" /var/log/mysqld-{master1,master2,slave1,slave2}.log # 查詢初始密碼
mysql -uroot -P33061 -p ## 分别執行 33061,33062,33063,33064
MySQL> SET PASSWORD = 'MySQL5.7'; EXIT;
           

4 MySQL 開啟庫間複制

雙主(M1/M2)雙從(S1/S2)，開啟主從複制：M1 => M2/S1/S2; M2 => M1/S1/S2

需要注意的是，多主單從複制，是 MySQL 5.7（2013-04-23）才有的特性(FOR CHANNEL)，是以 MySQL 版本必須是 5.7+。

https://www.percona.com/blog/2013/10/02/mysql-5-7-multi-source-replication/

http://www.oschina.net/translate/mysql-5-7-multi-source-replication

4.1 MySQL 各執行個體增加複制相關配置

## sudo vim /etc/my.cnf
## 其中的 master_info_repository/relay_log_info_repository 用于支援多主單從複制
[[email protected]]
server_id=33061
log_bin=mysql-bin
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=mysql
binlog-ignore-db=sys

[[email protected]]
server_id=33062
log_bin=mysql-bin
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=mysql
binlog-ignore-db=sys

[[email protected]]
server_id=33063
log_bin=mysql-bin
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=mysql
binlog-ignore-db=sys

[[email protected]]
server_id=33064
log_bin=mysql-bin
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=mysql
binlog-ignore-db=sys
           

儲存後重新開機所有 MySQL 服務：

sudo systemctl restart [email protected]{master1,master2,slave1,slave2}
           

4.2 MySQL 開啟複制

4.2.1 設定 M1 => M2/S1/S2

## 注：為示範友善，此處密碼放在指令行，安全級别高的環境應避免此方式。
mysql -uroot -pMySQL5. -P33061 -e "GRANT REPLICATION SLAVE ON *.* to 'master1'@'localhost' IDENTIFIED BY 'Master5.7';"
mysql -uroot -pMySQL5. -P33062 -e "CHANGE MASTER TO master_host='localhost', master_port=33061, master_user='master1', master_password='Master5.7' FOR CHANNEL 'M1';"
mysql -uroot -pMySQL5. -P33063 -e "CHANGE MASTER TO master_host='localhost', master_port=33061, master_user='master1', master_password='Master5.7' FOR CHANNEL 'M1';"
mysql -uroot -pMySQL5. -P33064 -e "CHANGE MASTER TO master_host='localhost', master_port=33061, master_user='master1', master_password='Master5.7' FOR CHANNEL 'M1';"
           

4.2.2 設定 M2 => M1/S1/S2

mysql -uroot -pMySQL5 -P33062 -e "GRANT REPLICATION SLAVE ON *.* to 'master2'@'localhost' IDENTIFIED BY 'Master5.7';"
mysql -uroot -pMySQL5 -P33061 -e "CHANGE MASTER TO master_host='localhost', master_port=33062, master_user='master2', master_password='Master5.7' FOR CHANNEL 'M2';"
mysql -uroot -pMySQL5 -P33063 -e "CHANGE MASTER TO master_host='localhost', master_port=33062, master_user='master2', master_password='Master5.7' FOR CHANNEL 'M2';"
mysql -uroot -pMySQL5 -P33064 -e "CHANGE MASTER TO master_host='localhost', master_port=33062, master_user='master2', master_password='Master5.7' FOR CHANNEL 'M2';"
           

4.2.3 立即啟動複制

相關資料: http://dev.mysql.com/doc/refman/5.7/en/start-slave.html

mysql -uroot -pMySQL5 -P33061 -e "START SLAVE FOR CHANNEL 'M2';"
mysql -uroot -pMySQL5 -P33062 -e "START SLAVE FOR CHANNEL 'M1';"
mysql -uroot -pMySQL5 -P33063 -e "START SLAVE FOR CHANNEL 'M1'; START SLAVE FOR CHANNEL 'M2';"
mysql -uroot -pMySQL5 -P33064 -e "START SLAVE FOR CHANNEL 'M1'; START SLAVE FOR CHANNEL 'M2';"

mysql -uroot -pMySQL5 -P33061 -e "SHOW SLAVE STATUS\G" | grep -E "Master_Host|Master_User|Master_Port|Last_Error"
mysql -uroot -pMySQL5 -P33062 -e "SHOW SLAVE STATUS\G" | grep -E "Master_Host|Master_User|Master_Port|Last_Error"
mysql -uroot -pMySQL5 -P33063 -e "SHOW SLAVE STATUS\G" | grep -E "Master_Host|Master_User|Master_Port|Last_Error"
mysql -uroot -pMySQL5 -P33064 -e "SHOW SLAVE STATUS\G" | grep -E "Master_Host|Master_User|Master_Port|Last_Error"

mysql -uroot -pMySQL5 -P33061 -e "STOP SLAVE FOR CHANNEL 'M2';"
mysql -uroot -pMySQL5 -P33062 -e "STOP SLAVE FOR CHANNEL 'M1';"
mysql -uroot -pMySQL5 -P33063 -e "STOP SLAVE FOR CHANNEL 'M1'; STOP SLAVE FOR CHANNEL 'M2';"
mysql -uroot -pMySQL5 -P33064 -e "STOP SLAVE FOR CHANNEL 'M1'; STOP SLAVE FOR CHANNEL 'M2';"
           

4.2.4 建立測試資料表

在 M1 執行，會自動複制至 M2/S1/S2。

mysql -uroot -pMySQL5 -P33061 -e "CREATE DATABASE mydb; CREATE TABLE mydb.mytab(id INT AUTO_INCREMENT, port INT, PRIMARY KEY(id));"
mysql -uroot -pMySQL5 -P33061 -e "DESC mydb.mytab;"
mysql -uroot -pMySQL5 -P33062 -e "DESC mydb.mytab;"
mysql -uroot -pMySQL5 -P33063 -e "DESC mydb.mytab;"
mysql -uroot -pMySQL5 -P33064 -e "DESC mydb.mytab;"
           

5 MyCAT 部署

5.1 安裝

https://github.com/MyCATApache/Mycat-download

https://github.com/MyCATApache/Mycat-download/tree/master/1.5-RELEASE

## 11M, 如果 wget 下載下傳慢，可在 Windows 下載下傳
wget https://github.com/MyCATApache/Mycat-download/raw/master/-RELEASE/Mycat-server--RELEASE--linux.tar.gz
sudo tar -zxf Mycat-server--RELEASE--linux.tar.gz -C /opt
export MYCAT_HOME=/opt/mycat
           

5.2 配置 JVM 參數

## sudo vim $MYCAT_HOME/conf/wrapper.conf
wrapper.java.additional=-XX:MaxDirectMemorySize=M
wrapper.java.additional=-Xmx128M
wrapper.java.additional=-Xms128M
           

5.3 配置讀寫分離規則

雙主雙從讀寫分離: balance=”1” writeType=”0” switchType=”1”

(1)dataHost.balance(READ 目标庫)

balance=”0”, READ => 目前 writeHost, 不開啟讀寫分離機制；

balance=”1”, READ => readHost|閑置的 writeHost

在雙主雙從模式下(M1 <–> M2, M1 -> S1, M2 -> S2)，M1 正常時，M2/S1/S2 都參與 SELECT 的負載均衡；

balance=”2”, READ => readhost|所有的 writeHost;

balance=”3”, READ => readhost;

(2)dataHost.writeType(WRITE 目标庫)

writeType=”0”, WRITE => writeHost

writeType=”1”, WRITE => readHost

writeType=”2”, WRITE => writeHost|readhost

(3)dataHost.switchType(切換的模式)

switchType=”-1”, 不自動切換

switchType= “1”, 自動切換(預設值)

switchType= “2”, 基于MySQL主從同步的狀态決定是否切換,心跳語句為 show slave status

switchType= “3”, 基于MySQL galary cluster的切換機制（适合叢集）（1.4.1），心跳語句為 show status like ‘wsrep%’。

## sudo vim $MYCAT_HOME/conf/schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://org.opencloudb/" >

    <schema name="mydb" checkSQLschema="false" sqlMaxLimit="100" dataNode="myNode" />

    <dataNode name="myNode" dataHost="myHost" database="mydb" />

    <dataHost name="myHost" maxCon="1000" minCon="10" dbType="mysql"
        dbDriver="native" balance="1" writeType="0" switchType="1">
        <heartbeat>SELECT 1</heartbeat>
        <writeHost host="master1" url="localhost:33061" user="root" password="MySQL5.7">
            <readHost host="slave1" url="localhost:33063" user="root" password="MySQL5.7" />
        </writeHost>
        <writeHost host="master2" url="localhost:33062" user="root" password="MySQL5.7">
            <readHost host="slave2" url="localhost:33064" user="root" password="MySQL5.7" />
        </writeHost>
    </dataHost>
</mycat:schema>
           

5.4 配置 MyCAT 代理

• 指定 MyCAT 端口：3306 為應用端口，3308 為管理端口；
• 設定用戶端使用者的帳号/密碼：admin 有讀寫權限，guest 隻有讀權限。

## sudo vim $MYCAT_HOME/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>
        <property name="serverPort">3306</property>
        <property name="managerPort">3308</property>
    </system>

    <user name="admin">
        <property name="password">admin123</property>
        <property name="schemas">mydb</property>
    </user>

    <user name="guest">
        <property name="password"></property>
        <property name="schemas">mydb</property>
        <property name="readOnly">true</property>
    </user>
</mycat:server>
           

5.5 配置為 systemd 服務

## sudo vim /usr/lib/systemd/system/mycat.service
## 注意: ExecStart/ExecStop 取值兩側不要用引号
[Unit]
Description=MyCAT Service
After=syslog.target network.target

[Service]
Type=forking
ExecStart=/opt/mycat/bin/mycat start
ExecStop=/opt/mycat/bin/mycat stop

[Install]
WantedBy=multi-user.target
           

設定開機自動啟動、并立即啟動：

sudo systemctl enable mycat # 設定為開機自動啟動
sudo systemctl start mycat # 立即啟動

# sudo systemctl stop mycat # 立即停止
# sudo systemctl disable mycat # 取消開機自動啟動
           

6 驗證部署

6.1 驗證讀資料

連續 100 次連接配接 MyCAT 查詢，根據 @@port 得知 MyCAT 将指令傳遞哪個 MySQL 結點執行。

6.1.1 MySQL 所有結點都在運作的情形

現象：讀資料時，是均衡地從目前存活的“閑置的 writeHost”(M2)和“readHost”(S1/S2) 中讀取。

6.1.2 MySQL 單個結點 S2 當機的情形

現象：讀資料時，是均衡地從目前存活的“閑置的 writeHost”(M2)和“readHost”(S1) 中讀取。

sudo systemctl stop [email protected]
sudo netstat -natp | grep LISTEN | grep mysqld
for i in {..}; do mysql -sN -uguest -e "SELECT @@port"; done
sudo systemctl start [email protected]
           

6.2 驗證寫資料

6.2.1 MySQL 所有結點都在運作

現象：所有 SQL 都傳遞給 writeHost M1 執行。

mysql -sN -uadmin -padmin123 -Dmydb -e "TRUNCATE TABLE mydb.mytab;"
for i in {}; do mysql -sN -uadmin -padmin123 -e "INSERT INTO mydb.mytab(port) VALUES(@@port);"; done
mysql -sN -uguest -e "SELECT port,COUNT(1) FROM mydb.mytab GROUP BY port ORDER BY port;"
           

6.2.2 MySQL 單個結點 M1 當機的情形

現象：所有 SQL 都傳遞給 writeHost M2 執行。

mysql -sN -uadmin -padmin123 -Dmydb -e "TRUNCATE TABLE mydb.mytab;"
mysql -sN -uroot -pMySQL5 -P33061 -e "SELECT @@port, COUNT(*) FROM mydb.mytab;"
mysql -sN -uroot -pMySQL5 -P33062 -e "SELECT @@port, COUNT(*) FROM mydb.mytab;"
mysql -sN -uroot -pMySQL5 -P33063 -e "SELECT @@port, COUNT(*) FROM mydb.mytab;"
mysql -sN -uroot -pMySQL5 -P33064 -e "SELECT @@port, COUNT(*) FROM mydb.mytab;"

sudo systemctl stop [email protected]
for i in {}; do mysql -sN -uadmin -padmin123 -e "INSERT INTO mydb.mytab(port) VALUES(@@port);"; done
mysql -sN -uguest -e "SELECT port,COUNT(1) FROM mydb.mytab GROUP BY port ORDER BY port;"
           

6.2.3 MySQL 單個結點 M1 恢複的情形

現象：當機期間的資料變更已經同步至 M1。

sudo systemctl start [email protected]
mysql -sN -uroot -pMySQL5 -P33061 -e "SELECT @@port, COUNT(*) FROM mydb.mytab;"
mysql -sN -uroot -pMySQL5 -P33062 -e "SELECT @@port, COUNT(*) FROM mydb.mytab;"
mysql -sN -uroot -pMySQL5 -P33063 -e "SELECT @@port, COUNT(*) FROM mydb.mytab;"
mysql -sN -uroot -pMySQL5 -P33064 -e "SELECT @@port, COUNT(*) FROM mydb.mytab;"