看!MySQL 8.2 数据库支持读写分离啦!

这篇具有很好参考价值的文章主要介绍了看!MySQL 8.2 数据库支持读写分离啦!。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

更多文章,欢迎关注作者公众号,欢迎一起交流。

mysql8.2,MySQL系列,-----【HA高可用】,数据库,MySQL,InnoDB Cluster,MySQL 8.2

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 firewalld

2)安装数据库软件所需的软件包

安装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-libs

3)创建用户和组

创建数据库所需的用户和组:

[root@clusternode01 ~]# groupadd mysql
[root@clusternode01 ~]# useradd -r -g mysql -s /bin/false mysql

4)规整化数据目录

为便于数据库管理,规整化目录用于存放对应的数据文件:

[root@clusternode01 ~]# cat dir.sh
mkdir -p /data/mysqldata/mydata
mkdir -p /data/mysqldata/innodb_ts
mkdir -p /data/mysqldata/log
mkdir -p /data/mysqldata/binlog
mkdir -p /data/mysqldata/relaylog
mkdir -p /data/mysqldata/innodb_log
mkdir -p /data/mysqldata/innodb_undo
mkdir -p /data/mysqldata/redolog_arch
mkdir -p /data/mysqldata/tmpdir
mkdir -p /data/mysqldata/mysecfiles
mkdir -p /data/mysqldata/sock
chown -R mysql:mysql /data/
chmod -R 750 /data
[root@clusternode01 ~]# source dir.sh

5)上传安装包

使用上传工具,将MySQL安装包上传至指定目录下,这里上传到/root目录:

[root@clusternode01 ~]# ll p35*
-rw-r--r--. 1 root root 556929507 Nov  3 22:11 p35939952_100_Linux-x86-64.zip
-rw-r--r--. 1 root root  19652639 Nov  3 22:11 p35940310_100_Linux-x86-64.zip
-rw-r--r--. 1 root root 102405437 Nov  3 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.xz

 7)创建软链接

[root@clusternode01 local]# ln -s mysql-commercial-8.2.0-linux-glibc2.28-x86_64 mysql

8)创建配置文件

创建配置文件/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:kM5bh

10)拷贝mysql.server至/etc/init.d目录,可使用service命令进行启停数据库

[root@clusternode01 mysql]# cp support-files/mysql.server /etc/init.d/mysql.server

11)启动数据库

[root@clusternode01 mysql]# service mysql.server start
Starting MySQL..... SUCCESS!
[root@clusternode01 mysql]# service mysql.server status
SUCCESS! MySQL running (4136)

12)登录数据库并修改用户root@localhost密码,创建root账户

[root@clusternode01 mysql]# ./bin/mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.2.0-commercial
Copyright (c) 2000, 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 '\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_profile

3 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 Nov  4 21:58 p35942582_100_Linux-x86-64.zip
[root@clusternode01 ~]# unzip -q p35942582_100_Linux-x86-64.zip

2)安装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-shell

3)配置环境变量,并使其生效

[root@clusternode01 local]# echo "export PATH=$PATH:/usr/local/mysql-shell/bin" >> /root/.bash_profile
[root@clusternode01 local]# . /root/.bash_profile

4)启动MySQL Shell检查安装和路径配置

[root@clusternode01 local]# mysqlsh
Please provide the password for 'root@localhost:3306': *********
Save password for 'root@localhost:3306'? [Y]es/[N]o/Ne[v]er (default No): Y
MySQL Shell 8.2.0-commercial
Copyright (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 8
Server version: 8.2.0-commercial MySQL Enterprise Server - Commercial
No default schema selected; type \use <schema> to set one.
MySQL  localhost:3306 ssl  JS >

启动MySQL Shell,默认是JavaScript模式,可以切换到sql、py模式。

3.2 安装MySQL Router

MySQL Router是InnoDB Cluster集群的一部分,是一种轻量级中间件,可在应用程序和后端MySQL服务器之间提供透明路由,MySQL 8.2的数据库读写分离也是使用Router来实现的。为了获得更好的性能,官方建议MySQL Router建议和应用服务器安装在同一台主机上,这里为了演示方便,将其安装在clusternode02节点上。

1)上传MySQL Router安装包

[root@clusternode02 ~]# ll -h p35940310_100_Linux-x86-64.zip
-rw-r--r--. 1 root root 19M Nov  4 22:30 p35940310_100_Linux-x86-64.zip
[root@clusternode02 ~]# unzip -q p35940310_100_Linux-x86-64.zip

 2)安装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-router

3)引导及启动MySQL Router

待InnoDB Cluster安装配置完成后,进行MySQL Router的引导与启动。

3.3 安装InnoDB Cluster集群

利用MySQL Shell安装和配置InnoDB Cluster 集群:

1)配置/etc/hosts

在clusternode01、clusternode02、clusternode03节点分别增加如下主机信息:

[root@clusternode01 ~]# vim /etc/hosts
192.168.56.101 clusternode01
192.168.56.102 clusternode02
192.168.56.103 clusternode03

2)通过MySQL Shell连接clusternode01实例,执行如下操作对实例进行安装前检查配置:

[root@clusternode01 ~]# mysqlsh
MySQL Shell 8.2.0-commercial

Copyright (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 21
Server version: 8.2.0-commercial MySQL Enterprise Server - Commercial
No default schema selected; type \use <schema> to set one.
 MySQL  localhost:3306 ssl  JS > 
 MySQL  localhost:3306 ssl  JS > 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:3306
Clients 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 detected

Checking instance configuration...
Instance configuration is compatible with InnoDB cluster

The instance 'clusternode01:3306' is valid to be used in an InnoDB cluster.

{
    "status": "ok"
}
 MySQL  localhost:3306 ssl  JS > 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:3306
Clients 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 detected

Checking instance configuration...
Instance configuration is compatible with InnoDB cluster

The instance 'clusternode02:3306' is valid to be used in an InnoDB cluster.

{
    "status": "ok"
}
 MySQL  localhost:3306 ssl  JS > 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:3306
Clients 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 detected

Checking instance configuration...
Instance configuration is compatible with InnoDB cluster

The instance 'clusternode03:3306' is valid to be used in an InnoDB cluster.

{
    "status": "ok"
}
 MySQL  localhost:3306 ssl  JS > 

3)配置实例,为集群使用做准备

 MySQL  localhost:3306 ssl  JS > 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:3306
Clients 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 clusteradmin
Password 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.
 MySQL  localhost:3306 ssl  JS > 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:3306
Clients 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 clusteradmin
Password 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.
 MySQL  localhost:3306 ssl  JS > 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:3306
Clients 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 clusteradmin
Password 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.
 MySQL  localhost:3306 ssl  JS > 

4)创建InnoDB Cluster集群

以用户clusteradmin登录MySQL Shell,连接clusternode01实例进行集群的创建:

 MySQL  localhost:3306 ssl  JS > \connect clusteradmin@clusternode01:3306
Creating 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): Y
Fetching schema names for auto-completion... Press ^C to stop.
Closing old connection...
Your MySQL connection id is 26
Server version: 8.2.0-commercial MySQL Enterprise Server - Commercial
No default schema selected; type \use <schema> to set one.
 MySQL  clusternode01:3306 ssl  JS > 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:3306

Instance 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 to
one server failure.

 MySQL  clusternode01:3306 ssl  JS > 

5)增加实例至InnoDB Cluster集群

InnoDB Cluster建议至少有3个实例,当其中一个故障后,其他服务器可接管,这里新增两个实例:

 MySQL  clusternode01:3306 ssl  JS > 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-3

WARNING: 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): C
Validating instance configuration at clusternode02:3306...

This instance reports its own address as clusternode02:3306

Instance 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 of
data 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 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: clusternode02:3306 is being cloned from clusternode01:3306
** Stage DROP DATA: Completed
** Clone Transfer  
    FILE COPY  ############################################################  100%  Completed
    PAGE COPY  ############################################################  100%  Completed
    REDO COPY  ############################################################  100%  Completed

NOTE: 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.

 MySQL  clusternode01:3306 ssl  JS > 
 MySQL  clusternode01:3306 ssl  JS > cluster.addInstance('clusteradmin@clusternode03:3306')
............................输出省略............................

6)查看InnoDB Cluster集群状态

MySQL  clusternode01:3306 ssl  JS > 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管理账户

 MySQL  clusternode01:3306 ssl  JS > 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=mysql
Please 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.conf

InnoDB 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]# ll
total 100
drwxr-xr-x 2 root  root    109 Nov  5 10:19 bin
drwxr-xr-x 4 root  root    149 Nov  5 10:19 lib
-rw-r--r-- 1  7161 31415 97887 Oct 12 19:49 LICENSE.router
drwxr-xr-x 3 root  root     18 Nov  5 10:19 man
-rw-r--r-- 1  7161 31415   733 Oct 12 19:49 README.router
drwx------ 5 mysql mysql   118 Nov  5 10:28 router_alen
drwxr-xr-x 3 root  root     17 Nov  5 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:3306
MySQL Shell 8.2.0-commercial

Copyright (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 3187
Server version: 8.2.0-commercial MySQL Enterprise Server - Commercial
No default schema selected; type \use <schema> to set one.
 MySQL  clusternode01:3306 ssl  JS > var cluster=dba.getCluster()
 MySQL  clusternode01:3306 ssl  JS > 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是只读端口

 MySQL  clusternode01:3306 ssl  JS > 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"
        }
    }
}
 MySQL  clusternode01:3306 ssl  JS >

3)连接MySQL数据库,使用读写分离端口6450进行连接,可以看出,执行查询语句时,自动路由到只读节点clusternode03,当开启事务进行时,自动路由到节点clusternode01,从而实现了数据库的读写分离:

[root@clusternode02 ~]# mysql -h127.0.0.1 -uroot -p -P6450
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 0
Server version: 8.2.0-router MySQL Enterprise Server - Commercial

Copyright (c) 2000, 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 '\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支持读写拆分,这对于优化数据库性能和可伸缩性来说是一个很有价值的功能,而无需对应用程序做任何更改,此配置可以将所有读流量定向到只读实例,并将所有写流量定向到读写实例。文章来源地址https://www.toymoban.com/news/detail-755545.html

到了这里,关于看!MySQL 8.2 数据库支持读写分离啦!的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • 【数据库】mysql主从复制与读写分离

      读写分离,基本的原理是让主数据库处理事务性增、改、删操作(INSERT、UPDATE、DELETE ),而从数据库处理SELECT查询操作。数据库复制被用来把事务性操作导致的变更同步到集群中的从数据库。   因为数据库的\\\"写”(写10000条数据可能要3分钟)操作是比较耗时的。   

    2024年02月11日
    浏览(32)
  • MySQL数据库 主从复制与读写分离

    读写分离,基本的原理是让主数据库处理事务性增、改、删操作(INSERT、UPDATE、DELETE),而从数据库处理SELECT查询操作。数据库复制被用来把事务性操作导致的变更同步到集群中的从数据库。 因为数据库的“写”(写10000条数据可能要3分钟)操作是比较耗时的。 但是数据库

    2024年02月10日
    浏览(16)
  • 【数据库七】MySQL主从复制与读写分离

    读写分离,基本的原理是让主数据库处理事务性增、改、删操作 (insert、update、delete),而 从数据库处理select查询操作 。 数据库复制被用来把事务性操作导致的变更同步到集群中的从数据库 。 因为数据库的“写”(写10000条数据可能要3分钟)操作是比较耗时的。 但是数据

    2024年02月11日
    浏览(28)
  • 华为云数据库 RDS for MySQL 的读写分离,凭什么打破企业数据瓶颈?

    随着云2.0时代的到来,众多企业发现人工智能、大数据算法能为企业发展带来巨大的利润,人工智能、大数据算法等计算机算法被应用于企业的生产和发展,云+业务逐渐成为现代化企业发展的潮流,对传统数据库模式提出了挑战。 企业的业务从线下发展到线上,这就使得服

    2024年02月22日
    浏览(20)
  • spring boot shardingsphere mybatis-plus druid mysql 搭建mysql数据库读写分离架构

    ##关于window mysql主从搭建简单教程 传送门 window mysql5.7 搭建主从同步环境-CSDN博客 ##父pom.xml ##模块pom.xml ##yml配置 ##mapper.xml ##TestMapper ##TestService ##TestController ##浏览器访问 ##数据库

    2024年01月21日
    浏览(26)
  • 数据库:mycat实现读写分离

    目录 一、mycat 1、mycat实现读写分离原理 2、mycat应用场景 3、mycat作用 4、mycat实现读写分离实战 1、mycat实现读写分离原理 ①用户进行读操作则由mycat转给配置的从数据库。 ②用户进行写操作则由mycat转给配置的主数据库。 ③转发规则由mycat配置文件中定义,那台是读那台是写

    2024年02月07日
    浏览(12)
  • 如何正确使用数据库的读写分离

    在应用系统发展的初期,我们并不知道以后会发展成什么样的规模,所以一开始不会考虑复杂的系统架构,复杂的系统架构费时费力,开发周期长,与系统发展初期这样的一个定位是不吻合的。所以,我们都会采用简单的架构,随着业务不断的发展,访问量不断升高,我们再

    2024年02月02日
    浏览(26)
  • 数据库应用:Mycat实现读写分离

    目录 一、理论 1.Mycat 2.Mycat安装启动 3.Mycat搭建读写分离 4.垂直分库 5.水平分表 6.Mycat高可用 7.Mycat安全设置 8.Mycat监控工具 二、实验 1.Mycat读写分离 2.Mycat监控安装 三、问题 1.Mycat命令无法补全 2.Mycat启动失败 3.zookeeper启动报错 四、总结 (1)概念 MyCAT是一款由阿里Cobar演变而来

    2024年02月15日
    浏览(14)
  • 项目练习:构建读写分离的数据库集群

    目录 一、目标实现 二、项目准备 三、项目实施 1.配置基础环境 1.1修改主机名 1.2配置3台群集虚拟机的/etc/hosts文件 1.3.配置网卡 1.4.配置本地yum源 2.部署Mycat中间件服务 2.1安装KDJ环境 3.部署MariaDB主从数据库集群服务 3.1安装MariaDB服务 3.2初始化MariaDB数据库 3.3配置数据库集群主

    2024年02月02日
    浏览(17)
  • 架构篇14:高性能数据库集群-读写分离

    高性能数据库集群的第一种方式是“读写分离”,其本质是将访问压力分散到集群中的多个节点,但是没有分散存储压力;第二种方式是“分库分表”,既可以分散访问压力,又可以分散存储压力。先来看看“读写分离”,下一篇我们再介绍“分库分表”。 读写分离的基本原

    2024年01月24日
    浏览(22)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包