基于MySQL 8.0搭建InnoDB Cluster集群

说明：

1）主机规划(IP+主机名)：

172.17.0.35 node01

172.17.0.29 node02

172.17.0.56 node03

2）软件版本

操作系统版本：CentOS 7.6

MySQL Server版本：8.0.32

MySQL Server安装目录：/app/

MySQL Data目录：/data/mysqldb/data

MySQL Shell版本：8.0.32

MySQL Shell安装目录：/app/

MySQL Router版本：8.0.32

MySQL Router安装目录：/app/

MySQL Router配置目录：/data/myrouter/6446

一、下载对应的软件包

cd /app

wget https://cdn.mysql.com/archives/mysql-8.0/mysql-8.0.32-linux-glibc2.12-x86_64.tar

wget https://cdn.mysql.com/archives/mysql-router/mysql-router-8.0.32-linux-glibc2.12-x86_64.tar.xz

wget https://cdn.mysql.com/archives/mysql-shell/mysql-shell-8.0.32-linux-glibc2.12-x86-64bit.tar.gz

二、系统准备

以下 6 小节操作需要在三台主机执行。

1、centos7 关闭防火墙

systemctl stop firewalld.service 或者 systemctl stop firewalld

systemctl disable firewalld.service 或者 systemctl disable firewalld

systemctl status firewalld

2、关闭 selinux

getenforce

setenforce 0

vim /etc/selinux/config

SELINUX=disabled

3、绑定 /etc/hosts 解析(示例如下)

vim /etc/hosts

172.17.0.35 node1

172.17.0.29 node2

172.17.0.30 node3

4、安装 yum 包

yum -y groupinstall “DeveLopment tools”

yum -y install ncurses ncurses-devel openssl-devel bison gcc gcc-c++ make

5、清理系统自带 MariaDB 环境

CentOS 7 版本的系统默认自带安装了 MariaDB，需要先清理。

##查询已安装的mariadb

rpm -qa |grep mariadb

或

yum list installed | grep mariadb

##卸载 mariadb 包，文件名为上述命令查询出来的文件

rpm -e --nodeps mariadb-libs-5.5.56-2.el7.x86_64

yum -y remove mariadb-libs.x86_64

6、内核参数修改

[root@node1 ~]# cat>>/etc/sysctl.conf <<EOF

fs.aio-max-nr = 1048576

fs.file-max = 681574400

kernel.shmmax = 137438953472

kernel.shmmni = 4096

kernel.sem = 250 32000 100 200

net.ipv4.ip_local_port_range = 9000 65000

net.core.rmem_default = 262144

net.core.rmem_max = 4194304

net.core.wmem_default = 262144

net.core.wmem_max = 1048586

EOF

[root@node1 ~]# sysctl -p

[root@node1 ~]# cat>>/etc/security/limits.conf <<EOF

mysql soft nproc 65536

mysql hard nproc 65536

mysql soft nofile 65536

mysql hard nofile 65536

EOF

[root@node1 ~]# cat>>/etc/pam.d/login <<EOF

session required /lib/security/pam_limits.so

session required pam_limits.so

EOF

[root@node1 ~]# cat>>/etc/profile<<EOF

if [ $USER = “mysql” ]; then

ulimit -u 16384 -n 65536

fi

EOF

三、安装 MySQL Server

1、三台主机创建数据库用户，创建实例所需目录

如果没有 /data 文件系统，可按照下面目录结构创建相对应的目录。

root 用户操作：

mkdir -p /app/

mkdir -p /data/mysqldb/conf/

mkdir -p /data/mysqldb/data/

mkdir -p /data/mysqldb/pid/

mkdir -p /data/mysqldb/socket/

mkdir -p /data/mysqldb/log/

mkdir -p /data/mysqldb/binlog/

mkdir -p /data/mysqldb/relaylog/

mkdir -p /data/mysqldb/slowlog/

mkdir -p /data/mysqldb/tmp/

groupadd mysql

useradd -g mysql mysql

chown -R mysql:mysql /data/mysqldb

chown -R mysql:mysql /app

echo mysql |passwd --stdin mysql

2、三台主机解压 MySQL server 安装程序包

mysql 用户操作

cd /app

tar xvf mysql-8.0.32-linux-glibc2.12-x86_64.tar.xz

mv mysql-8.0.32-linux-glibc2.12-x86_64 mysql8.0.32

3、三台主机配置 mysql 用户环境变量

配置环境变量

vim /etc/profile

PATH=/app/mysql8.0.32/bin:/app/mysql8.0.32/bin/lib:/usr/local/bin:/usr/local/sbin:/usr/bin:/bin:/usr/sbin:/sbin:/root/bin:/app/mysql-shell/bin:/app/mysqlrouter/bin:$PATH

export PATH

source /etc/profile

which mysql

4、编辑mysql初始化参数文件并初始化(参数文件名 mysql.conf)

Node1参数文件如下,Node2 和 Node3 节点需要注意server_id 不能重复，innodb_buffer_pool_size 内存一般设置为 OS 的 50%，report_host写本机地址，loose_group_replication_local_address 写本机地址和端口号。

node1：

vim /data/mysqldb/conf/mysql.conf

[mysqld]

#basic settings#

default-storage-engine= InnoDB

server_id = 353306

basedir = /app/mysql8.0.32

datadir = /data/mysqldb/data/

tmpdir = /data/mysqldb/tmp/

socket = /data/mysqldb/socket/mysql.sock

pid_file = /data/mysqldb/pid/mysqld.pid

port = 3306

default-time_zone = ‘+8:00’

character_set_server = utf8mb4

explicit_defaults_for_timestamp = 1

autocommit = 1

transaction_isolation = READ-COMMITTED

secure_file_priv = “/data/mysqldb/tmp/”

max_allowed_packet = 64M

lower_case_table_names = 1

default_authentication_plugin = mysql_native_password

sql_mode=‘STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION’

#connection#

back_log = 500

interactive_timeout = 300

wait_timeout = 1800

lock_wait_timeout = 300

max_user_connections = 800

skip_name_resolve = 1

max_connections = 2500

max_connect_errors = 1000

#table cache performance settings

#table_open_cache = 1024

#table_definition_cache = 1024

#table_open_cache_instances = 16

#session memory settings #

#read_buffer_size = 16M

#read_rnd_buffer_size = 32M

#sort_buffer_size = 32M

#tmp_table_size = 64M

#join_buffer_size = 128M

#thread_cache_size = 256

#log settings#

slow_query_log = ON

slow_query_log_file = /data/mysqldb/slowlog/slow.log

log_error = /data/mysqldb/log/mysqld.log

log_error_verbosity = 3

log_bin = /data/mysqldb/binlog/mysql_bin

log_bin_index = /data/mysqldb/binlog/mysql_binlog.index

#general_log_file = /data/mysql/generallog/general.log

log_queries_not_using_indexes = 1

log_slow_admin_statements = 1

#log_slow_slave_statements = 1

#expire_logs_days = 15

binlog_expire_logs_seconds = 2592000

long_query_time = 5

min_examined_row_limit = 100

log_throttle_queries_not_using_indexes = 1000

#log_bin_trust_function_creators = 1

log_replica_updates = 1

mysqlx_port = 33060

mysqlx_socket = /data/mysqldb/socket/mysqlx.sock

#innodb settings#

innodb_buffer_pool_size = 4G

innodb_buffer_pool_instances = 4

innodb_log_buffer_size = 100M

innodb_buffer_pool_load_at_startup = 1

innodb_buffer_pool_dump_at_shutdown = 1

innodb_lru_scan_depth = 4096

innodb_lock_wait_timeout = 20

innodb_io_capacity = 5000

innodb_io_capacity_max = 10000

innodb_flush_method = O_DIRECT

innodb_log_file_size = 1G

innodb_log_files_in_group = 3

innodb_purge_threads = 4

innodb_thread_concurrency = 200

innodb_print_all_deadlocks = 1

innodb_print_ddl_logs = 1

innodb_strict_mode = 1

innodb_sort_buffer_size = 32M

innodb_write_io_threads = 16

innodb_read_io_threads = 16

innodb_file_per_table = 1

innodb_stats_persistent_sample_pages = 64

innodb_autoinc_lock_mode = 2

innodb_online_alter_log_max_size = 1G

innodb_open_files = 4096

innodb_buffer_pool_dump_pct = 25

innodb_page_cleaners = 16

innodb_undo_log_truncate = 1

innodb_max_undo_log_size = 2G

innodb_purge_rseg_truncate_frequency = 128

innodb_flush_log_at_trx_commit = 1

log_timestamps = UTC

#replication settings#

sync_binlog = 1

binlog_format = ROW

gtid_mode = ON

enforce_gtid_consistency = ON

relay_log_recovery = 1

relay_log = /data/mysqldb/relaylog/relay.log

relay_log_index = /data/mysqldb/relaylog/mysql_relay.index

replica_parallel_type = LOGICAL_CLOCK

replica_parallel_workers = 16

binlog_gtid_simple_recovery = 1

replica_preserve_commit_order = 1

binlog_rows_query_log_events = 1

replica_transaction_retries = 10

#group replication settings#

transaction_write_set_extraction = XXHASH64

binlog_checksum = NONE

loose_group_replication_group_name = “957e8af0-bc63-11ea-bb19-005056a52572”

loose_group_replication_start_on_boot = off

loose_group_replication_local_address = “172.17.0.35:24901”

loose_group_replication_group_seeds = “172.17.0.35:24901,172.17.0.29:24901,172.17.0.56:24901”

loose_group_replication_bootstrap_group = off

loose_group_replication_ip_whitelist = “172.17.0.35/24”

loose_group_replication_member_expel_timeout=30

report_host = 172.17.0.35

report_port = 3306

#read_only=1

#super_read_only=1

binlog_transaction_dependency_tracking = WRITESET

Node 2 不同的部分参数

server_id = 293306

report_host = 172.17.0.29

loose_group_replication_local_address = “172.17.0.29:24901”

loose_group_replication_ip_whitelist = “172.17.0.29/24”

Node 3 不同的部分参数

server_id = 303306

report_host = 172.17.0.30

loose_group_replication_local_address = “172.17.0.30:24901”

loose_group_replication_ip_whitelist = “172.17.0.30/24”

mysql 用户分别初始化三台主机操作

mysqld --defaults-file=/data/mysqldb/conf/mysql.conf --initialize --user=mysql --basedir=/app/mysql8.0.32 --datadir=/data/mysqldb/data

等待两分钟，查看每台主机初始化日志是否出现错误。

cat /data/mysqldb/log/mysqld.log

5、启动及停止命令

分别启动三个节点 mysql server

mysqld_safe --defaults-file=/data/mysqldb/conf/mysql.conf --user=mysql &

-----如下关闭命令，本次不需要执行

mysqladmin -uroot -proot123 -S /data/mysqldb/socket/mysql.sock shutdown

6、修改 root@localhost 用户密码

root 密码可以根据需求自己修改,生产环境需使用 8 位以上的强密码。

mysql 用户查看日志中的密码分别登录进去修改 root@localhost 用户密码。

–默认密码在错误日志里

more /data/mysqldb/log/mysqld.log|grep password

mysql -uroot -proot123 -P3306 -S /data/mysqldb/socket/mysql.sock

输入临时密码：ljdj&wahl0Ay

alter user root@‘localhost’ identified by ‘root@123’;

flush privileges;

reset master;

四、配置 MGR 组复制

1、所有节点安装组复制插件

SQL 命令

mysql> INSTALL PLUGIN group_replication SONAME ‘group_replication.so’;

或者写入到配置文件 plugin-load = group_replication.so

查看所有插件，注意主复制插件是否为 active。

show plugins;

2、在 Node1 节点上创建复制用户和配置恢复通道

SET SQL_LOG_BIN=0;

CREATE USER repl@’%’ IDENTIFIED BY ‘repl’;

GRANT REPLICATION SLAVE ON . TO repl@’%’;

SET SQL_LOG_BIN=1;

CHANGE MASTER TO MASTER_USER=‘repl’, MASTER_PASSWORD=‘repl’ FOR CHANNEL ‘group_replication_recovery’;

使用 SET SQL_LOG_BIN=0; 来保证创建用户的操作不记录到 binlog 中，这么做可以避免其他服务加入时报事务冲突错误。

3、在 Node1 节点启动 MGR(只需在第一个节点执行即可)

SET GLOBAL group_replication_bootstrap_group=ON;

START GROUP_REPLICATION;

SET GLOBAL group_replication_bootstrap_group=OFF;

group_replication_bootstrap_group 参数设置为 ON，是为了表示以后加入集群的服务器都以这台服务器为基准。以后加入的就不需要再设置此参数。

登录node1节点

mysql -uroot -proot123 -P 3306 -S /data/mysqldb/socket/mysql.sock

–查询当前组成员及状态

mysql> select * from performance_schema.replication_group_members;

±--------------------------±-------------------------------------±------------±------------±-------------±------------±---------------±---------------------------+

| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |

±--------------------------±-------------------------------------±------------±------------±-------------±------------±---------------±---------------------------+ |

| group_replication_applier | 6eb9cce5-bb4a-11ee-afd3-fa163e8cdb46 | 172.17.0.35 | 3306 | ONLINE | PRIMARY | 8.0.32 | XCom | |

±--------------------------±-------------------------------------±------------±------------±-------------±------------±---------------±---------------------------+

4、其他节点加入组复制（其他节点执行）

Node2 和 Node3 执行

mysql -uroot -proot123 -P 3306 -S /data/mysqldb/socket/mysql.sock

先执行 reset master;

SET SQL_LOG_BIN=0;

CREATE USER repl@’%’ IDENTIFIED BY ‘repl’;

GRANT REPLICATION SLAVE ON . TO repl@’%’;

SET SQL_LOG_BIN=1;

CHANGE MASTER TO MASTER_USER=‘repl’, MASTER_PASSWORD=‘repl’ FOR CHANNEL ‘group_replication_recovery’;

START GROUP_REPLICATION;

5、启动完成之后在看 Group 信息

SELECT * FROM performance_schema.replication_group_members;

mysql> SELECT * FROM performance_schema.replication_group_members;

mysql> select * from performance_schema.replication_group_member_stats\G;

mysql> select * from performance_schema.replication_group_members;

| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |

±--------------------------±-------------------------------------±------------±------------±-------------±------------±---------------±---------------------------+

| group_replication_applier | 67696d2d-bb4b-11ee-b0bd-fa163ecc23fa | 172.17.0.29 | 3306 | ONLINE | SECONDARY | 8.0.32 | XCom |

| group_replication_applier | 6eb9cce5-bb4a-11ee-afd3-fa163e8cdb46 | 172.17.0.35 | 3306 | ONLINE | PRIMARY | 8.0.32 | XCom |

| group_replication_applier | d6513ad2-bb4f-11ee-8690-fa163ea1efcc | 172.17.0.30 | 3306 | ONLINE | SECONDARY | 8.0.32 | XCom |

±--------------------------±-------------------------------------±------------±------------±-------------±------------±---------------±---------------------------+

3 rows in set (0.00 sec)

6、连接 Node1 主库创建 root@’%’ 用户

节点1

mysql -uroot -proot123 -P 3306 -S /data/mysqldb/socket/mysql.sock

create user root@’%’ identified by ‘root@123’;

grant all privileges on . to root@’%’ with grant option;

flush privileges;

===================================

部署多实例需要添加如下两个参数(否则端口冲突)

mysqlx_port

mysqlx_socket

节点加入集群报错时，节点需要设置如下参数(使用默认密码插件时需要执行)

set global group_replication_recovery_get_public_key=on;

===========================

五、安装 MySQL Shell

初始化实例及创建纳管库，库名 mysql_innodb_cluster_metadata，只需要在一台中间件服务器安装即可，这里选择 Node3。

1、root 用户安装 MySQL Shell

#rpm -ivh mysql-shell-8.0.32-1.el7.x86_64.rpm

–这里下载的是 rpm 包，如果你下载的是二进制版本，可直接解压配置环境变量即可。

#tar -zxvf mysql-shell-8.0.32-linux-glibc2.12-x86-64bit.tar.gz

#ln -s mysql-shell-8.0.32-linux-glibc2.12-x86-64bit mysql-shell

#export PATH=$PATH:/app/mysql-shell/bin

2、连接数据库，添加纳管

在第一次连接，创建纳管时，需要从node1: primary 连接进去。否则会破坏 read_only（root 用户会破坏 super_read_only)。

mysqlsh root@172.17.0.35:3306 --py

添加纳管

dba.create_cluster(‘mysqlrouter’)

MySQL 172.17.0.35:3306 ssl Py > dba.create_cluster(‘mysqlrouter’)

A new InnoDB Cluster will be created on instance ‘172.17.0.35:3306’.

You are connected to an instance that belongs to an unmanaged replication group.

Do you want to setup an InnoDB Cluster based on this replication group? [Y/n]: Y

Creating InnoDB Cluster ‘mysqlrouter’ on ‘172.17.0.35:3306’…

Adding Seed Instance…

Adding Instance ‘172.17.0.35:3306’…

Adding Instance ‘172.17.0.29:3306’…

Adding Instance ‘172.17.0.30:3306’…

Resetting distributed recovery credentials across the cluster…

Cluster successfully created based on existing replication group.

Cluster:mysqlrouter

3、查看集群状态

c=dba.get_cluster()

c.status()

MySQL 172.17.0.35:3306 ssl Py > c=dba.get_cluster()

MySQL 172.17.0.35:3306 ssl Py > c.status()

{

"clusterName": "mysqlrouter", 

"defaultReplicaSet": {

    "name": "default", 

    "primary": "172.17.0.35:3306", 

    "ssl": "DISABLED", 

    "status": "OK", 

    "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 

    "topology": {

        "172.17.0.29:3306": {

            "address": "172.17.0.29:3306", 

            "memberRole": "SECONDARY", 

            "mode": "R/O", 

            "readReplicas": {}, 

            "replicationLag": "applier_queue_applied", 

            "role": "HA", 

            "status": "ONLINE", 

            "version": "8.0.32"

        }, 

        "172.17.0.30:3306": {

            "address": "172.17.0.30:3306", 

            "memberRole": "SECONDARY", 

            "mode": "R/O", 

            "readReplicas": {}, 

            "replicationLag": "applier_queue_applied", 

            "role": "HA", 

            "status": "ONLINE", 

            "version": "8.0.32"

        }, 

        "172.17.0.35:3306": {

            "address": "172.17.0.35:3306", 

            "memberRole": "PRIMARY", 

            "mode": "R/W", 

            "readReplicas": {}, 

            "replicationLag": "applier_queue_applied", 

            "role": "HA", 

            "status": "ONLINE", 

            "version": "8.0.32"

        }

    }, 

    "topologyMode": "Single-Primary"

}, 

"groupInformationSourceMember": "172.17.0.35:3306"
复制

}

MySQL 172.17.0.35:3306 ssl Py >

4、MySQL Shell 的其他方法

dba.help()

\help dba

查看方法具体使用：

dba.help(‘createCluster’);

MySQL Shell 工具集

\help util

5、删除纳管

本次不需要执行，只要删除元数据数据库即可，从 primary 连接。

dba.drop_metadata_schema()

6、查看纳管数据库

mysql_innodb_cluster_metadata

mysql> show databases;

±------------------------------+

| Database |

±------------------------------+

| information_schema |

| mysql |

| mysql_innodb_cluster_metadata |

| performance_schema |

| sys |

±------------------------------+

5 rows in set (0.00 sec)

mysql> use mysql_innodb_cluster_metadata;

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> show tables;

±----------------------------------------+

| Tables_in_mysql_innodb_cluster_metadata |

±----------------------------------------+

| async_cluster_members |

| async_cluster_views |

| clusters |

| clusterset_members |

| clusterset_views |

| clustersets |

| instances |

| router_rest_accounts |

| routers |

| schema_version |

| v2_ar_clusters |

| v2_ar_members |

| v2_clusters |

| v2_cs_clustersets |

| v2_cs_members |

| v2_cs_router_options |

| v2_gr_clusters |

| v2_instances |

| v2_router_rest_accounts |

| v2_routers |

| v2_this_instance |

±----------------------------------------+

21 rows in set (0.00 sec)

六、MySQL Router 安装配置

1、三节点安装 MySQL Router

在主节点服务器配置 mysql 用户执行

$ tar -xvf mysql-router-8.0.32-linux-glibc2.12-x86_64.tar.xz

$ mv mysql-router-8.0.32-linux-glibc2.12-x86_64 mysqlrouter

$ mkdir -p /data/mysqlrouter/mysqlrouter6446

2、初始化 MySQL Router

/app/mysqlrouter/bin/mysqlrouter --bootstrap root@172.17.0.35:3306 --directory /data/mysqlrouter/mysqlrouter6446 --conf-base-port 6446 --conf-use-sockets --force

/app/mysqlrouter/bin/mysqlrouter --bootstrap root@172.17.0.29:3306 --directory /data/mysqlrouter/mysqlrouter6446 --conf-base-port 6446 --conf-use-sockets --force

(注意：–conf-base-port 6446 这个参数是 mysqlrouter 参数 默认是 6446)

[mysql@node1 data]$ /app/mysqlrouter/bin/mysqlrouter --bootstrap root@172.17.0.35:3306 --directory /data/mysqlrouter/mysqlrouter6446 --conf-base-port 6446 --conf-use-sockets --force

Please enter MySQL password for root:

#Bootstrapping MySQL Router instance at ‘/data/mysqlrouter/mysqlrouter6446’…

Fetching Cluster Members

trying to connect to mysql-server at 172.17.0.35:3306

• 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 /data/mysqlrouter/mysqlrouter6446/mysqlrouter.conf

#MySQL Router configured for the InnoDB Cluster ‘mysqlrouter’

After this MySQL Router has been started with the generated configuration

$ /app/mysqlrouter/bin/mysqlrouter -c /data/mysqlrouter/mysqlrouter6446/mysqlrouter.conf
复制

InnoDB Cluster ‘mysqlrouter’ can be reached by connecting to:

##MySQL Classic protocol

• Read/Write Connections: localhost:6446, /data/mysqlrouter/mysqlrouter6446/mysql.sock

• Read/Only Connections: localhost:6447, /data/mysqlrouter/mysqlrouter6446/mysqlro.sock

##MySQL X protocol

• Read/Write Connections: localhost:6448, /data/mysqlrouter/mysqlrouter6446/mysqlx.sock

• Read/Only Connections: localhost:6449, /data/mysqlrouter/mysqlrouter6446/mysqlxro.sock

==========================================================

3、启动 MySQL Router

[mysql@node1 mysqlrouter6446]$ cd /data/mysqlrouter/mysqlrouter6446

[mysql@node1 mysqlrouter6446]$ ./start.sh

[mysql@node1 mysqlrouter6446]$ PID 29067 written to ‘/data/mysqlrouter/mysqlrouter6446/mysqlrouter.pid’

stopping to log to the console. Continuing to log to filelog

–除了上面脚本启动外，按提示中如下命令也可以启动：

/app/mysqlrouter/bin/mysqlrouter -c /data/mysqlrouter/mysqlrouter6446/mysqlrouter.conf

查看日志

cat mysqlrouter.log

[mysql@node1 log]$ cat mysqlrouter.log

4、使用 Router 登录 mysql 测试

mysql -uroot -p -h172.17.0.35 -P 6446（6446是 mysqlrouer 读写端口）

[mysql@node1 mysqlrouter6446]$ mysql -uroot -p -h172.17.0.35 -P 6446

[mysql@node1 mysqlrouter6446]$ mysql -uroot -p -h172.17.0.35 -P 6446

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 2017

Server version: 8.0.32 MySQL Community Server - GPL

Copyright © 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> show variables like ‘%read_only%’;

±----------------------±------+

| Variable_name | Value |

±----------------------±------+

| innodb_read_only | OFF |

| read_only | OFF |

| super_read_only | OFF |

| transaction_read_only | OFF |

±----------------------±------+

4 rows in set (0.01 sec)

mysql -uroot -p -h172.17.0.35 -P 6447（6447是 mysqlrouer 只读端口）

[mysql@node1 mysqlrouter6446]$ mysql -uroot -p -h172.17.0.35 -P 6447

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 19229

Server version: 8.0.32 MySQL Community Server - GPL

Copyright © 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> show variables like ‘%read_only%’

-> ;
复制

±----------------------±------+

| Variable_name | Value |

±----------------------±------+

| innodb_read_only | OFF |

| read_only | ON |

| super_read_only | ON |

| transaction_read_only | OFF |

±----------------------±------+

4 rows in set (0.00 sec)

======================================

5、MySQL Router systemctl 启动配置

vim /etc/systemd/system/mysqlrouter@3306.service

[Unit]

Description= Mysql router

Documentation=man:mysqlrouter(8)

After=network.target

After=syslog.target

[Install]

WantedBy=multi-user.target

[Service]

User=root

Group=root

Type=forking

CPUQuota=100%

TimeoutSec=30

PermissionsStartOnly=true

PIDFile=/data/mysqlrouter/mysqlrouter6446/mysqlrouter.pid

ExecStart=/usr/bin/bash -c “/data/mysqlrouter/mysqlrouter6446/start.sh &”

ExecStop=/usr/bin/bash -c “/data/mysqlrouter/mysqlrouter6446/stop.sh &”

LimitNOFILE = 102400

Restart=no

6、只读参数设置

[mysql@node1 ~]$ ps -ef | grep mysql

root 29285 27052 0 20:21 pts/0 00:00:00 su - mysql

mysql 29286 29285 0 20:21 pts/0 00:00:00 -bash

mysql 29991 29286 0 20:24 pts/0 00:00:00 mysql -uroot -px xxxxx -P 33060 -S /data/mysqldb/socket/mysql.sock

root 32298 32195 0 20:33 pts/3 00:00:00 su - mysql

mysql 32300 32298 0 20:33 pts/3 00:00:00 -bash

mysql 32393 32300 0 20:33 pts/3 00:00:00 ps -ef

mysql 32394 32300 0 20:33 pts/3 00:00:00 grep --color=auto mysql

mysql 36378 1 0 14:25 ? 00:00:00 /bin/sh /app/mysql8.0.32/bin/mysqld_safe --defaults-file=/data/mysqldb/conf/mysql.conf --user=mysql

mysql 38891 36378 0 14:25 ? 00:01:49 /app/mysql8.0.32/bin/mysqld --defaults-file=/data/mysqldb/conf/mysql.conf --basedir=/app/mysql8.0.32 --datadir=/data/mysqldb/data --plugin-dir=/app/mysql8.0.32/lib/plugin --log-error=/data/mysqldb/log/mysqld.log --pid-file=/data/mysqldb/pid/mysqld.pid --socket=/data/mysqldb/socket/mysql.sock --port=3306

mysql 92606 1 0 18:00 ? 00:00:49 /app/mysqlrouter/bin/mysqlrouter -c /data/mysqlroutermysqlrouter6446/mysqlrouter.conf

[mysql@node1 log]$ vim /data/mysqldb/conf/mysql.conf

read_only=1

super_read_only=1

然后登录两个从库，执行从库只读设置（注意只在从库执行，主库不用执行）。

mysql> set global read_only=on;

Query OK, 0 rows affected (0.00 sec)

mysql> set global super_read_only=on;

Query OK, 0 rows affected (0.00 sec)

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

±-----------------±---------±-------------±-----------------±--------------------------------------------+

| mysql_bin.000003 | 2022645 | | | 957e8af0-bc63-11ea-bb19-005056a52572:1-2079 |

±-----------------±---------±-------------±-----------------±--------------------------------------------+

1 row in set (0.00 sec)

[mysql@node1 ~]$ mysqlsh

[mysql@node1 ~]$ mysqlsh

MySQL Shell 8.0.32

Copyright © 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.

MySQL JS >

###shell.connect(‘root@172.17.0.35:3306’)

MySQL JS > shell.connect(‘root@172.17.0.35:3306’)

Creating a session to ‘root@172.17.0.35:3306’

Fetching schema names for auto-completion… Press ^C to stop.

Your MySQL connection id is 2140

Server version: 8.0.32 MySQL Community Server - GPL

No default schema selected; type \use to set one.

ClassicSession:root@172.17.0.35:3306

MySQL 172.17.0.35:3306 ssl JS >

###dba.getCluster()

MySQL 172.17.0.35:3306 ssl JS > dba.getCluster()

Cluster:mysqlrouter

MySQL 172.17.0.35:3306 ssl JS > var cluster = dba.getCluster()

MySQL 172.17.0.35:3306 ssl JS > cluster.status()

{

"clusterName": "mysqlrouter", 

"defaultReplicaSet": {

    "name": "default", 

    "primary": "172.17.0.35:3306", 

    "ssl": "DISABLED", 

    "status": "OK", 

    "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 

    "topology": {

        "172.17.0.29:3306": {

            "address": "172.17.0.29:3306", 

            "memberRole": "SECONDARY", 

            "mode": "R/O", 

            "readReplicas": {}, 

            "replicationLag": "applier_queue_applied", 

            "role": "HA", 

            "status": "ONLINE", 

            "version": "8.0.32"

        }, 

        "172.17.0.30:3306": {

            "address": "172.17.0.30:3306", 

            "memberRole": "SECONDARY", 

            "mode": "R/O", 

            "readReplicas": {}, 

            "replicationLag": "applier_queue_applied", 

            "role": "HA", 

            "status": "ONLINE", 

            "version": "8.0.32"

        }, 

        "172.17.0.35:3306": {

            "address": "172.17.0.35:3306", 

            "memberRole": "PRIMARY", 

            "mode": "R/W", 

            "readReplicas": {}, 

            "replicationLag": "applier_queue_applied", 

            "role": "HA", 

            "status": "ONLINE", 

            "version": "8.0.32"

        }

    }, 

    "topologyMode": "Single-Primary"

}, 

"groupInformationSourceMember": "172.17.0.35:3306"
复制

}