MGR单主模式读写分离配置

实验环境信息 MySQL主从信息

IP 角色
188.188.0.68 Master
188.188.0.69 Slave
188.188.0.70 Slave

ProxySQL版本: 2.0.3-29-g00f26d5

MGR单主模式读写分离配置与传统主从读写分离配置相比,不同的地方主要为以下2点:

  • 1、主从复制关系在表mysql_group_replication_hostgroups,而不是mysql_replication_hostgroups表。
  • 2、后端的MySQL中,需要自建一个MGR状态信息视图:sys.gr_member_routing_candidate_status。

~ ~

》》》服务器、用户配置篇《《《

1、添加后端数据库服务器

Admin> SELECT * FROM mysql_servers;
Empty set (0.00 sec)

Admin> INSERT INTO mysql_servers(hostgroup_id,hostname,port,comment) 
    -> VALUES 
    -> (1,'188.188.0.68',3306,'Master'),
    -> (1,'188.188.0.69',3306,'Slave_1'),
    -> (1,'188.188.0.70',3306,'Slave_2');
Query OK, 3 rows affected (0.00 sec)

Admin> SELECT * FROM mysql_servers;
+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname     | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 1            | 188.188.0.68 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              | Master  |
| 1            | 188.188.0.69 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              | Slave_1 |
| 1            | 188.188.0.70 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              | Slave_2 |
+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.00 sec)

2、配置监控

1)在MySQL中创建监控依赖视图

由于对MGR得监控是依赖于sys.gr_member_routing_candidate_status视图得,而该视图并非系统自带,所以需要手动建立。 相关脚本地址:https://github.com/lefred/mysql_gr_routing_check/下得addition_to_sys.sql脚本。 登录MGR得主节点,在sys库执行脚本:

# mysql -uroot -p -S /data/database-3306/mysql-3306.sock sys < /root/mysql_gr_routing_check-master/addition_to_sys.sql

查看结果

mysql> show tables from sys like 'gr%';
+------------------------------------+
| Tables_in_sys (gr%)                |
+------------------------------------+
| gr_member_routing_candidate_status |
+------------------------------------+
1 row in set (0.00 sec)

视图已经建立。

2)添加ProXYSQL监视后MYSQL所需得用户(需要在MySQL Server中已创建该用户):

MySQL中创建设置监控用户:

mysql> GRANT select on sys.gr_member_routing_candidate_status TO 'psql_monitor'@'188.188.0.%' IDENTIFIED BY '987654';

mysql> show grants for 'psql_monitor'@'188.188.0.%' ;
+--------------------------------------------------------------------------------------------+
| Grants for psql_monitor@188.188.0.%                                                        |
+--------------------------------------------------------------------------------------------+
| GRANT PROCESS, REPLICATION CLIENT ON *.* TO 'psql_monitor'@'188.188.0.%'                   |
| GRANT SELECT ON `sys`.`gr_member_routing_candidate_status` TO 'psql_monitor'@'188.188.0.%' |
+--------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

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

查看在ProxySQL中配置用户:

Admin> select * from global_variables where variable_name='mysql-monitor_username';
+------------------------+----------------+
| variable_name          | variable_value |
+------------------------+----------------+
| mysql-monitor_username | monitor        |
+------------------------+----------------+
1 row in set (0.00 sec)

Admin> select * from global_variables where variable_name='mysql-monitor_password';
+------------------------+----------------+
| variable_name          | variable_value |
+------------------------+----------------+
| mysql-monitor_password | monitor        |
+------------------------+----------------+
1 row in set (0.00 sec)

配置监控用户:

Admin> UPDATE global_variables SET variable_value='psql_monitor' WHERE variable_name='mysql-monitor_username';
Query OK, 1 row affected (0.00 sec)

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

Admin> select * from global_variables where variable_name='mysql-monitor_username';
+------------------------+----------------+
| variable_name          | variable_value |
+------------------------+----------------+
| mysql-monitor_username | psql_monitor   |
+------------------------+----------------+
1 row in set (0.00 sec)

Admin> select * from global_variables where variable_name='mysql-monitor_password';
+------------------------+----------------+
| variable_name          | variable_value |
+------------------------+----------------+
| mysql-monitor_password | 987654         |
+------------------------+----------------+
1 row in set (0.00 sec)
3)修改监控时间间隔
Admin> SELECT * FROM global_variables WHERE variable_name IN ('mysql-monitor_connect_interval','mysql-monitor_ping_interval','mysql-monitor_read_only_interval');
+----------------------------------+----------------+
| variable_name                    | variable_value |
+----------------------------------+----------------+
| mysql-monitor_connect_interval   | 60000          |
| mysql-monitor_ping_interval      | 10000          |
| mysql-monitor_read_only_interval | 1500           |
+----------------------------------+----------------+
3 rows in set (0.00 sec)

Admin> UPDATE global_variables SET variable_value='2000' WHERE variable_name IN ('mysql-monitor_connect_interval','mysql-monitor_ping_interval','mysql-monitor_read_only_interval');
Query OK, 3 rows affected (0.00 sec)
4)配置的生效和持久化

加载配置到RUNTIME层:

Admin> LOAD MYSQL VARIABLES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)

持久化配置到DISK层:

Admin> SAVE MYSQL VARIABLES TO DISK;
Query OK, 110 rows affected (0.02 sec)

3、后端MySQL服务健康检测

1)查看后端连接状态:
Admin> 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 |
+--------------+------+------------------+-------------------------+---------------+
| 188.188.0.70 | 3306 | 1556620581265582 | 387                     | NULL          |
| 188.188.0.69 | 3306 | 1556620581239483 | 413                     | NULL          |
| 188.188.0.68 | 3306 | 1556620581213375 | 432                     | NULL          |
| 188.188.0.70 | 3306 | 1556620579255264 | 381                     | NULL          |
| 188.188.0.68 | 3306 | 1556620579234288 | 426                     | NULL          |
| 188.188.0.69 | 3306 | 1556620579213311 | 492                     | NULL          |
| 188.188.0.70 | 3306 | 1556620577259808 | 443                     | NULL          |
| 188.188.0.68 | 3306 | 1556620577236530 | 437                     | NULL          |
| 188.188.0.69 | 3306 | 1556620577213253 | 500                     | NULL          |
| 188.188.0.70 | 3306 | 1556620575249404 | 468                     | NULL          |
+--------------+------+------------------+-------------------------+---------------+
10 rows in set (0.00 sec)

查看后端存活状态:

Admin> 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 |
+--------------+------+------------------+----------------------+------------+
| 188.188.0.70 | 3306 | 1556620607647791 | 85                   | NULL       |
| 188.188.0.68 | 3306 | 1556620607631491 | 80                   | NULL       |
| 188.188.0.69 | 3306 | 1556620607615190 | 89                   | NULL       |
| 188.188.0.70 | 3306 | 1556620605653805 | 83                   | NULL       |
| 188.188.0.69 | 3306 | 1556620605634458 | 85                   | NULL       |
| 188.188.0.68 | 3306 | 1556620605615106 | 88                   | NULL       |
| 188.188.0.70 | 3306 | 1556620603663711 | 84                   | NULL       |
| 188.188.0.68 | 3306 | 1556620603639370 | 93                   | NULL       |
| 188.188.0.69 | 3306 | 1556620603615027 | 94                   | NULL       |
| 188.188.0.69 | 3306 | 1556620601666891 | 81                   | NULL       |
+--------------+------+------------------+----------------------+------------+
10 rows in set (0.00 sec)
2)配置的生效和持久化

确认后端服务正常后,就可以加载到RUNTIME层进行启用和持久化到DISK层:

Admin> SELECT * FROM runtime_mysql_servers;
Empty set (0.00 sec)

Admin> SELECT * FROM disk.mysql_servers;
Empty set (0.00 sec)

加载配置到RUNTIME层:

Admin> LOAD MYSQL SERVERS TO RUNTIME;
Query OK, 0 rows affected (0.01 sec)

持久化配置到DISK层:

Admin> SAVE MYSQL SERVERS TO DISK;
Query OK, 0 rows affected (0.03 sec)

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 |
+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 1            | 188.188.0.68 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              | Master  |
| 1            | 188.188.0.70 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              | Slave_2 |
| 1            | 188.188.0.69 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              | Slave_1 |
+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.01 sec)

这里得hostgroup_id都是1,因为我们还没有配置读写分离组信息。

Admin> SELECT * FROM disk.mysql_servers;
+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname     | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 1            | 188.188.0.68 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              | Master  |
| 1            | 188.188.0.70 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              | Slave_2 |
| 1            | 188.188.0.69 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              | Slave_1 |
+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.01 sec)

4、MySQL主从复制架构信息配置

1)新增主从配置
Admin> SELECT * FROM mysql_group_replication_hostgroups;
Empty set (0.00 sec

插入读写、只读组配置信息:

Admin> INSERT INTO mysql_group_replication_hostgroups(writer_hostgroup,backup_writer_hostgroup,reader_hostgroup,offline_hostgroup,active,writer_is_also_reader,comment) VALUES (1,2,4,6,1,1,'MGR_MS');
Query OK, 1 row affected (0.00 sec)

说明:写组编号为1,其他可写节点所在组为2(因为MGR可多主),只读组为4,下线组为6;

Admin> 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 |
+------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+
| 1                | 2                       | 4                | 6                 | 1      | 1           | 1                     | 0                       | MGR_MS  |
+------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+
1 row in set (0.00 sec)
2)配置生效

将修改的配置加载到RUNTIME层:

Admin> LOAD MYSQL SERVERS TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)

查看MySQL分组:

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 |
+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 1            | 188.188.0.68 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              | Master  |
| 4            | 188.188.0.70 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              | Slave_2 |
| 4            | 188.188.0.69 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              | Slave_1 |
+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.00 sec)

可以看到,最初hostgroup_id都为1,现在分为了1和2组。

3)查看MySQL状态
Admin> SELECT * FROM monitor.mysql_server_group_replication_log ORDER BY time_start_us DESC LIMIT 10;
+--------------+------+------------------+-----------------+------------------+-----------+---------------------+-------+
| hostname     | port | time_start_us    | success_time_us | viable_candidate | read_only | transactions_behind | error |
+--------------+------+------------------+-----------------+------------------+-----------+---------------------+-------+
| 188.188.0.70 | 3306 | 1558242135902746 | 1781            | YES              | YES       | 0                   | NULL  |
| 188.188.0.69 | 3306 | 1558242135902307 | 1730            | YES              | YES       | 0                   | NULL  |
| 188.188.0.68 | 3306 | 1558242135901846 | 1713            | YES              | NO        | 0                   | NULL  |
| 188.188.0.70 | 3306 | 1558242130902767 | 1773            | YES              | YES       | 0                   | NULL  |
| 188.188.0.69 | 3306 | 1558242130902313 | 1772            | YES              | YES       | 0                   | NULL  |
| 188.188.0.68 | 3306 | 1558242130901853 | 1753            | YES              | NO        | 0                   | NULL  |
| 188.188.0.70 | 3306 | 1558242125902588 | 1734            | YES              | YES       | 0                   | NULL  |
| 188.188.0.69 | 3306 | 1558242125902140 | 1743            | YES              | YES       | 0                   | NULL  |
| 188.188.0.68 | 3306 | 1558242125901685 | 1726            | YES              | NO        | 0                   | NULL  |
| 188.188.0.70 | 3306 | 1558242120902548 | 1788            | YES              | YES       | 0                   | NULL  |
+--------------+------+------------------+-----------------+------------------+-----------+---------------------+-------+
10 rows in set (0.01 sec)

Slave的read_only属性都已识别到,而且没错误信息。

4)对配置存盘
Admin> SAVE MYSQL SERVERS TO DISK;
Query OK, 0 rows affected (0.03 sec)

Admin> SELECT * FROM disk.mysql_servers;
+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname     | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 1            | 188.188.0.68 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              | Master  |
| 1            | 188.188.0.69 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              | Slave_1 |
| 1            | 188.188.0.70 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              | Slave_2 |
+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.00 sec)

5、设置MySQL Users(连接后端使用)

1)在后端MySQL创建业务账号
Admin> SELECT * FROM mysql_users;
Empty set (0.00 sec)

-- 在后端MySQL上创建业务库及读写账号:

mysql> CREATE DATABASE test;
Query OK, 1 row affected (0.01 sec)

mysql> GRANT select,insert,update,delete ON test.* TO 'test_rw'@'188.188.0.%' IDENTIFIED BY '123456'; 
Query OK, 0 rows affected, 1 warning (0.00 sec)
2)将业务账号加入ProxySQL

-- 添加后端MySQL上的业务账号到 mysql_users

Admin> INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('test_rw','123456',1);
Query OK, 1 row affected (0.00 sec)

Admin> SELECT * FROM mysql_users;
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+
| username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections | comment |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+
| test_rw  | 123456   | 1      | 0       | 1                 | NULL           | 0             | 1                      | 0            | 1       | 1        | 10000           |         |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+
3 rows in set (0.00 sec)
3)配置的生效

再次,将配置加载到运行时以使其生效。

Admin> LOAD MYSQL USERS TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)

Admin> SELECT * FROM runtime_mysql_users;
+----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+
| username | password                                  | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections | comment |
+----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+
| test_rw  | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | 1      | 0       | 1                 |                | 0             | 1                      | 0            | 0       | 1        | 10000           |         |
| test_rw  | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | 1      | 0       | 1                 |                | 0             | 1                      | 0            | 1       | 0        | 10000           |         |
+----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+
2 rows in set (0.00 sec)

被添加的每个用户出现了成对的记录,这是因为每个用户在前端和后端都有一条用户记录;

4)加密MEMORY层用户密码

从RUNTIME层获取用户加密后密码,更新MEMORY层:

Admin> SELECT * FROM mysql_users;
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+
| username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections | comment |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+
| test_rw  | 123456   | 1      | 0       | 1                 | NULL           | 0             | 1                      | 0            | 1       | 1        | 10000           |         |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+
1 row in set (0.00 sec)

Admin> SAVE MYSQL USERS FROM RUNTIME;
Query OK, 0 rows affected (0.00 sec)

Admin> SELECT * FROM mysql_users;
+----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+
| username | password                                  | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections | comment |
+----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+
| test_rw  | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | 1      | 0       | 1                 |                | 0             | 1                      | 0            | 1       | 1        | 10000           |         |
+----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+
1 row in set (0.00 sec)
5)配置持久化

持久化用户信息到DISK层:

Admin> SELECT * FROM disk.mysql_users;
Empty set (0.00 sec)

Admin> SAVE MYSQL USERS TO DISK;
Query OK, 0 rows affected (0.01 sec)

Admin> SELECT * FROM disk.mysql_users;
+----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+
| username | password                                  | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections | comment |
+----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+
| test_rw  | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | 1      | 0       | 1                 |                | 0             | 1                      | 0            | 1       | 1        | 10000           |         |
+----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+
1 rows in set (0.00 sec)

6、业务账号可用性验证

到此,用户添加完毕,可用使用客户端连接ProxySQL尝试连接。

Admin> select * from stats.stats_mysql_query_digest ;
Empty set (0.01 sec)

ProxySQL中无SQL统计信息;

[root@localhost ~]# mysql -u test_rw -p123456 -h 188.188.0.71 -P6033 -e "SELECT 1"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---+
| 1 |
+---+
| 1 |
+---+

注意:连接端口默认是6033,不是6032(ProxySQL管理端口)也不是3306(MySQL端口)。

[root@localhost ~]# mysql -u test_rw -p123456 -h 188.188.0.71 -P6033 -e "SELECT @@port"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------+
| @@port |
+--------+
|   3306 |
+--------+

这里查询出了后端MySQL服务的端口。

Admin> select * from stats.stats_mysql_query_digest ;
+-----------+--------------------+----------+----------------+--------------------+----------------------------------+------------+------------+------------+----------+----------+----------+
| hostgroup | schemaname         | username | client_address | digest             | digest_text                      | count_star | first_seen | last_seen  | sum_time | min_time | max_time |
+-----------+--------------------+----------+----------------+--------------------+----------------------------------+------------+------------+------------+----------+----------+----------+
| 1         | information_schema | test_rw  |                | 0x1C46AE529DD5A40E | SELECT ?                         | 1          | 1558242482 | 1558242482 | 566      | 566      | 566      |
| 1         | information_schema | test_rw  |                | 0x831B091BA90D80E5 | SELECT @@port                    | 1          | 1558242494 | 1558242494 | 167      | 167      | 167      |
| 1         | information_schema | test_rw  |                | 0x226CD90D52A2BA0B | select @@version_comment limit ? | 2          | 1558242482 | 1558242494 | 0        | 0        | 0        |
+-----------+--------------------+----------+----------------+--------------------+----------------------------------+------------+------------+------------+----------+----------+----------+
3 rows in set (0.00 sec)

执行得SQL都跑到了组1上,即Master上。这时只是最简单的服务器、用户配置已经完成,读写分离在后面继续介绍!!!

》》》读写分离查询规则配置篇《《《

  • 注意: 正常逻辑应该是针对有必要的SQL进行读写分离配置,而不是粗暴的将所有查询都发送到Slave;

1、读写分离的正常逻辑

使用regex和digest进行读/写拆分是很正确的选择,有效设置读/写拆分的配置过程如下: 1)配置 ProxySQL 只将所有流量发送到一个 MySQL 节点——master(写和读); 2)在 stats_mysql_query_digest 中查找执行成本较高的 SELECT 语句; 3)确定哪些成本较高的 SELECT 语句应该被移动到Slave节点上执行; 4)配置 mysql_query_rules 将需要移动的 SELECT 语句发送到读节点; 因此,这个想法非常简单:只发送你想发送给 Slave/Reader 的内容,而不只是任何SELECT语句。

2、确定高成本的 SELECT 语句

可以从以下几种维度来查找成本较高的 SELECT 语句;

1)根据总执行时间查找最耗时的前5个查询:
Admin> SELECT digest,SUBSTR(digest_text,0,25),count_star,sum_time
    -> FROM stats.stats_mysql_query_digest 
    -> WHERE digest_text LIKE 'SELECT%' 
    -> ORDER BY sum_time DESC 
    -> LIMIT 5;
+--------------------+--------------------------+------------+----------+
| digest             | SUBSTR(digest_text,0,25) | count_star | sum_time |
+--------------------+--------------------------+------------+----------+
| 0x1C46AE529DD5A40E | SELECT ?                 | 1          | 543      |
| 0x38B78BFFAF23614A | select * from test.t1    | 1          | 356      |
| 0x831B091BA90D80E5 | SELECT @@port            | 1          | 171      |
| 0x226CD90D52A2BA0B | select @@version_comment | 3          | 0        |
+--------------------+--------------------------+------------+----------+
4 rows in set (0.00 sec)
2)根据计数查找前5个查询:
Admin> SELECT digest,SUBSTR(digest_text,0,25),count_star,sum_time 
    -> FROM stats_mysql_query_digest 
    -> WHERE digest_text LIKE 'SELECT%' 
    -> ORDER BY count_star DESC 
    -> LIMIT 5;
+--------------------+--------------------------+------------+----------+
| digest             | SUBSTR(digest_text,0,25) | count_star | sum_time |
+--------------------+--------------------------+------------+----------+
| 0x226CD90D52A2BA0B | select @@version_comment | 3          | 0        |
| 0x38B78BFFAF23614A | select * from test.t1    | 1          | 356      |
| 0x1C46AE529DD5A40E | SELECT ?                 | 1          | 543      |
| 0x831B091BA90D80E5 | SELECT @@port            | 1          | 171      |
+--------------------+--------------------------+------------+----------+
4 rows in set (0.01 sec)
3)根据最长执行时间查找前5个查询:
Admin> SELECT digest,SUBSTR(digest_text,0,25),count_star,sum_time,sum_time/count_star avg_time, min_time, max_time 
    -> FROM stats_mysql_query_digest 
    -> WHERE digest_text LIKE 'SELECT%' 
    -> ORDER BY max_time DESC 
    -> LIMIT 5;
+--------------------+--------------------------+------------+----------+----------+----------+----------+
| digest             | SUBSTR(digest_text,0,25) | count_star | sum_time | avg_time | min_time | max_time |
+--------------------+--------------------------+------------+----------+----------+----------+----------+
| 0x1C46AE529DD5A40E | SELECT ?                 | 1          | 543      | 543      | 543      | 543      |
| 0x38B78BFFAF23614A | select * from test.t1    | 1          | 356      | 356      | 356      | 356      |
| 0x831B091BA90D80E5 | SELECT @@port            | 1          | 171      | 171      | 171      | 171      |
| 0x226CD90D52A2BA0B | select @@version_comment | 3          | 0        | 0        | 0        | 0        |
+--------------------+--------------------------+------------+----------+----------+----------+----------+
4 rows in set (0.00 sec)
4)查找按总执行时间排序的前5个查询,最小执行时间至少为0.3毫秒:
Admin> SELECT digest,SUBSTR(digest_text,0,20),count_star,sum_time,sum_time/count_star avg_time, min_time, max_time 
    -> FROM stats_mysql_query_digest 
    -> WHERE digest_text LIKE 'SELECT%' AND min_time > 300 
    -> ORDER BY sum_time DESC 
    -> LIMIT 5;
+--------------------+--------------------------+------------+----------+----------+----------+----------+
| digest             | SUBSTR(digest_text,0,20) | count_star | sum_time | avg_time | min_time | max_time |
+--------------------+--------------------------+------------+----------+----------+----------+----------+
| 0x1C46AE529DD5A40E | SELECT ?                 | 1          | 543      | 543      | 543      | 543      |
| 0x38B78BFFAF23614A | select * from test.      | 1          | 356      | 356      | 356      | 356      |
+--------------------+--------------------------+------------+----------+----------+----------+----------+
2 rows in set (0.01 sec)
5)查找按总执行时间排序的前5个查询,平均执行时间至少为0.3毫秒。同时显示总执行时间的百分比:
Admin> SELECT digest,SUBSTR(digest_text,0,25),count_star,sum_time,sum_time/count_star avg_time, ROUND(sum_time*100.00/(SELECT SUM(sum_time) 
    -> FROM stats_mysql_query_digest),3) pct FROM stats_mysql_query_digest 
    -> WHERE digest_text LIKE 'SELECT%' AND sum_time/count_star > 300 
    -> ORDER BY sum_time DESC 
    -> LIMIT 5;
+--------------------+--------------------------+------------+----------+----------+--------+
| digest             | SUBSTR(digest_text,0,25) | count_star | sum_time | avg_time | pct    |
+--------------------+--------------------------+------------+----------+----------+--------+
| 0x1C46AE529DD5A40E | SELECT ?                 | 1          | 543      | 543      | 50.748 |
| 0x38B78BFFAF23614A | select * from test.t1    | 1          | 356      | 356      | 33.271 |
+--------------------+--------------------------+------------+----------+----------+--------+
2 rows in set (0.01 sec)

通过以上5个维度,大致可以找出目标SQL了。 这里,我们将 'select * from test.t1' 作为选定的目标,将其分配到Slave上进行读取。

3、编写查询规则

在与应用程序开发人员确认之后,可以将'select * from test.t1'的语句,即digset为 0x38B78BFFAF23614A 的查询转移到Slave_1上执行:

Admin> SELECT * FROM mysql_query_rules ;
Empty set (0.00 sec)

Admin> INSERT INTO mysql_query_rules (rule_id,active,digest,destination_hostgroup,apply) VALUES (1,1,'0x38B78BFFAF23614A',4,1);
Query OK, 1 row affected (0.00 sec)

注意:这里destination_hostgroup为4号的只读组。

Admin> SELECT * FROM mysql_query_rules \G
*************************** 1. row ***************************
              rule_id: 1
               active: 1
             username: NULL
           schemaname: NULL
               flagIN: 0
          client_addr: NULL
           proxy_addr: NULL
           proxy_port: NULL
               digest: 0x38B78BFFAF23614A
         match_digest: NULL
        match_pattern: NULL
 negate_match_pattern: 0
         re_modifiers: CASELESS
              flagOUT: NULL
      replace_pattern: NULL
destination_hostgroup: 4
            cache_ttl: NULL
   cache_empty_result: NULL
        cache_timeout: NULL
            reconnect: NULL
              timeout: NULL
              retries: NULL
                delay: NULL
    next_query_flagIN: NULL
       mirror_flagOUT: NULL
     mirror_hostgroup: NULL
            error_msg: NULL
               OK_msg: NULL
          sticky_conn: NULL
            multiplex: NULL
  gtid_from_hostgroup: NULL
                  log: NULL
                apply: 1
              comment: NULL
1 row in set (0.00 sec)

4、配置生效

Admin> LOAD MYSQL QUERY RULES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)

Admin> SELECT * FROM runtime_mysql_query_rules \G
*************************** 1. row ***************************
              rule_id: 1
               active: 1
             username: NULL
           schemaname: NULL
               flagIN: 0
          client_addr: NULL
           proxy_addr: NULL
           proxy_port: NULL
               digest: 0x38B78BFFAF23614A
         match_digest: NULL
        match_pattern: NULL
 negate_match_pattern: 0
         re_modifiers: CASELESS
              flagOUT: NULL
      replace_pattern: NULL
destination_hostgroup: 4
            cache_ttl: NULL
   cache_empty_result: NULL
        cache_timeout: NULL
            reconnect: NULL
              timeout: NULL
              retries: NULL
                delay: NULL
    next_query_flagIN: NULL
       mirror_flagOUT: NULL
     mirror_hostgroup: NULL
            error_msg: NULL
               OK_msg: NULL
          sticky_conn: NULL
            multiplex: NULL
  gtid_from_hostgroup: NULL
                  log: NULL
                apply: 1
              comment: NULL
1 row in set (0.00 sec)

5、验证设置结果

1)记录SQL的历史状态信息
Admin> SELECT * FROM stats.stats_mysql_query_digest WHERE digest='0x38B78BFFAF23614A';
+-----------+--------------------+----------+----------------+--------------------+-----------------------+------------+------------+------------+----------+----------+----------+
| hostgroup | schemaname         | username | client_address | digest             | digest_text           | count_star | first_seen | last_seen  | sum_time | min_time | max_time |
+-----------+--------------------+----------+----------------+--------------------+-----------------------+------------+------------+------------+----------+----------+----------+
| 1         | information_schema | test_rw  |                | 0x38B78BFFAF23614A | select * from test.t1 | 1          | 1557977769 | 1557977769 | 356      | 356      | 356      |
+-----------+--------------------+----------+----------------+--------------------+-----------------------+------------+------------+------------+----------+----------+----------+
1 rows in set (0.00 sec)

Admin> SELECT * FROM stats.stats_mysql_query_rules WHERE rule_id=1;
Empty set (0.00 sec)
2)使用业务账号向ProxySQL发出目标SQL:
[root@localhost ~]# mysql -u test_rw -p123456 -h 188.188.0.71 -P6033 -e "select * from test.t1;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
+----+------+

3)在ProxySQL中查看查询设置结果

Admin> SELECT * FROM stats.stats_mysql_query_digest WHERE digest='0x38B78BFFAF23614A';
+-----------+--------------------+----------+----------------+--------------------+-----------------------+------------+------------+------------+----------+----------+----------+
| hostgroup | schemaname         | username | client_address | digest             | digest_text           | count_star | first_seen | last_seen  | sum_time | min_time | max_time |
+-----------+--------------------+----------+----------------+--------------------+-----------------------+------------+------------+------------+----------+----------+----------+
| 4         | information_schema | test_rw  |                | 0x38B78BFFAF23614A | SELECT * from test.t1 | 1          | 1558243463 | 1558243463 | 793      | 793      | 793      |
| 1         | information_schema | test_rw  |                | 0x38B78BFFAF23614A | select * from test.t1 | 1          | 1557977769 | 1557977769 | 356      | 356      | 356      |
+-----------+--------------------+----------+----------------+--------------------+-----------------------+------------+------------+------------+----------+----------+----------+
2 rows in set (0.00 sec)

出现了hostgroup 2的查询记录,说明已经将目标SQL发送到了Slave服务上。

Admin> SELECT * FROM stats.stats_mysql_query_rules WHERE rule_id=1;
+---------+------+
| rule_id | hits |
+---------+------+
| 1       | 1    |
+---------+------+
1 row in set (0.00 sec)

规则被命中1次。

以上2个结果说明了目标SQL通过查询规则被发送到了Slave上。

6、配置持久化

Admin> SELECT * FROM disk.mysql_query_rules WHERE rule_id=1 ;
Empty set (0.00 sec)

Admin> SAVE MYSQL QUERY RULES TO DISK;
Query OK, 0 rows affected (0.01 sec)

Admin> SELECT * FROM disk.mysql_query_rules WHERE rule_id=1 \G
*************************** 1. row ***************************
              rule_id: 1
               active: 1
             username: NULL
           schemaname: NULL
               flagIN: 0
          client_addr: NULL
           proxy_addr: NULL
           proxy_port: NULL
               digest: 0x38B78BFFAF23614A
         match_digest: NULL
        match_pattern: NULL
 negate_match_pattern: 0
         re_modifiers: CASELESS
              flagOUT: NULL
      replace_pattern: NULL
destination_hostgroup: 4
            cache_ttl: NULL
   cache_empty_result: NULL
        cache_timeout: NULL
            reconnect: NULL
              timeout: NULL
              retries: NULL
                delay: NULL
    next_query_flagIN: NULL
       mirror_flagOUT: NULL
     mirror_hostgroup: NULL
            error_msg: NULL
               OK_msg: NULL
          sticky_conn: NULL
            multiplex: NULL
  gtid_from_hostgroup: NULL
                  log: NULL
                apply: 1
              comment: NULL
1 row in set (0.00 sec)

到此,利用ProxySQL对传统的主从结构读写分离已经实现。

》》》系统参数配置篇《《《

修改自动提交参数:

Admin> show variables like '%auto%';
+---------------------------------------+-------+
| Variable_name                         | Value |
+---------------------------------------+-------+
| mysql-forward_autocommit              | false |
| mysql-enforce_autocommit_on_reads     | false |
| mysql-autocommit_false_not_reusable   | false |
| mysql-autocommit_false_is_transaction | false |
| mysql-auto_increment_delay_multiplex  | 5     |
+---------------------------------------+-------+
5 rows in set (0.01 sec)

修改以下2个参数:

Admin> set mysql-forward_autocommit='true';

为true时,则SET autocommit = 0命令被转发到MySQL上后端。

Admin> set mysql-autocommit_false_not_reusable = 'true';

设置为true时,不会重新使用与autocommit = 0的连接,并在连接返回到连接池时销毁

使配置生效

Admin> save mysql variables to disk;

使配置持久化

Admin> load mysql variables to run;

》》》压测篇《《《

后端为MGR单主模式 可以直接用多线程压测PROXYSQL,就像压测普通MySQL一样。但要注意: 由于ProxySQL监控主从状态是靠在后端MySQL上建立一个sys.gr_member_routing_candidate_status的状态视图,而对该视图的查询实际上是对多个系统表查询结果的汇总。因此,在MySQL繁忙时访问该视图会导致监控程序获取信息超时而失败,继而误判主机状态而修改runtime_mysql_servers的状态,导致连接后端MGR线程断开。所以,压测时最好将该视图用一个同名表代替,并在里面写死服务器状态,这样就不会在监控程序访问时获取内容超时而误判异常,也就不会杀死后端连接线程。

~ ~ 完毕!