安装并登陆管理界面
1 wgethttps://github.com/sysown/proxysql/releases/download/v1.3.6/proxysql-1.3.6-1-centos67.x86_64.rpm
2 yum install perl-DBD-MySQL
3 rpm -ivh proxysql-1.3.6-1-centos67.x86_64.rpm
4 service proxysql start
5 proxysql --version
ProxySQL version 1.3.6-0-g434b376, codename Truls
6 登陆管理界面(配置信息从启动进程的配置文件查看)
mysql -u admin -padmin -h127.0.0.1 -P6032--prompt='proxysql>'
mysql -u admin -padmin -S /tmp/proxysql_admin.sock--prompt='proxysql>'
配置读写分离和路由规则
环境(三个后台服务器,一主两从的架构)
Master 10.9.160.248:3306
Slave1 10.9.117.107:3306 设置read_only
Slave2 10.9.150.86:3306 设置read_only
在db的主节点上创建proxy的监控账户和业务账户
GRANT ALLPRIVILEGES ON *.* TO 'monitor'@'%' IDENTIFIED BY ‘monitor’;
GRANT ALLPRIVILEGES ON *.* TO 'msandbox'@'%' IDENTIFIED BY ‘msandbox’;
在proxy管理命令行添加后端服务器列表
proxysql>INSERTINTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'10.9.160.248',3306);
proxysql>INSERTINTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'10.9.150.86',3306);
proxysql>INSERTINTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'10.9.117.107',3306);
注意到这时的hostgroup都为1,这没什么问题
即使生效
LOAD MYSQL SERVERS TO RUNTIME;
配置和注册监控账户
UPDATE global_variablesSET variable_value='monitor'WHERE variable_name='mysql-monitor_username';
UPDATE global_variables SET variable_value='monitor'WHERE variable_name='mysql-monitor_password';
这里填的账户和密码均为monitor的信息就是步骤1中在后端服务器创建的监控账户
即使生效,并持久化到磁盘
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 10;
+--------------+------+------------------+-------------------------+---------------+
| hostname | port | time_start_us | connect_success_time_us | connect_error |
+--------------+------+------------------+-------------------------+---------------+
| 10.9.160.248 | 3306 | 1497001266520778 | 688 | NULL |
| 10.9.150.86 | 3306 | 1497001266510692 | 541 | NULL |
| 10.9.117.107 | 3306 | 1497001266500632 | 718 | NULL |
| 10.9.160.248 | 3306 | 1497001206520719 | 652 | NULL |
| 10.9.150.86 | 3306 | 1497001206510580 | 618 | NULL |
| 10.9.117.107 | 3306 | 1497001206500519 | 744 | NULL |
| 10.9.160.248 | 3306 | 1497001146520616 | 699 | NULL |
| 10.9.150.86 | 3306 | 1497001146510561 | 557 | NULL |
| 10.9.117.107 | 3306 | 1497001146500487 | 759 | NULL |
| 10.9.160.248 | 3306 | 1497001086520458 | 595 | NULL |