ProxySQL + MGR单主模式的测试文档

拓扑图介绍：

MGR组(单主模式)：

192.168.43.141 MGR01

192.168.43.142 MGR02

192.168.43.143 MGR03

ProxySQL：

192.168.43.144 MGR04

OS : CentOS release 6.10 (Final) min

MySQL : mysql-5.7.31-1.el6.x86_64.rpm-bundle.tar

MGR的单主模式部署请参阅相关文档，这里不做介绍。

了解更多ProxySQL: https://github.com/sysown/proxysql/wiki

一、安装启动PrxoySQL

1.下载并安装ProxySQL

https://github.com/sysown/proxysql/releases

[root@mgr04 opt]# ls

proxysql-2.0.13-1-centos67.x86_64.rpm

[root@mgr04 opt]# rpm -ivh proxysql-2.0.13-1-centos67.x86_64.rpm

warning: proxysql-2.0.13-1-centos67.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 79953b49: NOKEY

error: Failed dependencies:

perl(DBD::mysql) is needed by proxysql-2.0.13-1.x86_64

perl(DBI) is needed by proxysql-2.0.13-1.x86_64

[root@mgr04 opt]# yum localinstall -y proxysql-2.0.13-1-centos67.x86_64.rpm

[root@mgr04 opt]# rpm -qa | grep proxysql

proxysql-2.0.13-1.x86_64

[root@mgr04 opt]# rpm -ql proxysql

/etc/init.d/proxysql

/etc/logrotate.d/proxysql

/etc/proxysql.cnf

/usr/bin/proxysql

/usr/share/proxysql/tools/proxysql_galera_checker.sh

/usr/share/proxysql/tools/proxysql_galera_writer.pl

[root@mgr04 opt]#

2.启动ProxySQL

[root@mgr04 opt]# /etc/init.d/proxysql status

ProxySQL is not running.

[root@mgr04 opt]# /etc/init.d/proxysql start

Starting ProxySQL: 2020-08-21 12:22:32 [INFO] Using config file /etc/proxysql.cnf

2020-08-21 12:22:32 [INFO] Using OpenSSL version: OpenSSL 1.1.1d 10 Sep 2019

2020-08-21 12:22:32 [INFO] No SSL keys/certificates found in datadir (/var/lib/proxysql). Generating new keys/certificates.

DONE!

[root@mgr04 opt]# /etc/init.d/proxysql status

ProxySQL is running (1836).

[root@mgr04 opt]# netstat -anultp | grep proxysql

tcp 0 0 0.0.0.0:6032 0.0.0.0:* LISTEN 1837/proxysql

tcp 0 0 0.0.0.0:6033 0.0.0.0:* LISTEN 1837/proxysql

注意：6032是ProxySQL的管理端口号，6033是对外服务的端口号，ProxySQL的用户名和密码都是默认的 admin

[root@mgr04 opt]# proxysql --version

ProxySQL version 2.0.13-107-g91737e0, codename Truls

3.安装mysql客户端

[root@mgr04 mysql]# ls

mysql-5.7.31-1.el6.x86_64.rpm-bundle.tar mysql-community-embedded-devel-5.7.31-1.el6.x86_64.rpm

mysql-community-client-5.7.31-1.el6.x86_64.rpm mysql-community-libs-5.7.31-1.el6.x86_64.rpm

mysql-community-common-5.7.31-1.el6.x86_64.rpm mysql-community-libs-compat-5.7.31-1.el6.x86_64.rpm

mysql-community-devel-5.7.31-1.el6.x86_64.rpm mysql-community-server-5.7.31-1.el6.x86_64.rpm

mysql-community-embedded-5.7.31-1.el6.x86_64.rpm mysql-community-test-5.7.31-1.el6.x86_64.rpm

[root@mgr04 mysql]# yum install mysql-community-common-5.7.31-1.el6.x86_64.rpm mysql-community-libs-* mysql-community-client-5.7.31-1.el6.x86_64.rpm -y

[root@mgr04 mysql]#

[root@mgr04 mysql]# rpm -qa | grep mysql

mysql-community-libs-5.7.31-1.el6.x86_64

mysql-community-libs-compat-5.7.31-1.el6.x86_64

mysql-community-common-5.7.31-1.el6.x86_64

mysql-community-client-5.7.31-1.el6.x86_64

4.登录PrxoySQL进行观察

[root@mgr04 mysql]# mysql -uadmin -padmin -h 127.0.0.1 -P 6032

mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 1

Server version: 5.5.30 (ProxySQL Admin Module)

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 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)

可见有五个库 : main、disk、stats 、monitor 和 stats_history

main : 内存配置数据库，即 MEMORY，表里存放后端 db 实例、用户验证、路由规则等信息

disk : 持久化的磁盘的配置

stats : 统计信息的汇总

monitor : 一些监控的收集信息，比如数据库的健康状态等

stats_history : 这个库是 ProxySQL 收集的有关其内部功能的历史指标

二、配置PrxoySQL以连接MGR的单主模式

1.在MGR主节点执行下面SQL语句，创建视图提供给ProxySQL判断该节点状态

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 ;

2.到各个节点检查视图是否创建成功?

mysql> SELECT * FROM sys.gr_member_routing_candidate_status;

+------------------+-----------+---------------------+----------------------+

+------------------+-----------+---------------------+----------------------+

| YES | YES | 0 | 0 |

+------------------+-----------+---------------------+----------------------+

1 row in set (0.01 sec)

3.在MGR主节点执行下面SQL语句,创建监控用户用于ProxySQL监控数据库状态

mysql> create user 'monitor'@'%' identified by 'Test.123';

Query OK, 0 rows affected (0.00 sec)

mysql> grant select on sys.* to 'monitor'@'%';

Query OK, 0 rows affected (0.01 sec)

4.在MGR主节点创建用户，用于ProxySQL访问

mysql> create user 'proxysql'@'%' identified by 'Test.123';

Query OK, 0 rows affected (0.00 sec)

mysql> grant all on *.* to 'proxysql'@'%';

Query OK, 0 rows affected (0.01 sec)

5.在ProxySQL节点，通过mysql_servers添加后端节点

[root@mgr04 mysql]# mysql -uadmin -padmin -h 127.0.0.1 -P 6032

mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 3

Server version: 5.5.30 (ProxySQL Admin Module)

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values (10,'192.168.43.141',3306,1,3000,10,'mgr01');

Query OK, 1 row affected (0.00 sec)

mysql> insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values (10,'192.168.43.142',3306,1,3000,10,'mgr02');

Query OK, 1 row affected (0.00 sec)

mysql> insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values (10,'192.168.43.143',3306,1,3000,10,'mgr03');

Query OK, 1 row affected (0.00 sec)

# 将mysql_servers表加载到runtime

mysql> LOAD mysql users TO RUNTIME;

Query OK, 0 rows affected (0.00 sec)

# 将mysql_servers表保存到磁盘

mysql> SAVE mysql servers TO DISK;

Query OK, 0 rows affected (0.08 sec)

mysql> select * from mysql_servers;

+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+

+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+

| 10 | 192.168.43.141 | 3306 | 0 | ONLINE | 1 | 0 | 3000 | 10 | 0 | 0 | mgr01 |

| 10 | 192.168.43.142 | 3306 | 0 | ONLINE | 1 | 0 | 3000 | 10 | 0 | 0 | mgr02 |

| 10 | 192.168.43.143 | 3306 | 0 | ONLINE | 1 | 0 | 3000 | 10 | 0 | 0 | mgr03 |

+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+

3 rows in set (0.00 sec)

6.设置监控用户账户密码

mysql> set mysql-monitor_username='monitor';

Query OK, 1 row affected (0.00 sec)

mysql> set mysql-monitor_password='Test.123';

Query OK, 1 row affected (0.00 sec)

mysql> LOAD mysql variables TO RUNTIME;

Query OK, 0 rows affected (0.00 sec)

mysql> SAVE mysql variables TO DISK;

Query OK, 134 rows affected (0.01 sec)

7.设置提供访问的用户

mysql> insert into mysql_users(username,password,active,default_hostgroup,transaction_persistent)values('proxysql','Test.123',1,10,1);

Query OK, 1 row affected (0.00 sec)

mysql> load mysql users to runtime;

Query OK, 0 rows affected (0.00 sec)

mysql> save mysql users to disk;

Query OK, 0 rows affected (0.02 sec)

8.配置mysql_group_replication_hostgroups表

mysql> 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,0);

Query OK, 1 row affected (0.00 sec)

mysql> load mysql servers to runtime;

Query OK, 0 rows affected (0.00 sec)

mysql> save mysql servers to disk;

Query OK, 0 rows affected (0.05 sec)

9.查看后端节点健康状态

mysql> SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 10 ;

+----------------+------+------------------+-------------------------+---------------+

+----------------+------+------------------+-------------------------+---------------+

| 192.168.43.143 | 3306 | 1597987440502669 | 2076 | NULL |

| 192.168.43.142 | 3306 | 1597987439970645 | 2235 | NULL |

| 192.168.43.141 | 3306 | 1597987439438378 | 886 | NULL |

| 192.168.43.141 | 3306 | 1597987380312209 | 1093 | NULL |

| 192.168.43.142 | 3306 | 1597987379875226 | 1284 | NULL |

| 192.168.43.143 | 3306 | 1597987379438351 | 1284 | NULL |

| 192.168.43.141 | 3306 | 1597987320560223 | 3088 | NULL |

| 192.168.43.143 | 3306 | 1597987319998252 | 989 | NULL |

| 192.168.43.142 | 3306 | 1597987319437814 | 1395 | NULL |

| 192.168.43.141 | 3306 | 1597987260922777 | 4105 | NULL |

+----------------+------+------------------+-------------------------+---------------+

10 rows in set (0.00 sec)

mysql> SELECT * FROM monitor.mysql_server_ping_log ORDER BY time_start_us DESC LIMIT 10;

+----------------+------+------------------+----------------------+------------+

+----------------+------+------------------+----------------------+------------+

| 192.168.43.141 | 3306 | 1597987469601397 | 1691 | NULL |

| 192.168.43.143 | 3306 | 1597987469519949 | 349 | NULL |

| 192.168.43.142 | 3306 | 1597987469438543 | 768 | NULL |

| 192.168.43.142 | 3306 | 1597987459675310 | 671 | NULL |

| 192.168.43.141 | 3306 | 1597987459556393 | 414 | NULL |

| 192.168.43.143 | 3306 | 1597987459437902 | 339 | NULL |

| 192.168.43.143 | 3306 | 1597987449595285 | 412 | NULL |

| 192.168.43.142 | 3306 | 1597987449515932 | 650 | NULL |

| 192.168.43.141 | 3306 | 1597987449437004 | 200 | NULL |

| 192.168.43.142 | 3306 | 1597987439659653 | 549 | NULL |

+----------------+------+------------------+----------------------+------------+

10 rows in set (0.00 sec)

10.查看MGR配置

mysql> select * from mysql_group_replication_hostgroups\G;

*************************** 1. row ***************************

writer_hostgroup: 10

backup_writer_hostgroup: 20

reader_hostgroup: 30

offline_hostgroup: 40

active: 1

max_writers: 1

writer_is_also_reader: 0

max_transactions_behind: 0

comment: NULL

1 row in set (0.00 sec)

------------------------------------------------------------------------------------------------

三、读写分离

1.规划读写组

默认的写组 | 后备的写组 | 读组 | 离线组

10 20 30 40

2.设置读写分离规则

mysql> insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(1,1,'^SELECT.*FOR UPDATE$',10,1);

Query OK, 1 row affected (0.00 sec)

mysql> insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(2,1,'^SELECT',30,1);

Query OK, 1 row affected (0.00 sec)

mysql> load mysql query rules to runtime;

Query OK, 0 rows affected (0.00 sec)

mysql> save mysql query rules to disk;

Query OK, 0 rows affected (0.03 sec)

3.测试读场景

[root@mgr04 mysql]# for i in `seq 1 10`; do mysql -u'proxysql' -p'Test.123' -h127.0.0.1 -P6033 -e 'select * from performance_schema.global_variables where variable_name="server_id";' ; done | grep server

mysql: [Warning] Using a password on the command line interface can be insecure.

server_id 2

mysql: [Warning] Using a password on the command line interface can be insecure.

server_id 3

mysql: [Warning] Using a password on the command line interface can be insecure.

server_id 2

mysql: [Warning] Using a password on the command line interface can be insecure.

server_id 3

mysql: [Warning] Using a password on the command line interface can be insecure.

server_id 3

mysql: [Warning] Using a password on the command line interface can be insecure.

server_id 2

mysql: [Warning] Using a password on the command line interface can be insecure.

server_id 3

mysql: [Warning] Using a password on the command line interface can be insecure.

server_id 2

mysql: [Warning] Using a password on the command line interface can be insecure.

server_id 3

mysql: [Warning] Using a password on the command line interface can be insecure.

server_id 3

观察可知：读操作是均分布到MGR02和MGR03上的（MGR02 -> server_id=2 ,MGR03 -> server_id=3）

4.测试写场景

mysql: [Warning] Using a password on the command line interface can be insecure.

server_id 1

mysql: [Warning] Using a password on the command line interface can be insecure.

server_id 1

mysql: [Warning] Using a password on the command line interface can be insecure.

server_id 1

mysql: [Warning] Using a password on the command line interface can be insecure.

server_id 1

mysql: [Warning] Using a password on the command line interface can be insecure.

server_id 1

mysql: [Warning] Using a password on the command line interface can be insecure.

server_id 1

mysql: [Warning] Using a password on the command line interface can be insecure.

server_id 1

mysql: [Warning] Using a password on the command line interface can be insecure.

server_id 1

mysql: [Warning] Using a password on the command line interface can be insecure.

server_id 1

mysql: [Warning] Using a password on the command line interface can be insecure.

server_id 1

观察可知：写操落到MGR01上（MGR01 -> server_id=1）

5.插入测试

MGR01上创建数据库和测试表：

mysql> create database mgr_test;

Query OK, 1 row affected (0.00 sec)

mysql> use mgr_test;

Database changed

mysql> create table t(id int primary key);

Query OK, 0 rows affected (0.04 sec)

ProxySQL插入数据：

[root@mgr04 mysql]# for i in `seq 1 10`; do mysql -u'proxysql' -p'Test.123' -h127.0.0.1 -P6033 -e "insert into mgr_test.t values($i)" ; done

mysql: [Warning] Using a password on the command line interface can be insecure.

MGR01,MGR02,MGR03上查看表数据：

mysql> select * from mgr_test.t;

+----+

| id |

+----+

| 1 |

| 2 |

| 3 |

| 4 |

| 5 |

| 6 |

| 7 |

| 8 |

| 9 |

| 10 |

+----+

10 rows in set (0.00 sec)

------------------------------------------------------------------------------------------------

四、MGR故障转移后的ProxySQL测试

1.关闭MGR01(主)的MySQL服务

[root@mgr01 opt]# /etc/init.d/mysqld stop

Stopping mysqld: [ OK ]

[root@mgr01 opt]#

MGR02的日志是这样的：

2020-08-21T06:12:43.820977Z 0 [Warning] Plugin group_replication reported: 'Members removed from the group: 192.168.43.141:3306'

2020-08-21T06:12:43.821024Z 0 [Note] Plugin group_replication reported: 'Primary server with address 192.168.43.141:3306 left the group. Electing new Primary.'

2020-08-21T06:12:43.821115Z 0 [Note] Plugin group_replication reported: 'A new primary with address 192.168.43.142:3306 was elected, enabling conflict detection until the new primary applies all relay logs.'

2020-08-21T06:12:43.821170Z 593 [Note] Plugin group_replication reported: 'This server is working as primary member.'

2020-08-21T06:12:43.821206Z 0 [Note] Plugin group_replication reported: 'Group membership changed to 192.168.43.142:3306, 192.168.43.143:3306 on view 15979849542410876:4.'

分析日志：我们发现MGR01挂掉后，MGR02成功担选Primary server。

2.在ProxySQL上进行读写测试

mysql: [Warning] Using a password on the command line interface can be insecure.

server_id 3

mysql: [Warning] Using a password on the command line interface can be insecure.

server_id 3

mysql: [Warning] Using a password on the command line interface can be insecure.

server_id 3

mysql: [Warning] Using a password on the command line interface can be insecure.

server_id 3

mysql: [Warning] Using a password on the command line interface can be insecure.

server_id 3

mysql: [Warning] Using a password on the command line interface can be insecure.

server_id 3

mysql: [Warning] Using a password on the command line interface can be insecure.

server_id 3

mysql: [Warning] Using a password on the command line interface can be insecure.

server_id 3

mysql: [Warning] Using a password on the command line interface can be insecure.

server_id 3

mysql: [Warning] Using a password on the command line interface can be insecure.

server_id 3

mysql: [Warning] Using a password on the command line interface can be insecure.

server_id 2

mysql: [Warning] Using a password on the command line interface can be insecure.

server_id 2

mysql: [Warning] Using a password on the command line interface can be insecure.

server_id 2

mysql: [Warning] Using a password on the command line interface can be insecure.

server_id 2

mysql: [Warning] Using a password on the command line interface can be insecure.

server_id 2

mysql: [Warning] Using a password on the command line interface can be insecure.

server_id 2

mysql: [Warning] Using a password on the command line interface can be insecure.

server_id 2

mysql: [Warning] Using a password on the command line interface can be insecure.

server_id 2

mysql: [Warning] Using a password on the command line interface can be insecure.

server_id 2

mysql: [Warning] Using a password on the command line interface can be insecure.

server_id 2

[root@mgr04 mysql]# for i in `seq 20 30`; do mysql -u'proxysql' -p'Test.123' -h127.0.0.1 -P6033 -e "insert into mgr_test.t values($i)" ; done mysql: [Warning] Using a password on the command line interface can be insecure.

mysql: [Warning] Using a password on the command line interface can be insecure.

[root@mgr04 mysql]#

mysql> select * from mgr_test.t;

+----+

| id |

+----+

| 1 |

| 2 |

| 3 |

| 4 |

| 5 |

| 6 |

| 7 |

| 8 |

| 9 |

| 10 |

| 20 |

| 21 |

| 22 |

| 23 |

| 24 |

| 25 |

| 26 |

| 27 |

| 28 |

| 29 |

| 30 |

+----+

21 rows in set (0.00 sec)

观察可知：现在读写完整，读是在MGR03上进行，写是在MGR02上进行。

3.恢复MGR01的MySQL服务继续测试

[root@mgr01 opt]# /etc/init.d/mysqld start

Starting mysqld: [ OK ]

mysql> START GROUP_REPLICATION;

Query OK, 0 rows affected (2.87 sec)

mysql> START GROUP_REPLICATION;

ERROR 3093 (HY000): The START GROUP_REPLICATION command failed since the group is already running.

mysql> SELECT * FROM performance_schema.replication_group_members;

+---------------------------+--------------------------------------+----------------+-------------+--------------+

+---------------------------+--------------------------------------+----------------+-------------+--------------+

+---------------------------+--------------------------------------+----------------+-------------+--------------+

3 rows in set (0.00 sec)

mysql: [Warning] Using a password on the command line interface can be insecure.

server_id 1

mysql: [Warning] Using a password on the command line interface can be insecure.

server_id 3

mysql: [Warning] Using a password on the command line interface can be insecure.

server_id 1

mysql: [Warning] Using a password on the command line interface can be insecure.

server_id 1

mysql: [Warning] Using a password on the command line interface can be insecure.

server_id 3

mysql: [Warning] Using a password on the command line interface can be insecure.

server_id 3

mysql: [Warning] Using a password on the command line interface can be insecure.

server_id 3

mysql: [Warning] Using a password on the command line interface can be insecure.

server_id 1

mysql: [Warning] Using a password on the command line interface can be insecure.

server_id 3

mysql: [Warning] Using a password on the command line interface can be insecure.

server_id 1

mysql: [Warning] Using a password on the command line interface can be insecure.

server_id 2

mysql: [Warning] Using a password on the command line interface can be insecure.

server_id 2

mysql: [Warning] Using a password on the command line interface can be insecure.

server_id 2

mysql: [Warning] Using a password on the command line interface can be insecure.

server_id 2

mysql: [Warning] Using a password on the command line interface can be insecure.

server_id 2

mysql: [Warning] Using a password on the command line interface can be insecure.

server_id 2

mysql: [Warning] Using a password on the command line interface can be insecure.

server_id 2

mysql: [Warning] Using a password on the command line interface can be insecure.

server_id 2

mysql: [Warning] Using a password on the command line interface can be insecure.

server_id 2

mysql: [Warning] Using a password on the command line interface can be insecure.

server_id 2

[root@mgr04 mysql]#

观察可知：MGR01恢复后变为只读模式，MGR02依然是写模式。

参阅：

https://github.com/sysown/proxysql/wiki

https://www.cnblogs.com/keme/p/12290977.html

https://blog.csdn.net/L835311324/article/details/91126335

https://www.cnblogs.com/f-ck-need-u/category/1252812.html

ProxySQL + MGR单主模式的测试文档

拓扑图介绍：

一、安装启动PrxoySQL

1.下载并安装ProxySQL

2.启动ProxySQL

3.安装mysql客户端

4.登录PrxoySQL进行观察

二、配置PrxoySQL以连接MGR的单主模式

1.在MGR主节点执行下面SQL语句，创建视图提供给ProxySQL判断该节点状态

2.到各个节点检查视图是否创建成功?

3.在MGR主节点执行下面SQL语句,创建监控用户用于ProxySQL监控数据库状态

4.在MGR主节点创建用户，用于ProxySQL访问

5.在ProxySQL节点，通过mysql_servers添加后端节点

# 将mysql_servers表加载到runtime

# 将mysql_servers表保存到磁盘

6.设置监控用户账户密码

7.设置提供访问的用户

8.配置mysql_group_replication_hostgroups表

9.查看后端节点健康状态

10.查看MGR配置

三、读写分离

1.规划读写组

2.设置读写分离规则

3.测试读场景

4.测试写场景

5.插入测试

四、MGR故障转移后的ProxySQL测试

1.关闭MGR01(主)的MySQL服务

2.在ProxySQL上进行读写测试

3.恢复MGR01的MySQL服务继续测试

参阅：

更多：