使用MySQ Clone Plugin部署MySQL Group Replication
1.文档背景介绍
本文主要介绍使 用MySQL8.0.17的新功能Clone插件来快速部署MySQL MGR,其中对于MySQL数据库的部署部分使用脚本化部署,不在本文细节范围(也就是默认已安装好MySQL)
2.环境配置信息
| IP | PORT | ROLE | Version |
|---|---|---|---|
| 10.186.61.109 | 3306 | MGR主节点 | MySQL8.0.20 |
| 10.186.61.109 | 3307 | MGR从节点 | MySQL8.0.20 |
| 10.186.61.109 | 3308 | MGR从节点 | MySQL8.0.20 |
3.MySQL安装及配置
以下是本文档环境的MySQL的安装配置文件示例,默认使用该配置模板在同一台服务器上初始化3个节点MySQL
该参数文件默认在MySQL初始化安装时已配置好MGR插件及Clone插件(生产环境也建议直接在配置文件中配置好)
[root@10-186-61-109 mysql_package80]# cat my.cnf
[client]
port = 3306
user = root
socket = /data/mysql/3306/data/mysqld.sock
password =
[mysql]
no-auto-rehash
default-character-set = utf8mb4
prompt ='\U[\d]> '
## Base Config
[mysqld]
server_id = 1004
port = 3306
mysqlx_port = 33060
admin_port = 33062
admin_address = 127.0.0.1
default_authentication_plugin = mysql_native_password
basedir = /data/mysql/3306/base
plugin_dir = /data/mysql/3306/base/lib/plugin/
datadir = /data/mysql/3306/data
tmpdir = /data/mysql/3306/tmp
secure_file_priv = /data/mysql/3306/tmp
pid_file = mysqld.pid
socket = /data/mysql/3306/data/mysqld.sock
mysqlx_socket = /data/mysql/3306/data/mysqlx.sock
default_storage_engine = InnoDB
disabled_storage_engines = "MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
character_set_server = utf8mb4
collation_server = utf8mb4_bin
explicit_defaults_for_timestamp = 1
skip_slave_start = 1
skip_name_resolve = 1
skip_external_locking = 1
lower_case_table_names = 1
log_timestamps = SYSTEM
transaction_isolation = READ-COMMITTED
read_only = 0
## Connections
max_connections = 2000
max_connect_errors = 1000000
interactive_timeout = 1800
wait_timeout = 1800
## Session Memory Config
read_buffer_size = 8M
read_rnd_buffer_size = 8M
sort_buffer_size = 2M
join_buffer_size = 8M
tmp_table_size = 64M
max_heap_table_size = 64M
max_allowed_packet = 64M
## Cache Config
key_buffer_size = 16M
table_definition_cache = 2000
table_open_cache = 2000
table_open_cache_instances = 8
thread_cache_size = 200
open_files_limit = 65535
binlog_cache_size = 1M
## Log Config
log_bin =/data/mysql/3306/binlog/mysql-bin
binlog_format = row
sync_binlog = 1
binlog_error_action = ABORT_SERVER
max_binlog_size = 250M
binlog_rows_query_log_events = 1
binlog_expire_logs_seconds = 604800
log_bin_trust_function_creators = 1
log_slave_updates = 1
relay_log = /data/mysql/3306/relaylog/mysql-relay
relay_log_recovery = 1
master_info_repository = TABLE
relay_log_info_repository = TABLE
log_error = mysql-error.log
slow_query_log_file = mysql-slow.log
slow_query_log = 1
long_query_time = 0.2
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_slow_slave_statements = 1
log_throttle_queries_not_using_indexes = 10
## InnoDB Config
innodb_file_per_table = 1
innodb_data_file_path = ibdata1:1024M:autoextend
innodb_flush_log_at_trx_commit = 1
innodb_buffer_pool_size = 128M
innodb_buffer_pool_instances = 8
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_buffer_pool_dump_pct = 25
innodb_lock_wait_timeout = 10
innodb_io_capacity = 1000
innodb_io_capacity_max = 2000
innodb_max_dirty_pages_pct = 60
innodb_flush_method = O_DIRECT
innodb_flush_neighbors = 0
innodb_log_group_home_dir = /data/mysql/3306/redolog
innodb_log_file_size = 2G
innodb_log_files_in_group = 3
innodb_log_buffer_size = 64M
innodb_purge_threads = 4
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_thread_concurrency = 0
innodb_print_all_deadlocks = 1
innodb_strict_mode = 1
innodb_sort_buffer_size = 8M
innodb_stats_persistent_sample_pages = 64
innodb_autoinc_lock_mode = 2
innodb_online_alter_log_max_size = 1G
innodb_open_files = 4096
innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:50G
## Undo Log Config
innodb_undo_log_truncate = 1
innodb_max_undo_log_size = 8G
## GTID Config
gtid_mode = ON
enforce_gtid_consistency = 1
binlog_gtid_simple_recovery = 1
## Semi sync Config
plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
loose_rpl_semi_sync_master_enabled = 0
loose_rpl_semi_sync_slave_enabled = 0
loose_rpl_semi_sync_master_timeout = 30000
loose_rpl_semi_sync_master_wait_for_slave_count = 1
loose_rpl_semi_sync_master_wait_no_slave = 0
## Password Validate Plugin
plugin-load-add = validate_password.so
loose_validate_password_policy = MEDIUM
## MTS Config
slave-parallel-type = LOGICAL_CLOCK
slave_parallel_workers = 8
slave_preserve_commit_order = ON
## Performance Schema Config
performance-schema-instrument = 'wait/lock/metadata/sql/mdl=ON'
performance-schema-instrument = 'memory/%=COUNTED'
## Other
innodb_numa_interleave = 1
## group replication
plugin_load_add = "group_replication.so"
binlog_checksum = NONE
transaction_write_set_extraction = XXHASH64
report_host = 10.186.61.109
loose_group_replication = FORCE_PLUS_PERMANENT
loose_group_replication_start_on_boot = OFF
loose_group_replication_group_name = f657ae0e-1111-11e8-8bd0-0242ac222222
loose_group_replication_local_address = "10.186.61.109:33061"
loose_group_replication_group_seeds = "10.186.61.109:33061,10.186.61.109:33071,10.186.61.109:33081"
loose_group_replication_single_primary_mode = ON
loose_group_replication_bootstrap_group = OFF
loose_group_replication_ip_whitelist = '10.186.61.109/24'
## MGR flow control
loose_group_replication_flow_control_applier_threshold = 250000
loose_group_replication_flow_control_certifier_threshold = 250000
## Clone Plugin
plugin-load-add = mysql_clone.so
loose_clone = FORCE_PLUS_PERMANENT
以下是初始化完成并启动后的MySQL相关信息
[root@10-186-61-109 ~]# ps -ef|grep mysql
mysql 2583 1 0 5月25 ? 00:16:57 /data/mysql/3306/base/bin/mysqld --defaults-file=/data/mysql/3306/my.cnf.3306 --daemonize
mysql 2697 1 0 5月25 ? 00:16:30 /data/mysql/3307/base/bin/mysqld --defaults-file=/data/mysql/3307/my.cnf.3307 --daemonize
mysql 2758 1 0 5月25 ? 00:16:13 /data/mysql/3308/base/bin/mysqld --defaults-file=/data/mysql/3308/my.cnf.3308 --daemonize
root 7695 7652 0 11:09 pts/0 00:00:00 grep --color=auto mysql
4.MGR主节点初始化
以下是对MGR主节点做初始化的具体步骤,当前指定的主节点是10.186.61.109服务器上的3306端口
-- 创建复制用户并设置密码(该处使用了5.7默认的mysql_native_password插件,简化操作及便于向下兼容)
root@localhost[(none)]> CREATE USER 'repl'@'10.186.%' IDENTIFIED WITH mysql_native_password BY 'vai3pae$Th9du7';
Query OK, 0 rows affected (0.01 sec)
-- 赋予复制相关权限(克隆操作在主节点涉及BACKUP_ADMIN权限,所以该用户也可以给克隆操作复用)
root@localhost[(none)]> GRANT REPLICATION SLAVE,BACKUP_ADMIN ON *.* TO 'repl'@'10.186.%';
Query OK, 0 rows affected (0.01 sec)
-- 配置MGR复制通道
root@localhost[(none)]> CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='vai3pae$Th9du7' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.02 sec)
-- 将该节点作为初始化节点启动
root@localhost[(none)]> SET GLOBAL group_replication_bootstrap_group=ON;
Query OK, 0 rows affected (0.00 sec)
-- 启动组复制
root@localhost[(none)]> start group_replication;
Query OK, 0 rows affected (3.32 sec)
-- 启动完成后需将初始化启动参数关闭,避免异常再次启动带来的问题(配置文件中也需设置为OFF)
root@localhost[(none)]> SET GLOBAL group_replication_bootstrap_group=OFF;
Query OK, 0 rows affected (0.00 sec)
-- 查看MGR当前状态(可以看到已经有一个处于online的主节点)
root@localhost[(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 | 2df8c2c1-9a54-11ea-b82d-02000aba3d6d | 10.186.61.109 | 3306 | ONLINE | PRIMARY | 8.0.20 |
+---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+
1 row in set (0.00 sec)
5.MGR从节点初始化
当主节点已经初始化完成后,从节点只需要使用克隆插件将主节点的数据拷贝过来并启动组复制即可,以下为具体的克隆操作
在3307和3308服务器上重复一下步骤即可
-- 在从节点配置需要克隆的数据源IP及端口
root@localhost[(none)]> set global clone_valid_donor_list='10.186.61.109:3306';
Query OK, 0 rows affected (0.00 sec)
-- 指定主节点的用户连接信息执行克隆操作(当前数据库中有10张100W数据库的sbtest表,虚机环境拷贝时间约30s)
root@localhost[(none)]> CLONE INSTANCE FROM 'repl'@'10.186.61.109':3306 IDENTIFIED BY 'vai3pae$Th9du7';
Query OK, 0 rows affected (28.12 sec)
-- 待克隆完成后,主从数据库的数据已经保持完全一致,这时按照相同步骤将从节点开启MGR复制通道即可
root@localhost[(none)]> CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='vai3pae$Th9du7' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.01 sec)
-- 在从节点启动组复制
root@localhost[(none)]> start group_replication;
Query OK, 0 rows affected (4.29 sec)
-- 查看复制状态可以看到已经有一个从节点已加入MGR集群且处于online状态
root@localhost[(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 | 2df8c2c1-9a54-11ea-b82d-02000aba3d6d | 10.186.61.109 | 3306 | ONLINE | PRIMARY | 8.0.20 |
| group_replication_applier | 6f34a346-9a54-11ea-af6b-02000aba3d6d | 10.186.61.109 | 3307 | ONLINE | SECONDARY | 8.0.20 |
+---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+
2 rows in set (0.00 sec)
待3307和3308端口的数据库均执行以上操作后,MGR集群状态如下所示
root@localhost[(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 | 2df8c2c1-9a54-11ea-b82d-02000aba3d6d | 10.186.61.109 | 3306 | ONLINE | PRIMARY | 8.0.20 |
| group_replication_applier | 6f34a346-9a54-11ea-af6b-02000aba3d6d | 10.186.61.109 | 3307 | ONLINE | SECONDARY | 8.0.20 |
| group_replication_applier | 6f8216ad-9a54-11ea-942e-02000aba3d6d | 10.186.61.109 | 3308 | ONLINE | SECONDARY | 8.0.20 |
+---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)
6.总结
在MySQL8.0.17以后,通过Clone Plugin(克隆插件)可以非常方便且快速的部署MGR集群环境,且操作步骤大大简化
实际上MySQL提供的InnoDB Cluster方案(MGR+mysqlsh+mysqlrouter)方案中,对集群的初始化也同样是使用克隆插件来完成.
转载请说明出处
|QQ:327488733@qq.com
