MySQL 8.0.42创建MGR集群
MySQL 8.0.42创建MGR集群
概述
关于MySQL MGR集群的介绍就不在这里做详细的介绍了，大家可以自己到官网上查看阅读。在这里主要是实际操作方面的内容
总体结构设计如下图
服务器节点信息
my.cnf配置
节点1
[mysqld]##basic settings###server-id=104port = 3309character-set-server = utf8mb4collation-server = utf8mb4_unicode_ciskip-name-resolvemax_connections=1000max_user_connections=800max_allowed_packet=512Mmax_connect_errors=100000datadir = /usr/local/mysql/datasocket = /usr/local/mysql/data/mysql.sockpid_file = /usr/local/mysql/data/mysql.pidtransaction_isolation = READ-COMMITTEDlower_case_table_names=1default_time_zone =+8:00open_files_limit=65535log_timestamps=systemwait_timeout=900interactive_timeout=900##innodb setting##innodb_buffer_pool_size = 256Minnodb_buffer_pool_instances = 1innodb_io_capacity=2000innodb_flush_method=O_DIRECTinnodb_flush_neighbors=0innodb_flush_log_at_trx_commit = 1innodb_print_all_deadlocks = 1innodb_online_alter_log_max_size=128Minnodb_lock_wait_timeout=10innodb_file_per_table=ONinnodb_doublewrite=ON##log settings##log-error = /usr/local/mysql/data/error.loglog-bin = /usr/local/mysql/data/mysql_bin.logslow_query_log = 1slow_query_log_file = /usr/local/mysql/data/mysql_slow_query.loglong_query_time = 10##replication settings##gtid-mode=onenforce-gtid-consistency=truemaster-info-repository=TABLErelay-log-info-repository=TABLEsync-master-info=1000relay_log_recovery = 1relay-log=/usr/local/mysql/data/relay-bin#binloglog_bin=/usr/local/mysql/data/binlog#expire_logs_days=10 #MySQL 5.7版本binlog_expire_logs_seconds = 604800 # 保留7天max_binlog_cache_size=200Msync_binlog=1##MGR settingsbinlog_checksum = NONElog_replica_updates = ONbinlog_format=rowplugin_load_add=\'group_replication.so\'#transaction_write_set_extraction =\'XXHASH64\'#loose-group_replication_group_name = \'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa\'#loose-group_replication_start_on_boot = off#loose-group_replication_local_address = \'192.168.56.104:10061\'#loose-group_replication_group_seeds =\'192.168.56.104:10061,192.168.56.105:10061,192.168.56.106:10061\'#loose-group_replication_bootstrap_group = off#loose-group_replication_ip_whitelist = \'192.168.56.104/24,192.168.56.105/24,192.168.56.106/24\'#loose-group_replication_member_weight=50#loose-group_replication_single_primary_mode=ON#loose-group_replication_enforce_update_everywhere_checks=OFF ###单主模式关闭，多主模式开启[client]port = 3309socket = /usr/local/mysql/data/mysql.sock节点2
[mysqld]##basic settings###server-id=105port = 3309character-set-server = utf8mb4collation-server = utf8mb4_unicode_ciskip-name-resolvemax_connections=1000max_user_connections=800max_allowed_packet=512Mmax_connect_errors=100000datadir = /usr/local/mysql/datasocket = /usr/local/mysql/data/mysql.sockpid_file = /usr/local/mysql/data/mysql.pidtransaction_isolation = READ-COMMITTEDlower_case_table_names=1default_time_zone =+8:00open_files_limit=65535log_timestamps=systemwait_timeout=900interactive_timeout=900##innodb setting##innodb_buffer_pool_size = 256Minnodb_buffer_pool_instances = 1innodb_io_capacity=2000innodb_flush_method=O_DIRECTinnodb_flush_neighbors=0innodb_flush_log_at_trx_commit = 1innodb_print_all_deadlocks = 1innodb_online_alter_log_max_size=128Minnodb_lock_wait_timeout=10innodb_file_per_table=ONinnodb_doublewrite=ON##log settings##log-error = /usr/local/mysql/data/error.loglog-bin = /usr/local/mysql/data/mysql_bin.logslow_query_log = 1slow_query_log_file = /usr/local/mysql/data/mysql_slow_query.loglong_query_time = 10##replication settings##gtid-mode=onenforce-gtid-consistency=truemaster-info-repository=TABLErelay-log-info-repository=TABLEsync-master-info=1000relay_log_recovery = 1relay-log=/usr/local/mysql/data/relay-bin#binloglog_bin=/usr/local/mysql/data/binlog#expire_logs_days=10 #MySQL 5.7版本binlog_expire_logs_seconds = 604800 # 保留7天max_binlog_cache_size=200Msync_binlog=1##MGR settingsbinlog_checksum = NONElog_replica_updates = ONbinlog_format=rowplugin_load_add=\'group_replication.so\'#transaction_write_set_extraction =\'XXHASH64\'#loose-group_replication_group_name = \'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa\'#loose-group_replication_start_on_boot = off#loose-group_replication_local_address = \'192.168.56.105:10061\'#loose-group_replication_group_seeds =\'192.168.56.104:10061,192.168.56.105:10061,192.168.56.106:10061\'#loose-group_replication_bootstrap_group = off#loose-group_replication_ip_whitelist = \'192.168.56.104/24,192.168.56.105/24,192.168.56.106/24\'#loose-group_replication_member_weight=50#loose-group_replication_single_primary_mode=ON#loose-group_replication_enforce_update_everywhere_checks=OFF ###单主模式关闭，多主模式开启[client]port = 3309socket = /usr/local/mysql/data/mysql.sock节点3
[mysqld]##basic settings###server-id=106port = 3309character-set-server = utf8mb4collation-server = utf8mb4_unicode_ciskip-name-resolvemax_connections=1000max_user_connections=800max_allowed_packet=512Mmax_connect_errors=100000datadir = /usr/local/mysql/datasocket = /usr/local/mysql/data/mysql.sockpid_file = /usr/local/mysql/data/mysql.pidtransaction_isolation = READ-COMMITTEDlower_case_table_names=1default_time_zone =+8:00open_files_limit=65535log_timestamps=systemwait_timeout=900interactive_timeout=900##innodb setting##innodb_buffer_pool_size = 256Minnodb_buffer_pool_instances = 1innodb_io_capacity=2000innodb_flush_method=O_DIRECTinnodb_flush_neighbors=0innodb_flush_log_at_trx_commit = 1innodb_print_all_deadlocks = 1innodb_online_alter_log_max_size=128Minnodb_lock_wait_timeout=10innodb_file_per_table=ONinnodb_doublewrite=ON##log settings##log-error = /usr/local/mysql/data/error.loglog-bin = /usr/local/mysql/data/mysql_bin.logslow_query_log = 1slow_query_log_file = /usr/local/mysql/data/mysql_slow_query.loglong_query_time = 10##replication settings##gtid-mode=onenforce-gtid-consistency=truemaster-info-repository=TABLErelay-log-info-repository=TABLEsync-master-info=1000relay_log_recovery = 1relay-log=/usr/local/mysql/data/relay-bin#binloglog_bin=/usr/local/mysql/data/binlog#expire_logs_days=10 #MySQL 5.7版本binlog_expire_logs_seconds = 604800 # 保留7天max_binlog_cache_size=200Msync_binlog=1##MGR settingsbinlog_checksum = NONElog_replica_updates = ONbinlog_format=rowplugin_load_add=\'group_replication.so\'#transaction_write_set_extraction =\'XXHASH64\'#loose-group_replication_group_name = \'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa\'#loose-group_replication_start_on_boot = off#loose-group_replication_local_address = \'192.168.56.106:10061\'#loose-group_replication_group_seeds =\'192.168.56.104:10061,192.168.56.105:10061,192.168.56.106:10061\'#loose-group_replication_bootstrap_group = off#loose-group_replication_ip_whitelist = \'192.168.56.104/24,192.168.56.105/24,192.168.56.106/24\'#loose-group_replication_member_weight=50#loose-group_replication_single_primary_mode=ON#loose-group_replication_enforce_update_everywhere_checks=OFF ###单主模式关闭，多主模式开启[client]port = 3309socket = /usr/local/mysql/data/mysql.sock三个节点的配置参数只是server-id和loose-group_replication_local_address不一样。在初始化的时候先注释掉mgr相关的参数，否则会提示ERROR，待初始化完成之后在取消注释
初始化数据库
所有节点
# 初始化数据目录/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --initialize --user=mysql --basedir=/usr/local/mysql # 生成的临时密码会显示在终端，务必记录！cat /usr/local/mysql/data/error.log # 启动关闭数据库/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf &/usr/local/mysql/bin/mysqladmin -uroot -p123456 --socket=/usr/local/mysql/data/mysql.sock shutdown &# 修改初始密码/usr/local/mysql/bin/mysql -uroot -p alter user \'root\'@\'localhost\' identified WITH mysql_native_password by \'123456\';flush privileges;配置MGR
节点1
#创建 MGR 同步用户
SET SQL_LOG_BIN=0;CREATE USER \'rpl_user\'@\'%\' IDENTIFIED WITH mysql_native_password BY \'rpl_pass\';GRANT REPLICATION SLAVE ON *.* TO rpl_user@\'%\';GRANT BACKUP_ADMIN ON *.* TO rpl_user@\'%\';FLUSH PRIVILEGES;SET SQL_LOG_BIN=1;select host,user,plugin from mysql.user;reset master;change master tomaster_user=\'rpl_user\',master_password=\'rpl_pass\'for channel \'group_replication_recovery\';#查看确认 MGR 组件
#install plugin group_replication soname \'group_replication.so\';show plugins;#启动MGR复制组
set global group_replication_bootstrap_group=on;start group_replication;set global group_replication_bootstrap_group=off;select * from performance_schema.replication_group_members;###查看读写属性select @@read_only, @@super_read_only;节点2
#创建 MGR 同步用户
SET SQL_LOG_BIN=0;CREATE USER rpl_user@\'%\' IDENTIFIED WITH mysql_native_password BY \'rpl_pass\';GRANT REPLICATION SLAVE ON *.* TO rpl_user@\'%\';GRANT BACKUP_ADMIN ON *.* TO rpl_user@\'%\';FLUSH PRIVILEGES;SET SQL_LOG_BIN=1;select host,user,plugin from mysql.user;reset master;change master tomaster_user=\'rpl_user\',master_password=\'rpl_pass\'for channel \'group_replication_recovery\';###启动 MGR 复制组start group_replication;select * from performance_schema.replication_group_members;###查看读写属性select @@read_only, @@super_read_only;节点3
#创建 MGR 同步用户
SET SQL_LOG_BIN=0;CREATE USER rpl_user@\'%\' IDENTIFIED WITH mysql_native_password BY \'rpl_pass\';GRANT REPLICATION SLAVE ON *.* TO rpl_user@\'%\';GRANT BACKUP_ADMIN ON *.* TO rpl_user@\'%\';FLUSH PRIVILEGES;SET SQL_LOG_BIN=1;select host,user,plugin from mysql.user;reset master;change master tomaster_user=\'rpl_user\',master_password=\'rpl_pass\'for channel \'group_replication_recovery\';###启动 MGR 复制组start group_replication;select * from performance_schema.replication_group_members;###查看读写属性select @@read_only, @@super_read_only;结果
mysql> select * from performance_schema.replication_group_members;+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+| group_replication_applier | 62f03723-6ade-11f0-8bc8-080027859595 | mgrser1 | 3309 | ONLINE | PRIMARY | 8.0.42 | XCom || group_replication_applier | 6afd9b46-6adf-11f0-88b8-080027238cc1 | mgrser2 | 3309 | ONLINE | SECONDARY | 8.0.42 | XCom || group_replication_applier | db344a82-6adf-11f0-ae5d-0800275e0275 | mgrser3 | 3309 | ONLINE | SECONDARY | 8.0.42 | XCom |+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+3 rows in set (0.00 sec)
