https://github.com/breeze2/mysql-proxy-docker
https://hub.docker.com/r/proxysql/proxysql
https://www.proxysql.com/documentation/installing-proxysql/
https://www.proxysql.com/documentation/getting-started/
主数据库执行 确认从库也有
# 用于proxysql监控
#insert into mysql.`user`(`host`,`user`,`password`) values("%","monitor",password("monitor"));
grant all privileges on *.* to 'monitor'@"%" identified by "monitor";
# 用于外部程序通过proxysql访问mysql
#insert into mysql.user(host,USER,`PASSWORD`) values("%","msandbox",password("msandbox"));
grant all privileges on *.* to 'msandbox'@"%" identified by 'msandbox';
flush privileges;
select * from mysql.user;
proxysql.cnf
datadir="/var/lib/proxysql"
# 管理平台参数
admin_variables =
{
admin_credentials="admin:admin;radmin:radmin"
mysql_ifaces="0.0.0.0:6032"
refresh_interval=2000
}
# mysql全局参数
mysql_variables =
{
threads=4
max_connections=204800
default_query_delay=0
default_query_timeout=36000000
have_compress=true
poll_timeout=2000
# interfaces="0.0.0.0:6033;/tmp/proxysql.sock"
interfaces="0.0.0.0:6033"
default_schema="information_schema"
stacksize=1048576
server_version="5.7.23"
connect_timeout_server=3000
# make sure to configure monitor username and password
# https://github.com/sysown/proxysql/wiki/Global-variables#mysql-monitor_username-mysql-monitor_password
monitor_username="monitor"
monitor_password="monitor"
monitor_history=600000
monitor_connect_interval=60000
monitor_ping_interval=10000
monitor_read_only_interval=1500
monitor_read_only_timeout=500
ping_interval_server_msec=120000
ping_timeout_server=500
commands_stats=true
sessions_sort=true
connect_retries_on_failure=10
}
# mysql用户参数
mysql_users =
(
{
username = "msandbox"
password = "msandbox"
default_hostgroup = 0
}
)
# mysql服务器参数，10.6.0.10是主库放在0组，其他是从库放在1组
mysql_servers =
(
{
address = "192.168.0.103"
port = 3357
weight = 1
hostgroup = 0
max_connections = 5000
},
{
address = "192.168.0.103"
port = 3357
weight = 2
hostgroup = 1
max_connections = 100000
},
{
address = "192.168.0.105"
port = 3357
weight = 2
hostgroup = 1
max_connections = 150000
}
)
# mysql请求规则，以下配置是读时加锁的请求发给0组，普通读取的请求发给1组，其他默认发给0组（上面的default_hostgroup）
mysql_query_rules:
(
{
rule_id=1
active=1
match_pattern="^SELECT .* FOR UPDATE$"
destination_hostgroup=0
apply=1
},
{
rule_id=2
active=1
match_pattern="^SELECT"
destination_hostgroup=1
apply=1
}
)
启动容器
docker pull proxysql/proxysql
docker run -p 16032:6032 -p 16033:6033 -p 16070:6070 -d \
--name proxysql \
-v /home/proxysql/conf/proxysql.cnf:/etc/proxysql.cnf \
-v /home/proxysql/data:/var/lib/proxysql \
proxysql/proxysql
测试连接
image.png
错误 9006
ProxySQL Error: connection is locked to hostgroup 1 but trying to reach hostgroup 0
解决方法
在你的pc机器上 找到mysql安装路径在 bin下 键入.没有数据库的在mysql官网下载一个免安装的版本不用安装数据库,只要mysql.exe
# 登录proxysql
mysql -h192.168.0.103 -P16032 -uradmin -pradmin
# 数据库信息
show databases;
set mysql-set_query_lock_on_hostgroup=0;
load mysql variables to runtime;
save mysql variables to disk;
service proxysql restart # 重启proxysql
匹配规则
现实中很多场景要求更新数据能立马查到数据，而主从同步在这方面无解，所以从规则上修改，一些需要及时查询的语句在主上。
# 用户登录
mysql -h192.168.0.103 -P16032 -uradmin -pradmin
# 删除规则
delete from mysql_query_rules;
# 插入新规则
insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)
VALUES
(1,1,'^SELECT.*FROM .*score_.*',0,1),
(2,1,'^select.*getSequenceNo.*',0,1),
(3,1,'^SELECT.*FROM.*recharge_granularity.*',0,1),
(4,1,'^SELECT.*FROM.*product_.*',0,1),
(5,1,'^SELECT.*FROM.*person_.*',0,1),
(6,1,'^SELECT.*FROM.*monitoring_.*',0,1),
(7,1,'^SELECT.*FROM.*menu_resource.*',0,1),
(8,1,'^SELECT.*FROM.*manager_logs_info.*',0,1),
(9,1,'^SELECT.*FROM.*industry_.*',0,1),
(10,1,'^SELECT.*FROM.*customized_info.*',0,1),
(11,1,'^SELECT.*FROM.*custom_dictionary_company.*',0,1),
(12,1,'^SELECT.*FROM.*base_tables_.*',0,1),
(13,1,'^SELECT.*FROM.*base_change_group.*',0,1),
(14,1,'^SELECT.*FROM.*order_.*',0,1),
(15,1,'^SELECT.*FROM.*value_order_item.*',0,1),
(16,1,'^SELECT.*FROM.*secu_.*',0,1),
(17,1,'^SELECT.*FOR UPDATE$',0,1),
(18,1,'^SELECT',1,1);
# 查询信息状态
select hostgroup hg,count_star cs,digest,digest_text dt from stats_mysql_query_digest;
# 查询规则
select rule_id,active,match_pattern,match_digest,destination_hostgroup,apply from mysql_query_rules;
# 修改规则
update mysql_query_rules
set match_digest='^select.*getSequenceNo.*'
where rule_id=2;
# 停止规则
update mysql_query_rules
set active=0
where rule_id=2;
# 删除规则
delete from mysql_query_rules where rule_id=2;
# 主从分组信息
select hostgroup_id,hostname,port,status,weight from mysql_servers;
# log
select * from mysql_server_ping_log;
select * from mysql_server_replication_lag_log;
select * from mysql_server_read_only_log;
# 写入新规则
insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)
VALUES (2,1,'^select.*getSequenceNo',0,1);
load mysql query rules to runtime;
save mysql query rules to disk;
最后要讲一下数据库建议是不用docker安装,直接源码安装,或者yum安装到本机上,很多人持的观点是:
- [第一] 数据库要追求性能,那么docker必然是损耗性能的,追求极致那么必然不能用docker,
- [第二] 数据库做动态扩容是很难办到的,必须要对数据库有足够了解了,进行配置修改才能完成.集群性能改造,所以对于绝大多数的人员来说,是无法完成扩容操作的.也就失去了docker的优点.
- [第三] ,很多企业来说,数据库搭建在内网上,他们都是在一开始就设计好数据库的大小,机器的数量,内存大小都已经确定好的,无法或者根本不需要进行扩容操作,
点波关注 系统搭建（docker）
网友评论