mysql proxysql的简单部署读写分离
环境需求(centos)
proxysql-server(1)-- 地址:proxysql-service
mysql-master-server(1)--- 地址:mysql1-service
mysql-slave-server(2)--- 地址:mysql2-service 和 mysql3-service
提前做好三台mysql的主从复制
mysql-master-server
创建一个监控账号和一个程序账号(自动同步到slave)
GRANT ALL ON *.* TO 'monitor'@'%' IDENTIFIED BY 'monitor'; GRANT ALL ON *.* TO 'proxysql'@'%' IDENTIFIED BY 'proxysql';
proxysql-server
1.安装proxysql
# vi /etc/yum.repos.d/proxysql.repo
[proxysql_repo]
name= ProxySQL YUM repository
baseurl=https://repo.proxysql.com/ProxySQL/proxysql-2.0.x/centos/\$releasever
gpgcheck=1
gpgkey=https://repo.proxysql.com/ProxySQL/repo_pub_key
# yum -y install proxysql
# /etc/init.d/proxysql start
2.安装mariadb-client(配置proxysql需要使用mysql命令)
# vi /etc/yum.repos.d/mariadb.repo
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.3.5/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
# yum install -y MariaDB-client
3.配置proxysql
# cat /etc/proxysql.conf
# 使用命令来配置连接数据库做读写分离,那么配置文件基本上可以不用修改
datadir="/var/lib/proxysql" # 这里可以修改进入proxysql的账号密码端口 admin_variables= { admin_credentials="admin:proxy1234" mysql_ifaces="0.0.0.0:6032" } mysql_variables= { threads=4 max_connections=2048 default_query_delay=0 default_query_timeout=36000000 have_compress=true poll_timeout=2000 interfaces="0.0.0.0:6033;/tmp/proxysql.sock" default_schema="information_schema" stacksize=1048576 server_version="5.5.30" connect_timeout_server=10000 monitor_username="proxysql" monitor_password="proxysql" monitor_history=600000 monitor_connect_interval=200000 monitor_ping_interval=200000 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_servers = ( ) mysql_users: ( ) mysql_query_rules: ( ) scheduler= ( ) mysql_replication_hostgroups= ( )
另外,proxysql涉及两个端口6032和6033。6032是连接proxysq的管理端口,6033是连接数据库的客户端端口。
进入proxysql
# mysql -uadmin -pproxy1234 -P6032 -h127.0.0.1 --prompt='proxysql> '
# 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;
远程连接(在网络互通情况下,也可以在proxysql本地127.0.0.1)
# mysql -unanjing -pnanjing -hproxysql-service -P6033