刻下岁月

逝者如斯夫,不舍昼夜…
Home  /  DB  /  proxysql 读写分离中间件

proxysql 读写分离中间件

十月 16, 2018 DB Leave a Comment

读写分离的,翻译的中文GitHub:https://github.com/malongshuai/proxysql/wiki

官方文档地址:https://github.com/sysown/proxysql/wiki

我的机器是centos6

安装启动

cat <<EOF | tee /etc/yum.repos.d/proxysql.repo
[proxysql_repo]
name= ProxySQL YUM repository
baseurl=http://repo.proxysql.com/ProxySQL/proxysql-1.4.x/centos/\$releasever
gpgcheck=1
gpgkey=http://repo.proxysql.com/ProxySQL/repo_pub_key
EOF
yum install proxysql OR yum install proxysql-version
service proxysql start
service proxysql stop
proxysql --version //查看版本
mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> ' //链接本地proxsql控制台数据库

我的机器列表,每台都同样的安装即可

101.209.33.1
101.209.33.2
101.209.33.3
101.209.33.4
101.209.33.5
101.209.33.6
101.209.33.7

我的配置

set mysql-default_charset='utf8mb4';
insert into mysql_servers  (hostgroup_id, hostname,port,weight,max_connections) values ('0','101.143.153.141', '3306', '1', '20000'),('1','101.143.153.142', '3306', '1', '20000'),('1','101.206.250.143', '3306', '1', '20000');
insert into mysql_users(username, password, default_hostgroup,transaction_persistent, active, default_schema) values ('xxx', 'xxx', '0', 1, 1, 'xxx');
INSERT INTO mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply) VALUES (1,1,'SELECT.*COUNT\(',1,1);
INSERT INTO mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply) VALUES (2,1,'SELECT.*SUM\(',1,1);
set mysql-monitor_username='xxx_r';
set mysql-monitor_password='xxx_r';
set admin-admin_credentials = 'admin:admin;admin2:admin2';
set admin-cluster_username = 'admin2';
set admin-cluster_password = 'admin2';
insert into proxysql_servers(hostname, port,weight) values 
('101.209.33.1', '6032','1'),
('101.209.33.2', '6032','1'),
('101.209.33.3', '6032','1'),
('101.209.33.4', '6032','1'),
('101.209.33.5', '6032','1'),
('101.209.33.6', '6032','1'),
('101.209.33.7', '6032','1');
set mysql-monitor_enabled='false';
set mysql-default_schema='xxx';
load mysql servers to runtime;
save mysql servers to disk;
load mysql users to runtime;
save mysql users to disk;
load mysql variables to runtime;
save mysql variables to disk;
load proxysql servers to runtime;
save proxysql servers to disk;
load admin variables to runtime;
save admin variables to disk;
load mysql query rules to runtime;
save mysql query rules to disk;

测试是否生效,能链接上并能查询那么则生效

mysql -ujxxx -pxxx -P6033 -h127.0.0.1

查看规则命中情况

select * from stats_mysql_query_rules;

查看集群规则等同步情况

select * from stats.stats_proxysql_servers_checksum

39total visits,28visits today

nginx 访问日志统计
原文地址:http://blog.lunhui.ren/archives/527
原创文章,转载请注明出处!

Leave a Reply

取消回复

近期文章

  • proxysql 读写分离中间件
  • nginx 访问日志统计
  • 统计mysql的binlog语句执行次数
  • git把功能开发错了分支
  • centos6安装mysql5.6
  • linux 查看端口命令
  • Cloudera Manager CHD 切换内置数据库PostgreSQL 到 Mysql
  • nginx 启动失败 (nginx: [emerg] socket() [::]:80 failed)

分类目录

  • DB11
  • English1
  • git2
  • Java16
  • Js4
  • Linux19
  • Nginx1
  • PHP5
  • Tools4
  • window2
  • 大数据2
  • 未分类3
  • 笑一笑1