一、安装ProxySQL

1.源码及下载地址：

https://github.com/sysown/proxysql

https://repo.proxysql.com/ProxySQL/proxysql-2.4.x/centos/7/

配置yum源：

cat > /etc/yum.repos.d/proxysql.repo << EOF
[proxysql]
name=ProxySQL YUM repository
baseurl=https://repo.proxysql.com/ProxySQL/proxysql-2.4.x/centos/$releasever
gpgcheck=1
gpgkey=https://repo.proxysql.com/ProxySQL/proxysql-2.4.x/repo_pub_key
EOF

2.安装

# rpm -ivh proxysql-xx-centos7.x86_64.rpm

# systemctl start proxysql

# chkconfig proxysql on

二、配置主从（参考Mysql 主从设置文章）

从库可设置 mysql> set global read_only=on

三、配置proxysql

1.mysql主库添加proxysql可以增删改查的账号(mysql上操作)

grant all on *.* to proxysql@'%' identified by '123456';

添加健康检测的帐号

grant select,show view on *.* to monitor@'%' identified by '123456';

2.创建组(proxysql 上操作: /usr/local/mysql/bin/mysql -uadmin -padmin -h 127.0.0.1 -P 6032)

insert into mysql_replication_hostgroups ( writer_hostgroup, reader_hostgroup, comment) values (10,20,'proxy');

3.添加 mysql 主机到 mysql_servers 表中(proxysql 上操作)

hostgroup_id 10表示写组，20表示读组

insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(10,'192.168.0.1',3306,1,'Write Group');

insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(20,'192.168.0.2',3306,1,'Read Group');

//查询

select * from mysql_servers;

4.添加刚才在mysql上创建的账号 (proxysql 上操作)

insert into mysql_users(username,password,default_hostgroup,transaction_persistent)values('proxysql','123456',10,1);

//查询

select* from mysql_users;

5.在proxysql主机端修改变量设置健康检测的账号

set mysql-monitor_username='monitor';

set mysql-monitor_password='123456';

四、添加读写分离的路由规则

1.将 select 查询语句全部路由至 hostgroup_id=20的组(也就是读组)
但是 select * from tb for update 这样的语句是会修改数据的，所以需要单独定义，将它路由至 hostgroup_id=10的组(也就是写组)
其他没有被规则匹配到的组将会被路由至用户默认的组(mysql_users 表中的 default_hostgroup
proxySQL是根据rule_id的顺序进行规则匹配的

insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(1,1,'^SELECT.*FOR UPDATE$',10,1);

insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(50,1,"^SHOW",20,1);

insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(51,1,'^SELECT',20,1);

//查询

select rule_id,active,match_digest,destination_hostgroup,apply from mysql_query_rules;

在管理界面运行下面命令

set mysql-set_query_lock_on_hostgroup=0;

set mysql-default_charset='utf8mb4';
set mysql-default_collation_connection='utf8mb4_general_ci';

select * from global_variables;

五、加载配置和变量

-- 持久化（在ProxySQL中执行）
save mysql servers to disk;
save mysql users to disk;
save mysql variables to disk;
save mysql query rules to disk;

-- 加载到线上（在ProxySQL中执行）
load mysql servers to runtime;
load mysql users to runtime;
load mysql variables to runtime;
load mysql query rules to runtime;

六、验证读写分离

1.登录 proxysql 客户端，测试访问

# /usr/local/mysql/bin/mysql -uproxysql -p -h 127.0.0.1 -P6033  -e "select @@hostname";

2.在 ProxySQL 中查看SQL请求路由信息stats_mysql_query_digest

select * from stats_mysql_query_digest;

select hostgroup,schemaname,username,digest_text,count_star from stats_mysql_query_digest;

七、日志查看方式

select * from mysql_server_ping_log;