MySQL8.0的MGR多主搭建与测试

环境： 虚拟机VMware， OS：CentOS Linux release 7.3.1611 (Core) 按照下述的单机Mysql8.0的安装方式进行安装完之后，将该机复制两台，分别修改IP为192.168.1.78和192.168.1.78，以及两台新机器配置文件/etc/my.cnf的serverid，还有要去数据路径下删掉auto.cnf不然配置不成。 vim /etc/hosts 192.168.1.78 mgr-mul1 192.168.1.79 mgr-mul2 192.168.1.80 mgr-mul3 要用hostname修改每台机器的主机名确保不相同，不然配置不成功。 一、单机安装(192.168.1.78) 官网下载8.0.11版本的tar.gz包解压，复制更名到/usr/local/mysql 创建数据路径及日志路径并把他们权限改给mysql用户： /data/mysql3306/mysql3306 /data/mysql3306/logs 配置文件如下：注意修改server-id及组相关ip vim /etc/my.cnf ----------------------------------------------------------------------- [client] port = 3306 socket = /tmp/mysql3306.sock default_character_set = utf8mb4 [mysql] default_character_set = utf8mb4 [mysqld] lower_case_table_names = 1 port = 3306 basedir = /usr/local/mysql8 datadir = /data/mysql3306/mysql3306 socket = /tmp/mysql3306.sock pid_file = /data/mysql3306/mysql3306.pid tmpdir = /tmp/ default_authentication_plugin = mysql_native_password skip_name_resolve = 1 character_set_server = utf8mb4 collation_server = utf8mb4_unicode_ci max_connections = 2000 max_connect_errors = 10000 interactive_timeout = 600 wait_timeout = 600 table_open_cache = 2048 thread_cache_size = 51 max_allowed_packet = 16M tmp_table_size = 256M max_heap_table_size = 96M sort_buffer_size = 4M read_buffer_size = 4M #??IO join_buffer_size = 4M read_rnd_buffer_size = 64M #??篓虏 bulk_insert_buffer_size = 64M # log_error = /data/mysql3306/logs/mysql-error.log log_timestamps = system slow_query_log = 1 slow_query_log_file = /data/mysql3306/logs/mysql-slow.log long_query_time = 1 log_queries_not_using_indexes = 1 log_throttle_queries_not_using_indexes =10 log_slow_admin_statements = 1 log_slow_slave_statements = 1 min_examined_row_limit = 100 log_queries_not_using_indexes = 1 log_output = FILE relay_log = /data/mysql3306/mysql3306/mysql-relay #binlog server_id = 1783306 log_bin = /data/mysql3306/mysql3306/mysql-bin binlog_expire_logs_seconds =604800 binlog_format = row max_binlog_size = 1024M max_binlog_cache_size = 8G binlog_cache_size = 4M sync_binlog = 1 master_info_repository = TABLE relay_log_info_repository = TABLE relay_log_recovery = ON log_slave_updates = 1 #innodb default_storage_engine = InnoDB innodb_page_size = 16384 innodb_data_home_dir = /data/mysql3306/mysql3306/ innodb_data_file_path = ibdata1:512M:autoextend innodb_log_group_home_dir = /data/mysql3306/mysql3306 innodb_buffer_pool_instances = 1 innodb_buffer_pool_size = 1024M innodb_buffer_pool_dump_pct = 40 innodb_page_cleaners = 8 innodb_log_file_size = 256M innodb_log_files_in_group = 2 innodb_log_buffer_size = 32M #default 16M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 30 innodb_strict_mode = 1 innodb_print_all_deadlocks = 1 innodb_buffer_pool_load_at_startup = 1 innodb_buffer_pool_dump_at_shutdown = 1 innodb_buffer_pool_dump_pct = 40 innodb_open_files = 65536 innodb_file_per_table = 1 innodb_lock_wait_timeout = 30 innodb_read_io_threads = 8 innodb_write_io_threads = 8 innodb_io_capacity = 200 innodb_flush_log_at_trx_commit = 1 innodb_flush_method = O_DIRECT innodb_purge_threads = 4 innodb_max_dirty_pages_pct = 75 transaction_isolation = READ-COMMITTED innodb_page_cleaners = 16 explicit_defaults_for_timestamp = 1 #undo innodb_undo_directory = /data/mysql3306/mysql3306/ innodb_undo_tablespaces = 3 innodb_undo_log_truncate = 1 innodb_max_undo_log_size = 1000M innodb_purge_rseg_truncate_frequency = 128 #rep slave-parallel-type=LOGICAL_CLOCK slave-parallel-workers=16 master_info_repository=TABLE relay_log_info_repository=TABLE relay_log_recovery=ON binlog_checksum=NONE slave_preserve_commit_order=1 #group_replication_flow_control_mode=DISABLED log_slave_updates=1 #GR enforce_gtid_consistency=1 gtid_mode = on transaction_write_set_extraction =XXHASH64 # off loose-group_replication_group_name ="a876d35e-9110-11e6-a365-882b2b5s09d6" #可在库里使用SELECT UUID()生成，一个组的所有机器都相同。 loose-group_replication_local_address ="192.168.1.78:24900" #本机IP loose-group_replication_group_seeds ="192.168.1.78:24900,192.168.1.79:24900,192.168.1.80:24900" loose-group_replication_bootstrap_group =off # off loose-group_replication_start_on_boot =off #非常重要 [mysqldump] max_allowed_packet = 16M [mysqldump] max_allowed_packet = 16M ----------------------------------------------------------------------- 初始化 /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --initialize-insecure --basedir=/usr/local/mysql --datadir=/data/mysql3306/mysql3306/ --explicit_defaults_for_timestamp --user=mysql 22个文件。 启动 /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --ledir=/usr/local/mysql/bin --datadir=/data/mysql3306/mysql3306 --user=mysql & echo "/usr/local/mysql8/bin/mysqld_safe --defaults-file=/etc/my.cnf --ledir=/usr/local/mysql8/bin --datadir=/data/mysql3306/mysql3306 --user=mysql &">>/etc/rc.local 登录： mysql alter user root@localhost identified by '123456'; create user root@'192.168.1.%' identified by '123456'; grant all on *.* to root@'192.168.1.%'; CREATE USER rep@'%' IDENTIFIED BY 'rep'; GRANT REPLICATION SLAVE ON *.* TO rep@'%'; flush privileges; 保存快照(before MGR-Multi)，然后复制两台，做以下几处修改： (1)修改虚拟机IP分别为192.168.1.79和192.168.1.80，并修改各自的主机名hostname与hosts文件对应; (2)然后修改各自的my.cnf里的server_id和loose-group_replication_local_address改为本机IP; (3)进入/data/mysql3306/mysql3306里删除auto.cnf文件。 然后各自保存快照(before MGR-Multi); 二、多主搭建 1、所有节点上安装组件： install plugin group_replication soname 'group_replication.so'; show plugins; 2、在192.168.1.78上：(只需要在这个上执行，跟单主模式相同) CHANGE MASTER TO MASTER_USER='rep',MASTER_PASSWORD='rep' FOR CHANNEL 'group_replication_recovery'; set persist group_replication_single_primary_mode=off; --单主的都是ON 临时开启bootstrap： SET GLOBAL group_replication_bootstrap_group=ON; start group_replication; SET GLOBAL group_replication_bootstrap_group=OFF; select * from performance_schema.replication_group_members; 3、等上述操作完成之后，在192.168.1.79和192.168.1.80依次执行： CHANGE MASTER TO MASTER_USER='rep',MASTER_PASSWORD='rep' FOR CHANNEL 'group_replication_recovery'; set persist group_replication_single_primary_mode=off; START GROUP_REPLICATION; 4、验证： 任意库执行：select * from performance_schema.replication_group_members; select * from performance_schema.replication_group_members; +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ | group_replication_applier | 5651f374-97c8-11e8-9966-000c295ce53e | mgr-mul1 | 3306 | ONLINE | PRIMARY | 8.0.11 | | group_replication_applier | 749eb449-97e1-11e8-ab87-000c29ee3a4b | mgr-mul3 | 3306 | ONLINE | PRIMARY | 8.0.11 | | group_replication_applier | 7753edfa-97e1-11e8-a10c-000c296952a6 | mgr-mul2 | 3306 | ONLINE | PRIMARY | 8.0.11 | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ 可以看到三台都在线、各自主机名正确，并且都是primary则表示MGR多主配置成功。 select * from performance_schema.replication_connection_status\G，查看是否有报错; 例如：select * from performance_schema.replication_connection_status\G; *************************** 1. row *************************** CHANNEL_NAME: group_replication_recovery GROUP_NAME: SOURCE_UUID: THREAD_ID: NULL SERVICE_STATE: OFF COUNT_RECEIVED_HEARTBEATS: 0 LAST_HEARTBEAT_TIMESTAMP: 0000-00-00 00:00:00.000000 RECEIVED_TRANSACTION_SET: LAST_ERROR_NUMBER: 0 LAST_ERROR_MESSAGE: LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000 LAST_QUEUED_TRANSACTION: LAST_QUEUED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000 LAST_QUEUED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000 LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP: 0000-00-00 00:00:00.000000 LAST_QUEUED_TRANSACTION_END_QUEUE_TIMESTAMP: 0000-00-00 00:00:00.000000 QUEUEING_TRANSACTION: QUEUEING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000 QUEUEING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000 QUEUEING_TRANSACTION_START_QUEUE_TIMESTAMP: 0000-00-00 00:00:00.000000 *************************** 2. row *************************** CHANNEL_NAME: group_replication_applier GROUP_NAME: 8ef01324-97ce-11e8-b4cc-000c295ce53e SOURCE_UUID: 8ef01324-97ce-11e8-b4cc-000c295ce53e THREAD_ID: NULL SERVICE_STATE: ON COUNT_RECEIVED_HEARTBEATS: 0 LAST_HEARTBEAT_TIMESTAMP: 0000-00-00 00:00:00.000000 RECEIVED_TRANSACTION_SET: 8ef01324-97ce-11e8-b4cc-000c295ce53e:1-7:1000006:2000006 LAST_ERROR_NUMBER: 0 LAST_ERROR_MESSAGE: LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000 LAST_QUEUED_TRANSACTION: 8ef01324-97ce-11e8-b4cc-000c295ce53e:2000006 LAST_QUEUED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 2018-08-08 09:30:19.410114 LAST_QUEUED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000 LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP: 2018-08-08 09:30:19.412069 LAST_QUEUED_TRANSACTION_END_QUEUE_TIMESTAMP: 2018-08-08 09:30:19.412081 QUEUEING_TRANSACTION: QUEUEING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000 QUEUEING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000 QUEUEING_TRANSACTION_START_QUEUE_TIMESTAMP: 0000-00-00 00:00:00.000000 三台机器都是group_replication_applier的状态为ON，group_replication_recovery的状态为OFF。 5、注意：############################################################################# 如果把三台全都关闭了再重启多主MGR的时候，要再使用bootstrap; ############################################################################# 三、测试 任意库建库建表，其他库均可同步。 四、问题 感觉MGR多主似乎不太稳定，经常会出现某个库掉线的情况又不会自动拉起来;用循环脚本测试一旦出现较高的并发，就会掉节点，不论是否用代理测试都掉节点，好像不太实用，也可能是因为我配置的什么地方参数有问题，希望有高人给指点一下。 拉起来节点只需要依次执行stop group_replication和start group_replication即可。 转自：https://blog.csdn.net/zhangwenjiang001/article/details/81503576