搭建MGR+ProxySQL集群

系统配置

• 设置好ip地址、网关
• 修改hosts文件
• 修改主机名称
• MySQL节点防火墙放行 3306 和 24901，ProxySQL放行6032和6033端口

MySQL配置

master节点

sudo cat >> /etc/my.cnf <<-‘EOF’

• 设置插件路径

  plugin_dir=/usr/lib64/mysql/plugin

• 服务器编号

  server_id=1

• 打开binlog的 GTID 模式

  gtid_mode=ON

  enforce_gitd_consistency=ON

• 关闭binlog校验

  binlog_checksum=NONE

• 定义事务期间哈希写入提取的算法，组复制模式下必须为 XXHASH64

  transaction_write_set_extraction=XXHASH64

• 服务器所在复制组的名称，有效的 UUID，所有节点相同

  loose-group-replication_group_name="7093d4fa-4425-4c2f-849b-841b62f40a01"

• 确定服务器是否应该在服务器启动期间启动组复制

  loose-group_replication_start_on_boot=OFF

• 为复制组其他成员提供的网络地址，指定为”主机:端口”的格式化字符串，此处端口为24901

  loose-group_replication_local_address="n0:24901"

• 建立新组成员到组的连接组成员列表

  loose-group_replication_group_seeds="n0:24901,n1:24901,n2:24901"

• 配置此服务器为引导组，此选项必须仅在一台服务器上设置，仅第一次或启动组时打开。成功引导组启动后，置为关闭

  loose-group_replication_bootstrap_group=OFF

• 启动MySQL服务器

​ systemctl start mysqld

slave节点

• 设置插件路径

  plugin_dir=/usr/lib64/mysql/plugin

• 服务器编号

  server_id=2

• 打开binlog的 GTID 模式

  gtid_mode=ON

  enforce_gitd_consistency=ON

• 关闭binlog校验

  binlog_checksum=NONE

• 此参数决定master节点到slave节点的请求是否基于RSA密钥对的密码交换所需的公钥

  loose-group_replication_recovery_get_public_key=ON

• 服务器所在复制组的名称，有效的 UUID，所有节点相同

  loose-group-replication_group_name="7093d4fa-4425-4c2f-849b-841b62f40a01"

• 确定服务器是否应该在服务器启动期间启动组复制

  loose-group_replication_start_on_boot=OFF

• 为复制组其他成员提供的网络地址，指定为”主机:端口”的格式化字符串，此处端口为24901

  loose-group_replication_local_address="n1:24901"

• 建立新组成员到组的连接组成员列表

  loose-group_replication_group_seeds="n0:24901,n1:24901,n2:24901"

• 配置此服务器为引导组，此选项必须仅在一台服务器上设置，仅第一次或启动组时打开。成功引导组启动后，置为关闭

  loose-group_replication_bootstrap_group=OFF

初始化MySQL

• 连接后修改初始密码

  ALTER USER 'root'@'localhost'  IDENTIFIED WITH mysql_native_password BY 'asAS123456!';

• 创建rpl_user账户，此账户用于实现主从数据同步

  CREATE USER rpl_user@'%' IDENTIFIED BY 'asAS123456!'; 

• 赋予主从同步权限

  GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';

• 创建一个远程连接用户，用于Navcate登录的时候使用，避免直接使用root登录

  create user 'remote'@'%' identified with mysql_native_password by 'asAS123456!'; 

• 为remote用户赋予所有数据库资源的访问权限

  grant all privileges on *.* to remote@'%';

• 让刚才的修改生效

  FLUSH PRIVILEGES; 

• 删除已产生的Binlog，一定要RESET MASTER，它会删除刚才已产生的Binlog，因为刚才Binglog包含创建用户这种高权限操作，用于主从同步的rpl_user账户是没有权限执行的，这就会导致RelayLog重放无法正确执行，导致从属服务器卡死在”RECEVERING”状态

  RESET MASTER; 

安装MGR插件

• 每一台节点，在MySQL提示符状态下，使用INSTALL PLUGIN安装MGR插件

  INSTALL PLUGIN group_replication SONAME 'group_replication.so'; 

进行组网数据同步

Master节点

• 在 primary.cnf 配置文件中把group_replication_bootstrap_group 参数设置成 OFF，在 primary 服务器启动时并不会直接启动复制组，通过下面的命令动态的开启复制组是我们的集群更安全。

  SET GLOBAL group_replication_bootstrap_group=ON; 
  START GROUP_REPLICATION; 
  SET GLOBAL group_replication_bootstrap_group=OFF; 

• 运行成功，可以在linux命令提示符下

  tail -f /var/log/mysqld.log 

• 看到下面的数据代表Master启动成功

  2022-03-04T03:15:12.930686Z 34 [System] [MY-011510] [Repl]  
  Plugin group_replication reported: 'This server is working as primary member.' 

Slave节点

• 指定主从账户与指定通信频道

  CHANGE MASTER TO MASTER_USER="rpl_user", MASTER_PASSWORD="asAS123456!" FOR CHANNEL 'group_replication_recovery'; 

• 开启组网数据同步

  START GROUP_REPLICATION; 

• 当两个从节点都运行完毕后，运行下面结果进行验证。如果看到一主两从都是“ONLINE”状态，代表组网成功

  SELECT * FROM performance_schema.replication_group_members; 

ProxySQL配置

• 连接到ProxySQL Admin，它是一个伪装为MySQL的ProxySQL服务端

  mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '

• 加入后端节点，从192.168.31.230~232

  insert into mysql_servers(hostgroup_id,hostname,port) values (10,'192.168.31.230',3306);
  insert into mysql_servers(hostgroup_id,hostname,port) values (10,'192.168.31.231',3306);
  insert into mysql_servers(hostgroup_id,hostname,port) values (10,'192.168.31.232',3306); 
  
  load mysql servers to runtime;
  save mysql servers to disk; 

• 在n0节点 192.168.31.230执行创建监控命令，该账户会通过主节点同步到其他从属节点

  set global validate_password.policy=0; 
  set global validate_password.length=4; 
  
  CREATE USER 'monitor'@'%' IDENTIFIED BY "monitor@1025";
  CREATE USER 'proxysql'@'%' IDENTIFIED BY "proxysql@1025"; 
  
  GRANT ALL PRIVILEGES ON *.* TO 'monitor'@'%' ;
  GRANT ALL PRIVILEGES ON *.* TO 'proxysql'@'%' ; 
  
  FLUSH PRIVILEGES; 

• 在n0主节点上执行下面语句来创建gr_member_routing_candidate_status视图，这是主从故障切换关键所在

  use sys;
  CREATE FUNCTION my_id() RETURNS TEXT(36) DETERMINISTIC NO SQL RETURN (SELECT @@global.server_uuid as my_id);
  
  CREATE FUNCTION gr_member_in_primary_partition() 
  	RETURNS VARCHAR(3) 
  	DETERMINISTIC 
  	BEGIN 
     		RETURN 
     			(SELECT IF( MEMBER_STATE='ONLINE' AND ((SELECT COUNT(*) FROM performance_schema.replication_group_members 				WHERE MEMBER_STATE NOT IN ('ONLINE', 'RECOVERING')) >=  ((SELECT COUNT(*) 
     			FROM performance_schema.replication_group_members)/2) = 0), 'YES', 'NO' )
              FROM performance_schema.replication_group_members
              JOIN performance_schema.replication_group_member_stats USING(member_id) where member_id=my_id()); 
  END
  
  CREATE VIEW gr_member_routing_candidate_status AS
  	SELECT sys.gr_member_in_primary_partition() as viable_candidate, 
  		IF( (SELECT (SELECT GROUP_CONCAT(variable_value)
  		FROM performance_schema.global_variables
  		WHERE variable_name IN ('read_only', 'super_read_only')) != 'OFF,OFF'), 'YES', 'NO') as read_only, 
  		Count_Transactions_Remote_In_Applier_Queue as transactions_behind, Count_Transactions_in_queue as 							'transactions_to_cert' from performance_schema.replication_group_member_stats where member_id=my_id(); 

• 在ProxySQL中设置监控账号与程序账号

  set mysql-monitor_username='monitor';
  set mysql-monitor_password='monitor@1025';
  insert into mysql_users(username,password,active,default_hostgroup,transaction_persistent) values('proxysql','proxysql@1025',1,10,1);

• 设置读写组，主负责写、从负责读，当MGR主库切换后，代理自动识别主从。ProxySQL代理每一个后端MGR集群时，都必须为这个MGR定义写组10、备写组20、读组30、离线组40。

  insert into mysql_group_replication_hostgroups (writer_hostgroup,backup_writer_hostgroup,reader_hostgroup, offline_hostgroup,active,max_writers,writer_is_also_reader,max_transactions_behind) values (10,20,30,40,1,1,0,100);

• 启用规则

  load mysql servers to runtime; 
  save mysql servers to disk; 
  load mysql users to runtime; 
  save mysql users to disk; 
  load mysql variables to runtime; 
  save mysql variables to disk; 

• 状态校验

  select hostgroup_id, hostname, port,status from runtime_mysql_servers; 

• 配置读写分离规则

  insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)
  VALUES (1,1,'^SELECT.*FOR UPDATE$',10,1),(2,1,'^SELECT',30,1);
  
  load mysql query rules to runtime; 
  save mysql query rules to disk;