windows服务器mysql使用mycat实现故障切换与读写分离

前言

技术选择

可选择的方案有很多，如NDB,InnoDB Cluster,PXC,MGC,mycat等等。但是其中有收费软件如NDB,还有一些windows版本的已经很久不更新了的如PXC，InnoDB Cluster是mysql自带的但相关的资料案例太少，最终选择的mycat，而且mycat可以实现分库分表等功能，后续如果使用就不用新增中间件了,而且应用程序基本可以做到0修改。现在mycat最新是mycat2的版本，但是技术太新，文档不完善，案例也少，因此选择的1.6.3版本
mysql数据库为8.0.19版本

服务架构

应用程序不直接访问数据库，访问mycat服务器，由mycat转发到数据库，写请求发送到16，读请求发送到17，如16故障，读写全由17负责，当17故障，读写全由16负责。

1.主从配置

1.1 主库配置

my.ini或是my.cof

[mysqld]
# mysql服务id,保证在整个集群环境中该ID唯一
server-id=101
# 开启二进制文件
log-bin=mysql-bin
#以下可以不配置
# 复制模式-混合模式复制
binlog_format = MIXED
# binlog过期清理时间
expire_logs_days = 7
# binlog每个日志文件大小
max_binlog_size = 100m
# binlog缓存大小
binlog_cache_size = 4m
# 最大binlog缓存大小
max_binlog_cache_size = 512m

配置完成后需重启数据库

# 重启服务或者执行命令
systemctl restart mysqld;

创建用于从库连接主库的账号与密码

# 创建用户slave只允许123.57.44.85访问的账号
CREATE USER 'slave'@'192.168.10.17' IDENTIFIED BY '123456';
# 分配权限
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.10.17';
# 刷新权限
FLUSH PRIVILEGES;

# 查询二进制的文件名和和位置，配置从库时会用到
# 每次重启数据库文件名会变，执行了sql语句位置会往前推进
mysql> show master status;
+------------------+----------+--------------+--------------------------------------------------------+-------------------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                                       | Executed_Gtid_Set                                     |
+------------------+----------+--------------+--------------------------------------------------------+-------------------------------------------------------+
| mysql-bin.000001 |     155 |  

1.2 从库配置

my.ini或是my.cof

[mysqld]
# mysql服务id,保证在整个集群环境中该ID唯一
server-id=102

配置完成后需重启数据库

# 重启服务或者执行命令
systemctl restart mysqld;

命令行执行,录入注库的信息

CHANGE MASTER TO MASTER_HOST="192.168.10.16",MASTER_PORT=3306,MASTER_USER="slave",MASTER_PASSWORD="123456",MASTER_LOG_FILE="mysql-bin.000011",MASTER_LOG_POS=857; 

1. MASTER_HOST 注库地址
2. MASTER_PORT 主库端口
3. MASTER_USER 在主库创建的slave账号
4. MASTER_PASSWORD slave账号的密码
5. MASTER_LOG_FILE 主库二进制文件名
6. MASTER_LOG_POS 二进制文件名执行到的位置

开启从库复制

start slave;

查看从库复制状态

show slave status\G;

IO与SQL线程运行状态为YES就是成功了

2.mycat

mycat1官方文档: 链接

2.1 安装

1.需修改Host文件才能访问mycat的管网

210.51.26.184 mycat.org.cn www.mycat.org.cn dl.mycat.org.cn

2.访问mycat.org.cn,下载mycat1.6.3版本

3.解压即用，解压后删除lib目录下的mysql-connector包,放入对应版本的mysql连接包
4.需要java1.8的环境
5.下载对应版本的MySQL Connector包放到lib里并删除原有的MySQL Connector包
linux系统下需要赋权限

chmod 777 mysql-connector-java-8.0.23.jar

2.2配置

conf/schema.xml

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
	<schema name="xls" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn_xls" />
	<schema name="dn_xls" checkSQLschema="true" sqlMaxLimit="100" dataNode="xls" />
	<dataHost name="jdbchost" maxCon="1000" minCon="10" balance="1" switchType="1" writeType="0" slaveThreshold="100" dbType="mysql" dbDriver="jdbc"> 
		<heartbeat>show slave status</heartbeat> 
		<writeHost host="M1" url="jdbc:mysql://172.16.12.175:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="exin2000" >
			<readHost host="S1" url="jdbc:mysql://172.16.12.176:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="exin2000" />
		</writeHost>
		<writeHost host="S1" url="jdbc:mysql://172.16.12.176:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="exin2000" />		
	</dataHost>
</mycat:schema>

conf/service.xml
在service.xml中最下方修改root账号或者新建账号

	<!--配置连接的用户,这就是应用程序访问mycat的账号和密码-->
	<user name="root" defaultAccount="true">
		<property name="password">123456</property>
		<!--可以访问的逻辑库,多个库用逗号隔开-->
		<property name="schemas">sut_device,sut_dm</property>
	</user>

3.运行

以管理员身份运行命令行，切换到mycat下的bin目录,

F:\新建文件夹\copy\mycat\bin>mycat
Usage: mycat { console : start : pause : resume : stop : restart : install : remove : status }

先执行 mycat install;注册服务
在执行 mycat start 启动服务
然后就可以访问了,应用程序访问mycat的方式和直接访问数据库的方式一致
运行日志和错误日志会存在在mycat/log里

3.已知问题

1.不知道是不支持部分函数还是嵌套问题，没做测试，总之是会报错

# 在正常直连数据库没问题,但是在连mycat以下语句会导致mycat断开连接
ORDER BY CAST( regexp_substr(CODE,'[0-9.]+') AS SIGNED INT) 
# 可使用+0的方式将字符串转换成数字
order by regexp_substr(code,'[0-9.]+')+0

2.用命令行访问mycat时要加–default-auth=mysql_native_password

# 加密方式不对密码不对
D:\mysql-8.0.19-winx64>mysql -h 172.16.1.13 -P 8066 -u root -p
Enter password: ********
ERROR 1045 (HY000): Access denied for user 'root', because password is error
# 下面这么写就可以了
mysql -h 172.16.1.13 -P 8066 -u root -p --default-auth=mysql_native_password

3.运行mycat start;闪退,大概率是参数配错了，log里基本都会有报错
4.主从复制不支持触发器,且要小心处理事件,主从服务器都有相同事件的话会产生错误,导致主从关系断开