发帖
翼度科技»论坛 › 编程开发 › mysql › 查看内容

高可用 proxysql + mysql MGR

青瓷文化

4

主题

4

帖子

12

积分

新手上路

Rank: 1

积分
12
显示全部楼层
MGR 配置

安装

系统：centos 7.9
  1. yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
  2. percona-release enable-only ps-8x-innovation release
  3. percona-release enable tools release
  4. yum install percona-server-server
复制代码
第一台机器配置
  1. # mgr
  2. # basic configure
  3. disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
  5. server_id=1
  6. gtid_mode=ON                     
  7. enforce_gtid_consistency=ON      
  8. log_bin=binlog
  9. binlog_format=ROW
  10. binlog_checksum=NONE
  11. log_replica_updates=ON
  14. ######################### MGR ##########################
  15. plugin_load_add='group_replication.so'
  16. loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
  17. loose-group_replication_start_on_boot=off
  18. loose-group_replication_local_address= "192.168.30.128:3406"
  19. loose-group_replication_group_seeds= "192.168.30.128:3406,192.168.30.139:3406,192.168.30.140:3406"
  20. loose-group_replication_bootstrap_group=off
  21. loose-group_replication_member_weight=50
  22. group_replication_single_primary_mode=ON
复制代码
操作
  1. -- 创建 分布式恢复的复制用户
  2. SET SQL_LOG_BIN=0;
  3. CREATE USER dba_repl@'%' IDENTIFIED BY 'MA6RuouuZZn4x_Hd';
  4. GRANT REPLICATION SLAVE ON *.* TO 'dba_repl'@'%';
  5. FLUSH PRIVILEGES;
  6. SET SQL_LOG_BIN=1;
  7. -- 创建复制用户后，必须提供 服务器的用户凭证，用于分布式 恢复。
  8. CHANGE REPLICATION SOURCE TO SOURCE_USER='dba_repl',
  9. SOURCE_PASSWORD='MA6RuouuZZn4x_Hd'
  10. FOR CHANNEL 'group_replication_recovery';
  12. -- 查看group_replication是否加载
  13. SHOW PLUGINS;
  15. SELECT PLUGIN_NAME,PLUGIN_STATUS,PLUGIN_TYPE,PLUGIN_LIBRARY,PLUGIN_LICENSE FROM information_schema.plugins WHERE PLUGIN_NAME='group_replication';
  18. -- 启动组复制
  19. SET GLOBAL group_replication_bootstrap_group=ON;
  20. START GROUP_REPLICATION;
  21. SET GLOBAL group_replication_bootstrap_group=OFF;
  24. -- 查看组复制状态
  25. SELECT * FROM performance_schema.replication_group_members;
复制代码
第二台机器配置
  1. # mgr
  2. # basic configure
  3. disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
  5. server_id=2
  6. gtid_mode=ON                     
  7. enforce_gtid_consistency=ON      
  8. log_bin=binlog
  9. binlog_format=ROW
  10. binlog_checksum=NONE
  11. log_replica_updates=ON
  14. ######################### MGR ##########################
  15. plugin_load_add='group_replication.so'
  16. loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
  17. loose-group_replication_start_on_boot=off
  18. loose-group_replication_local_address= "192.168.30.139:3406"
  19. loose-group_replication_group_seeds= "192.168.30.128:3406,192.168.30.139:3406,192.168.30.140:3406"
  20. loose-group_replication_bootstrap_group=off
  21. loose-group_replication_member_weight=40
  22. group_replication_single_primary_mode=ON
复制代码
操作
  1. -- 创建 分布式恢复的复制用户
  2. SET SQL_LOG_BIN=0;
  3. CREATE USER dba_repl@'%' IDENTIFIED BY 'MA6RuouuZZn4x_Hd';
  4. GRANT REPLICATION SLAVE ON *.* TO 'dba_repl'@'%';
  5. FLUSH PRIVILEGES;
  6. SET SQL_LOG_BIN=1;
  8. -- 创建复制用户后，必须提供 服务器的用户凭证，用于分布式 恢复。
  9. CHANGE REPLICATION SOURCE TO SOURCE_USER='dba_repl',
  10. SOURCE_PASSWORD='MA6RuouuZZn4x_Hd'
  11. FOR CHANNEL 'group_replication_recovery';
  14. -- 查看group_replication是否加载
  15. SHOW PLUGINS;
  17. SELECT PLUGIN_NAME,PLUGIN_STATUS,PLUGIN_TYPE,PLUGIN_LIBRARY,PLUGIN_LICENSE FROM information_schema.plugins WHERE PLUGIN_NAME='group_replication';
  19. -- 加入组复制
  20. START GROUP_REPLICATION;
复制代码
第三台机器配置
  1. # mgr
  2. # basic configure
  3. disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
  5. server_id=3
  6. gtid_mode=ON                     
  7. enforce_gtid_consistency=ON      
  8. log_bin=binlog
  9. binlog_format=ROW
  10. binlog_checksum=NONE
  11. log_replica_updates=ON
  14. ######################### MGR ##########################
  15. plugin_load_add='group_replication.so'
  16. loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
  17. loose-group_replication_start_on_boot=off
  18. loose-group_replication_local_address= "192.168.30.140:3406"
  19. loose-group_replication_group_seeds= "192.168.30.128:3406,192.168.30.139:3406,192.168.30.140:3406"
  20. loose-group_replication_bootstrap_group=off
  21. loose-group_replication_member_weight=40
  22. group_replication_single_primary_mode=ON
复制代码
操作
  1. -- 创建 分布式恢复的复制用户
  2. SET SQL_LOG_BIN=0;
  3. CREATE USER dba_repl@'%' IDENTIFIED BY 'MA6RuouuZZn4x_Hd';
  4. GRANT REPLICATION SLAVE ON *.* TO 'dba_repl'@'%';
  5. FLUSH PRIVILEGES;
  6. SET SQL_LOG_BIN=1;
  8. -- 创建复制用户后，必须提供 服务器的用户凭证，用于分布式 恢复。
  9. CHANGE REPLICATION SOURCE TO SOURCE_USER='dba_repl',
  10. SOURCE_PASSWORD='MA6RuouuZZn4x_Hd'
  11. FOR CHANNEL 'group_replication_recovery';
  14. -- 查看group_replication是否加载
  15. SHOW PLUGINS;
  17. SELECT PLUGIN_NAME,PLUGIN_STATUS,PLUGIN_TYPE,PLUGIN_LIBRARY,PLUGIN_LICENSE FROM information_schema.plugins WHERE PLUGIN_NAME='group_replication';
  19. -- 加入组复制
  20. START GROUP_REPLICATION;
复制代码
  1. # 查看成员状态
  3. mysql> select * from performance_schema.replication_group_members;
  4. +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
  5. | CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
  6. +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
  7. | group_replication_applier | bba9be3c-6b5d-11ef-ad42-000c2915875d | mysql_2     |        3306 | ONLINE       | PRIMARY     | 8.3.0          | XCom                       |
  8. | group_replication_applier | d270a98c-6b2e-11ef-bc60-000c29e07cfa | mysql_1     |        3306 | ONLINE       | SECONDARY   | 8.3.0          | XCom                       |
  9. | group_replication_applier | ef278bf7-6b5d-11ef-9936-000c2939881a | mysql_3     |        3306 | ONLINE       | SECONDARY   | 8.3.0          | XCom                       |
  10. +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
  11. 3 rows in set (0.00 sec)
复制代码
输出结果中主要几个列的解读如下：

  • MEMBER_ID 列值就是各节点的 server_uuid，用于唯一标识每个节点，在命令行模式下，调用 udf 时传入 MEMBER_ID 以指定各节点。
  • MEMBER_ROLE 表示各节点的角色，如果是 PRIMARY 则表示该节点可接受读写事务，如果是 SECONDARY 则表示该节点只能接受只读事务。如果只有一个节点是 PRIMARY，其余都是 SECONDARY，则表示当前处于 单主模式；如果所有节点都是 PRIMARY，则表示当前处于 多主模式。
  • MEMBER_STATE 表示各节点的状态，共有几种状态：ONLINE、RECOVERING、OFFLINE、ERROR、UNREACHABLE 等，下面分别介绍几种状态。
  • ONLINE ，表示节点处于正常状态，可提供服务。
  • RECOVERING ，表示节点正在进行分布式恢复，等待加入集群，这时候有可能正在从donor节点利用clone复制数据，或者传输binlog中。
  • OFFLINE ，表示该节点当前处于离线状态。提醒，在正要加入或重加入集群时，可能也会有很短瞬间的状态显示为 OFFLINE。
  • ERROR ，表示该节点当前处于错误状态，无法成为集群的一员。当节点正在进行分布式恢复或应用事务时，也是有可能处于这个状态的。当节点处于ERROR状态时，是无法参与集群事务裁决的。节点正在加入或重加入集群时，在完成兼容性检查成为正式MGR节点前，可能也会显示为ERROR状态。
  • UNREACHABLE ，当组通信消息收发超时时，故障检测机制会将本节点标记为怀疑状态，怀疑其可能无法和其他节点连接，例如当某个节点意外断开连接时。当在某个节点上看到其他节点处于 UNREACHABLE 状态时，有可能意味着此时部分节点发生了网络分区，也就是多个节点分裂成两个或多个子集，子集内的节点可以互通，但子集间无法互通。
  • 当节点的状态不是 ONLINE 时，就应当立即发出告警并检查发生了什么。
在节点状态发生变化时，或者有节点加入、退出时，表 performance_schema.replication_group_members 的数据都会更新，各节点间会交换和共享这些状态信息，因此可以在任意节点查看。
proxysql

proxysql 安装
  1. yum install proxysql
  2. systemctl start proxysql
  3. systemctl enable proxysql
复制代码
官方文档
proxysql 配置

添加组配置
  1. INSERT INTO mysql_group_replication_hostgroups (
  2.     writer_hostgroup,
  3.     backup_writer_hostgroup,
  4.     reader_hostgroup,
  5.     offline_hostgroup,
  6.     active,
  7.     max_writers,
  8.     writer_is_also_reader,
  9.     max_transactions_behind
  10. )
  11. VALUES (
  12.     2,  -- writer_hostgroup (写操作主机组)
  13.     4,  -- backup_writer_hostgroup (备份写操作主机组)
  14.     3,  -- reader_hostgroup (读操作主机组)
  15.     1,  -- offline_hostgroup (离线主机组)
  16.     1,  -- active (激活状态)
  17.     1,  -- max_writers (最大写操作主机数)
  18.     1,  -- writer_is_also_reader (写主机是否也充当读操作主机)
  19.     1000 -- max_transactions_behind (最大滞后事务数)
  20. );
  21. -- 加载到RUNTIME,保存到disk
  22. LOAD MYSQL VARIABLES TO RUNTIME;
  23. SAVE MYSQL VARIABLES TO DISK;
复制代码
配置监控

要在 MySQL 中创建用户，请连接到 PRIMARY 并执行
  1. CREATE USER 'monitor'@'%' IDENTIFIED BY 'Monitor@123456';
  2. GRANT USAGE, REPLICATION CLIENT ON *.* TO 'monitor'@'%';
复制代码
在proxysql 中执行
  1. set mysql-monitor_username='monitor';
  2. set mysql-monitor_password='Monitor@123456';
  3. UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';
  4. UPDATE global_variables SET variable_value='Monitor@123456' WHERE variable_name='mysql-monitor_password';
  6. --- 配置各种监控间隔
  7. -- UPDATE global_variables SET variable_value='2000' WHERE variable_name IN ('mysql-monitor_connect_interval','mysql-monitor_ping_interval','mysql-monitor_read_only_interval');
  9. SELECT * FROM global_variables WHERE variable_name LIKE 'mysql-monitor_%';
  12. -- 加载到RUNTIME,保存到disk
  13. LOAD MYSQL VARIABLES TO RUNTIME;
  14. SAVE MYSQL VARIABLES TO DISK;
复制代码
添加后端
  1. INSERT INTO mysql_servers (
  2.     hostgroup_id,
  3.     hostname,
  4.     port,
  5.     weight
  6. ) VALUES
  7. (2, '192.168.30.128', 3306, 100),
  8. (2, '192.168.30.139', 3306, 100),
  9. (3, '192.168.30.140', 3306, 100);
  12. LOAD MYSQL VARIABLES TO RUNTIME;
  13. SAVE MYSQL VARIABLES TO DISK;
  15. -- 查看主机
  16. select * from runtime_mysql_servers;
复制代码
添加用户
  1. insert into mysql_users
  2. (username,password,default_hostgroup,default_schema)
  3. values
  4. ('test','Zzj@123456',2,'information_schema');
复制代码
配置读写分离
  1. insert into mysql_query_rules(rule_id,username,active,match_digest,destination_hostgroup,apply)values(3,'test',1,'^SELECT.*FOR UPDATE$',2,1);
  2. insert into mysql_query_rules(rule_id,username,active,match_digest,destination_hostgroup,apply)values(4,'test',1,'^SELECT',3,1);
  3. --查看规则
  4. select rule_id,username,active,match_digest,destination_hostgroup,apply from mysql_query_rules;
复制代码
验证负载均衡
  1. for i in {1..112}; do  mysql -utest -pZzj@123456 -h192.168.30.128 -P6033 -e 'select @@hostname' -s -N; done
复制代码
ProxySQL其它的参数上的调整
  1. mysql-max_stmts_per_connection 从 20 调整为100
  2. -- 【放弃，影响不大】 mysql-threads 从 4 调整到 8
  4. update global_variables set variable_value='100' where variable_name ='mysql-max_stmts_per_connection';
  5. -- update global_variables set variable_value='8' where variable_name ='mysql-threads';
  7. -- 从原先的64MB改到128MB
  8. update global_variables set variable_value='134217728' where variable_name ='mysql-max_allowed_packet';
  10. update global_variables set variable_value='2000' where variable_name in('mysql-monitor_connect_interval','mysql-monitor_ping_interval','mysql-monitor_read_only_interval');
  12. -- 改下隔离级别，必须步骤！否则会出现RR隔离级别查到脏数据 【测试环境复现出来的问题】
  13. update global_variables set variable_value='REPEATABLE-READ' where variable_name ='mysql-default_tx_isolation';
  16. -- 更改下默认的字符集和字符排序集
  17. -- mysql-default_charset 默认是 utf8
  18. -- mysql-default_collation_connection 默认是 utf8_general_ci
  19. update global_variables set variable_value='utf8mb4' where variable_name ='mysql-default_charset';
  20. update global_variables set variable_value='utf8mb4_0900_ai_ci' where variable_name ='mysql-default_collation_connection';
  23. LOAD MYSQL VARIABLES TO RUNTIME;
  24. SAVE MYSQL VARIABLES TO DISK;
复制代码
报错处理
  1. -- onnection is locked to hostgroup 2 but trying to reach hostgroup 3
  2. -- https://stackoverflow.com/questions/72722871/9006-proxysql-error-connection-is-locked-to-hostgroup-xx-but-trying-to-reach-ho
  4. set mysql-set_query_lock_on_hostgroup=0;
  5. load mysql variables to runtime;
  6. save mysql variables to disk;
复制代码
来源:https://www.cnblogs.com/zuozhengjun/p/18470411
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！

上一篇: MySQL 通过 Next-Key Locking 技术（行锁+间隙锁）避免幻读问题

下一篇: 码城|计算机专业的00后转行数据分析，还有机会吗？【悟空非空也】

发表于 2024-10-17 05:31:04

举报 回复 使用道具

返回列表 发新帖

Archiver|手机版|小黑屋|翼度科技

Copyright © 2001-2020, Tencent Cloud.　Powered by Discuz! X3.4