proxy最大的作用是实现mysql的读写分离。其他可用作负载均衡,故障分析,查询分析,查询过滤和修改等。
位于客户端和mysql服务端之间。
mysql proxy作为一个中间代理,一个连接池,负责将前台应用的连接请求转发给后台的数据库。
并通过lua脚本,实现复杂的连接控制和过滤,从而实现读写分离和负载均衡。
对应用程序是透明的,只需要把应用连接到proxy的监听端口即可。
安装:
配置yum源地址:
cd /etc/yum.repos.d/
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
http://repo.proxysql.com/ProxySQL/proxysql-1.4.x/centos/
--下载地址
------------------------------
安装:
yum install proxysql
rpm -ql proxysql-1.4.10-1.x86_64
/etc/init.d/proxysql
/etc/proxysql.cnf
/usr/bin/proxysql
/usr/share/proxysql/tools/proxysql_galera_checker.sh
/usr/share/proxysql/tools/proxysql_galera_writer.pl
------------------------------
配置文件:
/etc/proxysql.cnf
配置文件只在第一次启动的时候读取进行初始化,后面只读取db文件。
cat /etc/proxysql.cnf
...省略...
admin_variables=
{
admin_credentials="admin:admin"
# mysql_ifaces="127.0.0.1:6032;/tmp/proxysql_admin.sock"
mysql_ifaces="0.0.0.0:6032"
# refresh_interval=2000
# debug=true
}
...省略...
启动:
/etc/init.d/proxysql start
[root@mysql1 ~]# proxysql --version
ProxySQL version v1.4.10-1-g5eb0f3e, codename Truls
proxysql日志:
/var/lib/proxysql/proxysql.log
------------------------------
--proxysql的管理端口:
netstat -naltp |grep -i proxy
tcp 0 0 0.0.0.0:6032 0.0.0.0:* LISTEN 5757/proxysql
tcp 0 0 0.0.0.0:6033 0.0.0.0:* LISTEN 5757/proxysql
tcp 0 0 127.0.0.1:6032 127.0.0.1:42597 ESTABLISHED 5757/proxysql
------------------------------
proxysql的管理接口:
yum install mysql
mysql -uadmin -padmin -h127.0.0.1 -P6032
show databases;
show tables;
show create table mysql_servers;
查看当前的管理用户名和密码:
select @@admin-admin_credentials;
修改或者增加管理用户和密码的两种方法:
set admin-admin_credentials='admin:admin;myuser:myuser';
update global_variables set variable_value='admin:root123;admin1:admin1'
where variable_name='admin-admin_credentials';
load admin variables to runtime;
save admin variables to disk;
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;
------------------------------
配置proxysql
需要在主库上创建proxy的监控账户和业务账户
GRANT ALL PRIVILEGES ON *.* TO 'monitor'@'%' IDENTIFIED BY 'monitor';
GRANT ALL PRIVILEGES ON *.* TO 'witt'@'%' IDENTIFIED BY 'witt';
在proxy管理命令行添加后端服务器列表并即时生效,信息持久化:
mysql -uadmin -padmin -h127.0.0.1 -P6032 --prompt='proxysql_db> '
insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment)
values(1,'192.168.56.121',3306,1,1000,10,'mysql1');
insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment)
values(1,'192.168.56.122',3306,1,1000,10,'mysql2');
insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment)
values(1,'192.168.56.125',3306,1,1000,10,'mysql3');
insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment)
values(100,'192.168.56.166',3306,1,1000,10,'mysql_1_2_3_vip');
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;
--所有节点组号都为1,之后都将,只有VIP的组号为100
查看当前注册的服务器列表:
select * from mysql_servers;
查看所有注册到proxysql的服务器连接日志:
SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 10;
SELECT * FROM monitor.mysql_server_ping_log ORDER BY time_start_us DESC LIMIT 10;
配置主从信息:
表示该复制组,1为写入组的编号,2为读取组的编号:
insert into mysql_replication_hostgroups(writer_hostgroup,reader_hostgroup,comment) values (1,100,'1master2slave');
LOAD MYSQL SERVERS TO RUNTIME;
save mysql servers to disk;
当生效后,proxysql会去检测后端的数据节点
假设该节点read_only为1,则会将该server移入group 100;
假设该节点read_only为0,则会将该server移入group 1;
这时再查mysql_servers表,就会发现节点的hostgroup_id根据read_only参数自动变化
配置和注册业务账户:
insert into mysql_users(username,password,active,default_hostgroup,transaction_persistent)
values('witt','witt',1,1,1);
save mysql users to mem;
load mysql users to runtime;
save mysql users to disk;
--配置业务用户,并把业务用户的密码加密,指定该用户的默认组号,select语句之外的sql都将连接组号100的库。
配置路由规则:
INSERT INTO mysql_query_rules(active,match_pattern,destination_hostgroup,apply)
VALUES(1,'^SELECT.*FOR UPDATE$',1,1);
INSERT INTO mysql_query_rules(active,match_pattern,destination_hostgroup,apply)
VALUES(1,'^SELECT',100,1);
load mysql query rules to runtime;
save mysql query rules to disk;
--select * from tbname for update;算DML,所以路由规则指向组号1,其他select查询语句分配给组号100
在进行一系列增删改查后,可以通过如下视图查看统计信息,记录了路由信息和用户执行的sql:
select * from stats_mysql_query_digest_reset;
delete from stats_mysql_query_digest;
--清理表
查看proxysql库中所有的表:
show tables from monitor;
show tables from stats;
------------------------------
排错:
1.
proxysql.log中提示:
2018-09-07 20:38:26 MySQL_Monitor.cpp:1437:monitor_ping(): [ERROR] Server 192.168.56.121:3306 missed 3 heartbeats, shunning it and killing all the connections. Disabling other checks until the node comes back online.
mysql_server_ping_log表中显示:
Lost connection to MySQL server at 'handshake: waiting for inital communication packet', system error: 110
需要配置/etc/hosts,或者配置skip_name_resolve=1
2.
mysql_server_ping_log显示:
Can't connect to MySQL server on '192.168.56.121' (107)
需要关闭防火墙