docker部署mysql MGR集群
mysql MGR 集群docker部署
一、修改主机名
hostnamectl set-hostname node1
hostnamectl set-hostname node2
hostnamectl set-hostname node3
修改主机名，三台机器都修改
二、修改hosts文件
cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
172.25.254.200 node1
172.25.254.201 node2
172.25.254.202 node3
将主机名对应的IP地址加入hosts文件，三台机器都写入
三、在主机上创建mysql窗口映射目录
mkdir -p /opt/mysql/data /opt/mysql/conf /opt/mysql/files
四、写入mysql配置文件
vim /opt/mysql/conf/my.cnf
node1 配置文件：
[client]
port = 3306
socket =/var/lib/mysql/mysqld.sock
[mysql]
no-auto-rehash
[mysqld]
port = 3306
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysqld.sock
log-error = /var/lib/mysql/error.log
pid-file = /var/lib/mysql/mysqld.pid
default_authentication_plugin=mysql_native_password
admin_port = 33064
mysqlx-port = 33063
bind-address = 0.0.0.0
#
# Disable other storage engines
#
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
skip_name_resolve = 1
default_time_zone = "+8:00"
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
init_connect='SET NAMES utf8mb4'
lower_case_table_names = 1
transaction_isolation = READ-COMMITTED
explicit_defaults_for_timestamp = true
open_files_limit = 65535
max_connections = 300
max_connect_errors = 600
back_log = 110
table_open_cache = 600
table_definition_cache = 700
table_open_cache_instances = 64
thread_stack = 512K
external-locking = FALSE
max_allowed_packet = 128M
sort_buffer_size = 4M
join_buffer_size = 4M
thread_cache_size = 20
interactive_timeout = 1800
wait_timeout = 1800
lock_wait_timeout = 3600
tmp_table_size = 64M
max_heap_table_size = 64M
report_host=node1
server-id = 1
#--###########################-- binlog配置 --##########################################
log-bin=mysql1-bin
binlog_format = ROW
binlog_row_image = FULL
binlog_expire_logs_seconds = 1209600
#master_info_repository = TABLE
#relay_log_info_repository = TABLE
log_replica_updates = ON
relay_log_recovery = 1
replica_skip_errors = ddl_exist_errors
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
#transaction_write_set_extraction = XXHASH64
binlog_cache_size = 4M
max_binlog_cache_size = 2G
max_binlog_size = 1G
gtid_mode = on
enforce_gtid_consistency=ON
# MySQL 8.0.21可以不设置
#binlog_checksum=NONE
key_buffer_size = 15M
read_buffer_size = 8M
read_rnd_buffer_size = 4M
bulk_insert_buffer_size = 64M
#--###########################-- innodb配置 --##########################################
innodb_thread_concurrency = 0
innodb_buffer_pool_size = 32G
innodb_buffer_pool_instances = 4
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_data_file_path = ibdata1:1G:autoextend
innodb_log_buffer_size = 32M
innodb_log_files_in_group = 3
innodb_log_file_size = 2G
innodb_undo_log_truncate = 1
innodb_max_undo_log_size = 2G
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 = 20
innodb_rollback_on_timeout = 1
innodb_print_all_deadlocks = 1
innodb_sort_buffer_size = 67108864
innodb_autoinc_lock_mode = 1
innodb_file_per_table = 1
innodb_online_alter_log_max_size = 4G
#--###########################-- MGR配置 --##########################################
# 需要等待MYSQL启动后执行SQL安装插件 #
# INSTALL PLUGIN group_replication SONAME 'group_replication.so'; #
#--###########################-- MGR配置 --##########################################
group_replication_group_name = "7cf70110-59f9-11ee-a293-246e967fa528"
group_replication_start_on_boot = off
group_replication_local_address = "172.25.254.200:33061"
group_replication_group_seeds = "172.25.254.200:33061,172.25.254.201:33061,172.25.254.202:33061"
group_replication_bootstrap_group = off
group_replication_autorejoin_tries=3 # 最大尝试次数
group_replication_member_expel_timeout=300 # 节点失联超时时间（秒）
node2配置文件：
[client]
port = 3306
socket =/var/lib/mysql/mysqld.sock
[mysql]
no-auto-rehash
[mysqld]
port = 3306
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysqld.sock
log-error = /var/lib/mysql/error.log
pid-file = /var/lib/mysql/mysqld.pid
default_authentication_plugin=mysql_native_password
admin_port = 33064
mysqlx-port = 33063
bind-address = 0.0.0.0
#
# Disable other storage engines
#
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
skip_name_resolve = 1
default_time_zone = "+8:00"
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
init_connect='SET NAMES utf8mb4'
lower_case_table_names = 1
transaction_isolation = READ-COMMITTED
explicit_defaults_for_timestamp = true
open_files_limit = 65535
max_connections = 300
max_connect_errors = 600
back_log = 110
table_open_cache = 600
table_definition_cache = 700
table_open_cache_instances = 64
thread_stack = 512K
external-locking = FALSE
max_allowed_packet = 128M
sort_buffer_size = 4M
join_buffer_size = 4M
thread_cache_size = 20
interactive_timeout = 1800
wait_timeout = 1800
lock_wait_timeout = 3600
tmp_table_size = 64M
max_heap_table_size = 64M
report_host=node2
server-id = 2
#--###########################-- binlog配置 --##########################################
log-bin=mysql2-bin
binlog_format = ROW
binlog_row_image = FULL
binlog_expire_logs_seconds = 1209600
#master_info_repository = TABLE
#relay_log_info_repository = TABLE
log_replica_updates = ON
relay_log_recovery = 1
replica_skip_errors = ddl_exist_errors
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
#transaction_write_set_extraction = XXHASH64
binlog_cache_size = 4M
max_binlog_cache_size = 2G
max_binlog_size = 1G
gtid_mode = on
enforce_gtid_consistency=ON
# MySQL 8.0.21可以不设置
#binlog_checksum=NONE
key_buffer_size = 15M
read_buffer_size = 8M
read_rnd_buffer_size = 4M
bulk_insert_buffer_size = 64M
#--###########################-- innodb配置 --##########################################
innodb_thread_concurrency = 0
innodb_buffer_pool_size = 32G
innodb_buffer_pool_instances = 4
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_data_file_path = ibdata1:1G:autoextend
innodb_log_buffer_size = 32M
innodb_log_files_in_group = 3
innodb_log_file_size = 2G
innodb_undo_log_truncate = 1
innodb_max_undo_log_size = 2G
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 = 20
innodb_rollback_on_timeout = 1
innodb_print_all_deadlocks = 1
innodb_sort_buffer_size = 67108864
innodb_autoinc_lock_mode = 1
innodb_file_per_table = 1
innodb_online_alter_log_max_size = 4G
#--###########################-- MGR配置 --##########################################
# 需要等待MYSQL启动后执行SQL安装插件 #
# INSTALL PLUGIN group_replication SONAME 'group_replication.so'; #
#--###########################-- MGR配置 --##########################################
group_replication_group_name = "7cf70110-59f9-11ee-a293-246e967fa528"
group_replication_start_on_boot = off
group_replication_local_address = "172.25.254.201:33061"
group_replication_group_seeds = "172.25.254.200:33061,172.25.254.201:33061,172.25.254.202:33061"
group_replication_bootstrap_group = off
group_replication_autorejoin_tries=3 # 最大尝试次数
group_replication_member_expel_timeout=300 # 节点失联超时时间（秒）
node3配置文件：
[client]
port = 3306
socket =/var/lib/mysql/mysqld.sock
[mysql]
no-auto-rehash
[mysqld]
port = 3306
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysqld.sock
log-error = /var/lib/mysql/error.log
pid-file = /var/lib/mysql/mysqld.pid
default_authentication_plugin=mysql_native_password
admin_port = 33064
mysqlx-port = 33063
bind-address = 0.0.0.0
#
# Disable other storage engines
#
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
skip_name_resolve = 1
default_time_zone = "+8:00"
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
init_connect='SET NAMES utf8mb4'
lower_case_table_names = 1
transaction_isolation = READ-COMMITTED
explicit_defaults_for_timestamp = true
open_files_limit = 65535
max_connections = 300
max_connect_errors = 600
back_log = 110
table_open_cache = 600
table_definition_cache = 700
table_open_cache_instances = 64
thread_stack = 512K
external-locking = FALSE
max_allowed_packet = 128M
sort_buffer_size = 4M
join_buffer_size = 4M
thread_cache_size = 20
interactive_timeout = 1800
wait_timeout = 1800
lock_wait_timeout = 3600
tmp_table_size = 64M
max_heap_table_size = 64M
report_host=node3
server-id = 3
#--###########################-- binlog配置 --##########################################
log-bin=mysql3-bin
binlog_format = ROW
binlog_row_image = FULL
binlog_expire_logs_seconds = 1209600
#master_info_repository = TABLE
#relay_log_info_repository = TABLE
log_replica_updates = ON
relay_log_recovery = 1
replica_skip_errors = ddl_exist_errors
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
#transaction_write_set_extraction = XXHASH64
binlog_cache_size = 4M
max_binlog_cache_size = 2G
max_binlog_size = 1G
gtid_mode = on
enforce_gtid_consistency=ON
# MySQL 8.0.21可以不设置
#binlog_checksum=NONE
key_buffer_size = 15M
read_buffer_size = 8M
read_rnd_buffer_size = 4M
bulk_insert_buffer_size = 64M
#--###########################-- innodb配置 --##########################################
innodb_thread_concurrency = 0
innodb_buffer_pool_size = 32G
innodb_buffer_pool_instances = 4
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_data_file_path = ibdata1:1G:autoextend
innodb_log_buffer_size = 32M
innodb_log_files_in_group = 3
innodb_log_file_size = 2G
innodb_undo_log_truncate = 1
innodb_max_undo_log_size = 2G
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 = 20
innodb_rollback_on_timeout = 1
innodb_print_all_deadlocks = 1
innodb_sort_buffer_size = 67108864
innodb_autoinc_lock_mode = 1
innodb_file_per_table = 1
innodb_online_alter_log_max_size = 4G
#--###########################-- MGR配置 --##########################################
# 需要等待MYSQL启动后执行SQL安装插件 #
# INSTALL PLUGIN group_replication SONAME 'group_replication.so'; #
#--###########################-- MGR配置 --##########################################
group_replication_group_name = "7cf70110-59f9-11ee-a293-246e967fa528"
group_replication_start_on_boot = off
group_replication_local_address = "172.25.254.202:33061"
group_replication_group_seeds = "172.25.254.200:33061,172.25.254.201:33061,172.25.254.202:33061"
group_replication_bootstrap_group = off
group_replication_autorejoin_tries=3 # 最大尝试次数
group_replication_member_expel_timeout=300 # 节点失联超时时间（秒）
五、修改配置文件注释MGR相关配置,docker启动mysql容器
注释以下配置文件内容，不注释容器无法启动，所有机器都需要操作：
#group_replication_group_name = "7cf70110-59f9-11ee-a293-246e967fa528"
#group_replication_start_on_boot = off
#group_replication_local_address = "172.25.254.202:33061"
#group_replication_group_seeds = "172.25.254.200:33061,172.25.254.201:33061,172.25.254.202:33061"
#group_replication_bootstrap_group = off
#group_replication_autorejoin_tries=3 # 最大尝试次数
#group_replication_member_expel_timeout=300 # 节点失联超时时间（秒）
注释完后，启动mysql容器：
docker启动命令；
node-1:
docker run -d \
--name mysql-node-1 \
--restart=always \
-v /opt/mysql/data:/var/lib/mysql \
-v /opt/mysql/conf:/etc/mysql/conf.d \
-e MYSQL_ROOT_PASSWORD=rootpass \
-e MYSQL_DATABASE=app_db \
-e MYSQL_USER=app_user \
-e MYSQL_PASSWORD=userpass \
-p 3306:3306 \
-p 33061:33061 \
mysql:8.0.26
node-2:
docker run -d \
--name mysql-node-2 \
--restart=always \
-v /opt/mysql/data:/var/lib/mysql \
-v /opt/mysql/conf:/etc/mysql/conf.d \
-e MYSQL_ROOT_PASSWORD=rootpass \
-e MYSQL_DATABASE=app_db \
-e MYSQL_USER=app_user \
-e MYSQL_PASSWORD=userpass \
-p 3306:3306 \
-p 33061:33061 \
mysql:8.0.26
node-3:
docker run -d \
--name mysql-node-3 \
--restart=always \
-v /opt/mysql/data:/var/lib/mysql \
-v /opt/mysql/conf:/etc/mysql/conf.d \
-e MYSQL_ROOT_PASSWORD=rootpass \
-e MYSQL_DATABASE=app_db \
-e MYSQL_USER=app_user \
-e MYSQL_PASSWORD=userpass \
-p 3306:3306 \
-p 33061:33061 \
mysql:8.0.26
六、使用navicat连接数据库，创建同步用户，安装插件，插件安装完成后取消第五步中注释的配置文件
navicat上选中数据库新建查询，执行以下语句（所有机器执行）：
SET SQL_LOG_BIN=0;
CREATE USER rpl_user@'%' IDENTIFIED WITH mysql_native_password BY 'PassWord123_';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
GRANT CONNECTION_ADMIN ON *.* TO rpl_user@'%';
GRANT BACKUP_ADMIN ON *.* TO rpl_user@'%';
GRANT GROUP_REPLICATION_STREAM ON *.* TO rpl_user@'%';
GRANT SELECT ON performance_schema.replication_group_members TO rpl_user@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;
CHANGE REPLICATION SOURCE TO SOURCE_USER='rpl_user', SOURCE_PASSWORD='PassWord123_' FOR CHANNEL 'group_replication_recovery';
安装mysql插件（所有机器执行,sql语句）：
INSTALL PLUGIN group_replication SONAME 'group_replication.so';
查看插件：
SHOW PLUGINS;
取消配置文件中注释的mysql MGR配置：
group_replication_group_name = "7cf70110-59f9-11ee-a293-246e967fa528"
group_replication_start_on_boot = off
group_replication_local_address = "172.25.254.202:33061"
group_replication_group_seeds = "172.25.254.200:33061,172.25.254.201:33061,172.25.254.202:33061"
group_replication_bootstrap_group = off
group_replication_autorejoin_tries=3 # 最大尝试次数
group_replication_member_expel_timeout=300 # 节点失联超时时间（秒）
重启容器:
docker restart mysql-node-1
docker restart mysql-node-2
docker restart mysql-node-3
重启完成后，navicat上查询MGR状态：
SHOW VARIABLES LIKE 'group_replication%';
七、创建集群
启动引导实例：
在node1中执行启动集群操作：
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION USER='rpl_user', PASSWORD='PassWord123_';
SET GLOBAL group_replication_bootstrap_group=OFF;
查看集群成员状态：
SELECT * FROM performance_schema.replication_group_members;
在其它机器上，登录mysql后执行加入操作；
START GROUP_REPLICATION USER='rpl_user', PASSWORD='PassWord123_';
SELECT * FROM performance_schema.replication_group_members;
SHOW BINLOG EVENTS;
可执行sql语句检查集群状态，所有节点状态online后，集群启动成功
SELECT * FROM performance_schema.replication_group_members;
八、集群节点故障后重新加入集群
集群节点故障后需要重新加入集群里时，查询集群状态为offline时，可执行以下sql语句，重新加入集群：
STOP GROUP_REPLICATION;
RESET SLAVE ALL;
CHANGE MASTER TO
MASTER_USER='rpl_user',
MASTER_PASSWORD='PassWord123_',
MASTER_AUTO_POSITION=1
FOR CHANNEL 'group_replication_recovery';
查看集群成员状态故障节点恢复为online状态，加入集群成功
SELECT * FROM performance_schema.replication_group_members;
浙公网安备 33010602011771号