mysql5.7+proxysql实现读写分离
系统环境:
Centos7.5 三台
IP地址和版本:
18.18.23.107 master
18.18.23.115 slave
18.18.23.116 proxysql
mysql版本: 5.7.22
proxysql 版本: 1.4.10
# wget https://github.com/sysown/proxysql/releases/download/v1.4.10/proxysql-1.4.10-1-centos7.x86_64.rpm
master和slave做基于二进制日志的主从复制:
1 master配置:
mysql-db1配置文件my.cnf :
server-id = 1
log-bin = data/services/mysql7/data/master-bin
binlog_format = mixed
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'18.18.23.115' identified by '123.cn';
mysql> flush privileges;
2.slave配置:
mysql-db2配置文件my.cnf :
server-id = 5
log-bin = data/services/mysql7/data/slave-bin
binlog_format = mixed
配置复制连接:
mysql> CHANGE MASTER TO MASTER_HOST='18.18.23.107',
-> MASTER_USER='slave',
-> MASTER_PORT=3306,
-> MASTER_PASSWORD='123.cn',
-> MASTER_LOG_FILE='master-bin.000058',
-> MASTER_LOG_POS=64641;
启动线程:
Mysql>START SLAVE;
查看复制状态:
Mysql>SHOW SLAVE STATUS\G;
检查:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
检测主从同步:
master创建数据库test:
mysql> create database test;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
| zabbix |
+--------------------+
6 rows in set (0.00 sec)
slave 查看是否同步:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
| zabbix |
+--------------------+
6 rows in set (0.00 sec)
3.proxysql服务器配置:
# yum -y install proxysql-1.4.10-1-centos7.x86_64.rpm
# rpm -ql proxysql
/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
proxysql配置文件:
# egrep -v "^#|^$" etc/proxysql.cnf
datadir="/data/services/proxysql" #数据目录
admin_variables=
{
admin_credentials="admin:admin" #连接管理端的用户名与密码
mysql_ifaces="0.0.0.0:6032" #管理端口，用来连接proxysql的管理数据库
}
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"
#指定转发端口，用于连接后端mysql数据库的，相当于代理作用
default_schema="information_schema"
stacksize=1048576
server_version="5.7.22" #指定后端mysql的版本
connect_timeout_server=3000
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
}
# etc/init.d/proxysql start
#proxysql客户端监听在6033端口上，管理端监听6032端口
# ss -lntup |grep proxy
tcp LISTEN 0 128 *:6032 *:* users:(("proxysql",pid=7032,fd=23))
tcp LISTEN 0 128 *:6033 *:* users:(("proxysql",pid=7032,fd=22))
tcp LISTEN 0 128 *:6033 *:* users:(("proxysql",pid=7032,fd=21))
tcp LISTEN 0 128 *:6033 *:* users:(("proxysql",pid=7032,fd=20))
tcp LISTEN 0 128 *:6033 *:* users:(("proxysql",pid=7032,fd=19))
连接proxysql管理端进行配置：
# ./bin/mysql -uadmin -padmin -h 127.0.0.1 -P6032
mysql> show databases;
mysql> show tables;
添加后端的mysql主机：
mysql> insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(1,'18.18.23.107',3306,1,'Write Group');
mysql> insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(2,'18.18.23.115',3306,1,'Read Group');
#使用insert语句添加主机到mysql_servers表中，其中：hostgroup_id 1 表示写组，2表示读组。
mysql> select * from mysql_servers;
添加可以访问后端主机的账号：
在master添加可以增删改查的账号.:
mysql> GRANT ALL ON *.* TO 'proxysql'@'18.18.23.%' IDENTIFIED BY '123.cn';
在proxysql主机上:
mysql> insert into mysql_users(username,password,default_hostgroup,transaction_persistent)values('proxysql','123.cn',1,1);
mysql> select * from mysql_users\G;
*************************** 1. row ***************************
username: proxysql
password: 123.cn
active: 1
use_ssl: 0
default_hostgroup: 1
default_schema: NULL
schema_locked: 0
transaction_persistent: 1
fast_forward: 0
backend: 1
frontend: 1
max_connections: 10000
1 row in set (0.00 sec)
添加健康监测的账号：
在master端添加:
mysql> GRANT SELECT ON *.* TO 'monitor'@'18.18.23.%' IDENTIFIED BY 'monitor';
在proxysql主机上:
mysql> set mysql-monitor_username='monitor';
mysql> set mysql-monitor_password='monitor';
添加读写分离的路由规则:
mysql> insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(1,1,'^SELECT.*FOR UPDATE$',1,1);
mysql> insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(2,1,'^SELECT',2,1);
mysql> select rule_id,active,match_digest,destination_hostgroup,apply from mysql_query_rules;
将修改的数据加载至RUNTIME中,使配置生效:
mysql> load mysql users to runtime;
mysql> load mysql servers to runtime;
mysql> load mysql query rules to runtime;
mysql> load mysql variables to runtime;
mysql> load admin variables to runtime;
永久保存配置:
mysql> save mysql users to disk;
mysql> save mysql servers to disk;
mysql> save mysql query rules to disk;
mysql> save mysql variables to disk;
mysql> save admin variables to disk;
测试读写分离:
连接proxysql客户端:
# mysql -uproxysql -p123.cn -h127.0.0.1 -P6033
尝试修改数据库和查询:
mysql> create database student;
mysql> create database yuan;
