码途未来(htmltoo.com):MyCAT 部署 MySQL 读写分离__博客

MyCAT 部署 MySQL 读写分离

2018/07 作者：ihunter 0 次 0

单一的 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])
删除 [mysqld@slave]，将改建为双主双从 4 个数据库

sudo systemctl stop mysqldsudo systemctl disable mysqld## 暂保留 MySQL 安装时的 [mysqld] 及其数据文件## 暂保留防火墙中的 3306 端口，让给 MyCAT 使用sudo systemctl stop mysqld@slavesudo systemctl disable mysqld@slavesudo 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 iptables123456789101112

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

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

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

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

[mysqld@slave2]
port=33064datadir=/var/lib/mysql-slave2
socket=/var/lib/mysql-slave2/mysql.sock
log-error=/var/log/mysqld-slave2.log
explicit_defaults_for_timestamp=truelog_timestamps=SYSTEM1234567891011121314151617181920212223242526272829303132

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}.log1234

设置为开机自动启动，并立即启动：

sudo systemctl enable mysqld@{master1,master2,slave1,slave2}sudo systemctl start mysqld@{master1,master2,slave1,slave2}12

3.4 修改 root 密码

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

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 用于支持多主单从复制[mysqld@master1]server_id=33061log_bin=mysql-binmaster_info_repository=TABLErelay_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=33062log_bin=mysql-binmaster_info_repository=TABLErelay_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=33063log_bin=mysql-binmaster_info_repository=TABLErelay_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=33064log_bin=mysql-binmaster_info_repository=TABLErelay_log_info_repository=TABLE
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=mysql
binlog-ignore-db=sys1234567891011121314151617181920212223242526272829303132333435363738394041

保存后重启所有 MySQL 服务：

sudo systemctl restart mysqld@{master1,master2,slave1,slave2}1

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';"12345

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';"1234

4.2.3 立即启动复制

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';"1234567891011121314

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;"12345

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/1.5-RELEASE/Mycat-server-1.5.1-RELEASE-20161130213509-linux.tar.gzsudo tar -zxf Mycat-server-1.5.1-RELEASE-20161130213509-linux.tar.gz -C /opt
export MYCAT_HOME=/opt/mycat1234

5.2 配置 JVM 参数

## sudo vim $MYCAT_HOME/conf/wrapper.confwrapper.java.additional.5=-XX:MaxDirectMemorySize=128M
wrapper.java.additional.10=-Xmx128M
wrapper.java.additional.11=-Xms128M1234

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

    

    

    
        SELECT 1
        
            
        
        
            
        
    1234567891011121314151617181920

5.4 配置 MyCAT 代理

指定 MyCAT 端口：3306 为应用端口，3308 为管理端口；
设置客户端用户的帐号/密码：admin 有读写权限，guest 只有读权限。

## sudo vim $MYCAT_HOME/conf/server.xml
    
        druidparser
        3306
        3308
    

    
        admin123
        mydb
    

    
        
        mydb
        true
    123456789101112131415161718192021

5.5 配置为 systemd 服务

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

[Service]Type=forkingExecStart=/opt/mycat/bin/mycat startExecStop=/opt/mycat/bin/mycat stop

[Install]WantedBy=multi-user.target12345678910111213

设置开机自动启动、并立即启动：

sudo systemctl enable mycat # 设置为开机自动启动sudo systemctl start mycat # 立即启动# sudo systemctl stop mycat # 立即停止# sudo systemctl disable mycat # 取消开机自动启动12345

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"; done1

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

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

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

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;"123

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;"123456789

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;"

标签：结点数据数据库情形中间件主从读数

赞(3) 更多分享

本文链接地址： https://b.htmltoo.com/blog-p576.html

上篇： CentOS服务器下对mysql的优化
下篇： Linux MariaDB 数据库主从复制配置