更多文章，欢迎关注作者公众号，欢迎一起交流。
MySQL 8.2.0创新版本已于2023-10-17发布，MySQL Router 8.2 支持数据库的读/写分离，这里将在InnoDB Cluster集群中演示数如何进行读写分离，本篇内容包括：MySQL Server数据库安装、MySQL Shell安装、MySQL Router安装、InnoDB Cluster安装与读写分离演示，若您只关注读写分离的演示，可直接跳至最后章节。
1 数据库环境
数据库环境为：
环境详情 | 节点1 | 节点2 | 节点3 |
Server IP | 192.168.56.101 | 192.168.56.102 | 192.168.56.103 |
Hostname | clusternode01 | clusternode02 | clusternode03 |
Server ID | 56101 | 56102 | 56103 |
DB Version | MySQL 8.2 | MySQL 8.2 | MySQL 8.2 |
OS | Redhat 8.7 | Redhat 8.7 | Redhat 8.7 |
2 数据库安装
分别在clusternode01、clusternode02、clusternode03节点安装MySQL数据库软件并初始化数据库，这里以在节点clusternode01为例进行创建，具体过程为：
1）关闭防火墙
关闭防火墙，并修改/etc/selinux/config文件的SELINUX值为disabled：
[root@clusternode01 ~]# systemctl status firewalld[root@clusternode01 ~]# systemctl stop firewalld[root@clusternode01 ~]# systemctl disable firewalld2）安装数据库软件所需的软件包
安装libaio库，该libaio库是数据目录初始化和后续服务器启动所需：
[root@clusternode01 ~]# yum install libaio对于Red at 8 / Oracle Linux 8 ，默认没有安装/lib64/libtinfo.so.5文件，该文件是MySQL客户端bin/mysql所需：
[root@clusternode01 ~]# yum install ncurses-compat-libs3）创建用户和组
创建数据库所需的用户和组：
[root@clusternode01 ~]# groupadd mysql[root@clusternode01 ~]# useradd -r -g mysql -s /bin/false mysql4）规整化数据目录
为便于数据库管理，规整化目录用于存放对应的数据文件：
[root@clusternode01 ~]# cat dir.shmkdir -p /data/mysqldata/mydatamkdir -p /data/mysqldata/innodb_tsmkdir -p /data/mysqldata/logmkdir -p /data/mysqldata/binlogmkdir -p /data/mysqldata/relaylogmkdir -p /data/mysqldata/innodb_logmkdir -p /data/mysqldata/innodb_undomkdir -p /data/mysqldata/redolog_archmkdir -p /data/mysqldata/tmpdirmkdir -p /data/mysqldata/mysecfilesmkdir -p /data/mysqldata/sockchown -R mysql:mysql /data/chmod -R 750 /data[root@clusternode01 ~]# source dir.sh5）上传安装包
使用上传工具，将MySQL安装包上传至指定目录下，这里上传到/root目录：
[root@clusternode01 ~]# ll p35*-rw-r--r--. 1 root root 556929507 Nov3 22:11 p35939952_100_Linux-x86-64.zip-rw-r--r--. 1 root root19652639 Nov3 22:11 p35940310_100_Linux-x86-64.zip-rw-r--r--. 1 root root 102405437 Nov3 22:11 p35942582_100_Linux-x86-64.zip安装包说明，公众号回复【MySQL 8.2安装包】即可获取：
35940310 MySQL Router 8.2.0 TAR for Generic Linux (glibc2.28) x86 (64bit) (Patchset)
35942582 MySQL Shell 8.2.0 TAR for Generic Linux (glibc2.28) x86 (64bit) (Patchset)
35939952 MySQL Commercial Server 8.2.0 TAR for Generic Linux (glibc2.28) x86 (64bit) (Patchset)
6）解压缩MySQL安装包
[root@clusternode01 ~]# unzip -q p35939952_100_Linux-x86-64.zip[root@clusternode01 ~]# cd /usr/local/[root@clusternode01 local]# tar xvf /root/mysql-commercial-8.2.0-linux-glibc2.28-x86_64.tar.xz7）创建软链接
[root@clusternode01 local]# ln -s mysql-commercial-8.2.0-linux-glibc2.28-x86_64 mysql8）创建配置文件
创建配置文件/etc/my.cnf，用于设置数据库相关的参数以及数据目录位置，因配置较多，这里省略，公众号回复【 my.cnf】获取完整配置文件，三个节点的server_id不一样。
9）初始化数据库
初始化数据库后会在错误日志中生成root@localhost初始化密码，需修改密码后方可使用数据库：
[root@clusternode01 local]# cd mysql[root@clusternode01 mysql]# ./bin/mysqld --initialize --user=mysql[root@clusternode01 ~]# more /data/mysqldata/log/clusternode01.err |grep "A temporary password"2023-11-04T22:08:40.283252+08:00 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: Fwu;-a:kM5bh10）拷贝mysql.server至/etc/init.d目录，可使用service命令进行启停数据库
[root@clusternode01 mysql]# cp support-files/mysql.server /etc/init.d/mysql.server11）启动数据库
[root@clusternode01 mysql]# service mysql.server startStarting MySQL..... SUCCESS![root@clusternode01 mysql]# service mysql.server statusSUCCESS! MySQL running (4136)12）登录数据库并修改用户root@localhost密码，创建root账户
[root@clusternode01 mysql]# ./bin/mysql -uroot -pEnter password:Welcome to the MySQL monitor.Commands end with ; or \g.Your MySQL connection id is 8Server version: 8.2.0-commercialCopyright (c) 2000, 2023, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> alter user 'root'@'localhost' identified by "alen#2023";Query OK, 0 rows affected (0.02 sec)mysql> create user root identified by "alen#2023";Query OK, 0 rows affected (0.04 sec)mysql> grant all on . to root with grant option;Query OK, 0 rows affected (0.03 sec)13）配置环境变量，并使其生效
[root@clusternode01 ~]# echo export "PATH=$PATH:/usr/local/mysql/bin" >> /root/.bash_profile[root@clusternode01 ~]# . .bash_profile3 InnoDB Cluster集群安装与配置
clusternode01、clusternode02、clusternode03节点的数据库安装并初始化完成后，接下来将配置InnoDB Cluster集群，InnoDB Cluster集群包括MySQL Server、MySQL Shell和MySQL Router。
3.1 安装MySQL Shell
MySQL Shell是一个客户端工具，用于管理MySQL实例、创建InnoDB Cluster，InnoDB ClusterSet等，·并与MySQL Router集成在一起，支持JavaScript、Python和SQL脚本，在节点clusternode01安装MySQL Shell工具：
1）上传MySQL Shell安装包
[root@clusternode01 ~]# ll p35942582_100_Linux-x86-64.zip-rw-r--r--. 1 root root 102405437 Nov4 21:58 p35942582_100_Linux-x86-64.zip[root@clusternode01 ~]# unzip -q p35942582_100_Linux-x86-64.zip2）安装MySQL Shell
[root@clusternode01 local]# cd /usr/local/[root@clusternode01 local]# tar zxvf /root/mysql-shell-commercial-8.2.0-linux-glibc2.28-x86-64bit.tar.gz[root@clusternode01 local]# ln -s mysql-shell-commercial-8.2.0-linux-glibc2.28-x86-64bit mysql-shell3）配置环境变量，并使其生效
[root@clusternode01 local]# echo "export PATH=$PATH:/usr/local/mysql-shell/bin" >> /root/.bash_profile[root@clusternode01 local]# . /root/.bash_profile4）启动MySQL Shell检查安装和路径配置
[root@clusternode01 local]# mysqlshPlease provide the password for 'root@localhost:3306': *********Save password for 'root@localhost:3306'" />[root@clusternode02 ~]# ll -h p35940310_100_Linux-x86-64.zip-rw-r--r--. 1 root root 19M Nov4 22:30 p35940310_100_Linux-x86-64.zip[root@clusternode02 ~]# unzip -q p35940310_100_Linux-x86-64.zip2）安装MySQL Router
[root@clusternode02 ~]# cd /usr/local/[root@clusternode02 local]# tar xvf /root/mysql-router-commercial-8.2.0-linux-glibc2.28-x86_64.tar.xz[root@clusternode02 local]# ln -s mysql-router-commercial-8.2.0-linux-glibc2.28-x86_64 mysql-router3）引导及启动MySQL Router
待InnoDB Cluster安装配置完成后，进行MySQL Router的引导与启动。
3.3 安装InnoDB Cluster集群
利用MySQL Shell安装和配置InnoDB Cluster 集群：
1）配置/etc/hosts
在clusternode01、clusternode02、clusternode03节点分别增加如下主机信息：
[root@clusternode01 ~]# vim /etc/hosts192.168.56.101 clusternode01192.168.56.102 clusternode02192.168.56.103 clusternode032）通过MySQL Shell连接clusternode01实例，执行如下操作对实例进行安装前检查配置：
[root@clusternode01 ~]# mysqlshMySQL Shell 8.2.0-commercialCopyright (c) 2016, 2023, 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:3306'Fetching schema names for auto-completion... Press ^C to stop.Your MySQL connection id is 21Server version: 8.2.0-commercial MySQL Enterprise Server - CommercialNo default schema selected; type \use to set one. MySQLlocalhost:3306 sslJS >MySQLlocalhost:3306 sslJS > dba.checkInstanceConfiguration('root@clusternode01:3306')Validating local MySQL instance listening at port 3306 for use in an InnoDB cluster...This instance reports its own address as clusternode01:3306Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.Checking whether existing tables comply with Group Replication requirements...No incompatible tables detectedChecking instance configuration...Instance configuration is compatible with InnoDB clusterThe instance 'clusternode01:3306' is valid to be used in an InnoDB cluster.{"status": "ok"} MySQLlocalhost:3306 sslJS > dba.checkInstanceConfiguration('root@clusternode02:3306')Validating MySQL instance at clusternode02:3306 for use in an InnoDB cluster...This instance reports its own address as clusternode02:3306Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.Checking whether existing tables comply with Group Replication requirements...No incompatible tables detectedChecking instance configuration...Instance configuration is compatible with InnoDB clusterThe instance 'clusternode02:3306' is valid to be used in an InnoDB cluster.{"status": "ok"} MySQLlocalhost:3306 sslJS > dba.checkInstanceConfiguration('root@clusternode03:3306')Validating MySQL instance at clusternode03:3306 for use in an InnoDB cluster...This instance reports its own address as clusternode03:3306Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.Checking whether existing tables comply with Group Replication requirements...No incompatible tables detectedChecking instance configuration...Instance configuration is compatible with InnoDB clusterThe instance 'clusternode03:3306' is valid to be used in an InnoDB cluster.{"status": "ok"} MySQLlocalhost:3306 sslJS > 3）配置实例，为集群使用做准备
MySQLlocalhost:3306 sslJS > dba.configureInstance('root@clusternode01:3306',{clusterAdmin: "clusteradmin"})Configuring local MySQL instance listening at port 3306 for use in an InnoDB cluster...This instance reports its own address as clusternode01:3306Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.Assuming full account name 'clusteradmin'@'%' for clusteradminPassword for new account: ************Confirm password: ************applierWorkerThreads will be set to the default value of 4.The instance 'clusternode01:3306' is valid to be used in an InnoDB cluster.Creating user clusteradmin@%.Account clusteradmin@% was successfully created.The instance 'clusternode01:3306' is already ready to be used in an InnoDB cluster.Successfully enabled parallel appliers. MySQLlocalhost:3306 sslJS > dba.configureInstance('root@clusternode02:3306',{clusterAdmin: "clusteradmin"})Configuring MySQL instance at clusternode02:3306 for use in an InnoDB cluster...This instance reports its own address as clusternode02:3306Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.Assuming full account name 'clusteradmin'@'%' for clusteradminPassword for new account: ************Confirm password: ************applierWorkerThreads will be set to the default value of 4.The instance 'clusternode02:3306' is valid to be used in an InnoDB cluster.Creating user clusteradmin@%.Account clusteradmin@% was successfully created.The instance 'clusternode02:3306' is already ready to be used in an InnoDB cluster.Successfully enabled parallel appliers. MySQLlocalhost:3306 sslJS > dba.configureInstance('root@clusternode03:3306',{clusterAdmin: "clusteradmin"})Configuring MySQL instance at clusternode03:3306 for use in an InnoDB cluster...This instance reports its own address as clusternode03:3306Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.Assuming full account name 'clusteradmin'@'%' for clusteradminPassword for new account: ************Confirm password: ************applierWorkerThreads will be set to the default value of 4.The instance 'clusternode03:3306' is valid to be used in an InnoDB cluster.Creating user clusteradmin@%.Account clusteradmin@% was successfully created.The instance 'clusternode03:3306' is already ready to be used in an InnoDB cluster.Successfully enabled parallel appliers. MySQLlocalhost:3306 sslJS > 4）创建InnoDB Cluster集群
以用户clusteradmin登录MySQL Shell，连接clusternode01实例进行集群的创建：
MySQLlocalhost:3306 sslJS > \connect clusteradmin@clusternode01:3306Creating a session to 'clusteradmin@clusternode01:3306'Please provide the password for 'clusteradmin@clusternode01:3306': ************Save password for 'clusteradmin@clusternode01:3306'? [Y]es/[N]o/Ne[v]er (default No): YFetching schema names for auto-completion... Press ^C to stop.Closing old connection...Your MySQL connection id is 26Server version: 8.2.0-commercial MySQL Enterprise Server - CommercialNo default schema selected; type \use to set one. MySQLclusternode01:3306 sslJS > var cluster=dba.createCluster('alencluster')A new InnoDB Cluster will be created on instance 'clusternode01:3306'.Disabling super_read_only mode on instance 'clusternode01:3306'.Validating instance configuration at clusternode01:3306...This instance reports its own address as clusternode01:3306Instance configuration is suitable.NOTE: Group Replication will communicate with other members using 'clusternode01:3306'. Use the localAddress option to override.* Checking connectivity and SSL configuration...Creating InnoDB Cluster 'alencluster' on 'clusternode01:3306'...Adding Seed Instance...NOTE: User 'mysql_innodb_cluster_56101'@'%' already existed at instance 'clusternode01:3306'. It will be deleted and created again with a new password.Cluster successfully created. Use Cluster.addInstance() to add MySQL instances.At least 3 instances are needed for the cluster to be able to withstand up toone server failure. MySQLclusternode01:3306 sslJS > 5）增加实例至InnoDB Cluster集群
InnoDB Cluster建议至少有3个实例，当其中一个故障后，其他服务器可接管，这里新增两个实例：
MySQLclusternode01:3306 sslJS > cluster.addInstance('clusteradmin@clusternode02:3306')WARNING: A GTID set check of the MySQL instance at 'clusternode02:3306' determined that it contains transactions that do not originate from the cluster, which must be discarded before it can join the cluster.clusternode02:3306 has the following errant GTIDs that do not exist in the cluster:bca813e5-7b1f-11ee-b9c1-080027509a77:1-3WARNING: Discarding these extra GTID events can either be done manually or by completely overwriting the state of clusternode02:3306 with a physical snapshot from an existing cluster 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): CValidating instance configuration at clusternode02:3306...This instance reports its own address as clusternode02:3306Instance configuration is suitable.NOTE: Group Replication will communicate with other members using 'clusternode02:3306'. Use the localAddress option to override.* Checking connectivity and SSL configuration...A new instance will be added to the InnoDB Cluster. Depending on the amount ofdata on the cluster this might take from a few seconds to several hours.Adding instance to the cluster...Monitoring recovery process of the new cluster member. Press ^C to stop monitoring and let it continue in background.Clone based state recovery is now in progress.NOTE: A server restart is expected to happen as part of the clone process. If theserver does not support the RESTART command or does not come back after awhile, you may need to manually start it back.* Waiting for clone to finish...NOTE: clusternode02:3306 is being cloned from clusternode01:3306** Stage DROP DATA: Completed** Clone TransferFILE COPY############################################################100%CompletedPAGE COPY############################################################100%CompletedREDO COPY############################################################100%CompletedNOTE: clusternode02:3306 is shutting down...* Waiting for server restart... ready * clusternode02:3306 has restarted, waiting for clone to finish...** Stage RESTART: Completed* Clone process has finished: 4.36 GB transferred in 1 min 36 sec (45.39 MB/s)State recovery already finished for 'clusternode02:3306'The instance 'clusternode02:3306' was successfully added to the cluster. MySQLclusternode01:3306 sslJS >MySQLclusternode01:3306 sslJS > cluster.addInstance('clusteradmin@clusternode03:3306')............................输出省略............................6）查看InnoDB Cluster集群状态
MySQLclusternode01:3306 sslJS > cluster.status(){"clusterName": "alencluster", "defaultReplicaSet": {"name": "default", "primary": "clusternode01:3306", "ssl": "REQUIRED", "status": "OK", "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", "topology": {"clusternode01:3306": {"address": "clusternode01:3306", "memberRole": "PRIMARY", "mode": "R/W", "readReplicas": {}, "replicationLag": "applier_queue_applied", "role": "HA", "status": "ONLINE", "version": "8.2.0"}, "clusternode02:3306": {"address": "clusternode02:3306", "memberRole": "SECONDARY", "mode": "R/O", "readReplicas": {}, "replicationLag": "applier_queue_applied", "role": "HA", "status": "ONLINE", "version": "8.2.0"}, "clusternode03:3306": {"address": "clusternode03:3306", "memberRole": "SECONDARY", "mode": "R/O", "readReplicas": {}, "replicationLag": "applier_queue_applied", "role": "HA", "status": "ONLINE", "version": "8.2.0"}}, "topologyMode": "Single-Primary"}, "groupInformationSourceMember": "clusternode01:3306"}7）创建Router管理账户
MySQLclusternode01:3306 sslJS > cluster.setupRouterAccount('routeradmin')Missing the password for new account routeradmin@%. Please provide one.Password for new account: ***********Confirm password: ***********Creating user routeradmin@%.Account routeradmin@% was successfully created.8）引导及启动MySQL Router在clusternode02节点进行MySQL Router的引导：[root@clusternode02 ~]# cd /usr/local/mysql-router/[root@clusternode02 mysql-router]# ./bin/mysqlrouter --bootstrap clusteradmin@clusternode01:3306 --directory router_alen --name router_alen --account=routeradmin --user=mysqlPlease enter MySQL password for clusteradmin: # Bootstrapping MySQL Router 8.2.0 (MySQL Enterprise - Commercial) instance at '/usr/local/mysql-router-commercial-8.2.0-linux-glibc2.28-x86_64/router_alen'...Please enter MySQL password for routeradmin: - Creating account(s) (only those that are needed, if any)- Verifying account (using it to run SQL queries that would be run by Router)- Storing account in keyring- Adjusting permissions of generated files- Creating configuration /usr/local/mysql-router-commercial-8.2.0-linux-glibc2.28-x86_64/router_alen/mysqlrouter.conf# MySQL Router 'router_alen' configured for the InnoDB Cluster 'alencluster'After this MySQL Router has been started with the generated configuration$ ./bin/mysqlrouter -c /usr/local/mysql-router-commercial-8.2.0-linux-glibc2.28-x86_64/router_alen/mysqlrouter.confInnoDB Cluster 'alencluster' can be reached by connecting to:## MySQL Classic protocol- Read/Write Connections: localhost:6446- Read/Only Connections:localhost:6447- Read/Write Split Connections: localhost:6450## MySQL X protocol- Read/Write Connections: localhost:6448- Read/Only Connections:localhost:6449[root@clusternode02 mysql-router]# 通过上述端口可以看到：
读写连接使用的端口为：6446
只读连接使用的端口为：6447
读写分离连接使用的端口为：6450
启动MySQL Router：
[root@clusternode02 mysql-router]# lltotal 100drwxr-xr-x 2 rootroot109 Nov5 10:19 bindrwxr-xr-x 4 rootroot149 Nov5 10:19 lib-rw-r--r-- 17161 31415 97887 Oct 12 19:49 LICENSE.routerdrwxr-xr-x 3 rootroot 18 Nov5 10:19 man-rw-r--r-- 17161 31415 733 Oct 12 19:49 README.routerdrwx------ 5 mysql mysql 118 Nov5 10:28 router_alendrwxr-xr-x 3 rootroot 17 Nov5 10:19 share[root@clusternode02 mysql-router]# ./router_alen/start.sh [root@clusternode02 mysql-router]# PID 8950 written to '/usr/local/mysql-router-commercial-8.2.0-linux-glibc2.28-x86_64/router_alen/mysqlrouter.pid'stopping to log to the console. Continuing to log to filelog[root@clusternode02 mysql-router]#4 读写分离演示
InnoDB Cluster安装配置完成并初始化MySQL Router后，进行读写分离的演示：
1）查看InnoDB Cluster集群状态，可以看出节点clusternode01处于读写状态，节点clusternode02和clusternode03处于只读状态：
[root@clusternode01 ~]# mysqlsh \connect clusteradmin@clusternode01:3306MySQL Shell 8.2.0-commercialCopyright (c) 2016, 2023, 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 'clusteradmin@clusternode01:3306'Fetching schema names for auto-completion... Press ^C to stop.Your MySQL connection id is 3187Server version: 8.2.0-commercial MySQL Enterprise Server - CommercialNo default schema selected; type \use to set one. MySQLclusternode01:3306 sslJS > var cluster=dba.getCluster() MySQLclusternode01:3306 sslJS > cluster.status(){"clusterName": "alencluster", "defaultReplicaSet": {"name": "default", "primary": "clusternode01:3306", "ssl": "REQUIRED", "status": "OK", "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", "topology": {"clusternode01:3306": {"address": "clusternode01:3306", "memberRole": "PRIMARY", "mode": "R/W", "readReplicas": {}, "replicationLag": "applier_queue_applied", "role": "HA", "status": "ONLINE", "version": "8.2.0"}, "clusternode02:3306": {"address": "clusternode02:3306", "memberRole": "SECONDARY", "mode": "R/O", "readReplicas": {}, "replicationLag": "applier_queue_applied", "role": "HA", "status": "ONLINE", "version": "8.2.0"}, "clusternode03:3306": {"address": "clusternode03:3306", "memberRole": "SECONDARY", "mode": "R/O", "readReplicas": {}, "replicationLag": "applier_queue_applied", "role": "HA", "status": "ONLINE", "version": "8.2.0"}}, "topologyMode": "Single-Primary"}, "groupInformationSourceMember": "clusternode01:3306"}2）查看MySQL Router信息，可以看出6450是读写分离端口，6446是读写端口，6447是只读端口
MySQLclusternode01:3306 sslJS > cluster.listRouters(){"clusterName": "alencluster", "routers": {"clusternode02::router_alen": {"hostname": "clusternode02", "lastCheckIn": "2023-11-05 11:13:20", "roPort": "6447", "roXPort": "6449", "rwPort": "6446", "rwSplitPort": "6450", "rwXPort": "6448", "version": "8.2.0"}}} MySQLclusternode01:3306 sslJS >3）连接MySQL数据库，使用读写分离端口6450进行连接，可以看出，执行查询语句时，自动路由到只读节点clusternode03，当开启事务进行时，自动路由到节点clusternode01，从而实现了数据库的读写分离：
[root@clusternode02 ~]# mysql -h127.0.0.1 -uroot -p -P6450Enter password: Welcome to the MySQL monitor.Commands end with ; or \g.Your MySQL connection id is 0Server version: 8.2.0-router MySQL Enterprise Server - CommercialCopyright (c) 2000, 2023, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> select @@hostname,@@port;+---------------+--------+| @@hostname| @@port |+---------------+--------+| clusternode03 | 3306 |+---------------+--------+1 row in set (0.03 sec)mysql> start transaction;Query OK, 0 rows affected (0.02 sec)mysql> select @@hostname,@@port;+---------------+--------+| @@hostname| @@port |+---------------+--------+| clusternode01 | 3306 |+---------------+--------+1 row in set (0.01 sec)mysql>4）会话级别设置读写分离
会话级别的读写分离配置可使用下面任一配置：
- ROUTER SET optionName=’value’
- query_attributes router.optionName value
optionName 和 values值如下：
- access_mode值为：
- read_write：所有会话流量都发送到读写服务器
- read_only：所有会话流量都发送到只读服务器
- auto：根据事务类型选择服务器，读操作发送到只读服务器，写操作发送到读写服务器
- wait_for_my_writes [ 0 | 1 ]：如果启用，则只读查询将等待会话的最后一个写入事务
- wait_for_my_writes_timeout [ 0 | 4294967295]：默认为1秒
示例演示：
mysql> router set access_mode='read_only';Query OK, 0 rows affected (0.00 sec)mysql> select @@hostname,@@port;+---------------+--------+| @@hostname| @@port |+---------------+--------+| clusternode03 | 3306 |+---------------+--------+1 row in set (0.02 sec)mysql> router set access_mode='read_write';Query OK, 0 rows affected (0.00 sec)mysql> select @@hostname,@@port;+---------------+--------+| @@hostname| @@port |+---------------+--------+| clusternode01 | 3306 |+---------------+--------+1 row in set (0.02 sec)mysql> commit;Query OK, 0 rows affected (0.00 sec)mysql>以上，便是MySQL 8.2 数据库读写分离的演示，MySQL Router 8.2支持读写拆分，这对于优化数据库性能和可伸缩性来说是一个很有价值的功能，而无需对应用程序做任何更改，此配置可以将所有读流量定向到只读实例，并将所有写流量定向到读写实例。