部署Mycat数据库中间件连接MySQL8.x高可用集群

LonelyMan 246 2021-07-20

主从复制搭建

角色 主机机名
master mysql1
slave1 mysql2
slave2 mysql3

创建容器

# 创建自定义网络, 使得容器间能通过主机名访问
docker network create sql-net

# 创建master主机
docker run -d -P --name mysql1 -p 4306:3306 --net=sql-net -v mysql1:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 mysql:latest
# 创建slave从机1
docker run -d -P --name mysql2 -p 4307:3306 --net=sql-net -v mysql2:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 mysql:latest
# 创建slave从机2
docker run -d -P --name mysql3 -p 4308:3306 --net=sql-net -v mysql3:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 mysql:latest

创建完成后如下

[root@centos ~]# docker ps
CONTAINER ID   IMAGE                   COMMAND                  CREATED       STATUS       PORTS                                                                                      NAMES
60d89cfcb140   mysql:latest            "docker-entrypoint.s…"   3 hours ago   Up 3 hours   0.0.0.0:4308->3306/tcp, :::4308->3306/tcp, 0.0.0.0:49171->33060/tcp, :::49171->33060/tcp   mysql3
ff3bdaaea587   mysql:latest            "docker-entrypoint.s…"   3 hours ago   Up 3 hours   0.0.0.0:4307->3306/tcp, :::4307->3306/tcp, 0.0.0.0:49170->33060/tcp, :::49170->33060/tcp   mysql2
79289a84a5e4   mysql:latest            "docker-entrypoint.s…"   3 hours ago   Up 3 hours   0.0.0.0:4306->3306/tcp, :::4306->3306/tcp, 0.0.0.0:49160->33060/tcp, :::49160->33060/tcp   mysql1

修改容器配置

# mysql1
docker exec -it mysql1 bash
#进入容器后运行以下命令
cat >> /etc/mysql/my.cnf <<EOF
server-id=1
#主库开启binlog日志
log-bin=/var/lib/mysql/mysql-bin
EOF
#退出该容器
exit

# mysql2
docker exec -it mysql2 bash
#进入容器后运行以下命令
cat >> /etc/mysql/my.cnf <<EOF
server-id=2
EOF
#退出该容器
exit

# mysql3
docker exec -it mysql3 bash
#进入容器后运行以下命令
cat >> /etc/mysql/my.cnf <<EOF
server-id=3
EOF
#退出该容器
exit

# 重启容器，使得配置生效
docker restart mysql1 mysql2 mysql3

配置主从复制

-- mysql1
-- 创建从机访问主机所需要的用户
create user 'slave'@'%' identified by '123456';
-- 为该用户授权同步复制权限
grant replication slave on *.* to 'slave'@'%';
-- 刷新权限使其生效
flush privileges;

查看主机详情并记下binary log二进制日志文件名以及位置 Position值

show master status;

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |     1721 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

配置从机

-- mysql2,mysql3
change master to master_host='mysql1', master_port=3306, master_user='slave', master_password='123456', master_log_file='mysql-bin.000001', master_log_pos=1721;
start slave ;

-- 查看从机状态
-- mysql8.x建议使用相关可视化工具执行，否则显示会十分混乱
show slave status;

检查Slave_IO_Running 和 Slave_SQL_Running 是否为 Yes ，以及Slave_IO_State 为 Waiting for master to send event，若显示正确则证明主从复制成功。

-- mysql1
-- 创建数据库与表并赋值
mysql> create database testdb;
Query OK, 1 row affected (0.03 sec)

mysql> use testdb;
Database changed
mysql> create table per(id int,name varchar(10));
Query OK, 0 rows affected (0.03 sec)

mysql> insert into per values(1,'a'),(2,'b');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from per;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | b    |
+------+------+
2 rows in set (0.00 sec)

-- mysql2,mysql3
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| testdb             |
+--------------------+
5 rows in set (0.00 sec)

mysql> use testdb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from per;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | b    |
+------+------+
2 rows in set (0.00 sec)

以上步骤完成了一主二从的相关配置。

Mycat-server搭建

主机 角色
mysql1 master (server_id=1)
mysql2 slave (server_id=2)
mysql3 slave (server_id=3)

配置mycat

lib库配置

将驱动包移动到mycat的lib目录下，并删除原本的5.x驱动包：

我是用的是mysql-connector-java-8.0.25.jar

使用chmod命令设置该驱动包的权限

schema.xml

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

	<schema name="testdb" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn1">
</schema>
	<schema name="information_schema" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn_schema"></schema>
	<dataNode name="dn1" dataHost="host1" database="testdb" />
	<dataNode name="dn_schema" dataHost="host1" database="information_schema" />
    <!-- dbDriver需修改为jdbc -->
	<dataHost name="host1" maxCon="1000" minCon="10" balance="0"
			  writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()</heartbeat>
		<!-- can have multi write hosts -->
		<!-- url需修改为jdbc格式 -->
		<writeHost host="hostM1" url="jdbc:mysql://localhost:4306" user="root"
 password="123456">
		   <readHost host="hostS1" url="jdbc:mysql://localhost:4307" user="root" password="123456" />
		   <readHost host="hostS2" url="jdbc:mysql://localhost:4308" user="root" password="123456" />
		</writeHost>
	</dataHost>
</mycat:schema>

server.xml

	<property name="useHandshakeV10">1</property>

	<user name="mycat">
		<property name="password">123456</property>
		<property name="schemas">testdb,information_schema</property>
	</user>

启动Mycat

[root@centos opt]# /opt/mycat/bin/mycat console
Running Mycat-server...
wrapper  | --> Wrapper Started as Console
wrapper  | Launching a JVM...
jvm 1    | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
jvm 1    |   Copyright 1999-2006 Tanuki Software, Inc.  All Rights Reserved.
jvm 1    | 
jvm 1    | Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.
jvm 1    | MyCAT Server startup successfully. see logs in logs/mycat.log

使用任意mysql-client访问Mycat，默认访问端口为8066

# mysql1容器内的mysql-client
root@ff3bdaaea587:/# mysql -umycat -p123456 -h10.10.0.100 -P8066 --default-auth=mysql_native_password
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.6.29-mycat-1.6.7.4-release-20200105164103 MyCat Server (OpenCloudDB)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> exit
Bye

相关问题

Mycat 目前还未全面支持MySQL 8以上的版本，可能会导致一些问题，例如：

  • Mycat连接MySQL 8时可能会报密码错误，因为新版的密码加密方式与旧版不一样

    • 解决方案：登录时加上--default-auth=mysql_native_password
    • 注：在替换mysql驱动包后似乎不需要添加上面的参数就能访问了。。。

  • 连接Mysql数据库时会出现数据库读取不正确的情况

    • 解决方案：将mycat的lib目录下原本的5.x驱动包替换为8.x版本

参考

https://blog.csdn.net/u013068184/article/details/107691389

https://blog.csdn.net/eyeofeagle/article/details/102841995

https://blog.csdn.net/eyeofeagle/article/details/117927398

https://blog.51cto.com/zero01/2466009

mysql 中间件 docker