mysql5.7.26搭建MGR单主及多主模式

使用mysql5.7.26搭建MGR单主及多主模式（参考链接https://www.jianshu.com/p/ca1af156f656）

一、搭建单主
  1.1前言：
		MySQL Group Replication（MGR）是MySQL官方在5.7.17版本引进的一个数据库高可用与高扩展的解决方案，以插件形式提供，实现了分布式下数据的最终一致性，总结MGR特点如下：
		高一致性：基于分布式paxos协议实现组复制，保证数据一致性；
		高容错性：自动检测机制，只要不是大多数节点都宕机就可以继续工作，内置防脑裂保护机制；
		高扩展性：节点的增加与移除会自动更新组成员信息，新节点加入后，自动从其他节点同步增量数据，直到与其他节点数据一致；
		高灵活性：提供单主模式和多主模式，单主模式在主库宕机后能够自动选主，所有写入都在主节点进行，多主模式支持多节点写入。

  1.2网络拓扑
	192.168.56.141 centos7.node2  ##从
	192.168.56.149 docker.node01  ##主
	192.168.56.150 centos7.node1  ##从
	
  1.3安装mysql
  1.3.1 从官网下载mysql安装包
  1.3.2 安装过程(每台服务器都做一遍)
    配置集群中全部节点的免密登录(以其中一个节点为例，其它的节点类似操作即可)：
	[root@docker .ssh]# ssh-keygen -t rsa 
	[root@docker .ssh]# ssh-copy-id -i ./id_rsa.pub root@192.168.56.150
	[root@docker .ssh]# ssh-copy-id -i ./id_rsa.pub root@192.168.56.141
	测试(所有节点操作)
	[root@docker .ssh]# ssh root@192.168.56.150
	[root@docker .ssh]# ssh root@192.168.56.141
	
	安装mysql5.7(所有节点操作)
	[root@docker local]# tar -zxvf mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
	[root@docker local]# cd /usr/local/
	[root@docker local]# ln -s mysql-5.7.26-linux-glibc2.12-x86_64/ mysql
	[root@docker local]# groupadd mysql
	[root@docker local]# useradd -r -g mysql mysql ##创建系统用户，并设置组
	[root@docker local]# chown -R mysql . ## 设置软件产品目录的属主
	[root@docker local]# chgrp -R mysql .
    [root@docker local]# vim /etc/profile.d/mysql.sh ##添加环境变量，添加如下内容
		export MYSQL_HOME=/usr/local/mysql
		export PATH=$MYSQL_HOME/bin:$PATH
	[root@docker local]# source /etc/profile
    [root@docker mysql]# cp support-files/mysql.server /etc/init.d/mysqld ##添加启动uni files/mysql
	[root@centos7 mysql_data]# systemctl stop firewalld.service  ##关闭防火墙
	[root@centos7 mysql_data]# systemctl disable firewalld.service 
	[root@centos7 mysql_data]# vim /etc/hosts
		添加如下内容：
		192.168.56.141 centos7.node2
		192.168.56.149 docker.node01
		192.168.56.150 centos7.node1
	[root@centos7 mysql_data]# vim /etc/my.cnf ###添加内容,下面的配置每台机器注意修改server-id、report_host、bind_address、group_replication_local_address都不一样，其它都一样
	
		[client]
		user=root
		password=wiki123

		[mysqld]
		########basic settings########
		server-id = 11
		port = 3306
		user = mysql
		bind_address = 192.168.56.149
		report_host=192.168.56.149
		report_port=3306
		autocommit = 0
		character_set_server=utf8mb4
		skip_name_resolve = 1
		max_connections = 800
		max_connect_errors = 1000
		datadir = /data/mysql_data
		transaction_isolation = READ-COMMITTED
		explicit_defaults_for_timestamp = 1
		join_buffer_size = 134217728
		tmp_table_size = 67108864
		tmpdir = /tmp
		max_allowed_packet = 16777216
		sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER"
		interactive_timeout = 1800
		wait_timeout = 1800
		read_buffer_size = 16777216
		read_rnd_buffer_size = 33554432
		sort_buffer_size = 33554432
		########log settings########
		log_error = error.log
		slow_query_log = 1
		slow_query_log_file = slow.log
		log_queries_not_using_indexes = 1
		log_slow_admin_statements = 1
		log_slow_slave_statements = 1
		log_throttle_queries_not_using_indexes = 10
		expire_logs_days = 90
		long_query_time = 2
		min_examined_row_limit = 100
		########replication settings########
		master_info_repository = TABLE
		relay_log_info_repository = TABLE
		log_bin = bin.log
		sync_binlog = 1
		gtid_mode = on
		enforce_gtid_consistency = 1
		log_slave_updates
		binlog_format = row
		binlog_checksum=NONE
		relay_log = relay.log
		relay_log_recovery = 1
		binlog_gtid_simple_recovery = 1
		slave_skip_errors = ddl_exist_errors
		########innodb settings########
		innodb_page_size = 8192
		innodb_buffer_pool_size = 2G
		innodb_buffer_pool_instances = 8
		innodb_buffer_pool_load_at_startup = 1
		innodb_buffer_pool_dump_at_shutdown = 1
		innodb_lru_scan_depth = 2000
		innodb_lock_wait_timeout = 5
		innodb_io_capacity = 4000
		innodb_io_capacity_max = 8000
		innodb_flush_method = O_DIRECT
		innodb_file_format = Barracuda
		innodb_file_format_max = Barracuda
		innodb_log_group_home_dir = /redolog/
		innodb_undo_directory = /undolog/
		innodb_undo_logs = 128
		innodb_undo_tablespaces = 3
		innodb_flush_neighbors = 1
		innodb_log_file_size = 1G
		innodb_log_buffer_size = 524288  ##512M
		innodb_purge_threads = 4
		innodb_large_prefix = 1
		innodb_thread_concurrency = 64
		innodb_print_all_deadlocks = 1
		innodb_strict_mode = 1
		innodb_sort_buffer_size = 67108864
		########semi sync replication settings########
		plugin_dir=/usr/local/mysql/lib/plugin
		plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so;group_replication=group_replication.so"###group_replication.so是mgr插件
		loose_rpl_semi_sync_master_enabled = 1
		loose_rpl_semi_sync_slave_enabled = 1
		loose_rpl_semi_sync_master_timeout = 5000
		####MGR SETTING#######
		loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
		loose-group_replication_start_on_boot=OFF
		loose-group_replication_local_address= "docker.node01:33061"
		loose-group_replication_group_seeds= "docker.node01:33061,centos7.node1:33061,centos7.node2:33061"
		loose-group_replication_bootstrap_group=OFF


		[mysqld-5.7]
		innodb_buffer_pool_dump_pct = 40
		innodb_page_cleaners = 4
		innodb_undo_log_truncate = 1
		innodb_max_undo_log_size = 524288 ##512M
		innodb_purge_rseg_truncate_frequency = 128
		binlog_gtid_simple_recovery=1
		log_timestamps=system
		transaction_write_set_extraction=XXHASH64
		show_compatibility_56=on
		
		创建目录(所有节点操作)：
		      创建my.cnf配置文件中的全部目录，并授权，下面以datadir = /data/mysql_data为例
			  [root@docker local]# mkdir -pv /data/mysql_data
			  [root@docker local]# chown -R mysql:mysql /data
			  [root@docker local]# chmod -R 770 /data
			  
	#######################################到这里，安装的准备工作已经完成，下面开始初始化数据库
	    初始化数据库(所有节点操作)
			[root@docker local]# mysqld --initialize --user=mysql
			[root@docker local]# mysqld_safe --user=mysql & ##启动数据库
			
	#######################################到这里，所有节点的数据都已经出事化完毕
        1.3.4 设置复制账号（所有节点操作）
			mysql> SET SQL_LOG_BIN=0;
			mysql> CREATE USER repl@'%' IDENTIFIED BY 'repl';
			mysql> GRANT REPLICATION SLAVE ON *.* TO repl@'%';
			mysql> FLUSH PRIVILEGES;
			mysql> SET SQL_LOG_BIN=1;
			mysql> CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='repl' FOR CHANNEL 'group_replication_recovery';

		1.3.5 启动MGR单主模式
		# 启动MGR，在主库(192.168.56.149)上执行
			mysql> SET GLOBAL group_replication_bootstrap_group=ON;
			mysql> START GROUP_REPLICATION;
			mysql> SET GLOBAL group_replication_bootstrap_group=OFF;

			# 查看MGR组信息
			mysql> SELECT * FROM performance_schema.replication_group_members;
					+---------------------------+--------------------------------------+----------------+-------------+--------------+
					| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST    | MEMBER_PORT | MEMBER_STATE |
					+---------------------------+--------------------------------------+----------------+-------------+--------------+
					| group_replication_applier | 16855150-f223-11e9-9b22-000c294aef44 | 192.168.56.149 |        3306 | ONLINE       |
					+---------------------------+--------------------------------------+----------------+-------------+--------------+
			# 其他节点加入MGR，在从库（192.168.56.150，192.168.56.141）上执行
			mysql> START GROUP_REPLICATION; ##启动时报错
				ERROR 3092 (HY000): The server is not configured properly to be an active member of the group. Please see more details on error log.
				出现这个现象的原因：
					MySQL是新装的没问题，但是每次新装MySQL都要修改密码，如果在修改密码的时候就已经把binlog_format=on配在了/etc/my.cnf中，
					那么修改密码的记录是存在在binlog日志中的。所以就会提示前文中的日志错误。
			    解决方案(每一个从节点执行)：
				mysql> RESET MASTER
				mysql> START GROUP_REPLICATION; ##从新启动，问题解决(错误解决参考链接：https://blog.csdn.net/snowhite91/article/details/83791997)
					
			# 查看MGR组信息(在主库(192.168.56.149)上执行)
			mysql> SELECT * FROM performance_schema.replication_group_members;
				+---------------------------+--------------------------------------+----------------+-------------+--------------+
				| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST    | MEMBER_PORT | MEMBER_STATE |
				+---------------------------+--------------------------------------+----------------+-------------+--------------+
				| group_replication_applier | 16855150-f223-11e9-9b22-000c294aef44 | 192.168.56.149 |        3306 | ONLINE       |
				| group_replication_applier | 2d1b48ec-dbd1-11e9-9f76-000c2995d20d | 192.168.56.141 |        3306 | ONLINE       |
				| group_replication_applier | 6bd815d0-f22b-11e9-b02f-000c29b9031b | 192.168.56.150 |        3306 | ONLINE       |
				+---------------------------+--------------------------------------+----------------+-------------+--------------+
				
				mysql> show variables like '%read_only%';##192,168.56.149自动为读写
						+-----------------------+-------+
						| Variable_name         | Value |
						+-----------------------+-------+
						| read_only             | OFF   |
						| super_read_only       | OFF   |
	
				    其它库，默认启动就为只读
				mysql> show variables like '%read_only%';
					+-----------------------+-------+
					| Variable_name         | Value |
					+-----------------------+-------+
					| read_only             | ON    |
					| super_read_only       | ON    |
  
			可以看到，3个节点状态为online，并且主节点为192.168.56.149，只有主节点可以写入，其他节点只读，MGR单主模式搭建成功
			
		1.3.6 测试插入数据
		mysql> create table t1(a int);
		mysql> insert into t1 values(1),(2),(3);
		ERROR 3098 (HY000): The table does not comply with the requirements by an external plugin.
		错误的原因是在MGR下，表必须要有主键
			mysql> create table t1(a int primary key);
			mysql> insert into t1 values(1),(2),(3);
			Query OK, 3 rows affected (0.00 sec)
			
二、将单主模式切换为多主模式
    2.1 要求
	MGR切换模式需要重新启动组复制，因些需要在所有节点上先关闭组复制，设置 group_replication_single_primary_mode=OFF 等参数，再启动组复制。
	2.2 切换步骤
		# 停止组复制(所有节点执行)：
		mysql> stop group_replication;
		mysql> set global group_replication_single_primary_mode=OFF;
		mysql> set global group_replication_enforce_update_everywhere_checks=ON;

		# 随便选择某个节点执行
		mysql> SET GLOBAL group_replication_bootstrap_group=ON; 
		mysql> START GROUP_REPLICATION; 
		mysql> SET GLOBAL group_replication_bootstrap_group=OFF;

		# 其他节点执行
		mysql> START GROUP_REPLICATION; 

		# 查看组信息，所有节点的 MEMBER_ROLE 都为 PRIMARY
		mysql> SELECT * FROM performance_schema.replication_group_members;
		+---------------------------+--------------------------------------+----------------+-------------+--------------+
		| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST    | MEMBER_PORT | MEMBER_STATE |
		+---------------------------+--------------------------------------+----------------+-------------+--------------+
		| group_replication_applier | 16855150-f223-11e9-9b22-000c294aef44 | 192.168.56.149 |        3306 | ONLINE       |
		| group_replication_applier | 2d1b48ec-dbd1-11e9-9f76-000c2995d20d | 192.168.56.141 |        3306 | ONLINE       |
		| group_replication_applier | 6bd815d0-f22b-11e9-b02f-000c29b9031b | 192.168.56.150 |        3306 | ONLINE       |
		+---------------------------+--------------------------------------+----------------+-------------+--------------+
		可以看到所有节点状态都是online，都可以读写，角色都是PRIMARY，MGR多主模式搭建成功。
		
三、从多主切换为单主
		# 所有节点执行
		mysql> stop group_replication;
		mysql> set global group_replication_enforce_update_everywhere_checks=OFF;
		mysql> set global group_replication_single_primary_mode=ON;

		# 主节点（192.168.56.149）执行
		SET GLOBAL group_replication_bootstrap_group=ON; 
		START GROUP_REPLICATION; 
		SET GLOBAL group_replication_bootstrap_group=OFF;


		# 从节点（192.168.56.150、192.168.56.149）执行
		START GROUP_REPLICATION; 

		# 查看MGR组信息
		mysql> SELECT * FROM performance_schema.replication_group_members;