取消
高可用 proxysql + mysql MGR

MGR 配置

安装

系统：C++entos 7.9

yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
percona-release enable-only ps-8x-innovation release
percona-release enable tools release
yum install percona-server-server

第一台机器配置

# mgr 
# basic configure
disabled_storage_engines="MyISAM，BLACKHOLE，FEDERATED，ARCHIVE，MEMORY"

server_id=1
gtid_mode=ON                      
enforce_gtid_consistency=ON      
log_bin=binlog
binlog_format=ROW
binlog_checksum=NONE
log_replica_updates=ON


######################### MGR ##########################
plugin_load_add='group_replication.so'
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "192.168.30.128:3406"
loose-group_replication_group_seeds= "192.168.30.128:3406，192.168.30.139:3406，192.168.30.140:3406"
loose-group_replication_Bootstrap_group=off
loose-group_replication_member_weight=50
group_replication_single_primary_mode=ON

操作

-- 创建 分布式恢复的复制用户
SET SQL_LOG_BIN=0;
CREATE USER dba_repl@'%' IDENTIFIED BY 'MA6RuouuZZn4x_Hd';
GRANT REPLICATION SLAVE ON *.* TO 'dba_repl'@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;
-- 创建复制用户后，必须提供 服务器的用户凭证，用于分布式 恢复。
CHANGE REPLICATION SOURCE TO SOURCE_USER='dba_repl'， 
SOURCE_PASSWORD='MA6RuouuZZn4x_Hd'
FOR CHANNEL 'group_replication_recovery';

-- 查看group_replication是否加载
SHOW PLUGINS;

SELECT PLUGIN_NAME，PLUGIN_STATUS，PLUGIN_TYPE，PLUGIN_LIBRARY，PLUGIN_LICENSE FROM information_schema.plugins WHERE PLUGIN_NAME='group_replication';


-- 启动组复制
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;


-- 查看组复制状态
SELECT * FROM performance_schema.replication_group_members;

第二台机器配置

# mgr 
# basic configure
disabled_storage_engines="MyISAM，BLACKHOLE，FEDERATED，ARCHIVE，MEMORY"

server_id=2
gtid_mode=ON                      
enforce_gtid_consistency=ON      
log_bin=binlog
binlog_format=ROW
binlog_checksum=NONE
log_replica_updates=ON


######################### MGR ##########################
plugin_load_add='group_replication.so'
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "192.168.30.139:3406"
loose-group_replication_group_seeds= "192.168.30.128:3406，192.168.30.139:3406，192.168.30.140:3406"
loose-group_replication_bootstrap_group=off
loose-group_replication_member_weight=40
group_replication_single_primary_mode=ON

操作

-- 创建 分布式恢复的复制用户
SET SQL_LOG_BIN=0;
CREATE USER dba_repl@'%' IDENTIFIED BY 'MA6RuouuZZn4x_Hd';
GRANT REPLICATION SLAVE ON *.* TO 'dba_repl'@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;

-- 创建复制用户后，必须提供 服务器的用户凭证，用于分布式 恢复。
CHANGE REPLICATION SOURCE TO SOURCE_USER='dba_repl'， 
SOURCE_PASSWORD='MA6RuouuZZn4x_Hd'
FOR CHANNEL 'group_replication_recovery';


-- 查看group_replication是否加载
SHOW PLUGINS;

SELECT PLUGIN_NAME，PLUGIN_STATUS，PLUGIN_TYPE，PLUGIN_LIBRARY，PLUGIN_LICENSE FROM information_schema.plugins WHERE PLUGIN_NAME='group_replication';

-- 加入组复制
START GROUP_REPLICATION;

第三台机器配置

# mgr 
# basic configure
disabled_storage_engines="MyISAM，BLACKHOLE，FEDERATED，ARCHIVE，MEMORY"

server_id=3
gtid_mode=ON                      
enforce_gtid_consistency=ON      
log_bin=binlog
binlog_format=ROW
binlog_checksum=NONE
log_replica_updates=ON


######################### MGR ##########################
plugin_load_add='group_replication.so'
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "192.168.30.140:3406"
loose-group_replication_group_seeds= "192.168.30.128:3406，192.168.30.139:3406，192.168.30.140:3406"
loose-group_replication_bootstrap_group=off
loose-group_replication_member_weight=40
group_replication_single_primary_mode=ON

操作

-- 创建 分布式恢复的复制用户
SET SQL_LOG_BIN=0;
CREATE USER dba_repl@'%' IDENTIFIED BY 'MA6RuouuZZn4x_Hd';
GRANT REPLICATION SLAVE ON *.* TO 'dba_repl'@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;

-- 创建复制用户后，必须提供 服务器的用户凭证，用于分布式 恢复。
CHANGE REPLICATION SOURCE TO SOURCE_USER='dba_repl'， 
SOURCE_PASSWORD='MA6RuouuZZn4x_Hd'
FOR CHANNEL 'group_replication_recovery';


-- 查看group_replication是否加载
SHOW PLUGINS;

SELECT PLUGIN_NAME，PLUGIN_STATUS，PLUGIN_TYPE，PLUGIN_LIBRARY，PLUGIN_LICENSE FROM information_schema.plugins WHERE PLUGIN_NAME='group_replication';

-- 加入组复制
START GROUP_REPLICATION;


# 查看成员状态

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 | bba9be3c-6b5d-11ef-ad42-000c2915875d | mysql_2     |        3306 | ONLINE       | PRIMARY     | 8.3.0          | XCom                       |
| group_replication_applier | d270a98c-6b2e-11ef-bc60-000c29e07cfa | mysql_1     |        3306 | ONLINE       | SECONDARY   | 8.3.0          | XCom                       |
| group_replication_applier | ef278bf7-6b5d-11ef-9936-000c2939881a | mysql_3     |        3306 | ONLINE       | SECONDARY   | 8.3.0          | XCom                       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
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 安装

yum install proxysql
systemctl start proxysql
systemctl enable proxysql

官方文档

proxysql 配置

添加组配置

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 (
    2，  -- writer_hostgroup (写操作主机组)
    4，  -- backup_writer_hostgroup (备份写操作主机组)
    3，  -- reader_hostgroup (读操作主机组)
    1，  -- offline_hostgroup (离线主机组)
    1，  -- active (激活状态)
    1，  -- max_writers (最大写操作主机数)
    1，  -- writer_is_also_reader (写主机是否也充当读操作主机)
    1000 -- max_transactions_behind (最大滞后事务数)
);
-- 加载到RUNTIME，保存到disk
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

配置监控

要在 MySQL 中创建用户，请连接到 PRIMARY 并执行

# mgr 
# basic configure
disabled_storage_engines="MyISAM，BLACKHOLE，FEDERATED，ARCHIVE，MEMORY"

server_id=1
gtid_mode=ON                      
enforce_gtid_consistency=ON      
log_bin=binlog
binlog_format=ROW
binlog_checksum=NONE
log_replica_updates=ON


######################### MGR ##########################
plugin_load_add='group_replication.so'
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "192.168.30.128:3406"
loose-group_replication_group_seeds= "192.168.30.128:3406，192.168.30.139:3406，192.168.30.140:3406"
loose-group_replication_Bootstrap_group=off
loose-group_replication_member_weight=50
group_replication_single_primary_mode=ON

0

在proxysql 中执行

# mgr 
# basic configure
disabled_storage_engines="MyISAM，BLACKHOLE，FEDERATED，ARCHIVE，MEMORY"

server_id=1
gtid_mode=ON                      
enforce_gtid_consistency=ON      
log_bin=binlog
binlog_format=ROW
binlog_checksum=NONE
log_replica_updates=ON


######################### MGR ##########################
plugin_load_add='group_replication.so'
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "192.168.30.128:3406"
loose-group_replication_group_seeds= "192.168.30.128:3406，192.168.30.139:3406，192.168.30.140:3406"
loose-group_replication_Bootstrap_group=off
loose-group_replication_member_weight=50
group_replication_single_primary_mode=ON

1

添加后端

# mgr 
# basic configure
disabled_storage_engines="MyISAM，BLACKHOLE，FEDERATED，ARCHIVE，MEMORY"

server_id=1
gtid_mode=ON                      
enforce_gtid_consistency=ON      
log_bin=binlog
binlog_format=ROW
binlog_checksum=NONE
log_replica_updates=ON


######################### MGR ##########################
plugin_load_add='group_replication.so'
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "192.168.30.128:3406"
loose-group_replication_group_seeds= "192.168.30.128:3406，192.168.30.139:3406，192.168.30.140:3406"
loose-group_replication_Bootstrap_group=off
loose-group_replication_member_weight=50
group_replication_single_primary_mode=ON

2

添加用户

# mgr 
# basic configure
disabled_storage_engines="MyISAM，BLACKHOLE，FEDERATED，ARCHIVE，MEMORY"

server_id=1
gtid_mode=ON                      
enforce_gtid_consistency=ON      
log_bin=binlog
binlog_format=ROW
binlog_checksum=NONE
log_replica_updates=ON


######################### MGR ##########################
plugin_load_add='group_replication.so'
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "192.168.30.128:3406"
loose-group_replication_group_seeds= "192.168.30.128:3406，192.168.30.139:3406，192.168.30.140:3406"
loose-group_replication_Bootstrap_group=off
loose-group_replication_member_weight=50
group_replication_single_primary_mode=ON

3

配置读写分离

# mgr 
# basic configure
disabled_storage_engines="MyISAM，BLACKHOLE，FEDERATED，ARCHIVE，MEMORY"

server_id=1
gtid_mode=ON                      
enforce_gtid_consistency=ON      
log_bin=binlog
binlog_format=ROW
binlog_checksum=NONE
log_replica_updates=ON


######################### MGR ##########################
plugin_load_add='group_replication.so'
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "192.168.30.128:3406"
loose-group_replication_group_seeds= "192.168.30.128:3406，192.168.30.139:3406，192.168.30.140:3406"
loose-group_replication_Bootstrap_group=off
loose-group_replication_member_weight=50
group_replication_single_primary_mode=ON

4

验证负载均衡

# mgr 
# basic configure
disabled_storage_engines="MyISAM，BLACKHOLE，FEDERATED，ARCHIVE，MEMORY"

server_id=1
gtid_mode=ON                      
enforce_gtid_consistency=ON      
log_bin=binlog
binlog_format=ROW
binlog_checksum=NONE
log_replica_updates=ON


######################### MGR ##########################
plugin_load_add='group_replication.so'
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "192.168.30.128:3406"
loose-group_replication_group_seeds= "192.168.30.128:3406，192.168.30.139:3406，192.168.30.140:3406"
loose-group_replication_Bootstrap_group=off
loose-group_replication_member_weight=50
group_replication_single_primary_mode=ON

5

ProxySQL其它的参数上的调整

# mgr 
# basic configure
disabled_storage_engines="MyISAM，BLACKHOLE，FEDERATED，ARCHIVE，MEMORY"

server_id=1
gtid_mode=ON                      
enforce_gtid_consistency=ON      
log_bin=binlog
binlog_format=ROW
binlog_checksum=NONE
log_replica_updates=ON


######################### MGR ##########################
plugin_load_add='group_replication.so'
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "192.168.30.128:3406"
loose-group_replication_group_seeds= "192.168.30.128:3406，192.168.30.139:3406，192.168.30.140:3406"
loose-group_replication_Bootstrap_group=off
loose-group_replication_member_weight=50
group_replication_single_primary_mode=ON

6

报错处理

# mgr 
# basic configure
disabled_storage_engines="MyISAM，BLACKHOLE，FEDERATED，ARCHIVE，MEMORY"

server_id=1
gtid_mode=ON                      
enforce_gtid_consistency=ON      
log_bin=binlog
binlog_format=ROW
binlog_checksum=NONE
log_replica_updates=ON


######################### MGR ##########################
plugin_load_add='group_replication.so'
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "192.168.30.128:3406"
loose-group_replication_group_seeds= "192.168.30.128:3406，192.168.30.139:3406，192.168.30.140:3406"
loose-group_replication_Bootstrap_group=off
loose-group_replication_member_weight=50
group_replication_single_primary_mode=ON

7