mysql proxysql的简单部署读写分离

GRANT ALL ON *.* TO 'monitor'@'%' IDENTIFIED BY 'monitor';
GRANT ALL ON *.* TO 'proxysql'@'%' IDENTIFIED BY 'proxysql';

# 使用命令来配置连接数据库做读写分离，那么配置文件基本上可以不用修改
datadir="/var/lib/proxysql"# 这里可以修改进入proxysql的账号密码端口
admin_variables={
    admin_credentials="admin:proxy1234"mysql_ifaces="0.0.0.0:6032"}
mysql_variables={
    threads=4max_connections=2048default_query_delay=0default_query_timeout=36000000have_compress=truepoll_timeout=2000interfaces="0.0.0.0:6033;/tmp/proxysql.sock"default_schema="information_schema"stacksize=1048576server_version="5.5.30"connect_timeout_server=10000monitor_username="proxysql"monitor_password="proxysql"monitor_history=600000monitor_connect_interval=200000monitor_ping_interval=200000monitor_read_only_interval=1500monitor_read_only_timeout=500ping_interval_server_msec=120000ping_timeout_server=500commands_stats=truesessions_sort=trueconnect_retries_on_failure=10}
mysql_servers =(
)
mysql_users:
(
)
mysql_query_rules:
(
)
scheduler=(
)
mysql_replication_hostgroups=(
)

# proxysql连接数据库
insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values(10,'mysql1-service',3306,1,1000,10,'write mysql');
insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values(20,'mysql2-service',3306,1,10000,10,'read mysql');
insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values(20,'mysql3-service',3306,1,10000,10,'read mysql');

# proxysql创建监控账号和程序账号，要和上面实际数据库里创建的账号一样
insert into mysql_users(username,password,active,default_hostgroup,transaction_persistent) values('proxysql','proxysql',1,10,1);
insert into mysql_users(username,password,active,default_hostgroup,transaction_persistent) values('monitor','monitor',1,10,1);

# 设置账号monitor为监控账号
UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';
UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_password';

# 设置rule
INSERT INTO mysql_query_rules(active,match_pattern,destination_hostgroup,apply) VALUES(1,'^SELECT.*FOR UPDATE$',10,1);
INSERT INTO mysql_query_rules(active,match_pattern,destination_hostgroup,apply) VALUES(1,'^SELECT',20,1);

# 保存，生效
load mysql servers to runtime;
load mysql users to runtime;
load mysql variables to runtime;

save mysql servers to disk;
save mysql users to disk;
save mysql variables to disk;
save mysql users to mem;
load mysql query rules to runtime;
save mysql query rules to disk;
# 查询监控数据，sql语句在哪个数据库执行的
select * from stats_mysql_query_digest;