高可用 proxysql + mysql MGR

zuozhengjun發表於2024-10-16
MySql

MGR 配置

安裝

系統：centos 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 並執行

CREATE USER 'monitor'@'%' IDENTIFIED BY 'Monitor@123456';
GRANT USAGE, REPLICATION CLIENT ON *.* TO 'monitor'@'%';

在proxysql 中執行


set mysql-monitor_username='monitor';
set mysql-monitor_password='Monitor@123456';
UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';
UPDATE global_variables SET variable_value='Monitor@123456' WHERE variable_name='mysql-monitor_password';

--- 配置各種監控間隔
-- UPDATE global_variables SET variable_value='2000' WHERE variable_name IN ('mysql-monitor_connect_interval','mysql-monitor_ping_interval','mysql-monitor_read_only_interval');

SELECT * FROM global_variables WHERE variable_name LIKE 'mysql-monitor_%';


-- 載入到RUNTIME,儲存到disk
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

新增後端

INSERT INTO mysql_servers (
    hostgroup_id,
    hostname,
    port,
    weight
) VALUES
(2, '192.168.30.128', 3306, 100),
(2, '192.168.30.139', 3306, 100),
(3, '192.168.30.140', 3306, 100);


LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

-- 檢視主機
select * from runtime_mysql_servers;

新增使用者

insert into mysql_users
(username,password,default_hostgroup,default_schema)
values
('test','Zzj@123456',2,'information_schema');

配置讀寫分離

insert into mysql_query_rules(rule_id,username,active,match_digest,destination_hostgroup,apply)values(3,'test',1,'^SELECT.*FOR UPDATE$',2,1);
insert into mysql_query_rules(rule_id,username,active,match_digest,destination_hostgroup,apply)values(4,'test',1,'^SELECT',3,1);
--檢視規則
select rule_id,username,active,match_digest,destination_hostgroup,apply from mysql_query_rules;

驗證負載均衡

for i in {1..112}; do  mysql -utest -pZzj@123456 -h192.168.30.128 -P6033 -e 'select @@hostname' -s -N; done

ProxySQL其它的引數上的調整

mysql-max_stmts_per_connection 從 20 調整為100
-- 【放棄，影響不大】 mysql-threads 從 4 調整到 8
 
update global_variables set variable_value='100' where variable_name ='mysql-max_stmts_per_connection';
-- update global_variables set variable_value='8' where variable_name ='mysql-threads';
 
-- 從原先的64MB改到128MB
update global_variables set variable_value='134217728' where variable_name ='mysql-max_allowed_packet';
 
update global_variables set variable_value='2000' where variable_name in('mysql-monitor_connect_interval','mysql-monitor_ping_interval','mysql-monitor_read_only_interval');
 
-- 改下隔離級別，必須步驟！否則會出現RR隔離級別查到髒資料 【測試環境復現出來的問題】
update global_variables set variable_value='REPEATABLE-READ' where variable_name ='mysql-default_tx_isolation';
 
 
-- 更改下預設的字符集和字元排序集
-- mysql-default_charset 預設是 utf8
-- mysql-default_collation_connection 預設是 utf8_general_ci
update global_variables set variable_value='utf8mb4' where variable_name ='mysql-default_charset';
update global_variables set variable_value='utf8mb4_0900_ai_ci' where variable_name ='mysql-default_collation_connection';
 
 
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

報錯處理

-- onnection is locked to hostgroup 2 but trying to reach hostgroup 3
-- https://stackoverflow.com/questions/72722871/9006-proxysql-error-connection-is-locked-to-hostgroup-xx-but-trying-to-reach-ho

set mysql-set_query_lock_on_hostgroup=0;
load mysql variables to runtime;
save mysql variables to disk;

相關文章