ProxySQL快速上手

安装并登陆管理界面

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          |