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=

    max_connections=

    default_query_delay=

    default_query_timeout=

    have_compress=true

    poll_timeout=

    interfaces="0.0.0.0:6033;/tmp/proxysql.sock"

    default_schema="information_schema"

    stacksize=

    server_version="5.5.30"

    connect_timeout_server=

    monitor_username="proxysql"

    monitor_password="proxysql"

    monitor_history=

    monitor_connect_interval=

    monitor_ping_interval=

    monitor_read_only_interval=

    monitor_read_only_timeout=

    ping_interval_server_msec=

    ping_timeout_server=

    commands_stats=true

    sessions_sort=true

    connect_retries_on_failure=

}

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(,'mysql1-service',,,,,'write mysql');

insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values(,'mysql2-service',,,,,'read mysql');

insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values(,'mysql3-service',,,,,'read mysql');


# proxysql创建监控账号和程序账号，要和上面实际数据库里创建的账号一样

insert into mysql_users(username,password,active,default_hostgroup,transaction_persistent) values('proxysql','proxysql',,,);

insert into mysql_users(username,password,active,default_hostgroup,transaction_persistent) values('monitor','monitor',,,);


# 设置账号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(,'^SELECT.*FOR UPDATE$',,);

INSERT INTO mysql_query_rules(active,match_pattern,destination_hostgroup,apply) VALUES(,'^SELECT',,);


# 保存，生效

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;