MySQL ProxySQL代理MGR

MySQL中間件 ProxySQL代理MGR

文章來(lái)源: 陶老師運(yùn)維筆記- 微信公眾號(hào)

ProxySQL

1. 背景介紹

ProxySQL介紹:

ProxySQL 是一個(gè)較輕量但功能強(qiáng)大的MySQL中間件。可以很好的支持 Master Slave， MGR, PXC等MySQL架構(gòu)，并提供連接池、讀寫(xiě)分離、日志記錄等功能。

MySQL MGR 介紹:

MySQL Group Replication（簡(jiǎn)稱MGR）是由多個(gè)實(shí)例節(jié)點(diǎn)共同組成一個(gè)數(shù)據(jù)庫(kù)集群，系統(tǒng)提交事務(wù)必須經(jīng)過(guò)半數(shù)以上節(jié)點(diǎn)同意方可提交,在集群中每個(gè)節(jié)點(diǎn)上都維護(hù)一個(gè)數(shù)據(jù)庫(kù)狀態(tài)機(jī)，保證節(jié)點(diǎn)間事務(wù)的一致性.

MySQL組復(fù)制分單主模式和多主模式，MGR復(fù)制技術(shù)僅解決了數(shù)據(jù)一致性問(wèn)題。<font color="red">當(dāng)Master 宕機(jī)，應(yīng)用系統(tǒng)可能仍需要修改數(shù)據(jù)庫(kù)主庫(kù)連接地址,才能保證服務(wù)的可用性:(。</font>

為解決此問(wèn)題，咱們可以在MRG上層增加一Proxy代理層，例如本文介紹的ProxySQL。

2. 測(cè)試環(huán)境

2.1 環(huán)境規(guī)劃

角色 版本 IP port server-id
Proxy-1 2.0.8 192.110.154.98 6032/6033 -
DB-1 MySQL5.7.23 192.110.103.41 3106 103413106
DB-2 MySQL5.7.23 192.110.103.42 3106 103423106
DB-3 MySQL5.7.23 192.110.103.43 3106 103433106

MGR信息: MySQL機(jī)器IP,192.110.103.41/42/43，Port:3106。MGR為單主模式,DB-1為Master。

192.110.103.41 : (none) > SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+---------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST   | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+---------------+-------------+--------------+
| group_replication_applier | 509810ee-f3d7-11e9-a7d5-a0369fac2de4 | 192.110.103.41 |        3106 | ONLINE       |
| group_replication_applier | 74eedba2-2314-11ea-9146-a0369fa6cce4 | 192.110.103.42 |        3106 | ONLINE       |
| group_replication_applier | ee4a9cec-f3d5-11e9-9ded-a0369fa6cd30 | 192.110.103.43 |        3106 | ONLINE       |
+---------------------------+--------------------------------------+---------------+-------------+--------------+
3 rows in set (0.00 sec)
192.110.103.41 : test > show global variables like '%group_replication_single_primary_mode%';
+---------------------------------------+-------+
| Variable_name                         | Value |
+---------------------------------------+-------+
| group_replication_single_primary_mode | ON    |
+---------------------------------------+-------+
1 row in set (0.00 sec)
#
SELECT ta.* ,tb.MEMBER_HOST,tb.MEMBER_PORT,tb.MEMBER_STATE FROM performance_schema.global_status ta,performance_schema.replication_group_members tb 
 WHERE ta.VARIABLE_NAME='group_replication_primary_member' and ta.VARIABLE_VALUE=tb.MEMBER_ID;
+----------------------------------+--------------------------------------+---------------+-------------+--------------+
| VARIABLE_NAME                    | VARIABLE_VALUE                       | MEMBER_HOST   | MEMBER_PORT | MEMBER_STATE |
+----------------------------------+--------------------------------------+---------------+-------------+--------------+
| group_replication_primary_member | 509810ee-f3d7-11e9-a7d5-a0369fac2de4 | 192.110.103.41 |        3106 | ONLINE       |
+----------------------------------+--------------------------------------+---------------+-------------+--------------+
#

3. ProxSQL安裝

下載
$wget https://github.com/sysown/proxysql/releases/download/v2.0.8/proxysql-2.0.8-1-centos67.x86_64.rpm
#安裝
$rpm -ivh proxysql-2.0.8-1-centos67.x86_64.rpm 
#查看版本
$proxysql --version
ProxySQL version 2.0.8-67-g877cab1, codename Truls

啟停/狀態(tài)

#查看版本
$proxysql --version
#存儲(chǔ)目錄
#ls  /var/lib/proxysql/
#啟動(dòng)/停止
$service proxysql start
$service proxysql stop
$service proxysql status  # 查看proxysql狀態(tài)
ProxySQL is running (20761).

測(cè)試

$proxysql --version
mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '
admin> show databases;

4. 配置主機(jī)

4.1 連接配置接口

成功啟動(dòng)后，就使用原始賬號(hào)admin/admin可以登錄proxy管理接口6032。

mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '
Admin> show databases;
+-----+---------------+-------------------------------------+
| seq | name          | file                                |
+-----+---------------+-------------------------------------+
| 0   | main          |                                     |
| 2   | disk          | /var/lib/proxysql/proxysql.db       |
| 3   | stats         |                                     |
| 4   | monitor       |                                     |
| 5   | stats_history | /var/lib/proxysql/proxysql_stats.db |
+-----+---------------+-------------------------------------+
5 rows in set (0.00 sec)

庫(kù)說(shuō)明:

  • main庫(kù)是ProxySQL最主要的庫(kù)， 是內(nèi)存數(shù)據(jù)庫(kù)，修改配置時(shí)必須將其持久化到disk永久保存。
  • disk庫(kù)是磁盤數(shù)據(jù)庫(kù)，該數(shù)據(jù)庫(kù)結(jié)構(gòu)和內(nèi)存數(shù)據(jù)庫(kù)完全一致。
  • stats庫(kù)是統(tǒng)計(jì)信息庫(kù)。這個(gè)庫(kù)中的數(shù)據(jù)一般是在檢索其內(nèi)數(shù)據(jù)時(shí)臨時(shí)填充的，它保存在內(nèi)存中。
  • monitor庫(kù)是監(jiān)控后端MySQL節(jié)點(diǎn)相關(guān)的庫(kù)，該庫(kù)中只有幾個(gè)log表存監(jiān)控信息。
  • stats_history庫(kù)是1.4.4版新增的庫(kù)，用于存放歷史統(tǒng)計(jì)數(shù)據(jù)。

4.2 配置主機(jī)

mysql_server表中增加MGR01組的機(jī)器,192.110.103.41:3106-192.110.103.43:3106。

#
select * from mysql_servers;
insert into mysql_servers(hostgroup_id,max_connections,comment,hostname,port) values(100,2000,'MGR01-01','192.110.103.41',3106);
insert into mysql_servers(hostgroup_id,max_connections,comment,hostname,port) values(100,2000,'MGR01-02','192.110.103.42',3106);
insert into mysql_servers(hostgroup_id,max_connections,comment,hostname,port) values(100,2000,'MGR01-03','192.110.103.43',3106);
#select hostgroup_id,hostname,port,status,weight from mysql_servers;
select * from mysql_servers where port=3106;
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+----------+
| hostgroup_id | hostname      | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment  |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+----------+
| 100          | 192.110.103.41 | 3106 | 0         | ONLINE | 1      | 0           | 2000            | 0                   | 0       | 0              | MGR01-01 |
| 100          | 192.110.103.42 | 3106 | 0         | ONLINE | 1      | 0           | 2000            | 0                   | 0       | 0              | MGR01-02 |
| 100          | 192.110.103.43 | 3106 | 0         | ONLINE | 1      | 0           | 2000            | 0                   | 0       | 0              | MGR01-03 |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+----------+

配置讀寫(xiě)分組:

對(duì)ProxySQL中的節(jié)點(diǎn)分組：writer_hostgroup、reader_hostgroup。#1.4x版本/2.x版本是有區(qū)別的
加載生效后，Monitor模塊就會(huì)開(kāi)始監(jiān)控mysql_replication_hostgroups表check_type，如read_only/super_read_only等值，當(dāng)監(jiān)控到read_only/super_read_only值后，就會(huì)按照read_only/super_read_only的值將某些節(jié)點(diǎn)自動(dòng)移動(dòng)到讀/寫(xiě)組。

#insert into mysql_replication_hostgroups values(100,102,'-'); #1.4x版本
insert into mysql_replication_hostgroups values(100,102,'read_only','-'); #2.x版本
#將剛才mysql_replication_hostgroups表的修改加載到RUNTIME生效。
load mysql servers to runtime;
save mysql servers to disk;
#
 select * from runtime_mysql_servers;
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+----------+
| hostgroup_id | hostname      | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment  |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+----------+
| 100          | 192.110.103.41 | 3106 | 0         | ONLINE | 1      | 0           | 2000            | 0                   | 0       | 0              | MGR01-01 |
| 102          | 192.110.103.42 | 3106 | 0         | ONLINE | 1      | 0           | 2000            | 0                   | 0       | 0              | MGR01-02 |
| 102          | 192.110.103.43 | 3106 | 0         | ONLINE | 1      | 0           | 2000            | 0                   | 0       | 0              | MGR01-03 |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+----------+
3 rows in set (0.00 sec)

5. 監(jiān)控后端節(jié)點(diǎn)

ProxySQL有兩種賬號(hào)：區(qū)分admin,stat管理接口的用戶名和mysql_users中的用戶名。

  • admin管理接口的用戶: 是連接到管理接口(默認(rèn)端口6032)上用來(lái)管理、配置ProxySQL的。
  • mysql_users表中的用戶: 是應(yīng)用程序連接ProxySQL(默認(rèn)端口6033)，以及ProxySQL連接后端MySQL Servers使用的用戶。它的作用是發(fā)送、路由SQL語(yǔ)句，類似于MySQL Server的3306端口。所以，這個(gè)表中的用戶必須已經(jīng)在后端MySQL Server上存在且授權(quán)了!

ProxySQL需要監(jiān)控后端節(jié)點(diǎn)的狀態(tài)權(quán)限。因?yàn)镻roxySQL需要通過(guò)每個(gè)節(jié)點(diǎn)的read_only值來(lái)自動(dòng)調(diào)整它們是屬于讀組還是寫(xiě)組。

DB上增加監(jiān)控賬號(hào):

在DB Master上創(chuàng)建ProxySQL監(jiān)控用戶。注意的是, ProxySQL代理MGR組復(fù)制時(shí)，是從MGR的系統(tǒng)視圖sys.gr_member_routing_candidate_status中獲取監(jiān)控指標(biāo)，所以授予監(jiān)控用戶對(duì)該視圖的查詢權(quán)限，因?yàn)闊o(wú)需從show slave status中獲取Seconds_Behind_Master，所以無(wú)需replication client權(quán)限。

#MySQL Master
create user monitor@'%' identified by 'P@ssword1!';
grant select on sys.* to monitor@'%';
#可不授replication client權(quán)限.
#grant replication client on *.* to monitor@'% ' identified by 'P@ssword1!';

ProxySQL上配置監(jiān)控:

#proxy
set mysql-monitor_username='monitor'; set mysql-monitor_password='P@ssword1!';
#檢查
select * from global_variables where variable_name like 'mysql-monitor%'; 

#生效
load mysql variables to runtime;
save mysql variables to disk;

檢查是否生效:

ProxySQL監(jiān)控模塊的指標(biāo)都保存在monitor庫(kù)的log表中。

#ProxySQL監(jiān)控模塊的指標(biāo)都保存在monitor庫(kù)的log表中。
mysql -uadmin -padmin -P6032 -h127.0.0.1 --prompt 'admin> '

admin> select * from mysql_server_connect_log order by time_start_us desc limit 6;
+---------------+------+------------------+-------------------------+---------------+
| hostname      | port | time_start_us    | connect_success_time_us | connect_error |
+---------------+------+------------------+-------------------------+---------------+
| 192.110.103.41 | 3106 | 1577096966015232 | 613                     | NULL          |
| 192.110.103.43 | 3106 | 1577096965257056 | 610                     | NULL          |
| 192.110.103.42 | 3106 | 1577096964498874 | 643                     | NULL          |
| 192.110.103.41 | 3106 | 1577096906082375 | 785                     | NULL          |
| 192.110.103.42 | 3106 | 1577096905290598 | 598                     | NULL          |
| 192.110.103.43 | 3106 | 1577096904498801 | 800                     | NULL          |
+---------------+------+------------------+-------------------------+---------------+
6 rows in set (0.00 sec)

select * from mysql_server_ping_log order by time_start_us desc limit 30;
+---------------+------+------------------+----------------------+------------+
| hostname      | port | time_start_us    | ping_success_time_us | ping_error |
+---------------+------+------------------+----------------------+------------+
| 192.110.103.43 | 3106 | 1577096914589065 | 195                  | NULL       |
| 192.110.103.42 | 3106 | 1577096914483012 | 187                  | NULL       |
| 192.110.103.41 | 3106 | 1577096914376942 | 193                  | NULL       |
| 192.110.103.41 | 3106 | 1577096904578993 | 192                  | NULL       |
| 192.110.103.43 | 3106 | 1577096904477957 | 182                  | NULL       |
| 192.110.103.42 | 3106 | 1577096904376893 | 183                  | NULL       |
+---------------+------+------------------+----------------------+------------+

6. 創(chuàng)建視圖sys.gr_member_routing_candidate_status(2.0.x可略)

  • <font color=red> proxysql 2.0.x 可略過(guò)此步驟，但1.4.12等舊版本需要。</font>
  • proxysql1.4.x盡管已原生支持MGR，但仍然需要在MGR節(jié)點(diǎn)中創(chuàng)建一張額外的系統(tǒng)視圖sys.gr_member_routing_candidate_status為ProxySQL提供監(jiān)控指標(biāo)。

若proxysql為1.4.x則需要在DB master上執(zhí)行，如下命令：

#wget https://files.cnblogs.com/files/f-ck-need-u/addition_to_sys.zip 
USE sys;

DELIMITER $$

CREATE FUNCTION IFZERO(a INT, b INT)
RETURNS INT
DETERMINISTIC
RETURN IF(a = 0, b, a)$$

CREATE FUNCTION LOCATE2(needle TEXT(10000), haystack TEXT(10000), offset INT)
RETURNS INT
DETERMINISTIC
RETURN IFZERO(LOCATE(needle, haystack, offset), LENGTH(haystack) + 1)$$

CREATE FUNCTION GTID_NORMALIZE(g TEXT(10000))
RETURNS TEXT(10000)
DETERMINISTIC
RETURN GTID_SUBTRACT(g, '')$$

CREATE FUNCTION GTID_COUNT(gtid_set TEXT(10000))
RETURNS INT
DETERMINISTIC
BEGIN
  DECLARE result BIGINT DEFAULT 0;
  DECLARE colon_pos INT;
  DECLARE next_dash_pos INT;
  DECLARE next_colon_pos INT;
  DECLARE next_comma_pos INT;
  SET gtid_set = GTID_NORMALIZE(gtid_set);
  SET colon_pos = LOCATE2(':', gtid_set, 1);
  WHILE colon_pos != LENGTH(gtid_set) + 1 DO
     SET next_dash_pos = LOCATE2('-', gtid_set, colon_pos + 1);
     SET next_colon_pos = LOCATE2(':', gtid_set, colon_pos + 1);
     SET next_comma_pos = LOCATE2(',', gtid_set, colon_pos + 1);
     IF next_dash_pos < next_colon_pos AND next_dash_pos < next_comma_pos THEN
       SET result = result +
         SUBSTR(gtid_set, next_dash_pos + 1,
                LEAST(next_colon_pos, next_comma_pos) - (next_dash_pos + 1)) -
         SUBSTR(gtid_set, colon_pos + 1, next_dash_pos - (colon_pos + 1)) + 1;
     ELSE
       SET result = result + 1;
     END IF;
     SET colon_pos = next_colon_pos;
  END WHILE;
  RETURN result;
END$$

CREATE FUNCTION gr_applier_queue_length()
RETURNS INT
DETERMINISTIC
BEGIN
  RETURN (SELECT sys.gtid_count( GTID_SUBTRACT( (SELECT
Received_transaction_set FROM performance_schema.replication_connection_status
WHERE Channel_name = 'group_replication_applier' ), (SELECT
@@global.GTID_EXECUTED) )));
END$$

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 != 'ONLINE') >=
((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));
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,
sys.gr_applier_queue_length() as transactions_behind, Count_Transactions_in_queue as 'transactions_to_cert' from performance_schema.replication_group_member_stats;$$

DELIMITER ;

查看該視圖：

192.110.103.41 :sys >  select * from sys.gr_member_routing_candidate_status;
+------------------+-----------+---------------------+----------------------+
| viable_candidate | read_only | transactions_behind | transactions_to_cert |
+------------------+-----------+---------------------+----------------------+
| YES              | NO        |                   0 |                    0 |
+------------------+-----------+---------------------+----------------------+
1 row in set (0.03 sec)

7. 錄入MGR hostgroups信息

7.1 mysql_group_replication_hostgroups表

select * from mysql_group_replication_hostgroups;
-- 表結(jié)構(gòu)
show create table mysql_group_replication_hostgroups\G
*************************** 1. row ***************************
       table: mysql_group_replication_hostgroups
Create Table: CREATE TABLE mysql_group_replication_hostgroups (
    writer_hostgroup INT CHECK (writer_hostgroup>=0) NOT NULL PRIMARY KEY,
    backup_writer_hostgroup INT CHECK (backup_writer_hostgroup>=0 AND backup_writer_hostgroup<>writer_hostgroup) NOT NULL,
    reader_hostgroup INT NOT NULL CHECK (reader_hostgroup<>writer_hostgroup AND backup_writer_hostgroup<>reader_hostgroup AND reader_hostgroup>0),
    offline_hostgroup INT NOT NULL CHECK (offline_hostgroup<>writer_hostgroup AND offline_hostgroup<>reader_hostgroup AND backup_writer_hostgroup<>offline_hostgroup AND offline_hostgroup>=0),
    active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1,
    max_writers INT NOT NULL CHECK (max_writers >= 0) DEFAULT 1,
    writer_is_also_reader INT CHECK (writer_is_also_reader IN (0,1,2)) NOT NULL DEFAULT 0,
    max_transactions_behind INT CHECK (max_transactions_behind>=0) NOT NULL DEFAULT 0,
    comment VARCHAR,
    UNIQUE (reader_hostgroup),
    UNIQUE (offline_hostgroup),
    UNIQUE (backup_writer_hostgroup))
1 row in set (0.00 sec)

各字段的意義如下：

  • writer_hostgroup：默認(rèn)的寫(xiě)組。后端read_only=0的節(jié)點(diǎn)會(huì)自動(dòng)分配到這個(gè)組中。
  • backup_writer_hostgroup：為備份節(jié)點(diǎn)組。超出max_writers數(shù)量的但允許寫(xiě)的節(jié)點(diǎn)都會(huì)放進(jìn)備份組backup_writer_hostgroup中。
  • reader_hostgroup：負(fù)責(zé)讀的組。讀請(qǐng)求都會(huì)路由到該主機(jī)組中的節(jié)點(diǎn),后端read_only=1的節(jié)點(diǎn)會(huì)自動(dòng)分配到這個(gè)組中。
  • offline_hostgroup：當(dāng)ProxySQL監(jiān)控并決定了某節(jié)點(diǎn)為OFFLINE后，會(huì)將其放進(jìn)組offline_hostgroup中。
  • active：當(dāng)啟用后，ProxySQL會(huì)監(jiān)控該主機(jī)組，并在不同組之間合理地移動(dòng)節(jié)點(diǎn)。
  • max_writers：該字段的值決定writer_hostgroup中最大允許的節(jié)點(diǎn)數(shù)。
  • writer_is_also_reader：決定一個(gè)節(jié)點(diǎn)升級(jí)為寫(xiě)節(jié)點(diǎn)(放進(jìn)writer_hostgroup)后是否仍然保留在reader_hostgroup組中提供讀服務(wù)。
  • max_transactions_behind： 當(dāng)某節(jié)點(diǎn)延后于寫(xiě)節(jié)點(diǎn)時(shí)，為了防止讀取到過(guò)期數(shù)據(jù)，ProxySQL可能會(huì)自動(dòng)避開(kāi)該節(jié)點(diǎn)。
  • comment：該字段用于說(shuō)明、注釋，可隨便定義。

注意：

  • ProxySQL代理每一個(gè)后端MGR集群時(shí)，都必須為這個(gè)MGR定義讀組、寫(xiě)組、備寫(xiě)組、離線組，且這四個(gè)組的值各不相同、不允許NULL、具有唯一性。
  • ProxySQL代理多主模型的MGR時(shí)，必須設(shè)置writer_is_also_reader=1。

7.2 錄入MGR組信息

如果想讓ProxySQL來(lái)自動(dòng)調(diào)整節(jié)點(diǎn)所屬讀、寫(xiě)組，需要開(kāi)啟read_only監(jiān)控，并在mysql_group_replication_hostgroups表中插入一條記錄。

插入數(shù)據(jù):

#proxySQL中insert 數(shù)據(jù)
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,comment) 
values(100,101,102,103,1,1,0,10,'cluster-mgr01');
#若是多個(gè)mgr可以再次insert
#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,comment) 
values(200,201,202,203,1,1,0,10,'cluster-mgr02');

#select * from runtime_mysql_replication_hostgroups;

上述配置中，本例中writer_is_also_reader設(shè)置為false，則master只負(fù)責(zé)寫(xiě)操作。

配置生效:

load mysql servers to runtime;
save mysql servers to disk;
查看錄入結(jié)果
select * from mysql_group_replication_hostgroups;
+------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+
| writer_hostgroup | backup_writer_hostgroup | reader_hostgroup | offline_hostgroup | active | max_writers | writer_is_also_reader | max_transactions_behind | comment |
+------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+
| 100              | 101                     | 102              | 103               | 1      | 1           | 1                     | 10                      | MGR01   |
+------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+

查看實(shí)時(shí)服務(wù)器狀態(tài)
select hostgroup_id, hostname, port,status from runtime_mysql_servers;
+--------------+---------------+------+--------+
| hostgroup_id | hostname      | port | status |
+--------------+---------------+------+--------+
| 100          | 192.110.103.41 | 3106 | ONLINE |
| 100          | 192.110.103.43 | 3106 | ONLINE |
| 100          | 192.110.103.42 | 3106 | ONLINE |
+--------------+---------------+------+--------+

查看檢測(cè)MGR節(jié)點(diǎn)狀態(tài)
#select hostname,port,viable_candidate,read_only,transactions_behind,error
from mysql_server_group_replication_log order by time_start_us desc limit 10;
select * from mysql_server_group_replication_log order by time_start_us desc limit 6; 
+---------------+------+------------------+-----------------+------------------+-----------+---------------------+-------+
| hostname      | port | time_start_us    | success_time_us | viable_candidate | read_only | transactions_behind | error |
+---------------+------+------------------+-----------------+------------------+-----------+---------------------+-------+
| 192.110.103.43 | 3106 | 1577540023301725 | 2288            | YES              | YES       | 0                   | NULL  |
| 192.110.103.42 | 3106 | 1577540023301423 | 2821            | YES              | YES       | 0                   | NULL  |
| 192.110.103.41 | 3106 | 1577540023301088 | 2563            | YES              | NO        | 0                   | NULL  |
| 192.110.103.43 | 3106 | 1577540018301516 | 2452            | YES              | YES       | 0                   | NULL  |
| 192.110.103.42 | 3106 | 1577540018301192 | 2824            | YES              | YES       | 0                   | NULL  |
| 192.110.103.41 | 3106 | 1577540018300907 | 2610            | YES              | NO        | 0                   | NULL  |
+---------------+------+------------------+-----------------+------------------+-----------+---------------------+-------+
6 rows in set (0.00 sec)

8. 配置mysql_users

在DB Master節(jié)點(diǎn)上執(zhí)行：

#MySQL Master, 可依用戶名來(lái)區(qū)分多組DB，來(lái)實(shí)現(xiàn)路由
grant select,insert,update,delete on *.* to 'mgr01'@'%' identified by 'P@ssword1!';
grant all on *.* to mgr01_root@'%' identified by 'P@ssword1!';

#proxy v2.0.8
delete from mysql_users;
insert into mysql_users(username,password,default_hostgroup,transaction_persistent) values('mgr01','P@ssword1!',100,1);
insert into mysql_users(username,password,default_hostgroup,transaction_persistent) values('mgr01_root','P@ssword1!',100,1);

#proxy v1.4.x
insert into mysql_users(username,password,default_hostgroup,transaction_persistent,comment) values('mgr01','P@ssword1!',100,1,'MRG01');
insert into mysql_users(username,password,default_hostgroup,transaction_persistent,comment) values('mgr01_root','P@ssword1!',100,1,'MGR01');
#
#select * from mysql_servers;    
#select * from mysql_group_replication_hostgroups;


admin> select username,password,active,default_hostgroup,max_connections,comment from mysql_users; 
+------------+------------+--------+-------------------+-----------------+---------+
| username   | password   | active | default_hostgroup | max_connections | comment |
+------------+------------+--------+-------------------+-----------------+---------+
| mgr01      | P@ssword1! | 1      | 100               | 10000           |         |
| mgr01_root | P@ssword1! | 1      | 100               | 10000           |         |
+------------+------------+--------+-------------------+-----------------+---------+
2 rows in set (0.00 sec)
#
load mysql users to runtime;
save mysql users to disk;

說(shuō)明:

  • 注意hostgroup要和mysql_servers中的組相一致，comment做為備注為MGR01機(jī)群。
  • 建議為不同的DB組使用不同的用戶名,如mgr01,mgr02，這樣一個(gè)proxy可方便路由多組DB。

9. 讀寫(xiě)分離

測(cè)試是否按預(yù)期進(jìn)行讀寫(xiě)分離。目前DB-1,192.110.103.41機(jī)器為主,read_only為off。

9.1 讀寫(xiě)分離配置

ProxySQL的路由規(guī)則非常靈活，可配置讀/寫(xiě)分離，也可配置從各項(xiàng)指標(biāo)中找出壓力大、執(zhí)行頻繁的語(yǔ)句單獨(dú)寫(xiě)規(guī)則、做緩存等等。

  • 查詢規(guī)則表有：mysql_query_rules和mysql_query_rules_fast_routing，后者是前者的擴(kuò)展表。
  • 讀寫(xiě)分離可配置mysql_query_rules，但是若是代理了多組DB則不方便配置mysql_query_rules(可不配置)。
select * from mysql_query_rules;
insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)
VALUES (1,1,'^SELECT.*FOR UPDATE$',100,1), (2,1,'^SELECT',102,1);

load mysql query rules to runtime;
save mysql query rules to disk;
#查看語(yǔ)句路由狀態(tài)：
select hostgroup,digest_text from stats_mysql_query_digest;

9.2 測(cè)試讀寫(xiě)分離

當(dāng)前DB情況:

192.110.103.41 : test > show global variables like '%group_replication_single_primary_mode%';
+---------------------------------------+-------+
| Variable_name                         | Value |
+---------------------------------------+-------+
| group_replication_single_primary_mode | ON    |
+---------------------------------------+-------+
1 row in set (0.00 sec)
#
SELECT ta.* ,tb.MEMBER_HOST,tb.MEMBER_PORT,tb.MEMBER_STATE FROM performance_schema.global_status ta,performance_schema.replication_group_members tb 
 WHERE ta.VARIABLE_NAME='group_replication_primary_member' and ta.VARIABLE_VALUE=tb.MEMBER_ID;
+----------------------------------+--------------------------------------+---------------+-------------+--------------+
| VARIABLE_NAME                    | VARIABLE_VALUE                       | MEMBER_HOST   | MEMBER_PORT | MEMBER_STATE |
+----------------------------------+--------------------------------------+---------------+-------------+--------------+
| group_replication_primary_member | 509810ee-f3d7-11e9-a7d5-a0369fac2de4 | 192.110.103.41 |        3106 | ONLINE       |
+----------------------------------+--------------------------------------+---------------+-------------+--------------+
#41 DB-1只讀為OFF, 42/43的DB為ON。
mysql -h  192.110.103.42  -P 3106 test -e "show global variables like '%only%';" 
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| innodb_optimize_fulltext_only | OFF   |
| innodb_read_only              | OFF   |
| read_only                     | ON    |
| super_read_only               | ON    |
| transaction_read_only         | OFF   |
| tx_read_only                  | OFF   |
+-------------------------------+-------+

DB創(chuàng)建測(cè)試表:

#在master上創(chuàng)建測(cè)試表
drop table if exists test.t1;
CREATE TABLE test.t1 (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  ctime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB ;

當(dāng)前proxy配置情況:

select * from runtime_mysql_servers;                                                                                           
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+----------+
| hostgroup_id | hostname      | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment  |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+----------+
| 100          | 192.110.103.41 | 3106 | 0         | ONLINE | 1      | 0           | 2000            | 0                   | 0       | 0              | MGR01-01 |
| 102          | 192.110.103.42 | 3106 | 0         | ONLINE | 1      | 0           | 2000            | 0                   | 0       | 0              | MGR01-02 |
| 102          | 192.110.103.43 | 3106 | 0         | ONLINE | 1      | 0           | 2000            | 0                   | 0       | 0              | MGR01-03 |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+----------+
3 rows in set (0.00 sec)

select * from runtime_mysql_group_replication_hostgroups;
+------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+
| writer_hostgroup | backup_writer_hostgroup | reader_hostgroup | offline_hostgroup | active | max_writers | writer_is_also_reader | max_transactions_behind | comment |
+------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+
| 100              | 101                     | 102              | 103               | 1      | 1           | 0                     | 10                      | MGR01   |
+------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+
1 row in set (0.00 sec)

#
select * from runtime_mysql_replication_hostgroups;
+------------------+------------------+------------+---------+
| writer_hostgroup | reader_hostgroup | check_type | comment |
+------------------+------------------+------------+---------+
| 100              | 102              | read_only  | -       |
+------------------+------------------+------------+---------+
1 row in set (0.00 sec)

#
select * from mysql_server_group_replication_log limit 6; 

#
select username,password,active,default_hostgroup,transaction_persistent,backend,frontend,comment from runtime_mysql_users;
+------------+-------------------------------------------+--------+-------------------+------------------------+---------+----------+---------+
| username   | password                                  | active | default_hostgroup | transaction_persistent | backend | frontend | comment |
+------------+-------------------------------------------+--------+-------------------+------------------------+---------+----------+---------+
| mgr01_root | *50572A5FABC7DA9CEE5EB5977EDDE59E38967422 | 1      | 100               | 1                      | 0       | 1        |         |
| mgr01      | *50572A5FABC7DA9CEE5EB5977EDDE59E38967422 | 1      | 100               | 1                      | 0       | 1        |         |
| mgr01_root | *50572A5FABC7DA9CEE5EB5977EDDE59E38967422 | 1      | 100               | 1                      | 1       | 0        |         |
| mgr01      | *50572A5FABC7DA9CEE5EB5977EDDE59E38967422 | 1      | 100               | 1                      | 1       | 0        |         |
+------------+-------------------------------------------+--------+-------------------+------------------------+---------+----------+---------+
4 rows in set (0.00 sec)

測(cè)試讀/寫(xiě):

#測(cè)試讀
mysql -umgr01 -pP@ssword1! -P6033 -h127.0.0.1 test -e "select @@server_id; "
mysql -umgr01 -pP@ssword1! -P6033 -h127.0.0.1 test -e "select * from test.t1; select @@server_id; "

#測(cè)試寫(xiě)
 mysql -umgr01 -pP@ssword1! -P6033 -h127.0.0.1 -e 'start transaction;select @@server_id;commit;'
+-------------+
| @@server_id |
+-------------+
|       10341 |
+-------------+

 mysql -umgr01 -pP@ssword1! -P6033 -h127.0.0.1 test -e "insert into test.t1(name) values(now());select @@server_id; "   


select hostgroup,digest_text from stats_mysql_query_digest ORDER BY sum_time DESC limit 10;  
proxysql_admin> select hostgroup,schemaname,username,digest_text from stats_mysql_query_digest order by last_seen desc limit 10;
+-----------+--------------------+----------+-----------------------------------------+
| hostgroup | schemaname         | username | digest_text                             |
+-----------+--------------------+----------+-----------------------------------------+
| 100       | test               | mgr01    | insert into test.t1(name) values(now()) |
| 100       | test               | mgr01    | select @@server_id                      |
| 100       | test               | mgr01    | select @@version_comment limit ?        |
| 100       | information_schema | mgr01    | select @@server_id                      |
| 100       | information_schema | mgr01    | select @@version_comment limit ?        |
| 100       | information_schema | mgr01    | commit                                  |
| 100       | information_schema | mgr01    | start transaction                       |
| 100       | test               | mgr01    | select * from test.t1                   |
| 100       | test               | mgr01    | insert into test.t1(name) values(?)     |
+-----------+--------------------+----------+-----------------------------------------+

10. MGR故障轉(zhuǎn)移

將MGR的某個(gè)節(jié)點(diǎn)停掉，咱們把DB1 master停掉，測(cè)試業(yè)務(wù)影響。

停止Master DB:

#當(dāng)前情況:
SELECT * FROM performance_schema.replication_group_members;
select * from performance_schema.replication_group_member_stats;
192.110.103.41 : test > stop group_replication;

讀寫(xiě)DB影響:

可以發(fā)現(xiàn)proxy hostgroup會(huì)自動(dòng)切換，業(yè)務(wù)通過(guò)proxy寫(xiě)操作不會(huì)受影響。

##
hostgroup 100信息將會(huì)變化：由192.110.103.41變?yōu)?2了。 
proxysql_admin> select * from runtime_mysql_servers;
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+----------+
| hostgroup_id | hostname      | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment  |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+----------+
| 100          | 192.110.103.42 | 3106 | 0         | ONLINE | 1      | 0           | 2000            | 0                   | 0       | 0              | MGR01-02 |
| 102          | 192.110.103.41 | 3106 | 0         | ONLINE | 1      | 0           | 2000            | 0                   | 0       | 0              | MGR01-01 |
| 102          | 192.110.103.42 | 3106 | 0         | ONLINE | 1      | 0           | 2000            | 0                   | 0       | 0              | MGR01-02 |
| 102          | 192.110.103.43 | 3106 | 0         | ONLINE | 1      | 0           | 2000            | 0                   | 0       | 0              | MGR01-03 |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+----------+
4 rows in set (0.00 sec)

##寫(xiě)已變成了新的機(jī)器42。業(yè)務(wù)仍可正常寫(xiě)入數(shù)據(jù)。
mysql -uroot -pP@ssword1! -P6033 -h127.0.0.1 test -e "insert into test.t1(name) values('a113');select @@server_id; "   #自增id不會(huì)鏈續(xù)。 
+-------------+
| @@server_id |
+-------------+
|       10342 |
+-------------+

#

參考:

陶老師運(yùn)維筆記
最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請(qǐng)結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡(jiǎn)書(shū)系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容