proxysql 安装部署及常用的操作

详细部署比较简单，参考 https://www.cnblogs.com/keme/p/12290977.html

相关的命令需要记录下：

# 启动ProxySQL
systemctl start proxysql.service 

# 查看 ProxySQL 的版本
proxysql --version

# 管理员登录 ProxySQL
/usr/local/mysql/bin/mysql -uadmin -padmin -h 127.0.0.1 -P 6032

# 配置 ProxySQL 所需账户， 在mysql 的主库上面操作

#proxysql 的监控账户
create user 'monitor'@'10.0.0.%' identified by '123456';
grant all privileges on *.* to 'monitor'@'10.0.0.%' with grant option;

#proxysql 的对外访问账户
create user 'proxysql'@'10.0.0.%' identified by '123456';
grant all privileges on *.* to 'proxysql'@'10.0.0.%' with grant option;

flush privileges;

# writer_hostgroup 和reader_hostgroup 写组和读组都要大于0且不能相同，我的环境下，写组定义与10，读组定义为20
insert into mysql_replication_hostgroups ( writer_hostgroup, reader_hostgroup, comment) values (10,20,'proxy');
load mysql servers to runtime;
save mysql servers to disk;

#  添加主从服务器节点

insert into mysql_servers(hostgroup_id,hostname,port) values (10,'10.0.0.160',3306);
insert into mysql_servers(hostgroup_id,hostname,port) values (20,'10.0.0.161',3306);
insert into mysql_servers(hostgroup_id,hostname,port) values (20,'10.0.0.162',3306);

# ProxySQL监控 MySQL 后端节点
# 为 ProxySQL 配置监控账号
set mysql-monitor_username='monitor';
set mysql-monitor_password='123456';

# 或者
UPDATE global_variables SET variable_value='monitor'
 WHERE variable_name='mysql-monitor_username';
UPDATE global_variables SET variable_value='123456'
 WHERE variable_name='mysql-monitor_password';

load mysql variables to runtime;
save mysql variables to disk;

#  验证监控信息，ProxySQL 监控模块的指标都保存在monitor库的log表中 
select * from monitor.mysql_server_connect_log;

# 对心跳信息的监控(对ping 指标的监控)
select * from mysql_server_ping_log limit 10;

# 看看read_only的日志监控
select * from mysql_server_read_only_log limit 10;

# 为 ProxySQL 配置对外访问账号
# 配置mysql_users 表，将proxysql用户添加到该表中
insert into mysql_users (username,password,default_hostgroup) values ('proxysql','123456',10);
load mysql users to runtime;
save mysql users to disk;

# 配置读写分离策略：路由规则
insert into mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply) values (1,1,'^select.*for update$',10,1);
insert into mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply) values (2,1,'^select',20,1);
load mysql query rules to runtime;
save mysql query rules to disk;

#select … for update规则的rule_id必须要小于普通的select规则的rule_id，因为ProxySQL是根据rule_id的顺序进行规则匹配的

# 测试读
mysql -uproxysql -p123456 -P 6033 -h 127.0.0.1 -e "select @@server_id;"

# 如果想在 ProxySQL 中查看SQL请求路由信息stats_mysql_query_digest
select hostgroup,schemaname,username,digest_text,count_star from  stats_mysql_query_digest;

#count_start 统计sql 语句次数，可以分析哪些 sql ，频繁执行
#读写分离设置成功后，还可以调权重，比如让某台机器承受更多的读操作 

update mysql_servers set weight=10 hostname='10.0.0.161'；
load mysql servers to runtime;
save mysql servers to disk;