MySQL innodb cluster-8.0.25 部署文档

MySQL innoDB Cluster 部署

环境如下
mysql-router-8.0.25-linux-glibc2.12-x86_64.tar.xz
mysql-8.0.25-linux-glibc2.12-x86_64.tar.xz
mysql-shell-8.0.25-linux-glibc2.12-x86-64bit.tar.gz


服务器信息：

CentOS Linux release 7.9.2009 (Core)
192.168.35.50 master01
192.168.35.51 node01
192.168.35.52 node02

配置环境变量

echo "export PATH=$PATH:/usr/local/mysql/bin" >>/etc/profile
echo "export PATH=$PATH:/usr/local/myshell/bin" >>/etc/profile
echo "export PATH=$PATH:/usr/local/myrouter/bin" >>/etc/profile
source /etc/profile

权限

chown -R mysql:mysql /usr/local/myshell
chown -R mysql:mysql /usr/local/mysql
chown -R mysql:mysql /usr/local/myrouter

chown -R mysql:mysql /data
chmod a+x /data -R


1.初始化数据库 每个节点都操作一遍


/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf --initialize-insecure
/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf &
SET SQL_LOG_BIN=0;
CREATE USER 'root'@'%' IDENTIFIED BY '123456';
grant all privileges on *.* to root@'%';
grant all on *.* to 'root'@'%' with grant option;
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;
INSTALL PLUGIN group_replication SONAME 'group_replication.so';
INSTALL PLUGIN clone SONAME 'mysql_clone.so';

2. 创建集群用户信息
在mysqlsh中执行
dba.configureLocalInstance('root@master01:3306',{clusterAdmin:'gradmin',clusterAdminPassword:'grpass'});
dba.configureLocalInstance('root@node01:3306',{clusterAdmin:'gradmin',clusterAdminPassword:'grpass'});
dba.configureLocalInstance('root@node02:3306',{clusterAdmin:'gradmin',clusterAdminPassword:'grpass'});


MySQL JS > dba.configureLocalInstance('root@master01:3306',{clusterAdmin:'gradmin',clusterAdminPassword:'grpass'});dba.configureLocalInstance('root@master01:3306',{clusterAdmin:'gradmin',clusterAdminPassword:'grpass'});
Please provide the password for 'root@master01:3306': ******
Save password for 'root@master01:3306'? [Y]es/[N]o/Ne[v]er (default No): yy
Configuring local MySQL instance listening at port 3306 for use in an InnoDB cluster...

This instance reports its own address as master01: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 'gradmin'@'%' for gradmin

applierWorkerThreads will be set to the default value of 4.

NOTE: Some configuration options need to be fixed:
+----------------------------------------+---------------+----------------+----------------------------+
| Variable | Current Value | Required Value | Note |
+----------------------------------------+---------------+----------------+----------------------------+
| binlog_transaction_dependency_tracking | COMMIT_ORDER | WRITESET | Update the server variable |
+----------------------------------------+---------------+----------------+----------------------------+

Do you want to perform the required configuration changes? [y/n]: yy

Cluster admin user 'gradmin'@'%' created.
Configuring instance...
The instance 'master01:3306' was configured to be used in an InnoDB cluster.
MySQL JS > dba.configureLocalInstance('root@node01:3306',{clusterAdmin:'gradmin',clusterAdminPassword:'grpass'}); dba.configureLocalInstance('root@node01:3306',{clusterAdmin:'gradmin',clusterAdminPassword:'grpass'});
Please provide the password for 'root@node01:3306': ******
Save password for 'root@node01:3306'? [Y]es/[N]o/Ne[v]er (default No): yy
Configuring MySQL instance at node01:3306 for use in an InnoDB cluster...

This instance reports its own address as node01: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 'gradmin'@'%' for gradmin

applierWorkerThreads will be set to the default value of 4.

NOTE: Some configuration options need to be fixed:
+----------------------------------------+---------------+----------------+----------------------------+
| Variable | Current Value | Required Value | Note |
+----------------------------------------+---------------+----------------+----------------------------+
| binlog_transaction_dependency_tracking | COMMIT_ORDER | WRITESET | Update the server variable |
+----------------------------------------+---------------+----------------+----------------------------+

Do you want to perform the required configuration changes? [y/n]: yy

Cluster admin user 'gradmin'@'%' created.
Configuring instance...
The instance 'node01:3306' was configured to be used in an InnoDB cluster.
MySQL JS > dba.configureLocalInstance('root@node02:3306',{clusterAdmin:'gradmin',clusterAdminPassword:'grpass'}); dba.configureLocalInstance('root@node02:3306',{clusterAdmin:'gradmin',clusterAdminPassword:'grpass'});
Please provide the password for 'root@node02:3306': ******
Save password for 'root@node02:3306'? [Y]es/[N]o/Ne[v]er (default No): yy
Configuring MySQL instance at node02:3306 for use in an InnoDB cluster...

This instance reports its own address as node02: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 'gradmin'@'%' for gradmin

applierWorkerThreads will be set to the default value of 4.

NOTE: Some configuration options need to be fixed:
+----------------------------------------+---------------+----------------+----------------------------+
| Variable | Current Value | Required Value | Note |
+----------------------------------------+---------------+----------------+----------------------------+
| binlog_transaction_dependency_tracking | COMMIT_ORDER | WRITESET | Update the server variable |
+----------------------------------------+---------------+----------------+----------------------------+

Do you want to perform the required configuration changes? [y/n]: yy

Cluster admin user 'gradmin'@'%' created.
Configuring instance...
The instance 'node02:3306' was configured to be used in an InnoDB cluster.
MySQL JS >




3.创建集群



mysqlsh --uri gradmin:grpass@master01:3306

MySQL master01:3306 ssl JS > var x = dba.createCluster('mycluster') var x = dba.createCluster('mycluster')
A new InnoDB cluster will be created on instance 'master01:3306'.

Validating instance configuration at master01:3306...

This instance reports its own address as master01:3306

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

Creating InnoDB cluster 'mycluster' on 'master01: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.

MySQL master01:3306 ssl JS > var x = dba.getCluster()var x = dba.getCluster()
MySQL master01:3306 ssl JS > print (x.status())print (x.status())
{
"clusterName": "mycluster",
"defaultReplicaSet": {
"name": "default",
"primary": "master01:3306",
"ssl": "REQUIRED",
"status": "OK_NO_TOLERANCE",
"statusText": "Cluster is NOT tolerant to any failures.",
"topology": {
"master01:3306": {
"address": "master01:3306",
"memberRole": "PRIMARY",
"mode": "R/W",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.25"
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "master01:3306"




4. 添加集群，以Incremental的方式

x = dba.getCluster()
x.addInstance('gradmin:grpass@node01:3306')
x.addInstance('gradmin:grpass@node02:3306')

添加节点1

MySQL master01:3306 ssl JS > x.addInstance('gradmin:grpass@node01:3306')x.addInstance('gradmin:grpass@node01:3306')

NOTE: The target instance 'node01:3306' has not been pre-provisioned (GTID set is empty). The Shell is unable to decide whether incremental state recovery can correctly provision it.
The safest and most convenient way to provision a new instance is through automatic clone provisioning, which will completely overwrite the state of 'node01:3306' with a physical snapshot from an existing cluster member. To use this method by default, set the 'recoveryMethod' option to 'clone'.

The incremental state recovery may be safely used if you are sure all updates ever executed in the cluster were done with GTIDs enabled, there are no purged transactions and the new instance contains the same GTID set as the cluster or a subset of it. To use this method by default, set the 'recoveryMethod' option to 'incremental'.

Please select a recovery method [C]lone/[I]ncremental recovery/[A]bort (default Clone): II
Validating instance configuration at node01:3306...

This instance reports its own address as node01:3306

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

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.
State recovery already finished for 'node01:3306'

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

MySQL master01:3306 ssl JS > rint (x.status())rint (x.status())rint (x.status()rint (x.status)rint (x.statu)rint (x.stat)rint (x.stat)
ReferenceError: rint is not defined
MySQL master01:3306 ssl JS >
MySQL master01:3306 ssl JS > print (x.status())print (x.status())
{
"clusterName": "mycluster",
"defaultReplicaSet": {
"name": "default",
"primary": "master01:3306",
"ssl": "REQUIRED",
"status": "OK_NO_TOLERANCE",
"statusText": "Cluster is NOT tolerant to any failures.",
"topology": {
"master01:3306": {
"address": "master01:3306",
"memberRole": "PRIMARY",
"mode": "R/W",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.25"
},
"node01:3306": {
"address": "node01:3306",
"memberRole": "SECONDARY",
"mode": "R/O",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.25"
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "master01:3306"
}


添加节点2

MySQL master01:3306 ssl JS > x.addInstance('gradmin:grpass@node02:3306')x.addInstance('gradmin:grpass@node02:3306')

5.显示集群信息


MySQL master01:3306 ssl JS > print (x.status())print (x.status())
{
"clusterName": "mycluster",
"defaultReplicaSet": {
"name": "default",
"primary": "master01:3306",
"ssl": "REQUIRED",
"status": "OK",
"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
"topology": {
"master01:3306": {
"address": "master01:3306",
"memberRole": "PRIMARY",
"mode": "R/W",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.25"
},
"node01:3306": {
"address": "node01:3306",
"memberRole": "SECONDARY",
"mode": "R/O",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.25"
},
"node02:3306": {
"address": "node02:3306",
"memberRole": "SECONDARY",
"mode": "R/O",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.25"
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "master01:3306"


切换主
可以通过setPrimaryInstance 来切换指定主
隐藏实例，
x.setInstanceOption("192.168.35.51:3306","tag:_hidden",true)


测试一下，

ALL OS REBOOT

mysqlsh --uri root@master01:3306

dba.rebootClusterFromCompleteOutage('myCluster')


[root@master01 17:44:37~]# mysqlsh --uri root@master01:3306
MySQL Shell 8.0.25

Copyright (c) 2016, 2021, 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 session to 'root@master01:3306'
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 22
Server version: 8.0.25 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
MySQL master01:3306 ssl JS > dba.rebootClusterFromCompleteOutage('myCluster')dba.rebootClusterFromCompleteOutage('myCluster')
Restoring the cluster 'myCluster' from complete outage...

The instance 'node01:3306' was part of the cluster configuration.
Would you like to rejoin it to the cluster? [y/N]: yy

The instance 'node02:3306' was part of the cluster configuration.
Would you like to rejoin it to the cluster? [y/N]: yy

master01:3306 was restored.
Rejoining 'node01:3306' to the cluster.
Rejoining instance 'node01:3306' to cluster 'mycluster'...
The instance 'node01:3306' was successfully rejoined to the cluster.

Rejoining 'node02:3306' to the cluster.
Rejoining instance 'node02:3306' to cluster 'mycluster'...
The instance 'node02:3306' was successfully rejoined to the cluster.

The cluster was successfully rebooted.

<Cluster:mycluster>





router 配置与安装

[root@master01 10:22:48/usr/local/myrouter]# mkdir -pv /opt/mysqlrouter
mkdir: created directory ‘/opt/mysqlrouter’
[root@master01 10:24:04/usr/local/myrouter]# mysqlrouter --bootstrap root@master01:3306 --directory /opt/mysqlrouter --conf-use-sockets --force --user=root

[root@master01 10:37:19/usr/local/myrouter]# mysqlrouter -c /opt/mysqlrouter/mysqlrouter.conf &
[1] 6569


通过mysqlrouter.conf配置信息发现，
mycluster_rw listening on 6446
mycluster_ro listening on 6447

[root@master01 14:48:54/opt/mysqlrouter/log]# mysql -uroot -p123456 -P6446 --protocol=TCP -e"select @@hostname"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| master01 |
+------------+
[root@master01 14:50:10/opt/mysqlrouter/log]# mysql -uroot -p123456 -P6446 --protocol=TCP -e"select @@hostname"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| master01 |


[root@master01 15:19:11/opt/mysqlrouter/log]# mysql -uroot -p123456 -P6447 --protocol=TCP -e"select @@hostname"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| node01 |
+------------+
[root@master01 15:19:17/opt/mysqlrouter/log]# mysql -uroot -p123456 -P6447 --protocol=TCP -e"select @@hostname"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| node02 |
+------------+




my3306.cnf配置信息


[client]
port = 3306
socket = /tmp/mysql3306.sock

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

[mysqld]
user = mysql
port = 3306
basedir = /usr/local/mysql
datadir = /data/mysql/mysql3306/data
pid-file = MYSQL.pid
character-set-server = utf8mb4
skip_name_resolve = 1

default_time_zone = "+8:00"

default_authentication_plugin=mysql_native_password

open_files_limit = 65535
back_log = 1024
max_connections = 512
max_connect_errors = 10000
table_open_cache = 1024
table_definition_cache = 1024
table_open_cache_instances = 64
thread_stack = 512K
external-locking = FALSE
max_allowed_packet = 32M
sort_buffer_size = 4M
join_buffer_size = 4M
thread_cache_size = 768
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/mysql3306/logs/slow.log
log-error = /data/mysql/mysql3306/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 = 100
log_slow_admin_statements = 1
log_slow_slave_statements = 1
server-id = 3306 ---每个节点不一样
log-bin =/data/mysql/mysql3306/logs/mybinlog
sync_binlog = 1
binlog_cache_size = 4M
max_binlog_cache_size = 2G
max_binlog_size = 2Gf

binlog_expire_logs_seconds = 604800
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
binlog_checksum = NONE
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
log-slave-updates=1

transaction_isolation = REPEATABLE-READ
#innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 256M
innodb_buffer_pool_instances = 2
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_data_file_path = ibdata1:256M:autoextend
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 32M
innodb_log_file_size = 256M
innodb_log_files_in_group = 2
innodb_max_undo_log_size = 256M
innodb_undo_directory = /data/mysql/mysql3306/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


# some var for MySQL 8
log_error_verbosity = 3
innodb_print_ddl_logs = 1

#innodb_dedicated_server = 0

innodb_status_file = 1
innodb_status_output = 0
innodb_status_output_locks = 0


transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="5dbabbe6-8050-49a0-9131-1de449167446"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "192.168.35.50:23306" ----根据实际节点配置
loose-group_replication_ip_whitelist="192.168.35.50,192.168.35.51,192.168.35.52"
loose-group_replication_group_seeds= "192.168.35.50:23306,192.168.35.51:23306,192.168.35.52:23306"
loose-group_replication_bootstrap_group= off

# 使用MGR的多主模式
#loose-group_replication_single_primary_mode=off
#loose-group_replication_enforce_update_everywhere_checks=on


# 使用MGR的单主模式
loose-group_replication_single_primary_mode=on
loose-group_replication_enforce_update_everywhere_checks=off



#并行复制

slave_parallel_type=LOGICAL_CLOCK
slave_parallel_workers=2
slave_preserve_commit_order=1

[mysqldump]
quick
max_allowed_packet = 32M