plugin.super mysql_使用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)方案中,对集群的初始化也同样是使用克隆插件来完成.