MySQL8.0 Innodb Cluster部署文档（内含基于MGR/使用mysqlshell两种方式）

最新推荐文章于 2024-02-29 16:56:30 发布

_梓杰_

最新推荐文章于 2024-02-29 16:56:30 发布

阅读量2.9k

点赞数 2

分类专栏： MySQL

本文链接：https://blog.csdn.net/qq_42979842/article/details/100082361

版权

MySQL 专栏收录该内容

126 篇文章 15 订阅

订阅专栏

文章目录

一、环境说明

1.1 版本说明

软硬件信息	参数
配置	2C4G
操作系统版本	CentOS Linux release 7.7.1908 (Core)
内核版本	3.10.0-1062.el7.x86_64
MySQL版本	MySQL 8.0.20
MySQL Shell版本	MySQL Shell 8.0.20
MySQL Router版本	MySQL Router 8.0.20

1.2 服务器规划

服务器	IP	端口	角色
node1	192.168.137.86	3306	Master，shell，router
node2	192.168.137.87	3306	Slave
node3	192.168.137.88	3306	Slave

1.3 配置目录规划

软件	目录
MySQL软件安装目录	/usr/local/mysql/mysqlserver
MySQL数据文件目录	/data/mysql/data
MySQL日志文件目录	/data/mysql/logs
MySQLbinlog日志目录	/data/mysql/binlog
MySQL配置文件目录	/etc/my.cnf
MySQL Shell安装目录	/usr/local/mysql
MySQL Router安装目录	/usr/local/mysql/mysqlserver

二、基础配置

所有3个节点都执行下述操作

2.1 关闭selinux和防火墙

systemctl stop firewalld
systemctl disable firewalld
setenforce 0 && sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config

2.2 配置IP域名映射

cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.137.86 node1
192.168.137.87 node2
192.168.137.88 node3

2.3 更改I/O调度模式

echo deadline > /sys/block/sda/queue/scheduler
cat /sys/block/sda/queue/scheduler

2.4 文件系统选择

文件系统最好选择xfs，它比ext4更方便管理。

2.5 操作系统调优

cat>>/etc/sysctl.conf <<EOF
vm.swappiness = 0
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

cat>>/etc/security/limits.conf <<EOF
mysql soft nproc 65536
mysql hard nproc 65536
mysql soft nofile 65536
mysql hard nofile 65536
EOF

cat>>/etc/profile<<EOF
if [ $USER = "mysql" ]; then
    ulimit -u 16384 -n 65536
fi
EOF

2.6 关闭numa

# sed -i 's/GRUB_CMDLINE_LINUX.*/GRUB_CMDLINE_LINUX="crashkernel=auto rd.lvm.lv=centos\/root rd.lvm.lv=centos\/swap rhgb quiet numa=off"/g' /etc/default/grub
# grub2-mkconfig -o /etc/grub2.cfg
# cat /etc/grub2.cfg
# reboot
# cat /proc/cmdline
# dmesg | grep -i numa

三、部署MySQL

3.1 创建MySQL用户组和用户

groupadd mysql
useradd -g mysql mysql -s /sbin/nologin
id mysql

3.2 配置目录

mkdir -pv /data/mysql/{data,logs,binlog,tmp,redo}
chown -R mysql:mysql /data/mysql/

2.3 解压安装包

mkdir /usr/local/mysql/program -pv
tar xf mysql-8.0.20-el7-x86_64.tar -C /usr/local/mysql/program/
cd /usr/local/mysql/program/
tar xf mysql-8.0.20-el7-x86_64.tar.gz -C /usr/local/mysql/
tar xf mysql-test-8.0.20-el7-x86_64.tar.gz  -C /usr/local/mysql/
tar xf mysql-router-8.0.20-el7-x86_64.tar.gz -C /usr/local/mysql/

2.4 软链接程序目录并配置环境变量

ln -s /usr/local/mysql/mysql-8.0.20-el7-x86_64/ /usr/local/mysql/mysqlserver
ln -s /usr/local/mysql/mysql-router-8.0.20-el7-x86_64/ /usr/local/mysql/mysqlrouter
export PATH=/usr/local/mysql/mysqlserver/bin:/usr/local/mysql/mysqlrouter/bin:$PATH
echo 'export PATH=/usr/local/mysql/mysqlserver/bin:/usr/local/mysql/mysqlrouter/bin:$PATH' >> /etc/profile
source /etc/profile

2.5 修改配置文件

[client]
port    = 3306
socket  = /data/mysql/data/mysql.sock

[mysql]
prompt="\u@mysql \R:\m:\s [\d]> "
no-auto-rehash

[mysqld]
user=mysql
port=3306
default_authentication_plugin=mysql_native_password
basedir=/usr/local/mysql/mysqlserver
datadir=/data/mysql/data
tmpdir =/data/mysql/tmp
socket=/data/mysql/data/mysql.sock
innodb_doublewrite=1
pid-file=mysql.pid
character-set-server = utf8mb4
skip_name_resolve = 1
default_time_zone = "+8:00"
open_files_limit= 65535
back_log = 1024
max_connections = 256
max_user_connections = 64
max_connect_errors = 10000
autocommit = 1
table_open_cache = 1024
table_definition_cache = 1024
table_open_cache_instances = 4
thread_stack = 512K
external-locking = FALSE
max_allowed_packet = 32M
sort_buffer_size = 4M
join_buffer_size = 4M
innodb_sort_buffer_size = 64M
thread_cache_size = 384
interactive_timeout = 600
wait_timeout = 600
tmp_table_size = 32M
max_heap_table_size = 32M
slow_query_log = 1
log_timestamps = SYSTEM
slow_query_log_file = /data/mysql/logs/slow.log
log-error = /data/mysql/logs/error.log
long_query_time = 0.1
log_queries_not_using_indexes =1
log_throttle_queries_not_using_indexes = 60
min_examined_row_limit = 0
log_slow_admin_statements = 1
log_slow_slave_statements = 1
server-id = 33061
log-bin = /data/mysql/binlog/binlog
sync_binlog = 1
binlog_cache_size = 4M
max_binlog_cache_size = 2G
max_binlog_size = 1G
auto_increment_offset=1
auto_increment_increment=1
expire_logs_days = 7
master_info_repository = TABLE
relay_log_info_repository = TABLE
gtid_mode = on
enforce_gtid_consistency = 1
log_slave_updates
slave-rows-search-algorithms = 'INDEX_SCAN,HASH_SCAN'
binlog_format = row
relay_log = /data/mysql/binlog/relaylog
relay_log_recovery = 1
relay-log-purge = 1
key_buffer_size = 32M
read_buffer_size = 8M
read_rnd_buffer_size = 4M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
lock_wait_timeout = 3600
explicit_defaults_for_timestamp = 1
innodb_thread_concurrency = 0
innodb_sync_spin_loops = 100
innodb_spin_wait_delay = 30
transaction_isolation = READ-COMMITTED
innodb_buffer_pool_size = 2048M
innodb_buffer_pool_instances = 2
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_log_group_home_dir = /data/mysql/redo/
innodb_data_file_path = ibdata1:1G:autoextend
innodb_temp_data_file_path=ibtmp1:500M:autoextend
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 32M
innodb_log_file_size = 2G
innodb_log_files_in_group = 2
innodb_max_undo_log_size = 4G
innodb_undo_directory = /data/mysql/data
innodb_undo_tablespaces = 2
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
innodb_flush_sync = 0
innodb_flush_neighbors = 0
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_purge_threads = 4
innodb_page_cleaners = 4
innodb_open_files = 65535
innodb_max_dirty_pages_pct = 50
innodb_flush_method = O_DIRECT
innodb_lru_scan_depth = 4000
innodb_checksum_algorithm = crc32
innodb_lock_wait_timeout = 10
innodb_rollback_on_timeout = 1
innodb_print_all_deadlocks = 1
innodb_file_per_table = 1
innodb_online_alter_log_max_size = 4G
innodb_stats_on_metadata = 0
innodb_undo_log_truncate = 1
slave_preserve_commit_order=1

log_error_verbosity = 3
innodb_print_ddl_logs = 1
binlog_expire_logs_seconds = 604800
innodb_status_file = 1
innodb_status_output = 0
innodb_status_output_locks = 0

performance_schema = 1
performance_schema_instrument = '%memory%=on'
performance_schema_instrument = '%lock%=on'

innodb_monitor_enable="module_innodb"
innodb_monitor_enable="module_server"
innodb_monitor_enable="module_dml"
innodb_monitor_enable="module_ddl"
innodb_monitor_enable="module_trx"
innodb_monitor_enable="module_os"
innodb_monitor_enable="module_purge"
innodb_monitor_enable="module_log"
innodb_monitor_enable="module_lock"
innodb_monitor_enable="module_buffer"
innodb_monitor_enable="module_index"
innodb_monitor_enable="module_ibuf_system"
innodb_monitor_enable="module_buffer_page"
innodb_monitor_enable="module_adaptive_hash"

slave_parallel_type=LOGICAL_CLOCK
slave_parallel_workers=2
binlog_group_commit_sync_no_delay_count=10


innodb_redo_log_archive_dirs=/back/redo
log-slave-updates=1
binlog_transaction_dependency_tracking=writeset_session

binlog_checksum=NONE
master_info_repository=TABLE
relay_log_info_repository=TABLE
transaction_write_set_extraction = XXHASH64
loose-group_replication_group_name = 'e88cd6a7-8a12-4368-aeea-21d6b68b2982'
loose-group_replication_start_on_boot = OFF
loose-group_replication_local_address = '192.168.137.86:33061'
loose-group_replication_group_seeds = '192.168.137.86:33061,192.168.137.87:33061,192.168.137.88:33061'
loose-group_replication_bootstrap_group = OFF

[mysqldump]
quick
max_allowed_packet = 32M

ln -s /usr/local/mysql/my.cnf /etc/my.cnf

2.6 初始化

mysqld-debug --defaults-file=/etc/my.cnf --initialize-insecure &

2.7 启动数据库

mysqld_safe --defaults-file=/etc/my.cnf &

四、配置MGR

#创建复制账号
SET SQL_LOG_BIN=0;
CREATE USER repl@'%' IDENTIFIED BY 'repl';
GRANT REPLICATION SLAVE ON *.* TO repl@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;
CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='repl' FOR CHANNEL 'group_replication_recovery';

3.1 启动MGR单主模式

#以下步骤所有节点均执行
mysql  #登陆数据库
mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so'; #安装插件
mysql> SET SQL_LOG_BIN=0; #关闭binlog
mysql> CREATE USER repl@'%' IDENTIFIED BY 'repl'; #创建复制账号
mysql> GRANT REPLICATION SLAVE ON *.* TO repl@'%'; #授权
mysql> FLUSH PRIVILEGES; #刷新权限
mysql> SET SQL_LOG_BIN=1; #开启binlog
mysql> CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='repl' FOR CHANNEL 'group_replication_recovery'; #创建复制

启动MGR，在192.168.137.86上执行
mysql> SET GLOBAL group_replication_bootstrap_group=ON;
mysql> START GROUP_REPLICATION;
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;

将其他节点加入MGR，在从库192.168.137.87、192.168.137.88上执行

mysql> START GROUP_REPLICATION;
#查看MGR组信息
root@mysql 02:30:  [(none)]>  SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 19b47031-c397-11ea-a83b-000c29652de7 | node2       |        3306 | ONLINE       | SECONDARY   | 8.0.20         |
| group_replication_applier | 627eae86-c38a-11ea-9dcf-000c2989a02a | node1       |        3306 | ONLINE       | PRIMARY     | 8.0.20         |
| group_replication_applier | 652b5dc9-c397-11ea-a416-000c2936f782 | node3       |        3306 | ONLINE       | SECONDARY   | 8.0.20         |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)

#查看MGR组信息


#如果在启动过程中报错，可以参考performance_schema.replication_connection_status或者日志输出解决问题。

2019-08-26T06:44:59.710514Z 17 [ERROR] [MY-010584] [Repl] Slave I/O for channel 'group_replication_recovery': error connecting to master 'repl@192.168.0.118:3306' - retry-time: 60 retries: 1 message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection. Error_code: MY-002061
2019-08-26T06:44:59.713143Z 16 [ERROR] [MY-011582] [Repl] Plugin group_replication reported: 'There was an error when connecting to the donor server. Please check that group_replication_recovery channel credentials and all MEMBER_HOST column values of performance_schema.replication_group_members table are correct and DNS resolvable.'
2019-08-26T06:44:59.713159Z 16 [ERROR] [MY-011583] [Repl] Plugin group_replication reported: 'For details please check performance_schema.replication_connection_status table and error log messages of Slave I/O for channel group_replication_recovery.'
从报错可以看出是caching_sha2_password插件的问题，进行更改即可，更改前后记得关闭和开启binlog。

ALTER USER 'repl'@'%' IDENTIFIED WITH mysql_native_password BY 'repl';

3.2 切换为MGR多主模式

#以下步骤所有节点均执行
mysql> stop group_replication;
#是否启动单主模式，如果启动，则本实例是主库，提供读写，其他实例仅提供读,如果为off就是多主模式了
mysql> set global group_replication_single_primary_mode=OFF;
#多主模式下,强制检查每一个实例是否允许该操作,如果不是多主,可以关闭
mysql> set global group_replication_enforce_update_everywhere_checks=ON;
#只在一个节点上执行，任意节点都可以
mysql> SET GLOBAL group_replication_bootstrap_group=ON; 
mysql> START GROUP_REPLICATION; 
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
#在其余节点执行
mysql> START GROUP_REPLICATION; 
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST   | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 2a7335b3-c743-11e9-bcbb-000c29586226 | 192.168.0.118 |        3306 | ONLINE       | PRIMARY     | 8.0.17         |
| group_replication_applier | 9bff969a-c743-11e9-90c0-000c29a2154f | 192.168.0.119 |        3306 | ONLINE       | PRIMARY     | 8.0.17         |
| group_replication_applier | c8dca41c-c744-11e9-8443-000c2943d916 | 192.168.0.120 |        3306 | ONLINE       | PRIMARY     | 8.0.17         |
+---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+
3 rows in set (0.03 sec)

3.3 切换回MGR单主模式

#以下步骤所有节点均执行
mysql> stop group_replication;
mysql> set global group_replication_enforce_update_everywhere_checks=OFF;
mysql> set global group_replication_single_primary_mode=ON;
#主节点执行，在192.168.0.118上执行
SET GLOBAL group_replication_bootstrap_group=ON; 
START GROUP_REPLICATION; 
SET GLOBAL group_replication_bootstrap_group=OFF;
#在其余节点执行
START GROUP_REPLICATION; 

查看MGR组信息

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST   | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 2a7335b3-c743-11e9-bcbb-000c29586226 | 192.168.0.118 |        3306 | ONLINE       | PRIMARY     | 8.0.17         |
| group_replication_applier | 9bff969a-c743-11e9-90c0-000c29a2154f | 192.168.0.119 |        3306 | ONLINE       | SECONDARY   | 8.0.17         |
| group_replication_applier | c8dca41c-c744-11e9-8443-000c2943d916 | 192.168.0.120 |        3306 | ONLINE       | SECONDARY   | 8.0.17         |
+---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)

四、配置 Innodb Cluster

4.1 创建MySQL Shell管理账号

create user 'dba'@'%' identified by 'dba';
grant all on *.* to 'dba'@'%' with grant option;
FLUSH PRIVILEGES;

4.2 检查实例配置

AdminAPI提供dba.checkInstanceConfiguration()方法以验证实例是否满足组复制要求，该方法不会更改实例上的任何配置，也不会检查实例上的任何数据。下面是检查192.168.137.86:3306的命令和输出：

 MySQL  192.168.137.86:3306 ssl  JS > dba.checkInstanceConfiguration('dba@192.168.137.86:3306')
Please provide the password for 'dba@192.168.137.86:3306': ***
Save password for 'dba@192.168.137.86:3306'? [Y]es/[N]o/Ne[v]er (default No):
Validating local MySQL instance listening at port 3306 for use in an InnoDB cluster...

This instance reports its own address as node1: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 'node1:3306' is valid to be used in an InnoDB cluster.

{
    "status": "ok"
}

#对集群中的每个服务器实例重复此过程
#使用dba.configureInstance()修复

4.3 配置实例

AdminAPI提供了dba.configureInstance()函数，用于检查实例是否针对InnoDB Cluster 进行了适当配置，并在发现任何与InnoDB Cluster不兼容的设置时配置实例。如果实例不需要更改配置，dba.configureInstance()命令输出确认实例已准备好使用InnoDB Cluster。根据MySQL Shell连接到实例的方式以及在实例上运行的MySQL版本，能够通过将这些更改自动保存到远程实例的配置文件（缺省为MySQL实例数据目录下的mysqld-auto.cnf文件）来持久化更改，或者手动更改实例的配置文件。无论进行配置更改的方式如何，必须重新启动实例以确保服务器检测到配置更改。下面是配置192.168.137.86:3306的命令和输出：

 MySQL  192.168.137.86:3306 ssl  JS > dba.configureInstance('dba@192.168.137.86:3306')
Please provide the password for 'dba@192.168.137.86:3306': ***
Save password for 'dba@192.168.137.86:3306'? [Y]es/[N]o/Ne[v]er (default No):
Configuring local MySQL instance listening at port 3306 for use in an InnoDB cluster...

This instance reports its own address as node1: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.

The instance 'node1:3306' is valid to be used in an InnoDB cluster.
The instance 'node1:3306' is already ready to be used in an InnoDB cluster.

一旦针对实例发出dba.configureInstance()，该命令首先检查实例的当前设置是否适合InnoDB Cluster使用，并将在一个报告中显示InnoDB Cluster所需的设置。这步与dba.checkInstanceConfiguration的作用类似。
当针对MySQL Shell当前运行的本地MySQL实例发出dba.configureInstance()时，它会尝试自动配置实例。对远程实例发出dba.configureInstance()时，如果实例支持自动保持配置更改，则可以选择执行此操作。满足以下要求的实例会自动支持持久配置更改,

    对于动态服务器变量，在dba.configureInstance()后不需要重新启动实例，但对只读服务器变量，需要重启实例。此信息显示在发出dba.configureInstance()之后生成的报告中。如果实例支持RESTART语句，则MySQL Shell可以关闭然后启动实例。这可确保mysqld检测到对实例配置文件所做的更改。

4.4 创建集群

准备好实例后，使用dba.createCluster()函数创建集群，参数为自定义的集群名称。在创建集群之前，MySQL Shell必须连接到一个实例，并将该实例用作群集的种子实例。种子实例将被复制到添加到群集的其它实例，从而使它们成为种子实例的副本。当发出dba.createCluster(name)时，MySQL Shell会创建与连接到的服务器实例的会话。下面是以192.168.137.86作为种子实例创建名为testCluster集群的命令及输出：

 MySQL  192.168.137.86:3306 ssl  JS >  dba.createCluster('testCluster')
A new InnoDB cluster will be created on instance '192.168.137.86:3306'.

Validating instance configuration at 192.168.137.86:3306...

This instance reports its own address as node1:3306

Instance configuration is suitable.
NOTE: Group Replication will communicate with other members using 'node1:33061'. Use the localAddress option to override.

Creating InnoDB cluster 'testCluster' on 'node1:3306'...

Adding Seed Instance...
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.

<Cluster:testCluster>

dba.createCluster函数返回一个Cluster对象。千万注意，一旦服务器实例属于一个集群，只使用MySQL Shell和AdminAPI来管理它们。尝试在实例添加到群集后手动更改组复制的配置不受支持。同样，不支持修改对InnoDB Cluster至关重要的服务器变量，例如在使用AdminAPI配置实例后的server_uuid。

4.5 向集群添加实例

使用Cluster.addInstance(instance)函数向集群添加更多实例，其中instance是已配置实例的连接信息。下面是将192.168.137.87:3306实例添加到集群的命令及输出：

MySQL  192.168.137.86:3306 ssl  JS > var cluster = dba.getCluster()
MySQL  192.168.137.86:3306 ssl  JS > cluster.addInstance('dba@192.168.137.87:3306')
Please provide the password for 'dba@192.168.137.87:3306': ***
Save password for 'dba@192.168.137.87:3306'? [Y]es/[N]o/Ne[v]er (default No):

WARNING: A GTID set check of the MySQL instance at 'node2:3306' determined that it contains transactions that do not originate from the cluster, which must be discarded before it can join the cluster.

node2:3306 has the following errant GTIDs that do not exist in the cluster:
d58a4141-c415-11ea-99a1-000c29652de7:1-3

WARNING: Discarding these extra GTID events can either be done manually or by completely overwriting the state of node2: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
NOTE: Group Replication will communicate with other members using 'node2:33061'. Use the localAddress option to override.

Validating instance configuration at 192.168.137.87:3306...

This instance reports its own address as node2:3306

Instance configuration is suitable.
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: node2:3306 is being cloned from node1:3306
** Stage DROP DATA: Completed
** Clone Transfer
    FILE COPY  ############################################################  100%  Completed
    PAGE COPY  ############################################################  100%  Completed
    REDO COPY  ############################################################  100%  Completed
** Stage RECOVERY: \
NOTE: node2:3306 is shutting down...

* Waiting for server restart... ready
* node2:3306 has restarted, waiting for clone to finish...
* Clone process has finished: 1.12 GB transferred in 5 sec (224.16 MB/s)

State recovery already finished for 'node2:3306'

The instance '192.168.137.87:3306' was successfully added to the cluster.

首先调用dba.getCluster()函数获取集群对象，并赋予一个变量。如果未指定群集名称，则返回默认群集。缺省情况下使用dba.getCluster()时，MySQL Shell会尝试连接到群集的主实例。设置connectToPrimary选项以配置此行为。如果connectToPrimary为true且活动的全局MySQL Shell会话不是主实例，则会查询主节点的集群，并且集群对象将连接到该集群。如果connectToPrimary为false，则集群对象使用活动会话，就是与MySQL Shell当前全局会话相同的实例。

如果使用的是MySQL 8.0.17或更高版本，可以选择实例如何恢复与群集同步所需的事务，可选方式有克隆和增量两种，缺省为增量。只有当加入实例恢复了先前由集群处理的所有事务时，它才能作为在线实例加入并开始处理事务。同样在8.0.17及更高版本中，可以使用waitRecovery选项控制Cluster.addInstance()的行为方式，让恢复操作在后台进行或监视MySQL Shell中的不同级别的进度。
如果要在一个已经包含大量数据（几十G会数百G）的集群中添加实例，推荐的方法是使用xtrabackup复制一个主节点副本，然后添加该副本实例时使用增量恢复。这样做有两个明显的优点，一是可以在对主节点影响最小的情况下联机执行，二是减少实例恢复所需时间。

4.6 查看集群状态

集群对象提供status()方法，可以检查集群状态。在检查InnoDB Cluster的状态之前，需要通过连接到集群中的任何实例来获取对InnoDB Cluster对象的引用。但如果要更改群集的配置，则必须连接到可读写实例。status()从所连接的服务器实例的集群视图检索集群的状态，并输出状态报告。所连接实例的状态直接影响状态报告中提供的信息，因此应确保连接实例的状态为ONLINE。下面是连接192.168.137.86:3306获取集群状态及输出：

 MySQL  192.168.137.88:3306 ssl  JS > var cluster = dba.getCluster()
 MySQL  192.168.137.88:3306 ssl  JS > cluster.status()
{
    "clusterName": "testCluster",
    "defaultReplicaSet": {
        "name": "default",
        "primary": "node1:3306",
        "ssl": "REQUIRED",
        "status": "OK",
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
        "topology": {
            "node1:3306": {
                "address": "node1:3306",
                "mode": "R/W",
                "readReplicas": {},
                "replicationLag": null,
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.20"
            },
            "node2:3306": {
                "address": "node2:3306",
                "mode": "R/O",
                "readReplicas": {},
                "replicationLag": null,
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.20"
            },
            "node3:3306": {
                "address": "node3:3306",
                "mode": "R/O",
                "readReplicas": {},
                "replicationLag": null,
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.20"
            }
        },
        "topologyMode": "Single-Primary"
    },
    "groupInformationSourceMember": "node1:3306"

Cluster.status()的输出提供以下信息：

clusterName：在dba.createCluster期间分配给此集群的名称。
defaultReplicaSet：属于InnoDB Cluster并包含数据集的服务器实例。
primary：仅在群集以单主模式运行时显示。显示当前主实例的地址。如果未显示此字段，则群集将以多主模式运行。
ssl：群集是否使用安全连接。显示REQUIRED或DISABLED的值，缺省为REQUIRED，具体取决于在createCluster()或addInstance()期间如何配置memberSslMode选项。此参数返回的值对应于实例上的group_replication_ssl_mode服务器变量的值。
status：集群状态，反映了此群集可提供的高可用性。实例状态为以下之一：ONLINE，实例在线并参与群集；OFFLINE，实例已失去与其它实例的连接；RECOVERING，实例尝试通过在成为ONLINE成员之前检索所需的事务来尝试与集群同步；UNREACHABLE，实例已丢失与群集的通信；ERROR，实例在恢复阶段或应用事务时遇到错误。实例进入ERROR状态后，super_read_only选项设置为ON，要退出ERROR状态，必须手动设置super_read_only = OFF。MISSING，实例的状态，它是已配置群集的一部分，但当前不可用。MISSING状态特定于InnoDB Cluster，它不是Group Replication生成的状态。MySQL Shell使用此状态来指示在元数据中注册但在实时群集视图中找不到的实例。
topology：已添加到集群的实例。
实例的主机名。
role：此实例在集群中提供的功能，目前只有HA。
mode：服务器是读写（“R/W”）还是只读（“R/O”）。从版本8.0.17开始，从实例中super_read_only变量的当前状态以及群集是否具有仲裁派生的。在之前版本中，mode的值来自实例是作为主实例还是辅助实例。通常，如果实例是主要的，则模式为“R/W”，如果为辅助实例，则模式为“R/O”。无论super_read_only变量的状态如何，群集中没有可见仲裁的任何实例都标记为“R/O”。
groupInformationSourceMember：用于获取有关集群的信息的内部连接，显示为类似URI的连接字符串，通常为最初用于创建集群的连接。
        要显示有关群集的更多信息，可使用扩展选项。如Cluster.status({'extended'：value})提供附加信息，value值控制输出信息：

0：默认值，禁用附加信息。
1：包括有关组复制报告的组复制协议版本，组名称，集群成员UUID，集群成员角色和状态以及受防护系统变量列表的信息。
2：包括有关连接和应用程序处理的事务的信息。

4.7 基于已有MGR创建Cluster

如果MGR已经创建好了，那么将adoptFromGR选项传递给dba.createCluster()函数，就可以创建Innodb Cluster。

如下面所示，组复制是单主，192.168.137.86:3306是主节点，使用MySQL Shell连接到组成员，上面4.2到4.6的步骤不进行。

[root@node1 ~]# mysqlsh --uri dba@192.168.137.86:3306
Please provide the password for 'dba@192.168.137.86:3306': ***
Save password for 'dba@192.168.137.86:3306'? [Y]es/[N]o/Ne[v]er (default No):
MySQL Shell 8.0.20

Copyright (c) 2016, 2020, Oracle and/or its affiliates. All rights reserved.
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 session to 'dba@192.168.137.86:3306'
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 29
Server version: 8.0.20 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
 MySQL  192.168.137.86:3306 ssl  JS >  var cluster = dba.createCluster('zijieprodCluster', {adoptFromGR: true});
A new InnoDB cluster will be created based on the existing replication group on instance '192.168.137.86:3306'.

Creating InnoDB cluster 'zijieprodCluster' on 'node1:3306'...

Adding Seed Instance...
Adding Instance 'node2:3306'...
Adding Instance 'node1:3306'...
Adding Instance 'node3:3306'...
Resetting distributed recovery credentials across the cluster...
WARNING: User 'mysql_innodb_cluster_33061'@'%' already existed at instance 'node1:3306'. It will be deleted and created again with a new password.
Cluster successfully created based on existing replication group.

新群集与组复制的模式匹配。如果组复制以单主模式运行，则会创建单主群集。如果组复制以多主模式运行，则会创建多主集群。

五、配置MySQL router

5.1 配置

MySQL Router可以使用–bootstrap选项基于InnoDB Cluster的元数据进行自我配置。这会自动配置MySQL Router以将连接路由到群集的服务器实例。客户端应用程序连接到MySQL Router提供的端口，无需了解InnoDB Cluster拓扑。如果发生意外故障，InnoDB Cluster会自动调整，MySQL Router会检测到更改。这消除了客户端应用程序处理故障转移的需求。MySQL不建议手动配置MySQL Router以重定向到InnoDB Cluster的端口，而是建议始终使用–bootstrap选项，因为这可确保MySQL Router从InnoDB Cluster的元数据中获取其配置。使用生产部署时，建议将MySQL Router部署于客户端应用程序所在的每台计算机上。

MySQL Router使用包含的元数据缓存插件来检索InnoDB Cluster的元数据，该元数据由构成InnoDB Cluster的服务器实例地址列表及其在集群中的角色组成。MySQL Router从它所连接的实例中检索InnoDB Cluster元数据，例如172.16.1.125:3306：

[root@node1 ~]# mysqlrouter --user=mysql --bootstrap dba@192.168.137.86:3306
Please enter MySQL password for dba:
# Bootstrapping system MySQL Router instance...

- 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/mysql-router-8.0.20-el7-x86_64/mysqlrouter.conf

# MySQL Router configured for the InnoDB Cluster 'testCluster'

After this MySQL Router has been started with the generated configuration

   $ /etc/init.d/mysqlrouter restart
or
   $ systemctl start mysqlrouter
or
   $ mysqlrouter -c /usr/local/mysql/mysql-router-8.0.20-el7-x86_64/mysqlrouter.conf

the cluster 'testCluster' can be reached by connecting to:

## MySQL Classic protocol

- Read/Write Connections: localhost:6446
- Read/Only Connections:  localhost:6447

## MySQL X protocol

- Read/Write Connections: localhost:64460
- Read/Only Connections:  localhost:64470

基于检索到的InnoDB Cluster元数据，MySQL Router自动配置mysqlrouter.conf文件，包括带有bootstrap_server_addresses的metadata_cache部分，其中包含集群中所有服务器实例的地址。下面是引导时自动生成的/usr/local/mysql/mysql-router-8.0.20-el7-x86_64/mysqlrouter.conf文件的内容：

[root@node1 ~]# cat /usr/local/mysql/mysql-router-8.0.20-el7-x86_64/mysqlrouter.conf
# File automatically generated during MySQL Router bootstrap
[DEFAULT]
name=system
user=mysql
keyring_path=/usr/local/mysql/mysql-router-8.0.20-el7-x86_64/var/lib/mysqlrouter/keyring
master_key_path=/usr/local/mysql/mysql-router-8.0.20-el7-x86_64/mysqlrouter.key
connect_timeout=15
read_timeout=30
dynamic_state=/usr/local/mysql/mysql-router-8.0.20-el7-x86_64/bin/../var/lib/mysqlrouter/state.json

[logger]
level = INFO

[metadata_cache:testCluster]
cluster_type=gr
router_id=1
user=mysql_router1_9ddav117lpyu
metadata_cluster=testCluster
ttl=0.5
auth_cache_ttl=-1
auth_cache_refresh_interval=2
use_gr_notifications=0

[routing:testCluster_rw]
bind_address=0.0.0.0
bind_port=6446
destinations=metadata-cache://testCluster/?role=PRIMARY
routing_strategy=first-available
protocol=classic

[routing:testCluster_ro]
bind_address=0.0.0.0
bind_port=6447
destinations=metadata-cache://testCluster/?role=SECONDARY
routing_strategy=round-robin-with-fallback
protocol=classic

[routing:testCluster_x_rw]
bind_address=0.0.0.0
bind_port=64460
destinations=metadata-cache://testCluster/?role=PRIMARY
routing_strategy=first-available
protocol=x

[routing:testCluster_x_ro]
bind_address=0.0.0.0
bind_port=64470
destinations=metadata-cache://testCluster/?role=SECONDARY
routing_strategy=round-robin-with-fallback
protocol=x

通过在引导MySQL Router之后添加另一个服务器实例来更改群集的拓扑时，需要根据更新的元数据更新bootstrap_server_addresses。可以使用–bootstrap选项重新引导MySQL Router并重启MySQL Router达到此目的。
生成的MySQL Router配置会创建用于连接到群集的TCP端口，包括使用经典MySQL协议和X协议与群集通信的端口，缺省值如下：

6446：用于经典MySQL协议读写会话，MySQL Router将传入连接重定向到主服务器实例。
6447：对于经典MySQL协议只读会话，MySQL Router将传入连接重定向到其中一个辅助服务器实例。
64460：用于X协议读写会话，MySQL Router将传入连接重定向到主服务器实例。
64470：用于X协议只读会话，MySQL Router将传入连接重定向到其中一个辅助服务器实例。        传入连接的重定向方式取决于所使用的群集类型。使用单主群集时，默认情况下，MySQL Router会发布X协议和经典协议端口，客户端连接到这些端口（如6446或64460）以进行读写会话，并重定向到群集的单个主节点。使用多主群集时，读写会话将以循环方式重定向到其中一个主实例。例如，到端口6446的第一个连接将被重定向到主实例1，到端口6446的第二个连接将被重定向到主实例2，依此类推。对于传入的只读连接，MySQL Router以循环方式将连接重定向到其中一个辅助实例。

5.2 连接测试

使用–bootstrap选项进行引导后，执行下面的命令后台启动MySQL Router：

mysqlrouter --user=root -c /usr/local/mysql/mysql-router-8.0.20-el7-x86_64/mysqlrouter.conf &

mysql -udba -pdba -P6446  -N -r -B -e"select @@hostname"
mysql -udba -pdba -P6447  -N -r -B -e"select @@hostname"
mysqlsh --sql -udba -pdba -P64460 -e"select @@hostname"
mysqlsh --sql -udba -pdba -P64470 -e"select @@hostname"

六、使用MySQL Shell管理InnoDB Cluster

6.1 dba操作

 MySQL  192.168.137.86:3306 ssl  JS > dba.help()
NAME
      dba - InnoDB cluster and replicaset management functions.

DESCRIPTION
      Entry point for AdminAPI functions, including InnoDB clusters and replica
      sets.

      InnoDB clusters

      The dba.configureInstance() function can be used to configure a MySQL
      instance with the settings required to use it in an InnoDB cluster.

      InnoDB clusters can be created with the dba.createCluster() function.

      Once created, InnoDB cluster management objects can be obtained with the
      dba.getCluster() function.

      InnoDB ReplicaSets

      The dba.configureReplicaSetInstance() function can be used to configure a
      MySQL instance with the settings required to use it in a replicaset.

      ReplicaSets can be created with the dba.createReplicaSet() function.

      Once created, replicaset management objects can be obtained with the
      dba.getReplicaSet() function.

      Sandboxes

      Utility functions are provided to create sandbox MySQL instances, which
      can be used to create test clusters and replicasets.

PROPERTIES
      verbose
            Controls debug message verbosity for sandbox related dba
            operations.

FUNCTIONS
      checkInstanceConfiguration(instance[, options])
            Validates an instance for MySQL InnoDB Cluster usage.

      configureInstance([instance][, options])
            Validates and configures an instance for MySQL InnoDB Cluster
            usage.

      configureLocalInstance(instance[, options])
            Validates and configures a local instance for MySQL InnoDB Cluster
            usage.

      configureReplicaSetInstance([instance][, options])
            Validates and configures an instance for use in an InnoDB
            ReplicaSet.

      createCluster(name[, options])
            Creates a MySQL InnoDB cluster.

      createReplicaSet(name[, options])
            Creates a MySQL InnoDB ReplicaSet.

      deleteSandboxInstance(port[, options])
            Deletes an existing MySQL Server instance on localhost.

      deploySandboxInstance(port[, options])
            Creates a new MySQL Server instance on localhost.

      dropMetadataSchema(options)
            Drops the Metadata Schema.

      getCluster([name][, options])
            Retrieves a cluster from the Metadata Store.

      getReplicaSet()
            Returns an object representing a ReplicaSet.

      help([member])
            Provides help about this object and it's members

      killSandboxInstance(port[, options])
            Kills a running MySQL Server instance on localhost.

      rebootClusterFromCompleteOutage([clusterName][, options])
            Brings a cluster back ONLINE when all members are OFFLINE.

      startSandboxInstance(port[, options])
            Starts an existing MySQL Server instance on localhost.

      stopSandboxInstance(port[, options])
            Stops a running MySQL Server instance on localhost.

      upgradeMetadata([options])
            Upgrades (or restores) the metadata to the version supported by the
            Shell.

      SEE ALSO

      - For general information about the AdminAPI use: \? AdminAPI
      - For help on a specific function use: \? dba.<functionName>

      e.g. \? dba.deploySandboxInstance

_梓杰_

关注

2
点赞
踩
7

收藏

觉得还不错? 一键收藏
打赏
0
评论
MySQL8.0 Innodb Cluster部署文档（内含基于MGR/使用mysqlshell两种方式）

文章目录MySQL8.0MGR单主/多主安装与切换(Centos7.3)一、基础环境配置1.1 关闭防火墙和selinux1.1.1 关闭防火墙1.1.2 关闭selinux1.2 配置IP域名映射1.3 更改I/O调度模式1.4 设置swap分区大小1.5 文件系统选择1.6 操作系统限制1.7 关闭numa二、实例安装2.1 创建MySQL用户组和用户2.2 创建相关目录2.3 解压安装包并设...
复制链接

扫一扫