mycat2搭建双主双从mysql集群

---

说明：
服务器为虚拟机Centos7
mysql版本为5.7
mycat2 文档等资料： https://www.yuque.com/ccazhw/ml3nkf
mycat2 命令：https://www.yuque.com/ccazhw/ml3nkf/f9f24306bbd3992c1baff00cdb0956a4
架构图：

Maste1负责所有写请求，Master2,Slave1,Slave2负责所有读请求。当Master1宕机时，Master2则负责所有读请求，Slave1,Slave2负责所有读请求。Master2为备用机。

编号	角色名称	IP地址	机器名称
1	Master1	192.168.1.124	vm4
2	Slave1	192.168.1.125	vm5
3	Master2	192.168.1.126	vm6
4	Slave2	192.168.1.127	vm7

---

一、mycat2下载与启动

步骤

1. 下载mycat2的zip文件(mycat2-install-template-1.21.zip )
2. 下载mycat2的jar文件(mycat2-1.21-release-jar-with-dependencies.jar)
3. 解压.zip文件
4. 把jar文件放入解压的.zip文件/lib文件夹下
5. 编辑prototypeDs.datasource.json文件，连接master数据库
6. 启动mycat

可能出现问题

1. 解压.zip文件提示： -bash: unzip: 未找到命令
   解决方式：需要下载unzip插件

# 下载unzip插件
yum install unzip

2. 执行启动命令 mycat/bin/mycat start时报错：-bash: mycat/bin/mycat: 权限不够
   解决方式：给bin/文件夹赋权

# 给bin/文件夹赋权
chmod -R 777 bin/

执行步骤
步骤1-4

# 1.下载mycat2的zip文件
wget http://dl.mycat.org.cn/2.0/install-template/mycat2-install-template-1.21.zip
# 2.下载mycat2的jar文件
wget http://dl.mycat.org.cn/2.0/1.21-release/mycat2-1.21-release-jar-with-dependencies.jar
# 3.解压.zip文件
unzip mycat2-install-template-1.21.zip
# 4.把jar文件当如解压的文件
mv mycat2-1.21-release-jar-with-dependencies.jar mycat/lib/

---

步骤5

# 5.编辑prototypeDs.datasource.json文件，连接master数据库
# 源文件如下
{
        "dbType":"mysql",
        "idleTimeout":60000,
        "initSqls":[],
        "initSqlsGetConnection":true,
        "instanceType":"READ_WRITE",
        "maxCon":1000,
        "maxConnectTimeout":3000,
        "maxRetryCount":5,
        "minCon":1,
        "name":"prototypeDs",
        "password":"123456",
        "type":"JDBC",
        "url":"jdbc:mysql://localhost:3306/mysql?useUnicode=true&serverTimezone=Asia/Shanghai&characterEncoding=UTF-8",
        "user":"root",
        "weight":0
}
# password ---> 修改成master数据库密码;
# user ---> 修改成master数据库账号
# url修改成业务库(mysql物理库，需要有生成这个数据库)
# 修改后
{
        "dbType":"mysql",
        "idleTimeout":60000,
        "initSqls":[],
        "initSqlsGetConnection":true,
        "instanceType":"READ_WRITE",
        "maxCon":1000,
        "maxConnectTimeout":3000,
        "maxRetryCount":5,
        "minCon":1,
        "name":"prototypeDs",
        "password":"Root@123",
        "type":"JDBC",
        "url":"jdbc:mysql://192.168.1.124:3306/clound_kernel?useUnicode=true&serverTimezone=Asia/Shanghai&characterEncoding=UTF-8",
        "user":"root",
        "weight":0
}

---

步骤6

# 6.启动mycat2(url链接中数据库如果不存在，则启动报错)
mycat/bin/mycat start
# 登录mycat2的数据库(检查mycat2的是否启动成功)
mysql -uroot -p123456 -h 192.168.1.124 -P8066

说明：源文件字段说明，以及mycat其他命令可查看mycat2官方文档

二、搭建双主双从集群

1.修改mysql配置文件(my.cnf)

步骤

1. 修改Master配置文件(Master1,Master2),重启mysql
2. 修改Slave配置文件(Slave1,Slave2),重启mysql
3. Master主机上生成Slave用户，并授权(Master1,Master2)
4. 配置slave复制Master(Slave1,Slave2)
5. 配置Master主机之间相互复制(Master1,Master2)

执行步骤
步骤1：修改Master配置文件(Master1,Master2),重启mysql

# 查看mysql原配置文件
cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
max_allowed_packet=16M

修改Master1后的配置文件

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
max_allowed_packet=16M

# 以下是新增配置

# 服务器唯一id
server-id=4

# 启用二进制日志
log-bin=mysql-bin

# 设置不要复制的数据库
binlog-ignore-db=mysql
binlog-ignore-db=information_schema

# 设置需要复制的数据库
binlog-do-db=clound_kernel

# 设置logbin格式
binlog_format=STATEMENT

# 在作为从库的时,有写入操作也要更新二进制日志文件
log-slave-updates

修改后的Master2配置文件

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
max_allowed_packet=16M

# 以下是新增配置
# 服务器唯一id
server-id=5

# 启用二进制日志
log-bin=mysql-bin

# 设置不要复制的数据库
binlog-ignore-db=mysql
binlog-ignore-db=information_schema

# 设置需要复制的数据库
binlog-do-db=clound_kernel

# 设置logbin格式
binlog_format=STATEMENT

# 在作为从库的时,有写入操作也要更新二进制日志文件
log-slave-updates

ps : Master2与Master1配置区别就只有server-id值不一样

---

重启Master1,Master2的mysql服务

# 重启mysql服务命令
service mysqld restart

步骤2：修改Slave配置文件(Slave1,Slave2),重启mysql
修改后的Slave1配置文件

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
max_allowed_packet=16M

# 以下是新增配置
# 服务器唯一id
server-id=6

# 启用中继日志
relay-log=mysql-relay

修改后的Slave2配置文件

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
max_allowed_packet=16M


# 以下是新增配置
# 服务器唯一id
server-id=7

# 启用中继日志
relay-log=mysql-relay

ps：my.cnf配置文件中binlog_format值说明

STATEMENT模式（SBR）
每一条会修改数据的sql语句会记录到binlog中。
优点是并不需要记录每一条sql语句和每一行的数据变化，减少了binlog日志量，节约IO，提高性能。
缺点是在某些情况下会导致master-slave中的数据不一致(如sleep()函数， last_insert_id()，以及user-defined functions(udf)等会出现问题)
ROW模式（RBR）
不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了，修改成什么样了。而且不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。
缺点是会产生大量的日志，尤其是alter table的时候会让日志暴涨。
MIXED模式（MBR）
以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式。
生产环境建议使用MIXED模式

重启Slave1,Slave2的mysql服务

# 重启mysql服务命令
service mysqld restart

步骤3:Master主机上生成Slave用户，并授权(Master1,Master2)
需要分别在Master1,Master2 执行

# 生成Slave用户，并授权
GRANT REPLICATION SLAVE ON *.* to 'slave'@'%' IDENTIFIED BY 'Slave@123';

步骤4:配置slave复制Master(Slave1,Slave2)

# 查看Master1状态
SHOW MASTER STATUS;

结果如下：

执行复制Master1逻辑

# 执行复制Master1命令
CHANGE MASTER TO
master_host='192.168.1.124',
master_user='slave',
master_password='Slave@123',
master_port=3306,
master_log_file='mysql-bin.000001',
master_log_pos=324;

# 启动复制功能
START SLAVE;

# 检查是否复制成功
SHOW SLAVE STATUS;

结果如下：
Slave_IO_Running与Slave_SQL_Running为Yes，则表示复制成功

执行复制Master2逻辑

# 查看Master2状态
SHOW MASTER STATUS;

结果如下：

执行复制Master2逻辑

# 执行复制Master2命令
CHANGE MASTER TO
master_host='192.168.1.126',
master_user='slave',
master_password='Slave@123',
master_port=3306,
master_log_file='mysql-bin.000002',
master_log_pos=154;

# 启动复制功能
START SLAVE;

# 检查是否复制成功
SHOW SLAVE STATUS;

结果如下：
Slave_IO_Running与Slave_SQL_Running为Yes，则表示复制成功

步骤5: 配置Master主机之间相互复制(Master1,Master2)

Master1复制Master2

# 执行复制Master2命令
CHANGE MASTER TO
master_host='192.168.1.126',
master_user='slave',
master_password='Slave@123',
master_port=3306,
master_log_file='mysql-bin.000002',
master_log_pos=154;

# 启动复制功能
START SLAVE;

# 检查是否复制成功
SHOW SLAVE STATUS;

结果如下：
Slave_IO_Running与Slave_SQL_Running为Yes，则表示复制成功

Master2复制Master1

# 执行复制Master2命令
CHANGE MASTER TO
master_host='192.168.1.124',
master_user='slave',
master_password='Slave@123',
master_port=3306,
master_log_file='mysql-bin.000001',
master_log_pos=324;

# 启动复制功能
START SLAVE;

# 检查是否复制成功
SHOW SLAVE STATUS;

结果如下：
Slave_IO_Running与Slave_SQL_Running为Yes，则表示复制成功

ps: 相关主从mysql命令

# 停止从库复制功能
STOP SLAVE;
# 重置Master
RESET MASTER;
# 重置Slave
RESET SLAVE;

2.配置mycat集群，实现多主多从

2.1 创建数据库，配置数据源,重启mycat

mycat执行命令

# 创建clound_kernel数据库
CREATE DATABASE clound_kernel;

# Master1数据源
/*+ mycat:createDataSource{ "name":"rwSepw1","url":"jdbc:mysql://192.168.1.124:3306/clound_kernel?useSSL=false&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true", "user":"root","password":"Root@123" } */;

# Slave1数据源
/*+ mycat:createDataSource{ "name":"rwSepr1","url":"jdbc:mysql://192.168.1.125:3306/clound_kernel?useSSL=false&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true", "user":"root","password":"Root@123" } */;

# Master2数据源
/*+ mycat:createDataSource{ "name":"rwSepw2","url":"jdbc:mysql://192.168.1.126:3306/clound_kernel?useSSL=false&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true", "user":"root","password":"Root@123" } */;

# Slave2数据源
/*+ mycat:createDataSource{ "name":"rwSepr2","url":"jdbc:mysql://192.168.1.127:3306/clound_kernel?useSSL=false&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true", "user":"root","password":"Root@123" } */;

查看mycat数据库中数据源

# 查看数据源
/*+ mycat:showDataSources{} */

结果如图：

生成集群

# 生成集群
/*! mycat:createCluster{
	"clusterType":"MASTER_SLAVE",
	"heartbeat":{
		"heartbeatTimeout":1000,
		"maxRetry":3,
		"minSwitchTimeInterval":300,
		"slaveThreshold":0
	},
	"masters":[
		"rwSepw1","rwSepw2"
	],
	"maxCon":2000,
	"name":"clound_kernel_cluster",
	"readBalanceType":"BALANCE_ALL",
	"replicas":[
		"rwSepw2","rwSepr1","rwSepr2"
	],
	"switchType":"SWITCH"
} */;

# 查看集群
/*+ mycat:showClusters{} */;

结果如图：

编辑schema下clound_kernel数据库对应的clound_kernel.schema.json文件

# 编辑clound_kernel.schema.json文件
vi conf/schemas/clound_kernel.schema.json

# 原文件内容
{
        "customTables":{},
        "globalTables":{},
        "normalProcedures":{},
        "normalTables":{},
        "schemaName":"clound_kernel",
        "shardingTables":{},
        "views":{}
}

# 修改后文件
{
        "customTables":{},
        "globalTables":{},
        "normalProcedures":{},
        "normalTables":{},
        "schemaName":"clound_kernel",
        "shardingTables":{},
        "views":{},
        "targetName":"clound_kernel_cluster" //值为集群或者数据源
}

最后重启mycat

# 重启mycat
mycat/bin/mycat restart

3.测试

mycat数据库执行sql命令

# mycat 打开clound_kernel数据库
use clound_kernel;
# 创建测试表
CREATE TABLE clound_kernel.`test_model`(`id` bigint NOT NULL AUTO_INCREMENT, `name` varchar(50) DEFAULT NULL,PRIMARY KEY (`id`)) ENGINE=INNODB DEFAULT CHARSET=utf8;
# 插入数据
INSERT INTO clound_kernel.test_model(`name`)VALUES('zhangsan');
# 插入服务器名称，查询时用于辨别mycat是否配置成功
INSERT INTO clound_kernel.test_model(`name`)VALUES(@@hostname);
# 查看test_model数据库
SELECT * FROM clound_kernel.test_model;

结果如下：id为2的name值，为四台不同的服务器名称

4. 中间遇到的报错/问题

4.1 MySql报错Slave failed to initialize relay log info structure from the repository

4.1.1 解决方法

# 执行命令(清除master信息和relay日志的信息，删除所有的relay日志文件，并开始创建一个全新的中继日志)
RESET SLAVE;

报错原因： 从库已经存在之前的relay log

4.2 MySql报错This operation cannot be performed with a running slave io thread; run STOP SLAVE IO_THREAD FOR CHANNEL ‘’ first.

4.2.1 解决方法

STOP SLAVE

报错原因： SLAVE已启动(看报错内容则可得知)

4.3 MySql检查从库是否复制成功时Slave_IO_Running为Connecting，Slave_SQL_Running为Yes

4.3.1 解决方法

1. 能否使用shell命令：telnet 192.168.1.126:3306 访问到主库
2. 排查防火墙是否打开
3. 排查端口是否屏蔽
4. 能否使用slave登录主库
5. 也有可能是命令CHANGE MASTER TO 参数错误
6. 删除auto.conf文件，重启mysql服务

问题原因： 从库访问不了主库，auto.conf值一致

mycat2 安装文件

链接：https://pan.baidu.com/s/12nee3d1fVuvNGKKowa6u5g 
提取码：av5u