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

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/

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值