1、安装ProxySQL

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


# yum install proxysql OR yum install proxysql-version
yum install proxysql 

systemctl enable --now proxysql

2、从库设定read_only参数

set global read_only=1;
set global super_read_only=1;

3、主库创建监控用户和应用用户

create user 'monitor'@'%' identified with mysql_native_password by 'mysql';
grant replication client on *.* to 'monitor'@'%';
flush privileges;

create user app@'%' identified with mysql_native_password by 'mysql';
grant all on *.* to zero@'%';
flush privileges;

4、进行配置

mysql -uadmin -padmin -h127.0.0.1 -P6032

4.1、配置读写组编号

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

load mysql servers to runtime;
save mysql servers to disk;

select * from mysql_replication_hostgroups;

4.2、添加主机到ProxySQL

insert into mysql_servers(hostgroup_id,hostname,port) values (10,'172.16.10.101',3306);
insert into mysql_servers(hostgroup_id,hostname,port) values (20,'172.16.10.102',3306);
insert into mysql_servers(hostgroup_id,hostname,port) values (20,'172.16.10.103',3306);

load mysql servers to runtime;
save mysql servers to disk;

select * from mysql_servers;

4.3、主库只写（选择操作）

delete from mysql_servers where hostgroup_id='20' and hostname='172.16.10.101';

load mysql servers to runtime;
save mysql servers to disk;

select * from mysql_servers;

4.4、配置监控

set mysql-monitor_username='monitor';
set mysql-monitor_password='mysql';

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

select * from global_variables where variable_name in('mysql-monitor_username','mysql-monitor_password');
+------------------------+----------------+
| variable_name          | variable_value |
+------------------------+----------------+
| mysql-monitor_password | mysql          |
| mysql-monitor_username | monitor        |
+------------------------+----------------+


# 检查连接到MySQL的日志

select * from monitor.mysql_server_ping_log order by time_start_us desc limit 6;

select * from monitor.mysql_server_connect_log order by time_start_us desc limit 6;

4.5、配置应用用户

insert into mysql_users(username,password,default_hostgroup) values('app','mysql',10);

load mysql users to runtime;
save mysql users to disk;

select * from mysql_users;

4.6、实用的读写路由规则

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 * from mysql_query_rules;

5、测试读写分离

mysql -h127.0.0.1 -P 6033 -uzero -pmysql -e "begin;select @@server_id;commit;"
mysql -h127.0.0.1 -P 6033 -uzero -pmysql -e "select @@server_id;"

select * from stats_mysql_query_digest;

6、添加远程登录用户

mysql -uadmin -padmin -h127.0.0.1 -P6032

select @@admin-admin_credentials;

set admin-admin_credentials='admin:admin;root:proxysql@123';

select @@admin-admin_credentials;

load admin variables to runtime;

save admin variables to disk;

• 远程登录

mysql -uroot -pproxysql@123 -h172.16.10.21 -P16032

7、开启ProxySQL的web监控功能

# 开启web监控功能
SET admin-web_enabled='true';
LOAD ADMIN VARIABLES TO RUNTIME;
SAVE ADMIN VARIABLES TO DISK;
select * from global_variables where variable_name LIKE 'admin-web_enabled';
select @@admin-web_enabled;

lsof -i:6080


# 浏览器访问
https://172.16.10.21:6080
用户名和密码： stats / stats