mysql8.4+mysql router读写分离

以下为容器环境内搭建
准备工作:

拉取镜像:

镜像	版本
mysql	8.4
container-registry.oracle.com/mysql/community-router	8.4

下载mysql_shell
mysql-shell-9.0.1-linux-glibc2.17-x86-64bit.tar.gz
下载地址: https://downloads.mysql.com/archives/shell/

参考
这里对这篇文章进行扩展, 容器环境及一些异常情况的处理

1. 准备mysql配置文件
   注意点: sql-mode与老版本(sql_mode)不一样, binlog-ignore-db=mysql去掉

mysql_m1: master.cnf

[mysqld]
server-id=100gtid_mode=on
enforce-gtid-consistency=onexplicit_defaults_for_timestamp=true
lower_case_table_names=1
sql-mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION#binlog-ignore-db=mysql
max_allowed_packet=128M

mysql_s1: 配置文件(master.cnf)

[mysqld]
server-id=200gtid_mode=on
enforce-gtid-consistency=onexplicit_defaults_for_timestamp=true
lower_case_table_names=1
sql-mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION#binlog-ignore-db=mysql
max_allowed_packet=128M

# 以下容器需要在同一网段内, 否则创建
services:mysql_m1:image: mysql:8.4volumes:- ${PWD}/mysql-shell-9.0.1-linux-glibc2.17-x86-64bit:/mysql-shell- ${PWD}/mysql_m1/data:/var/lib/mysql- ${PWD}/mysql_m1/conf/master.conf:/etc/mysql/conf.d/master.cnfports :- "3306:3306"environment:- MYSQL_ROOT_PASSWORD=rootmysql_s1:image: mysql:8.4volumes:- ${PWD}/mysql_s1/data:/var/lib/mysql- ${PWD}/mysql_s1/conf/master.conf:/etc/mysql/conf.d/master.cnfports :- "3306:3306"environment:- MYSQL_ROOT_PASSWORD=root

2. 通过docker-compose方式启动以上两个mysql容器,进入容器初始化ReplicaSet
   进入mysql shell所在目录,
   ./mysql_shell/bin/mysqlsh

./mysqlsh mysql://root@localhost:3310
Please provide the password for 'root@localhost:3310': ****
Save password for 'root@localhost:3310'? [Y]es/[N]o/Ne[v]er (default No): n
MySQL Shell 9.0.1Copyright (c) 2016, 2024, 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 '\?' for help; '\quit' to exit.
Creating a Classic session to 'root@localhost:3310'
Fetching global names for auto-completion... Press ^C to stop.
Your MySQL connection id is 8
Server version: 8.4.3 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.MySQL  localhost:3310 ssl  SQL > MySQL  localhost:3310 ssl  SQL > \js
Switching to JavaScript mode...MySQL  localhost:3310 ssl  JS >

创建用户并给权限

create user rw_shell@'%' identified by 'xxx';
这里还需要设置权限(参考文章开头的链接)

MySQL  localhost:3310 ssl  JS > dba.configureReplicaSetInstance('rw_shell@localhost:3311', {"clusterAdmin": "rw_shell@'%'"})
// 输入登陆密码

创建ReplicaSet 报错: MySQL  localhost:3310 ssl  JS > rs = dba.createReplicaSet("prd_op_service")
A new replicaset with instance '4a64c4d2402e:3306' will be created.* Checking MySQL instance at 4a64c4d2402e:3306This instance reports its own address as 4a64c4d2402e:3306
4a64c4d2402e:3306: Instance configuration is suitable.
Dba.createReplicaSet: 4a64c4d2402e:3306: instance has binlog filters configured, but they are not supported in InnoDB ReplicaSets. (MYSQLSH 51150)报错: 
mysql_m1_1       [ERROR] [MY-013797] [Server] Option --authentication-policy is set to an invalid value. Please check if the specified authentication plugins are valid.
mysql_m1_1       [ERROR] [MY-013236] [Server] The designated data directory /var/lib/mysql/ is unusable. You can remove all files that the server added to it.
mysql_m1_1       [ERROR] [MY-010119] [Server] Aborting
mysql_m1_1       [System] [MY-015018] [Server] MySQL Server Initialization - end.
mysql_cluster_mysql_m1_1 exited with code 1

master.cnf配置文件里:
authentication_policy=mysql_native_password // 去掉
binlog-ignore-db=mysql // 去掉
sql_mode=xxx // 改为 => sql-mode 修改sql模式以支持full_group_by

3. 创建ReplicaSet

 MySQL  localhost:3310 ssl  JS > var rs = dba.createReplicaSet("prd_op_service")
A new replicaset with instance '513842479f48:3306' will be created.* Checking MySQL instance at 513842479f48:3306This instance reports its own address as 513842479f48:3306
513842479f48:3306: Instance configuration is suitable.* Checking connectivity and SSL configuration...
* Updating metadata...ReplicaSet object successfully created for 513842479f48:3306.
Use rs.addInstance() to add more asynchronously replicated instances to this replicaset and rs.status() to check its status.MySQL  localhost  JS > rs.status()
{"replicaSet": {"name": "prd_op_service", "primary": "940d1a52cbdf:3306", "status": "AVAILABLE", "statusText": "All instances available.", "topology": {"940d1a52cbdf:3306": {"address": "940d1a52cbdf:3306", "instanceRole": "PRIMARY", "mode": "R/W", "status": "ONLINE"}}, "type": "ASYNC"}
}

4. 增加从节点, addInstance后,需要去手动重启从节点

 MySQL  localhost  JS > rs.addInstance('mysql_s1:3306')Adding instance to the replicaset...* Performing validation checksThis instance reports its own address as 54ef15541a78:3306
54ef15541a78:3306: Instance configuration is suitable.* Checking async replication topology...* Checking connectivity and SSL configuration...* Checking transaction state of the instance...WARNING: A GTID set check of the MySQL instance at '54ef15541a78:3306' determined that it contains transactions that do not originate from the replicaset, which must be discarded before it can join the replicaset.Instance '54ef15541a78:3306' has the following errant GTIDs that do not exist in the replicaset:6ce93fa1-a31e-11ef-8917-0242ac190002:1-7WARNING: Discarding these extra GTID events can either be done manually or by completely overwriting the state of '54ef15541a78:3306' with a physical snapshot from an existing replicaset member. To use this method by default, set the 'recoveryMethod' option to 'clone'.Having extra GTID events is not expected, and it is recommended to investigate this further and ensure that the data can be removed prior to choosing the clone recovery method.Please select a recovery method [C]lone/[A]bort (default Abort): c
* Updating topology
Monitoring Clone based state recovery of the new member. Press ^C to abort the operation.
Clone based state recovery is now in progress.NOTE: A server restart is expected to happen as part of the clone process. If the
server does not support the RESTART command or does not come back after a
while, you may need to manually start it back.* Waiting for clone to finish...
NOTE: 54ef15541a78:3306 is being cloned from 940d1a52cbdf:3306
** Stage DROP DATA: Completed
** Clone Transfer  FILE COPY  ============================================================    0%  In ProgressPAGE COPY  ============================================================    0%  Not StartedREDO COPY  ============================================================    0%  Not StartedNOTE: 54ef15541a78:3306 is shutting down...* Waiting for server restart... timeout 
WARNING: Clone process appears to have finished and tried to restart the MySQL server, but it has not yet started back up.Please make sure the MySQL server at '54ef15541a78:3306' is properly restarted. The operation will be reverted, but you may retry adding the instance after restarting it. 
ERROR: Error adding instance to replicaset: MYSQLSH 51156: Timeout waiting for server to restart
Reverting topology changes...
ERROR: Error while reverting replication changes: MySQL Error 2013: Lost connection to MySQL server during queryChanges successfully reverted.
ERROR: 54ef15541a78:3306 could not be added to the replicaset
ReplicaSet.addInstance: Timeout waiting for server to restart (MYSQLSH 51156)MySQL  localhost  JS > rs.status();
{"replicaSet": {"name": "prd_op_service", "primary": "940d1a52cbdf:3306", "status": "AVAILABLE", "statusText": "All instances available.", "topology": {"54ef15541a78:3306": {"address": "54ef15541a78:3306", "instanceRole": "SECONDARY", "mode": "R/O", "replication": {"applierStatus": "APPLIED_ALL", "applierThreadState": "Waiting for an event from Coordinator", "applierWorkerThreads": 4, "receiverStatus": "ON", "receiverThreadState": "Waiting for source to send event", "replicationLag": null, "replicationSsl": "TLS_AES_128_GCM_SHA256 TLSv1.3", "replicationSslMode": "REQUIRED"}, "status": "ONLINE"}, "940d1a52cbdf:3306": {"address": "940d1a52cbdf:3306", "instanceRole": "PRIMARY", "mode": "R/W", "status": "ONLINE"}}, "type": "ASYNC"}
}

5. 启动路由节点
   参考 https://dev.mysql.com/doc/mysql-router/8.4/en/mysql-router-installation-docker.html
   访问6450端口,数据查询正常(连接从节点),开启事务访问正常(进入主节点)

测试路由是否生效./mysqlsh -u rw_shell -P 6450 -pMySQL  localhost:6450 ssl  SQL > select @@hostname;
+--------------+
| @@hostname   |
+--------------+
| 54ef15541a78 | // 从节点
+--------------+
1 row in set (0.0011 sec)
Statement ID: 3736MySQL  localhost:6450 ssl  SQL > begin; // 事务开始
Query OK, 0 rows affected (0.0036 sec)
Statement ID: 6895MySQL  localhost:6450 ssl  ★  SQL > select @@hostname;
+--------------+
| @@hostname   |
+--------------+
| 940d1a52cbdf | // 主节点
+--------------+
1 row in set (0.0006 sec)
Statement ID: 6967MySQL  localhost:6450 ssl  ★  SQL > commit; // 提交
Query OK, 0 rows affected (0.0006 sec)
Statement ID: 7407MySQL  localhost:6450 ssl  SQL > select @@hostname;
+--------------+
| @@hostname   |
+--------------+
| 54ef15541a78 | // 从节点
+--------------+
1 row in set (0.0011 sec)
Statement ID: 4408

6. 启动应用服务连接mysql_router

连接mysql_router:6450端口报错:

Caused by: java.sql.SQLNonTransientConnectionException: Public Key Retrieval is not allowed
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:110)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:828)
at com.mysql.cj.jdbc.ConnectionImpl.(ConnectionImpl.java:448)
at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:241)
at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:198)

连接url加上allowPublicKeyRetrieval参数

Caused by: java.sql.SQLNonTransientConnectionException: Could not create connection to database server.
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:110)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63)
at com.mysql.cj.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:1001)
at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:818)
…略
Caused by: java.lang.ArrayIndexOutOfBoundsException: Index 8 out of bounds for length 7
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:110)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63)

连接url加上trackSessionState参数 参考
最终
jdbc:mysql://xxx:6450/xxx?allowPublicKeyRetrieval=true&trackSessionState=true