视频:<MySQL ProxySQL 管理实战>
环境准备
IP | Server-id | 数据库版本 | 备注 |
192.168.2.150 | proxysql-2.4.1-1 | 代理节点 | |
192.168.2.100 | Mysql 5.7.32 | 主库节点 | |
192.168.2.101 | Mysql 5.7.32 | 从库节点 | |
192.168.2.102 | Mysql 5.7.32 | 从库节点 |
环境拓扑
ProxySQL安装(略)
MySQL主从部署(略)
配置主机组【规划集群】
--查看当前主机组状态
proxysql>select * from mysql_replication_hostgroups;
--配置读写主机组
proxysql>INSERT INTO mysql_replication_hostgroups (writer_hostgroup,reader_hostgroup,comment) VALUES (1,2,'TestCluster');
proxysql>LOAD MYSQL SERVERS TO RUNTIME;
proxysql>SAVE MYSQL SERVERS TO DISK;
--查看当前主机组状态
proxysql>select * from mysql_servers;
proxysql>SELECT * FROM monitor.mysql_server_read_only_log ORDER BY time_start_us DESC LIMIT 3;添加后端MySQL节点
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)
--添加mysql后端节点
proxysql>INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'192.168.2.100',3306);
Query OK, 1 row affected (0.00 sec)
proxysql>INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'192.168.2.101',3306);
Query OK, 1 row affected (0.00 sec)
proxysql>INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'192.168.2.102',3306);
Query OK, 1 row affected (0.00 sec)
proxysql>select * from mysql_servers \G;
*************************** 1. row ***************************
hostgroup_id: 1
hostname: 192.168.2.100
port: 3306
gtid_port: 0
status: ONLINE
weight: 1
compression: 0
max_connections: 1000
max_replication_lag: 0
use_ssl: 0
max_latency_ms: 0
comment:
*************************** 2. row ***************************
hostgroup_id: 1
hostname: 192.168.2.101
port: 3306
gtid_port: 0
status: ONLINE
weight: 1
compression: 0
max_connections: 1000
max_replication_lag: 0
use_ssl: 0
max_latency_ms: 0
comment:
*************************** 3. row ***************************
hostgroup_id: 1
hostname: 192.168.2.102
port: 3306
gtid_port: 0
status: ONLINE
weight: 1
compression: 0
max_connections: 1000
max_replication_lag: 0
use_ssl: 0
max_latency_ms: 0
comment:
3 rows in set (0.00 sec)
proxysql>
--加载到RUNTIME，并保存到disk
proxysql>load mysql servers to runtime;
Query OK, 0 rows affected (0.00 sec)
proxysql>save mysql servers to disk;
Query OK, 0 rows affected (0.05 sec)
proxysql>select * from runtime_mysql_servers \G;
*************************** 1. row ***************************
hostgroup_id: 1
hostname: 192.168.2.100
port: 3306
gtid_port: 0
status: ONLINE
weight: 1
compression: 0
max_connections: 1000
max_replication_lag: 0
use_ssl: 0
max_latency_ms: 0
comment:
*************************** 2. row ***************************
hostgroup_id: 1
hostname: 192.168.2.102
port: 3306
gtid_port: 0
status: ONLINE
weight: 1
compression: 0
max_connections: 1000
max_replication_lag: 0
use_ssl: 0
max_latency_ms: 0
comment:
*************************** 3. row ***************************
hostgroup_id: 1
hostname: 192.168.2.101
port: 3306
gtid_port: 0
status: ONLINE
weight: 1
compression: 0
max_connections: 1000
max_replication_lag: 0
use_ssl: 0
max_latency_ms: 0
comment:
3 rows in set (0.01 sec)
ERROR: No query specified
proxysql>配置监控后端MySQL节点
proxysql>SELECT * FROM global_variables WHERE variable_name LIKE 'mysql-monitor_%';
+----------------------------------------------------------------------+----------------+
| variable_name | variable_value |
+----------------------------------------------------------------------+----------------+
| mysql-monitor_enabled | true |
| mysql-monitor_connect_timeout | 600 |
| mysql-monitor_ping_max_failures | 3 |
| mysql-monitor_ping_timeout | 1000 |
| mysql-monitor_read_only_max_timeout_count | 3 |
| mysql-monitor_replication_lag_group_by_host | false |
| mysql-monitor_replication_lag_interval | 10000 |
| mysql-monitor_replication_lag_timeout | 1000 |
| mysql-monitor_replication_lag_count | 1 |
| mysql-monitor_groupreplication_healthcheck_interval | 5000 |
| mysql-monitor_groupreplication_healthcheck_timeout | 800 |
| mysql-monitor_groupreplication_healthcheck_max_timeout_count | 3 |
| mysql-monitor_groupreplication_max_transactions_behind_count | 3 |
| mysql-monitor_groupreplication_max_transactions_behind_for_read_only | 1 |
| mysql-monitor_galera_healthcheck_interval | 5000 |
| mysql-monitor_galera_healthcheck_timeout | 800 |
| mysql-monitor_galera_healthcheck_max_timeout_count | 3 |
| mysql-monitor_replication_lag_use_percona_heartbeat | |
| mysql-monitor_query_interval | 60000 |
| mysql-monitor_query_timeout | 100 |
| mysql-monitor_slave_lag_when_null | 60 |
| mysql-monitor_threads_min | 8 |
| mysql-monitor_threads_max | 128 |
| mysql-monitor_threads_queue_maxsize | 128 |
| mysql-monitor_wait_timeout | true |
| mysql-monitor_writer_is_also_reader | true |
| mysql-monitor_username | monitor |
| mysql-monitor_password | monitor |
| mysql-monitor_history | 600000 |
| mysql-monitor_connect_interval | 60000 |
| mysql-monitor_ping_interval | 10000 |
| mysql-monitor_read_only_interval | 1500 |
| mysql-monitor_read_only_timeout | 500 |
+----------------------------------------------------------------------+----------------+
# 在master上执行：
mysql> create user monitor@'192.168.2.%' identified by 'monitor';
mysql> grant replication client on *.* to monitor@'192.168.2.%';
#proxysql上设置监控用户及密码
set mysql-monitor_username='monitor';
set mysql-monitor_password='monitor';
load mysql variables to runtime;
save mysql variables to disk;
--对连接的监控日志
proxysql> SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 3;
--对心跳的监控日志
proxysql> SELECT * FROM monitor.mysql_server_ping_log ORDER BY time_start_us DESC LIMIT 3;配置 mysql users
--在后端mysql数据库创建应用用户
root@(none)>grant all on *.* to testProxy@'192.168.2.%' identified by '123456';
root@(none)>flush privileges;
--在proxysql上配置mysql user
proxysql>INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('testProxy','123456',1);
proxysql>select * from mysql_users;
--使配置生效
proxysql>LOAD MYSQL USERS TO RUNTIME;
proxysql>SAVE MYSQL USERS TO DISK;
proxysql>
--测试，通过proxysql连接后端数据库
[root@proxySQL ~]# mysql -u testProxy -p123456 -h 192.168.2.150 -P6033 -e"SELECT @@port"
+--------+
| @@port |
+--------+
| 3306 |
+--------+
[root@proxySQL ~]#通用读写分离：配置路由规则
DO NOT USE THESE RULES IN PRODUCTION, GENERIC READ/WRITE RULES LEAD TO PROBLEMS
DELETE FROM mysql_query_rules;
UPDATE mysql_users SET default_hostgroup=1; # by default, all goes to HG1
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;
INSERT INTO mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply)
VALUES
(1,1,'^SELECT.*FOR UPDATE$',1,1),
(2,1,'^SELECT',2,1);
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;
--测试
mysql -u testProxy -p123456 -h 192.168.2.150 -P6033 -e"SELECT @@server_id FOR UPDATE";
mysql -u testProxy -p123456 -h 192.168.2.150 -P6033 -e"SELECT @@server_id ";
--查看路由的匹配次数
select * from stats_mysql_query_rules;- 所有
SELECT FOR UPDATE语句将会被发送到 HG1 - 所有其他的
SELECT语句将会发送到 HG2 - 缺省情况下，所有其他的非 SELECT 语句将被发送到 HG1
- 注意：ProxySQL是按rule_id的顺序进行匹配的，^SELECT.*FOR UPDATE$的rule_id必须要小于普通的SELECT规则的rule_id
