签到成功

知道了

CNDBA社区CNDBA社区

MySQL 高可用架构: ProxySQL + MGR(单主模式) 搭建手册

2022-05-29 09:57 2330 0 原创 MySQL
作者: dave

在之前的博客中,我们陆续介绍了MySQL 的几种高可用架构,如下:

MySQL 高可用架构:双主 + keepalived 搭建手册
https://www.cndba.cn/dave/article/108023

MySQL 高可用架构:双主+ haproxy + keepalived
https://www.cndba.cn/dave/article/108024

MySQL 5.7 主主 双向复制 环境搭建手册
https://www.cndba.cn/dave/article/108022https://www.cndba.cn/cndba/dave/article/108031

MySQL 高可用架构:双主 + keepalived 搭建手册
https://www.cndba.cn/dave/article/108023

MySQL 高可用架构: MGR 搭建手册
https://www.cndba.cn/dave/article/108030

本篇继续MGR 架构的延续:ProxySQL + MGR。

1 为什么要采用ProxySQL + MGR 架构


其实从容灾角度来说,MGR 是满足,但是MGR 本身无法做到无感知切换。 所以我们需要通过和ProxySQL 一起使用,来满足故障的无感知切换 。

https://www.cndba.cn/cndba/dave/article/108031

思路大致如下:三个数据库节点使用单主模式的主复制,应用连接ProxySQL读写后端数据库节点,ProxySQL根据SQL的读写性质,分发到后端对应的数据库节点,如果后端MGR主节点down了,ProxySQL可以做到自动切换,将MGR选举出来的新主作为新的可写节点整个过程应用无需任何变动,整个切换过程秒级别的间隔

2 MGR 环境搭建


这里不再过多描述,直接参考之前的博客:

https://www.cndba.cn/cndba/dave/article/108031

MySQL 高可用架构: MGR 说明
https://www.cndba.cn/dave/article/108027

MySQL 高可用架构: MGR 搭建手册
https://www.cndba.cn/dave/article/108030

当前的MGR 是单主模式,主库是mongod1:

mysql>  SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | a8dcf7b7-de93-11ec-a313-fa163e2997dc | mongodb2    |        3406 | ONLINE       |
| group_replication_applier | d0088f31-de93-11ec-8f5b-fa163ede7970 | mongodb3    |        3406 | ONLINE       |
| group_replication_applier | eadc7c4e-de90-11ec-a79a-fa163e7035c7 | mongodb1    |        3406 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.03 sec)

mysql> show status like 'group_replication_primary_member';
+----------------------------------+--------------------------------------+
| Variable_name                    | Value                                |
+----------------------------------+--------------------------------------+
| group_replication_primary_member | eadc7c4e-de90-11ec-a79a-fa163e7035c7 |
+----------------------------------+--------------------------------------+
1 row in set (0.00 sec)

mysql>

3 ProxySQL 部署


3.1 安装ProxySQL

我们这里不复用资源,直接将ProxySQL 安装到mongodb4上:

[root@mongodb3 ~]# cat /etc/hosts
127.0.0.1   localhost
172.31.185.120 mongodb1
172.31.185.165 mongodb2
172.31.185.131 mongodb3
172.30.34.0 mongodb4
[root@mongodb3 ~]#

下载地址:

https://proxysql.com/documentation/installing-proxysql/
[root@www.cndba.cn ~]# wget https://github.com/sysown/proxysql/releases/download/v2.3.2/proxysql-2.3.2-1-centos7.x86_64.rpm

注意这里使用的是yum 来安装rpm ,会自动解决依赖关系:https://www.cndba.cn/cndba/dave/article/108031

[root@www.cndba.cn ~]# yum install -y proxysql-2.3.2-1-centos7.x86_64.rpm
Loaded plugins: fastestmirror, langpacks
Examining proxysql-2.3.2-1-centos7.x86_64.rpm: proxysql-2.3.2-1.x86_64
Marking proxysql-2.3.2-1-centos7.x86_64.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package proxysql.x86_64 0:2.3.2-1 will be installed
--> Processing Dependency: perl(DBD::mysql) for package: proxysql-2.3.2-1.x86_64
Loading mirror speeds from cached hostfile
--> Running transaction check
---> Package perl-DBD-MySQL.x86_64 0:4.023-6.el7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

===================================================================================================================================================
 Package                          Arch                     Version                        Repository                                          Size
===================================================================================================================================================
Installing:
 proxysql                         x86_64                   2.3.2-1                        /proxysql-2.3.2-1-centos7.x86_64                    58 M
Installing for dependencies:
 perl-DBD-MySQL                   x86_64                   4.023-6.el7                    c7.8                                               140 k

Transaction Summary
===================================================================================================================================================
Install  1 Package (+1 Dependent package)

Total size: 58 M
Total download size: 140 k
Installed size: 59 M
Downloading packages:
perl-DBD-MySQL-4.023-6.el7.x86_64.rpm                                                                                       | 140 kB  00:00:00
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : perl-DBD-MySQL-4.023-6.el7.x86_64                                                                                               1/2
  Installing : proxysql-2.3.2-1.x86_64                                                                                                         2/2
warning: group proxysql does not exist - using root
warning: group proxysql does not exist - using root
Created symlink from /etc/systemd/system/multi-user.target.wants/proxysql.service to /etc/systemd/system/proxysql.service.
  Verifying  : perl-DBD-MySQL-4.023-6.el7.x86_64                                                                                               1/2
  Verifying  : proxysql-2.3.2-1.x86_64                                                                                                         2/2

Installed:
  proxysql.x86_64 0:2.3.2-1

Dependency Installed:
  perl-DBD-MySQL.x86_64 0:4.023-6.el7

Complete!
[root@www.cndba.cn ~]#

启动ProxySQL:

[root@www.cndba.cn ~]# service proxysql start
Redirecting to /bin/systemctl start proxysql.service
[root@www.cndba.cn ~]# service proxysql status
Redirecting to /bin/systemctl status proxysql.service
● proxysql.service - High Performance Advanced Proxy for MySQL
   Loaded: loaded (/etc/systemd/system/proxysql.service; enabled; vendor preset: disabled)
   Active: active (running) since Sun 2022-05-29 08:49:28 CST; 6s ago
  Process: 14482 ExecStart=/usr/bin/proxysql --idle-threads -c /etc/proxysql.cnf $PROXYSQL_OPTS (code=exited, status=0/SUCCESS)
 Main PID: 14488 (proxysql)
    Tasks: 24
   CGroup: /system.slice/proxysql.service
           ├─14488 /usr/bin/proxysql --idle-threads -c /etc/proxysql.cnf
           └─14489 /usr/bin/proxysql --idle-threads -c /etc/proxysql.cnf

May 29 08:49:28 dm8 systemd[1]: Starting High Performance Advanced Proxy for MySQL...
May 29 08:49:28 dm8 proxysql[14482]: 2022-05-29 08:49:28 [INFO] Using config file /etc/proxysql.cnf
May 29 08:49:28 dm8 proxysql[14482]: 2022-05-29 08:49:28 [INFO] Current RLIMIT_NOFILE: 102400
May 29 08:49:28 dm8 proxysql[14482]: 2022-05-29 08:49:28 [INFO] Using OpenSSL version: OpenSSL 1.1.1j  16 Feb 2021
May 29 08:49:28 dm8 proxysql[14482]: 2022-05-29 08:49:28 [INFO] No SSL keys/certificates found in datadir (/var/lib/proxysql). Generatin...ficates.
May 29 08:49:28 dm8 systemd[1]: Started High Performance Advanced Proxy for MySQL.
Hint: Some lines were ellipsized, use -l to show in full.
[root@www.cndba.cn ~]#

3.2 配置ProxySQL

3.2.1 创建相关函数(MGR 主节点执行)

在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 ;

到各个节点检查视图是否创建成功:

mysql> 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.00 sec)

mysql> SELECT * FROM sys.gr_member_routing_candidate_status;
+------------------+-----------+---------------------+----------------------+
| viable_candidate | read_only | transactions_behind | transactions_to_cert |
+------------------+-----------+---------------------+----------------------+
| YES              | YES       |                   0 |                    0 |
+------------------+-----------+---------------------+----------------------+
1 row in set (0.01 sec)

3.2.2 创建相关用户(MGR主节点)

在MGR主节点执行下面SQL语句,创建监控用户用于ProxySQL监控数据库状态:https://www.cndba.cn/cndba/dave/article/108031

mysql> create user 'monitor'@'%' identified by 'monitor';
Query OK, 0 rows affected (0.02 sec)

mysql> grant select on sys.* to 'monitor'@'%';
Query OK, 0 rows affected (0.01 sec)

在MGR主节点创建用户,用于ProxySQL访问

mysql> create user 'proxysql'@'%' identified by 'proxysql';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all on *.* to 'proxysql'@'%';
Query OK, 0 rows affected (0.00 sec)

3.2.3 连接ProxySQL并进行相关限制

登入管理端口(默认管理用户admin,密码admin,管理端口6032,客户端口6033)

[mysql@www.cndba.cn ~]$ mysql -uadmin -padmin --prompt='proxysql> ' -P6032 -h127.0.0.1
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 2
Server version: 5.5.30 (ProxySQL Admin Module)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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.

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

proxysql>

在mysql_servers添加后端节点:

proxysql> insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values (10,'172.31.185.120',3406,1,3000,10,'mgr_node1');
Query OK, 1 row affected (0.00 sec)

proxysql> insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values (10,'172.31.185.165',3406,1,3000,10,'mgr_node2');
Query OK, 1 row affected (0.00 sec)

proxysql> insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values (10,'172.31.185.131',3406,1,3000,10,'mgr_node3');
Query OK, 1 row affected (0.00 sec)

proxysql>
# 将mysql_servers表加载到runtime
proxysql>  LOAD mysql users TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)

# 将mysql_servers表保存到磁盘
proxysql> SAVE mysql servers TO DISK;
Query OK, 0 rows affected (0.04 sec)

proxysql>

设置监控用户账户密码

proxysql> set mysql-monitor_username='monitor';
Query OK, 1 row affected (0.00 sec)

proxysql> set mysql-monitor_password='monitor';
Query OK, 1 row affected (0.00 sec)

proxysql> LOAD mysql variables TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)

proxysql>  SAVE mysql variables TO DISK;
Query OK, 147 rows affected (0.01 sec)

proxysql>

设置提供访问的用户https://www.cndba.cn/cndba/dave/article/108031

proxysql> insert into mysql_users(username,password,active,default_hostgroup,transaction_persistent)values('proxysql','proxysql',1,10,1);
Query OK, 1 row affected (0.00 sec)

proxysql>  load mysql users to runtime;
Query OK, 0 rows affected (0.00 sec)

proxysql> save mysql users to disk;
Query OK, 0 rows affected (0.01 sec)

配置mysql_group_replication_hostgroups表:

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(10,20,30,40,1,1,0,0);
Query OK, 1 row affected (0.00 sec)

proxysql> load mysql servers to runtime;
Query OK, 0 rows affected (0.01 sec)

proxysql> save mysql servers to disk;
Query OK, 0 rows affected (0.05 sec)

设置读写分离规则

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

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

proxysql> load mysql query rules to runtime;
Query OK, 0 rows affected (0.00 sec)

proxysql> save mysql query rules to disk;
Query OK, 0 rows affected (0.02 sec)

3.3 查看相关状态

3.3.1 查看后端节点健康状态

proxysql> SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 10 ;
+----------------+------+------------------+-------------------------+---------------+
| hostname       | port | time_start_us    | connect_success_time_us | connect_error |
+----------------+------+------------------+-------------------------+---------------+
| 172.31.185.165 | 3406 | 1653786847191915 | 849                     | NULL          |
| 172.31.185.120 | 3406 | 1653786846487403 | 893                     | NULL          |
| 172.31.185.131 | 3406 | 1653786845782853 | 776                     | NULL          |
| 172.31.185.165 | 3406 | 1653786786615714 | 1033                    | NULL          |
| 172.31.185.120 | 3406 | 1653786786199228 | 822                     | NULL          |
| 172.31.185.131 | 3406 | 1653786785782791 | 1324                    | NULL          |
| 172.31.185.165 | 3406 | 1653786727321063 | 1084                    | NULL          |
| 172.31.185.131 | 3406 | 1653786726551944 | 6977                    | NULL          |
| 172.31.185.120 | 3406 | 1653786725782856 | 1190                    | NULL          |
| 172.31.185.131 | 3406 | 1653786667163748 | 1483                    | NULL          |
+----------------+------+------------------+-------------------------+---------------+
10 rows in set (0.00 sec)

proxysql> SELECT * FROM monitor.mysql_server_ping_log ORDER BY time_start_us DESC LIMIT 10;
+----------------+------+------------------+----------------------+------------+
| hostname       | port | time_start_us    | ping_success_time_us | ping_error |
+----------------+------+------------------+----------------------+------------+
| 172.31.185.131 | 3406 | 1653786906054353 | 201                  | NULL       |
| 172.31.185.120 | 3406 | 1653786905948161 | 277                  | NULL       |
| 172.31.185.165 | 3406 | 1653786905841942 | 282                  | NULL       |
| 172.31.185.165 | 3406 | 1653786895998378 | 172                  | NULL       |
| 172.31.185.120 | 3406 | 1653786895920175 | 287                  | NULL       |
| 172.31.185.131 | 3406 | 1653786895841918 | 321                  | NULL       |
| 172.31.185.165 | 3406 | 1653786886066497 | 236                  | NULL       |
| 172.31.185.131 | 3406 | 1653786885954183 | 261                  | NULL       |
| 172.31.185.120 | 3406 | 1653786885841835 | 279                  | NULL       |
| 172.31.185.131 | 3406 | 1653786876019542 | 222                  | NULL       |
+----------------+------+------------------+----------------------+------------+
10 rows in set (0.00 sec)

3.3.2 查看MGR配置

proxysql> 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   # 决定一个节点升级为写节点(放进writer_hostgroup)后是否仍然保留在reader_hostgroup组中提供读服务。如果mgr多主模式需要设置为1
max_transactions_behind: 0   #  该字段决定最多延后写节点多少个事务
                comment: NULL # 注释
1 row in set (0.00 sec)

3.3.3 查看MGR相关的监控指标

proxysql> select * from mysql_server_group_replication_log desc limit 10;
+----------------+------+------------------+-----------------+------------------+-----------+---------------------+-------+
| hostname       | port | time_start_us    | success_time_us | viable_candidate | read_only | transactions_behind | error |
+----------------+------+------------------+-----------------+------------------+-----------+---------------------+-------+
| 172.31.185.120 | 3406 | 1653786786000097 | 4668            | YES              | NO        | 0                   | NULL  |
| 172.31.185.120 | 3406 | 1653786791000148 | 4335            | YES              | NO        | 0                   | NULL  |
| 172.31.185.120 | 3406 | 1653786796000175 | 2984            | YES              | NO        | 0                   | NULL  |
| 172.31.185.120 | 3406 | 1653786801000345 | 2492            | YES              | NO        | 0                   | NULL  |
| 172.31.185.120 | 3406 | 1653786806000289 | 7286            | YES              | NO        | 0                   | NULL  |
| 172.31.185.120 | 3406 | 1653786811000409 | 3906            | YES              | NO        | 0                   | NULL  |
| 172.31.185.120 | 3406 | 1653786816000431 | 3699            | YES              | NO        | 0                   | NULL  |
| 172.31.185.120 | 3406 | 1653786821000562 | 7134            | YES              | NO        | 0                   | NULL  |
| 172.31.185.120 | 3406 | 1653786826000546 | 2377            | YES              | NO        | 0                   | NULL  |
| 172.31.185.120 | 3406 | 1653786831000694 | 3771            | YES              | NO        | 0                   | NULL  |
+----------------+------+------------------+-----------------+------------------+-----------+---------------------+-------+
10 rows in set (0.00 sec)

4 读写分离测试


4.1 测试读场景

注意这里连的是ProxySQL 的节点:

[mysql@www.cndba.cn ~]$ for i in `seq 1 10`; do mysql -uproxysql -pproxysql -h172.30.34.0 -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       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       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@www.cndba.cn ~]$

这里的读操作默认都到了2和3上.

4.2 测试写场景

同样连接的是ProxySQL 节点:https://www.cndba.cn/cndba/dave/article/108031

[mysql@www.cndba.cn ~]$ for i in `seq 1 10`; do mysql -uproxysql -pproxysql -h172.30.34.0 -P6033 -e 'select * from performance_schema.global_variables where variable_name="server_id" for update;' ; done  | grep server
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
[mysql@www.cndba.cn ~]$

For update操作都打到主节点上了。

连接ProxySQL执行写入:

[mysql@www.cndba.cn ~]$ for i in `seq 2 11`; do mysql -uproxysql -pproxysql -h172.30.34.0 -P6033 -e "insert into cndba.cndba values($i,$i+1)" ; 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.
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.
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.
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.
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.
[mysql@www.cndba.cn ~]$

到从库 查询:

mysql> select * from cndba.cndba;
+----+--------------------------+
| id | url                      |
+----+--------------------------+
|  1 | http://www.cndba.cn/dave |
|  2 | 3                        |
|  3 | 4                        |
|  4 | 5                        |
|  5 | 6                        |
|  6 | 7                        |
|  7 | 8                        |
|  8 | 9                        |
|  9 | 10                       |
| 10 | 11                       |
| 11 | 12                       |
+----+--------------------------+
11 rows in set (0.00 sec)

5 MGR 故障转移测试


5.1 关闭主库

mysql> shutdown;
Query OK, 0 rows affected (0.00 sec)

此时的主库变成了节点2:https://www.cndba.cn/cndba/dave/article/108031

https://www.cndba.cn/cndba/dave/article/108031

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | a8dcf7b7-de93-11ec-a313-fa163e2997dc | mongodb2    |        3406 | ONLINE       |
| group_replication_applier | d0088f31-de93-11ec-8f5b-fa163ede7970 | mongodb3    |        3406 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
2 rows in set (0.00 sec)

mysql> show status like 'group_replication_primary_member';
+----------------------------------+--------------------------------------+
| Variable_name                    | Value                                |
+----------------------------------+--------------------------------------+
| group_replication_primary_member | a8dcf7b7-de93-11ec-a313-fa163e2997dc |
+----------------------------------+--------------------------------------+
1 row in set (0.00 sec)

mysql>

测试ProxySQL中已经排除了节点1:

[mysql@www.cndba.cn ~]$  mysql -uadmin -padmin -P6032 -h127.0.0.1 -e "select hostgroup_id, hostname, port,status from runtime_mysql_servers;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------+----------------+------+---------+
| hostgroup_id | hostname       | port | status  |
+--------------+----------------+------+---------+
| 10           | 172.31.185.165 | 3406 | ONLINE  |
| 30           | 172.31.185.131 | 3406 | ONLINE  |
| 20           | 172.31.185.120 | 3406 | SHUNNED |
+--------------+----------------+------+---------+
[mysql@www.cndba.cn ~]$

5.2 执行写入操作

[mysql@www.cndba.cn ~]$ for i in `seq 12 22`; do mysql -uproxysql -pproxysql -h172.30.34.0 -P6033 -e "insert into cndba.cndba values($i,$i+1)" ; 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.
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.
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.
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.
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.
mysql: [Warning] Using a password on the command line interface can be insecure.
[mysql@www.cndba.cn ~]$

[mysql@www.cndba.cn ~]$ mysql -uproxysql -pproxysql -h172.30.34.0 -P6033 -e "select * from cndba.cndba"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+--------------------------+
| id | url                      |
+----+--------------------------+
|  1 | http://www.cndba.cn/dave |
|  2 | 3                        |
|  3 | 4                        |
|  4 | 5                        |
|  5 | 6                        |
|  6 | 7                        |
|  7 | 8                        |
|  8 | 9                        |
|  9 | 10                       |
| 10 | 11                       |
| 11 | 12                       |
| 12 | 13                       |
| 13 | 14                       |
| 14 | 15                       |
| 15 | 16                       |
| 16 | 17                       |
| 17 | 18                       |
| 18 | 19                       |
| 19 | 20                       |
| 20 | 21                       |
| 21 | 22                       |
| 22 | 23                       |
+----+--------------------------+
[mysql@www.cndba.cn ~]$

此时通过ProxySQL 可以正常写入和查询。

5.3 重启节点1:

启动节点1的MySQL 和 复制组:

[mysql@mongodb1 ~]$ mysqld_safe --defaults-file=/data/mysql/3406/my3406.cnf  &
[1] 30576
[mysql@mongodb1 ~]$ mysql -uroot -proot -S /data/mysql/3406/mysql.sock
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 8
Server version: 5.7.33-log MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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> start group_replication;
Query OK, 0 rows affected (2.88 sec)

mysql>

查看成员状态,恢复正常:https://www.cndba.cn/cndba/dave/article/108031

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | a8dcf7b7-de93-11ec-a313-fa163e2997dc | mongodb2    |        3406 | ONLINE       |
| group_replication_applier | d0088f31-de93-11ec-8f5b-fa163ede7970 | mongodb3    |        3406 | ONLINE       |
| group_replication_applier | eadc7c4e-de90-11ec-a79a-fa163e7035c7 | mongodb1    |        3406 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.00 sec)

mysql>  show status like 'group_replication_primary_member';
+----------------------------------+--------------------------------------+
| Variable_name                    | Value                                |
+----------------------------------+--------------------------------------+
| group_replication_primary_member | a8dcf7b7-de93-11ec-a313-fa163e2997dc |
+----------------------------------+--------------------------------------+
1 row in set (0.00 sec)

mysql>

6 小结


通过测试,我们看到了ProxySQL的价值,即MGR底层的改变,并不影响业务,通过ProxySQL 可以做到读写分离和无感知的故障切换。

版权声明:本文为博主原创文章,未经博主允许不得转载。

用户评论
* 以下用户言论只代表其个人观点,不代表CNDBA社区的观点或立场
dave

dave

关注

人的一生应该是这样度过的:当他回首往事的时候,他不会因为虚度年华而悔恨,也不会因为碌碌无为而羞耻;这样,在临死的时候,他就能够说:“我的整个生命和全部精力,都已经献给世界上最壮丽的事业....."

  • 2241
    原创
  • 3
    翻译
  • 550
    转载
  • 187
    评论
  • 访问:6767737次
  • 积分:4258
  • 等级:核心会员
  • 排名:第1名
精华文章
    最新问题
    查看更多+
    热门文章
      热门用户
      推荐用户
        Copyright © 2016 All Rights Reserved. Powered by CNDBA · 皖ICP备2022006297号-1·

        QQ交流群

        注册联系QQ