Mysql-Proxy
Mysql-Proxy是mysql官方提供的Mysql中间件服务,上游可接入若干个Mysql-client,后端可连接若干个Mysql-server
它使用Mysql协议,任何使用Mysql-client的上游无需修改任何代码,即可迁移至Mysql-Proxy上
Mysql-Proxy可以分析与修改请求;拦截查询和修改结果则需要通过编写Lua脚本来完成
Mysql-Proxy允许用户指定Lua脚本对请求进行拦截,对请求进行分析与修改,它还允许用户指定Lua脚本对服务器的返回结果
进行修改,加入一些结果集或者去除一些结果集均可
特点
1、 sql拦截与修改
2、 性能分析与监控
3、读写分离
4、请求路由
操作流程
首先搭建基于 Gtid
的主从复制
server1(master)–rw
ip:172.25.30.1/24
yum install -y mysql-*.rpm
vim /etc/my.cnf
server_id=1
log-bin=mysql-bin
gtid_mode=ON
enforce_gtid_consistency=true
systemctl start mysqld
cd /var/lib/mysql
grep password /var/log/mysql.log
mysql -uroot -p
alter user root@localhost identified by 'Wps+123ld';
grant replication slave on *.* to repl@'172.25.30.%' identified by 'Wps+123ld';
show master status;
server2(slave)=r
ip:172.25.30.2/24
yum install -y mysql-*.rpm
vim /etc/my.cnf
server_id=2
gtid_mode=ON
enforce_gtid_consistency=true
systemctl start mysqld
cd /var/lib/mysql
grep password /var/log/mysql.log
mysql -uroot -p
alter user root@localhost identified by 'Wps+123ld';
change master to master_host='172.25.30.1',master_user='repl',master_password='Wps+123ld'',master_auto_position=1;
start slave;
show slave status\G;
Slave_IO_Running: Yes
IO线程已正常运行
Slave_SQL_Running: Yes
SQL线程已正常运行
show tables;
gtid_executed
是否添加
server3(proxy)–代理
ip:172.25.30.3/24
yum install -y mysql-*.rpm
tar xf mysql-proxy-*.tar.gz -C /usr/local
cd /usr/local
mv mysql-proxy-* mysql-proxy
cd mysql-proxy/bin
./mysql-proxy --help
./mysql-proxy --help-proxy
mkdir conf
cd conf
vim mysql-proxy.conf
1 [mysql-proxy]
2 proxy-address=0.0.0.0:3306
mysql-proxy运行的端口
3 proxy-read-only-backend-addresses=172.25.30.2:3306
slave节点:只读
4 proxy-backend-addresses=172.25.30.1:3306
master节点:可读可写
5 proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua
lua脚本的路径
6 pid-file=/usr/local/mysql-proxy/log/mysql-proxy.pid
进程pid的位置
7 log-file=/usr/local/mysql-proxy/log/mysql-proxy.log
日志位置
8 plugins=proxy
9 log-level=debug
定义日志级别
10 keepalive=true
mysql-proxy崩溃时尝试重启
11 daemon=true
打入后台
find . -name *.lua
cd share/doc/mysql-proxy
pwd
/usr/local/mysql-proxy/share/doc/mysql-proxy
vim rw-splitting.lua
修改主机数量
40 min_idle_connections = 1,
最小连接数
41 max_idle_connections = 2,
最大连接数
cd /usr/local/mysql-proxy/
mkdir log
建立目录,存放进程pid和日志
chmod 660 /usr/local/mysql-proxy/conf/mysql-proxy.conf
修改配置文件的权限
/usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf
netstat -ntlp
3306 mysql-proxy
cd /usr/local/mysql-proxy/log
cat mysql-proxy.log
查看日志
测试
server1
mysql -uroot -pWps+123ld
grant insert,update,select on *.* to leon@'%' identified by 'Wps+123ld';
flush privileges;
create database test;
use test;
create table usertb (
-> username varchar(20) not null,
-> password varchar(20) not null);
insert into usertb values ('user1','111');
select * from usertb;
server2
select * from usertb;
数据同步
server3
yum install -y lsof
lsof -i 3306
真实主机1次
mysql -h 172.25.30.3 -uleon -pWps+123ld
show databases;
server3
lsof -i 3306
listen server1
真实主机2次
mysql -h 172.25.30.3 -uleon -pWps+123ld
show databases;
server3
lsof -i 3306
listen server1
真实主机3次
mysql -h 172.25.30.3 -uleon -pWps+123ld
show databases;
server3
lsof -i 3306
listen server2
用户数量很多的时候,数据库的代理就会把后端的数据库实现读写分离
server1是写的数据库
server2是读的数据库
server1和server2满足gtid的异步复制的时候,真机往数据库写入的东西其实是写入了server1,并没有写入server2,server2上面的数据是复制过去的
因此客户在server1、server2上面都能查到刚刚写进去的数据
当关闭server1和server2的异步复制的时候,真机往数据库写入数据的东西只写进了server1,没有写进去server2,server2也没有复制一份
因为server1可以查看到,server2和用户都查不到刚刚写进去的数据,此时的客户读的是server2