MyCAT+MySQL 读写分离部署

朱雀 2022-07-13 11:49 301阅读 0赞

单一的 MySQL 服务器一旦宕机，会造成单点故障，整个系统无法运转，所以要有主库(Master)和从库(Slave备库)两份，主库故障时自动切换至备库。另外，大多企业应用是写的少(INSERT/DELETE/UPDATE)查的多(SELECT)，所以还要实现读写分离，以便根据读写比例分别扩展读、写库节点的数目。这么复杂的切库与数据分流，如果在应用内部实现，会很繁琐，而且耦合度高，可采用数据库中间件实现这些功能。本文是使用 MyCAT 实现双主(写)双从(读) MySQL 的部署。  
  
**作者：**王克锋  
**出处：**[https://kefeng.wang/2016/12/22/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]  
电子书: [http://mycat.io/document/Mycat\_V1.6.0.pdf][http_mycat.io_document_Mycat_V1.6.0.pdf]  
纸质书: [http://blog.csdn.net/wind520/article/details/53213691][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 安装及其单机多实例部署][MySQL]  
将创建端口号为 33061/33062/33063/33064 的 4 个 MySQL 实例。

### 3.1 停用之前的两个数据库 ###

停用 \[mysqld\], 端口 3306 将让给 MyCAT 使用(伪装成原 \[mysqld\])  
删除 \[mysqld@slave\]，将改建为双主双从 4 个数据库

sudo systemctl stop mysqld
    sudo systemctl disable mysqld
    ## 暂保留 MySQL 安装时的 [mysqld] 及其数据文件
    ## 暂保留防火墙中的 3306 端口，让给 MyCAT 使用
    
    sudo systemctl stop mysqld@slave
    sudo systemctl disable mysqld@slave
    sudo rm -rf /var/lib/mysql-slave/ /var/log/mysqld-slave.log ## 删除数据和日志文件
    sudo vim /etc/my.cnf ## 删除 [mysqld@slave] 一节
    
    sudo vim /etc/sysconfig/iptables ## 删除 --dport 33061 一行
    sudo systemctl restart iptables

### 3.2 增加 4 个 \[mysqld@xxx\] 配置 ###

## sudo vim /etc/my.cnf
    [mysqld@master1]
    port=33061
    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=33062
    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=33063
    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=33064
    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 mysqld@{master1,master2,slave1,slave2}
    sudo systemctl disable mysqld@{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 mysqld@{master1,master2,slave1,slave2}
    sudo systemctl start mysqld@{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/][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][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 用于支持多主单从复制
    [mysqld@master1]
    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
    
    [mysqld@master2]
    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
    
    [mysqld@slave1]
    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
    
    [mysqld@slave2]
    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 mysqld@{master1,master2,slave1,slave2}

### 4.2 MySQL 开启复制 ###

#### 4.2.1 设置 M1 => M2/S1/S2 ####

## 注：为演示方便，此处密码放在命令行，安全级别高的环境应避免此方式。
    mysql -uroot -pMySQL5.7 -P33061 -e "GRANT REPLICATION SLAVE ON *.* to 'master1'@'localhost' IDENTIFIED BY 'Master5.7';"
    mysql -uroot -pMySQL5.7 -P33062 -e "CHANGE MASTER TO master_host='localhost', master_port=33061, master_user='master1', master_password='Master5.7' FOR CHANNEL 'M1';"
    mysql -uroot -pMySQL5.7 -P33063 -e "CHANGE MASTER TO master_host='localhost', master_port=33061, master_user='master1', master_password='Master5.7' FOR CHANNEL 'M1';"
    mysql -uroot -pMySQL5.7 -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.7 -P33062 -e "GRANT REPLICATION SLAVE ON *.* to 'master2'@'localhost' IDENTIFIED BY 'Master5.7';"
    mysql -uroot -pMySQL5.7 -P33061 -e "CHANGE MASTER TO master_host='localhost', master_port=33062, master_user='master2', master_password='Master5.7' FOR CHANNEL 'M2';"
    mysql -uroot -pMySQL5.7 -P33063 -e "CHANGE MASTER TO master_host='localhost', master_port=33062, master_user='master2', master_password='Master5.7' FOR CHANNEL 'M2';"
    mysql -uroot -pMySQL5.7 -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][http_dev.mysql.com_doc_refman_5.7_en_start-slave.html]

mysql -uroot -pMySQL5.7 -P33061 -e "START SLAVE FOR CHANNEL 'M2';"
    mysql -uroot -pMySQL5.7 -P33062 -e "START SLAVE FOR CHANNEL 'M1';"
    mysql -uroot -pMySQL5.7 -P33063 -e "START SLAVE FOR CHANNEL 'M1'; START SLAVE FOR CHANNEL 'M2';"
    mysql -uroot -pMySQL5.7 -P33064 -e "START SLAVE FOR CHANNEL 'M1'; START SLAVE FOR CHANNEL 'M2';"
    
    mysql -uroot -pMySQL5.7 -P33061 -e "SHOW SLAVE STATUS\G" | grep -E "Master_Host|Master_User|Master_Port|Last_Error"
    mysql -uroot -pMySQL5.7 -P33062 -e "SHOW SLAVE STATUS\G" | grep -E "Master_Host|Master_User|Master_Port|Last_Error"
    mysql -uroot -pMySQL5.7 -P33063 -e "SHOW SLAVE STATUS\G" | grep -E "Master_Host|Master_User|Master_Port|Last_Error"
    mysql -uroot -pMySQL5.7 -P33064 -e "SHOW SLAVE STATUS\G" | grep -E "Master_Host|Master_User|Master_Port|Last_Error"
    
    mysql -uroot -pMySQL5.7 -P33061 -e "STOP SLAVE FOR CHANNEL 'M2';"
    mysql -uroot -pMySQL5.7 -P33062 -e "STOP SLAVE FOR CHANNEL 'M1';"
    mysql -uroot -pMySQL5.7 -P33063 -e "STOP SLAVE FOR CHANNEL 'M1'; STOP SLAVE FOR CHANNEL 'M2';"
    mysql -uroot -pMySQL5.7 -P33064 -e "STOP SLAVE FOR CHANNEL 'M1'; STOP SLAVE FOR CHANNEL 'M2';"

#### 4.2.4 创建测试数据表 ####

在 M1 执行，会自动复制至 M2/S1/S2。

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

## 5 MyCAT 部署 ##

### 5.1 安装 ###

[https://github.com/MyCATApache/Mycat-download][https_github.com_MyCATApache_Mycat-download]  
[https://github.com/MyCATApache/Mycat-download/tree/master/1.5-RELEASE][https_github.com_MyCATApache_Mycat-download_tree_master_1.5-RELEASE]

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

### 5.2 配置 JVM 参数 ###

## sudo vim $MYCAT_HOME/conf/wrapper.conf
    wrapper.java.additional.5=-XX:MaxDirectMemorySize=128M
    wrapper.java.additional.10=-Xmx128M
    wrapper.java.additional.11=-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) 中读取。

for i in {
       1..100}; do mysql -sN -uguest -e "SELECT @@port"; done

#### 6.1.2 MySQL 单个结点 S2 宕机的情形 ####

现象：读数据时，是均衡地从当前存活的“闲置的 writeHost”(M2)和“readHost”(S1) 中读取。

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

### 6.2 验证写数据 ###

#### 6.2.1 MySQL 所有结点都在运行 ####

现象：所有 SQL 都交付给 writeHost M1 执行。

mysql -sN -uadmin -padmin123 -Dmydb -e "TRUNCATE TABLE mydb.mytab;"
    for i in {
       1..100}; 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.7 -P33061 -e "SELECT @@port, COUNT(*) FROM mydb.mytab;"
    mysql -sN -uroot -pMySQL5.7 -P33062 -e "SELECT @@port, COUNT(*) FROM mydb.mytab;"
    mysql -sN -uroot -pMySQL5.7 -P33063 -e "SELECT @@port, COUNT(*) FROM mydb.mytab;"
    mysql -sN -uroot -pMySQL5.7 -P33064 -e "SELECT @@port, COUNT(*) FROM mydb.mytab;"
    
    sudo systemctl stop mysqld@master1
    for i in {
       1..100}; 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 mysqld@master1
    mysql -sN -uroot -pMySQL5.7 -P33061 -e "SELECT @@port, COUNT(*) FROM mydb.mytab;"
    mysql -sN -uroot -pMySQL5.7 -P33062 -e "SELECT @@port, COUNT(*) FROM mydb.mytab;"
    mysql -sN -uroot -pMySQL5.7 -P33063 -e "SELECT @@port, COUNT(*) FROM mydb.mytab;"
    mysql -sN -uroot -pMySQL5.7 -P33064 -e "SELECT @@port, COUNT(*) FROM mydb.mytab;"

[https_kefeng.wang_2016_12_22_mycat-mysql]: https://kefeng.wang/2016/12/22/mycat-mysql/
[-_-_-]: https://creativecommons.org/licenses/by-nc-nd/3.0/deed.zh
[http_mycat.io]: http://mycat.io/
[http_mycat.io_document_Mycat_V1.6.0.pdf]: http://mycat.io/document/Mycat_V1.6.0.pdf
[http_blog.csdn.net_wind520_article_details_53213691]: http://blog.csdn.net/wind520/article/details/53213691
[MySQL]: https://kefeng.wang/2016/12/20/mysql-installation/
[https_www.percona.com_blog_2013_10_02_mysql-5-7-multi-source-replication]: 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]: http://www.oschina.net/translate/mysql-5-7-multi-source-replication
[http_dev.mysql.com_doc_refman_5.7_en_start-slave.html]: http://dev.mysql.com/doc/refman/5.7/en/start-slave.html
[https_github.com_MyCATApache_Mycat-download]: https://github.com/MyCATApache/Mycat-download
[https_github.com_MyCATApache_Mycat-download_tree_master_1.5-RELEASE]: https://github.com/MyCATApache/Mycat-download/tree/master/1.5-RELEASE