用 ProxySQL 实现 MySQL 的读写分离

-- 连接到MySQL主库创建ProxySQL的monitor监控账号
mysql> CREATE USER 'monitor'@'%' IDENTIFIED BY 'monitor';
mysql> GRANT SUPER, REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO 'monitor'@'%';

-- 连接到ProxySQL管理界面
# mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '

-- 设置一个应用程序使用的用户
mysql> INSERT INTO mysql_users(username, password, default_hostgroup, transaction_persistent) 
VALUES ('rw', '123456', 1, 1);

-- 配置服务器组
mysql> INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (0, '192.168.198.239', 6666);
mysql> INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (1, '192.168.198.239', 6667);

-- 配置读写分离规则
mysql> INSERT INTO mysql_query_rules (rule_id, active, match_digest, destination_hostgroup, apply) 
VALUES (1, 1, '^SELECT.*FOR UPDATE$', 0, 1);

mysql> INSERT INTO mysql_query_rules (rule_id, active, match_digest, destination_hostgroup, apply) 
VALUES (2, 1, '^SELECT', 1, 1);

-- 匹配所有非SELECT语句
mysql> INSERT INTO mysql_query_rules (rule_id, active, match_digest, destination_hostgroup, apply) 
VALUES (3, 1, '^((?!SELECT).)*$', 0, 1);

-- 配置复制组
mysql> INSERT INTO mysql_replication_hostgroups (writer_hostgroup, reader_hostgroup, comment)
VALUES (0, 1, 'Read Write Split Host Groups');

-- 启用监控
mysql> UPDATE global_variables SET variable_value='true' WHERE variable_name='mysql-monitor_enabled';

mysql> UPDATE global_variables SET variable_value='2000' WHERE variable_name IN 
('mysql-monitor_connect_interval',
'mysql-monitor_ping_interval',
'mysql-monitor_read_only_interval',
'mysql-monitor_replication_lag_interval');

-- 配置复制延迟检查
mysql> UPDATE mysql_servers SET max_replication_lag = 10 WHERE hostgroup_id = 1;

-- 启用查询统计
mysql> UPDATE global_variables SET variable_value='true' WHERE variable_name='mysql-query_digests';
mysql> UPDATE global_variables SET variable_value='3600' WHERE variable_name='mysql-query_digests_max_digest_length';
mysql> UPDATE global_variables SET variable_value='5000' WHERE variable_name='mysql-query_digests_max_query_length';

-- 配置连接池
mysql> UPDATE global_variables SET variable_value='1500' WHERE variable_name='mysql-max_connections';
mysql> UPDATE global_variables SET variable_value='100' WHERE variable_name='mysql-default_max_latency_ms';

-- 应用更改
mysql> LOAD MYSQL USERS TO RUNTIME;
mysql> SAVE MYSQL USERS TO DISK;

mysql> LOAD MYSQL SERVERS TO RUNTIME;
mysql> SAVE MYSQL SERVERS TO DISK;

mysql> LOAD MYSQL QUERY RULES TO RUNTIME;
mysql> SAVE MYSQL QUERY RULES TO DISK;

mysql> LOAD MYSQL VARIABLES TO RUNTIME;
mysql> SAVE MYSQL VARIABLES TO DISK;