# 安裝
yum localinstall -y ./proxysql-2.2.0-1-centos7.x86_64.rpm
# 啟動
systemctl start proxysql

# 1. 登錄管理端
mysql -uadmin -padmin -h127.0.0.1 -P6032
# 2. 配置主從庫
use main;
insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(1,'192.168.0.10',3306,1,'master1');
insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(2,'192.168.0.11',3306,1,'slave1');
# 2.2 檢查添加結(jié)果
select * from main.mysql_servers;
# 2.3 加載配置到RUNTIME
load mysql servers to runtime;
# 2.4 寫盤保存
save mysql servers to disk;
# 3. 配置proxysql客戶端賬戶密碼。
# 設(shè)置所有請求默認到hostgroupid為1的實例然后再根據(jù)路由分發(fā)
# transaction_persistent為1開啟事務(wù)支持
insert into mysql_users(username,password,default_hostgroup,transaction_persistent)values('root','123456',1,1);
# 3.2 查詢賬戶添加結(jié)果
select * from mysql_users;
# 3.3 保存
load mysql users to runtime;
save mysql users to disk;
# 4. 配置健康檢測賬號
# 4.1 登錄后端master的mysql控制臺
GRANT replication client ON *.* TO 'monitor'@'%' IDENTIFIED BY '123456';
flush privileges;
# 4.2 切換回proxy的控制臺
set mysql-monitor_username='monitor';
set mysql-monitor_password='123456';
load mysql variables to runtime;
save mysql variables to disk;
# 5. 配置讀寫分離路由
# 將select語句全部路由至hostgroup_id=2的組，也就是從節(jié)點
# select * from tb for update這樣的語句是修改數(shù)據(jù)的，所以需要單獨定義，將它路由至hostgroup_id=1的組(也就是主節(jié)點)
# 其他沒有被規(guī)則匹配到的組將會被路由至用戶默認的組(mysql_users表中的default_hostgroup)
insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(1,1,'^SELECT.*FOR UPDATE$',1,1);
insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(2,1,'^SELECT',2,1);
load mysql query rules to runtime;
load admin variables to runtime;
save mysql query rules to disk;
save admin variables to disk;

mysql -uroot -p'123456' -P6033 -h 127.0.0.1
# 隨便執(zhí)行點sql語句

select * from stats.stats_mysql_query_digest;
-- 或者 select hostgroup,schemaname,digest_text,count_star,sum_time,min_time,max_time,sum_rows_affected,sum_rows_sent from stats.stats_mysql_query_digest;

set mysql-threads=16;
-- 查看
show variables like '%mysql-threads%';