虚拟化容器,大数据,DBA,中间件,监控。

Mysql proxy实现读写分离

21 12月
作者:admin|分类:应用管理

MySQL读写分离概念

MYSQL读写分离的原理其实就是让Master数据库处理事务性增、删除、修改、更新操作(CREATE、INSERT、UPDATE、DELETE),而让Slave数据库处理SELECT操作,MYSQL读写分离前提是基于MYSQL主从复制,这样可以保证在Master上修改数据,Slave同步之后,WEB应用可以读取到Slave端的数据。

 

读写分离实现方式

实现MYSQL读写分离可以基于第三方插件,也可以通过开发修改代码实现,具体实现的读写分离的常见方式有如下四种:

Amoeba读写分离;

MySQL-Proxy读写分离;

Mycat读写分离;

 

Mysql-proxy简介

mysql-proxy是官方提供的mysql中间件产品可以实现负载平衡,读写分离,failover等。

MySQL Proxy就是这么一个中间层代理,简单的说,MySQL Proxy就是一个连接池,负责将前台应用的连接请求转发给后台的数据库,并且通过使用lua脚本,可以实现复杂的连接控制和过滤从而实现读写分离和负载平衡。对于应用来说,MySQL Proxy是完全透明的,应用则只需要连接到MySQL Proxy的监听端口即可。

当然,这样proxy机器可能成为单点失效,但完全可以使用多个proxy机器做为冗余,在应用服务器的连接池配置中配置到多 个proxy的连接参数即可。

从图中可以看到,SQL语句并不直接进入到master数据库或者slave数据库,而是进入到 proxy,然后proxy判断这条语句是有关写的语句(包括insert、update、delete)还 是读语句(select),当是写语句的时候,那么proxy将向master所在的服务器发出请 求,同理,如果是读语句的时候,proxy将向slave所在的服务器发出请求。

 

应用背景:在开发工作中,有时候会遇见某个sql 语句需要锁表,导致暂时不能使用读的服务,这样就会影响现有业务,使用主从复制,让主库负责写,从库负责读,这样即使主库出现了锁表的情景,通过读从库也可以保证业务的正常运作。

 

基于mysql-proxy实现读写分离

 

环境准备 proxy centos7.4+mysql5.5

proxy可以选择和mysql部署在同一台服务器,也可以选择单独部署在另一台独立服务器。

server1

192.168.179.100

master

server2

192.168.179.99

slave

server3

192.168.179.101

mysql-proxy

 

下载mysql-proxy:

[root@localhost ~]#

wget http://mirrors.163.com/mysql/Downloads/MySQL-Proxy/mysql-proxy-0.8.4-linuxel6-x86-64bit.tar.gz

[root@localhost src]# ls

debug  kernels  mysql-proxy-0.8.4-linux-el6-x86-64bit.tar.gz

[root@localhost src]# tar xf mysql-proxy-0.8.4-linux-el6-x86-64bit.tar.gz

[root@localhost src]# mv mysql-proxy-0.8.4-linux-el6-x86-64bit  /usr/local/

[root@localhost local]# mv mysql-proxy-0.8.4-linux-el6-x86-64bit   mysql-proxy

[root@localhost local]# cd mysql-proxy/ 

[root@localhost mysql-proxy]# ls  --可以看到已经是二进制的包,不需要编译安装

bin  include  lib  libexec  licenses  share

 

配置proxy环境变量

[root@localhost ~]# echo "export PATH=/usr/local/mysql-proxy/bin:$PATH" > /etc/profile.d/mysql-proxy.sh

[root@localhost ~]# . /etc/profile.d/mysql-proxy.sh  --执行脚本来配置root用户环境变量

[root@localhost ~]# echo $PATH

/usr/local/mysql-proxy/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin

 

启动MYSQL-Proxy中间件

[root@localhost ~]# useradd -r mysql-proxy  --添加mysql-proxy系统用户,这个用户是需要在主库授权用来读写分离的

[root@localhost ~]#

mysql-proxy --daemon --log-level=debug --user=mysql-proxy --keepalive --log-file=/var/log/mysql-proxy.log --plugins="proxy" --proxy-backend-addresses="192.168.179.99:3306" --proxy-read-only-backend-addresses="192.168.179.100:3306" --proxy-lua-script="/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua" --plugins=admin --admin-username="admin" --admin-password="admin" --admin-lua-script="/usr/local/mysql-proxy/lib/mysql-proxy/lua/admin.lua"

 

启动的相关参数

 Mysql-Proxy的相关参数详解如下:

--help-all :获取全部帮助信息;

--proxy-address=host:port :代理服务监听的地址和端口,默认为4040;

--admin-address=host:port :管理模块监听的地址和端口,默认为4041;

--proxy-backend-addresses=host:port :后端写的mysql服务器的地址和端口;

--proxy-read-only-backend-addresses=host:port :后端只读mysql服务器的地址和端口;

--proxy-lua-script=file_name :完成mysql代理功能的Lua脚本;

--daemon :以守护进程模式启动mysql-proxy;

--keepalive :在mysql-proxy崩溃时尝试重启之;

--log-file=/path/to/log_file_name :日志文件名称;

--log-level=level :日志级别;

--log-use-syslog :基于syslog记录日志;

--plugins=plugin :在mysql-proxy启动时加载的插件;

--user=user_name :运行mysql-proxy进程的用户;

--defaults-file=/path/to/conf_file_name :默认使用的配置文件路径,其配置段使用[mysqlproxy]标识;

--proxy-skip-profiling :禁用profile;

--pid-file=/path/to/pid_file_name :进程文件名;

 

[root@localhost ~]# netstat -tpln | grep 40  --出现两个端口4040 4041才证明服务正常启动 如果只有4040杀掉进程pkill mysql-proxy 再重启服务,执行上面的语句

4040端口是给数据端口,即数据的读写都是通过该端口  4041是管理端口的,可以查看读写状态

tcp        0      0 0.0.0.0:4040            0.0.0.0:*               LISTEN      1830/mysql-proxy    

tcp        0      0 0.0.0.0:4041            0.0.0.0:*               LISTEN      1830/mysql-proxy .

 

通过proxy查看读写分离状态

基于4041端口MySQL-Proxy查看读写分离状态,登录4041管理端口 :

[root@localhost ~]# yum install mariadb -y  先下载mysql的客户端工具就可以使用mysql命令了来登入到4041管理端口了

[root@localhost ~]# mysql -h192.168.179.101 -uadmin -padmin -P4041  --通过之前proxy配置的用户admin通过4041端口来登入到mysql proxy的页面

MySQL [(none)]> select * from backends;  --可以看到主库是可读可写的,从库是只读的

+-------------+----------------------+---------+------+------+-------------------+

| backend_ndx | address              | state   | type | uuid | connected_clients |

+-------------+----------------------+---------+------+------+-------------------+

|           1 | 192.168.179.99:3306  | unknown  | rw   | NULL |                0 |

|           2 | 192.168.179.100:3306  | unknown  | ro    | NULL |                 0 |

+-------------+----------------------+---------+------+------+-------------------+

这时可以看到后端数据库信息,只是状态为unknown,表示还没有客户端连接,可以通过4040代理端口通过查询数据等操作激活。

 

 

在master主库上192.168.179.99授权proxy用户

授权proxy,授权proxy用户,这个用户是要给到前端开发人员的,对数据库具有读写功能
mysql> grant all on *.* to "mysql-proxy"@"192.168.179.101" identified by "123456";

Query OK, 0 rows affected (0.01 sec)

允许mysql-proxy用户从192.168.179.101上来登入到主库或者从库来进行对数据库所有操作

mysql> flush privileges;

Query OK, 0 rows affected (0.01 sec)

mysql> select user,host from mysql.user; --在主库授权完可以看到在从库上也是授权了,因为同步所以就不需要再到从库上再次授权,

+-------------+-----------------------+

| user        | host                  |

+-------------+-----------------------+

| mysql-proxy | 192.168.179.101       |

 

 

通过proxy代理创建数据库,验证写是走主库

通过4040代理端口插入数据,该sql语句会走master,于是可以激活master状态:

[root@localhost ~]# mysql -h192.168.179.101 -umysql-proxy -p123456 -P4040

MySQL [(none)]> create database students charset utf8;  --这是写操作

Query OK, 1 row affected (0.00 sec)

在4041管理端口,再次查看

 

[root@localhost ~]# mysql -h192.168.179.101 -uadmin -padmin -P4041  --再次登入mysql proxy终端查看

MySQL [(none)]> select * from backends;  --可以看到主库已经激活了up

+-------------+----------------------+---------+------+------+-------------------+

| backend_ndx | address              | state   | type | uuid | connected_clients |

+-------------+----------------------+---------+------+------+-------------------+

|           1 | 192.168.179.99:3306  | up      | rw   | NULL |                 0 |

|           2 | 192.168.179.100:3306 | unknown | ro   | NULL |                 0 |

+-------------+----------------------+---------+------+------+-------------------+

 

 

通过代理查询数据是走从库

通过proxy创建了一张表,然后插入数据,查询该表

MySQL [students]> select * from t1;

+------+----------+

| id   | name     |

+------+----------+

|    1 | xiaoming |

+------+----------+

1 row in set (0.00 sec)

可以看到通过4040代理端口查询数据,该sql语句会走slave,于是可以激活slave状态

MySQL [(none)]> select * from backends;

+-------------+----------------------+---------+------+------+-------------------+

| backend_ndx | address              | state   | type | uuid | connected_clients |

+-------------+----------------------+---------+------+------+-------------------+

|           1 | 192.168.179.99:3306  | up      | rw   | NULL |                 0 |

|           2 | 192.168.179.100:3306 | up | ro   | NULL |                 0 |

+-------------+----------------------+---------+------+------+-------------------+

2 rows in set (0.00 sec)

 

 

或者换种方式验证查询的数据是不是来自从库

mysql> insert into t1 values(2,"xiaohua");  --在从库插入数据,这个时候主库是没有该条数据的

Query OK, 1 row affected (0.01 sec)

mysql> select * from t1;  --从库查询数据

+------+----------+

| id   | name     |

+------+----------+

|    1 | xiaoming |

|    2 | xiaohua  |

+------+----------+

2 rows in set (0.00 sec)

 

 

[root@localhost ~]#  mysql -h192.168.179.101  -umysql-proxy -p123456 -P4040 -e "select * from students.t1"  --再去通过proxy查询,可以看到xiaohua数据是来源于从库的,可以看到读操作的数据来源于从库

+------+----------+

| id   | name     |

+------+----------+

|    1 | xiaoming |

|    2 | xiaohua  |

+------+----------+

 

 

[root@localhost ~]# mysql -h192.168.179.101  -umysql-proxy -p123456 -P4040  --通过proxy插入数据向t1插入数据

MySQL [(none)]> use students;

Database changed

MySQL [students]> insert into t1 values(3,"lihua");

Query OK, 1 row affected (0.01 sec)

mysql> select * from t1;  --在主库查询

+------+----------+

| id   | name     |

+------+----------+

|    1 | xiaoming |

|    3 | lihua    |

+------+----------+

 

mysql> select * from t1;  --从库查看数据,可以看到通过proxy插入主库的数据同步到了从库

+------+----------+

| id   | name     |

+------+----------+

|    1 | xiaoming |

|    2 | xiaohua  |

|    3 | lihua    |

+------+----------+

如果主库宕机了,是不能提供写操作的,只能进行读操作,不能将从库切换为主库,如果需要实现自动切换需要使用mycat来实现

 

 

浏览475 评论0
返回
目录
返回
首页
Haproxy实现Mysql负载均衡 Skywalking UI使用