ProxySQL新版本对MGR的原生支持-更加简单不依赖脚本

最新推荐文章于 2024-09-09 17:52:59 发布

zuozhiji

最新推荐文章于 2024-09-09 17:52:59 发布

阅读量2.7k

点赞数

分类专栏： MYSQL

本文链接：https://blog.csdn.net/jolly10/article/details/80418758

版权

MYSQL 专栏收录该内容

108 篇文章 3 订阅

订阅专栏

ProxySQL在MySQL的代理和负载均衡中一直处于领先地位。其中包含了诸如缓存查询，多路复用，流量镜像，读写分离，路由等等的强力功能。在最新的功能性增强中，包含了对MGR的原生支持，不在需要使用第三方脚本进行适配。

最新的增强中，提供了对单写和多写集群组的支持，甚至可以在多写组上指定只由某个成员进行写入操作。

在新版本的ProxySQL中，比如在一个七个节点的多写集群中，指定2组写节点，2组备用写节点，3个只读节点的操作。即ProxySQL虽然识别出来所有的节点皆为写节点，但只路由写操作到选定的两个写节点（通过Hostgroup的方式），同时将另外两个写节点添加到备用写节点组中，最后三个读节点加入读组。（本段中的组皆为ProxySQL中的hostgroup含义）。

除此之外，还可以限制连接访问集群中超出最大设定落后事务值的慢节点（应该是通过mysql_servers.max_replication_lag来控制）。

下面我们看下对于用户来说有哪些明显的变化，开始进行admin端口连接后会发现比之前多了一个mysql_group_replication_hostgroups表

mysql> show tables;
+--------------------------------------------+
| tables                                     |
+--------------------------------------------+
| global_variables                           |
| mysql_collations                           |
| mysql_group_replication_hostgroups         |
| mysql_query_rules                          |
| mysql_query_rules_fast_routing             |
| mysql_replication_hostgroups               |
| mysql_servers                              |
| mysql_users                                |
| proxysql_servers                           |
| runtime_checksums_values                   |
| runtime_global_variables                   |
| runtime_mysql_group_replication_hostgroups |
| runtime_mysql_query_rules                  |
| runtime_mysql_query_rules_fast_routing     |
| runtime_mysql_replication_hostgroups       |
| runtime_mysql_servers                      |
| runtime_mysql_users                        |
| runtime_proxysql_servers                   |
| runtime_scheduler                          |
| scheduler                                  |
+--------------------------------------------+
20 rows in set (0.00 sec)

下面来做一个测试，环境还是多主的组复制

qht131 172.17.61.131 master1

qht132 172.17.61.132 master2

qht133 172.17.61.133 master3

qht134 172.17.61.134 proxysql

1.先初始化Proxysql，将之前的proxysql数据都删除

delete from scheduler ;
delete from mysql_servers;
delete from mysql_users;
delete from mysql_query_rules;
delete from mysql_group_replication_hostgroups ;

LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;
LOAD SCHEDULER TO RUNTIME;
SAVE SCHEDULER TO DISK;
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

2.增加proxysql所需要的视图以及存储过程（我已运行过就跳过）

# mysql -p < addition_to_sys.sql

3.proxysql增加帐号

mysql> INSERT INTO MySQL_users(username,password,default_hostgroup) VALUES ('proxysql','proxysql',2);
Query OK, 1 row affected (0.00 sec)

mysql> UPDATE global_variables SET variable_value='proxysql' where variable_name='mysql-monitor_username';
Query OK, 1 row affected (0.01 sec)

mysql> UPDATE global_variables SET variable_value='proxysql' where variable_name='mysql-monitor_password';  
Query OK, 1 row affected (0.01 sec)

4.mysql_servers插入数据

mysql>  insert into mysql_servers (hostgroup_id, hostname, port) values(2,'172.17.61.131',3306);
Query OK, 1 row affected (0.00 sec)

mysql>  insert into mysql_servers (hostgroup_id, hostname, port) values(2,'172.17.61.132',3306);
Query OK, 1 row affected (0.00 sec)

mysql>  insert into mysql_servers (hostgroup_id, hostname, port) values(2,'172.17.61.133',3306);
Query OK, 1 row affected (0.00 sec)

mysql>  select * from mysql_servers;
+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname      | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 2            | 172.17.61.131 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 2            | 172.17.61.132 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 2            | 172.17.61.133 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.00 sec)

5.将MGR集群的分组定义和关键参数写入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 (2,4,3,1,1,1,0,100);
Query OK, 1 row affected (0.04 sec)

6.将上面对proxysql所有的变更都加载到环境中

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

7.接下来检查下ProxySQL是如何将MGR节点分发到ProxySQL各个组中：

表名前面多了一个runtime前缀

group2为writer group，group4为backup_writer_group,说明qht133为当前Proxysql连接的节点，其它两个为备用写节点

mysql> select hostgroup_id, hostname, status  from runtime_mysql_servers;
+--------------+---------------+--------+
| hostgroup_id | hostname      | status |
+--------------+---------------+--------+
| 2            | 172.17.61.133 | ONLINE |
| 4            | 172.17.61.132 | ONLINE |
| 4            | 172.17.61.131 | ONLINE |
+--------------+---------------+--------+
3 rows in set (0.03 sec)

8.如果qht133出现故障看能否切换到其它节点

将qht133设为read only

mysql> set global read_only=1;
Query OK, 0 rows affected (0.00 sec)

再看一下runtime_mysql_servers的状态，已将qht133设为group3了，group3为reader_group。现在可写的节点改成了qht132。


mysql>  select hostgroup_id, hostname, status  from runtime_mysql_servers;
+--------------+---------------+--------+
| hostgroup_id | hostname      | status |
+--------------+---------------+--------+
| 2            | 172.17.61.132 | ONLINE |
| 3            | 172.17.61.133 | ONLINE |
| 4            | 172.17.61.131 | ONLINE |
+--------------+---------------+--------+
3 rows in set (0.01 sec)

将qht133恢复为可写模式后，runtime_mysql_servers也恢复了过来

mysql> set global read_only=0;
Query OK, 0 rows affected (0.00 sec)

mysql>  select hostgroup_id, hostname, status  from runtime_mysql_servers;
+--------------+---------------+--------+
| hostgroup_id | hostname      | status |
+--------------+---------------+--------+
| 2            | 172.17.61.133 | ONLINE |
| 4            | 172.17.61.132 | ONLINE |
| 4            | 172.17.61.131 | ONLINE |
+--------------+---------------+--------+
3 rows in set (0.00 sec)

参考：

http://lefred.be/content/mysql-group-replication-native-support-in-proxysql/