使用mysql-proxy配置mysql读写分离

简介

对于很多大型网站(pv值百万、千万)来说,在所处理的业务中,其中有70%的业务是查询(select)相关的业务操作(新闻网站,插入一条新闻。查询操作),剩下的则是写(insert、update、delete,只要能对MySQL的数据造成更改的操作都叫写操作)操作。在使用负载均衡集群之后,可以很大程度的提升网站的整体性能,但是最终的数据处理的压力还是会落到MySQL数据库上,所有很有必要使用一些技术来提升MySQL的负载能力。(读写分离)

写操作专门交给写服务器处理(一般网站来说写是比较少的 读写比 4:1) 那么需要把读的任务分配多台服务器来完成的架构,就叫做读写分离。

实现方式

第一种:php程序上自己做逻辑判断,写php代码的时候,自己在程序上做逻辑判读写匹配。select,insert、update、delete做正则匹配,根据结果选择写服务器(主服务器)。如果是select操作则选择读服务器(从服务器器) mysql_connect(‘读写的区分’)

第二种:MySQL- Proxy是实现”读写分离(Read/Write Splitting)”的一个软件(MySQL官方提供 ,也叫中间件),基本的原理是让主数据库处理写操作(insert、update、delete),而从数据库处理查询操作(select)。而数据库的一致性则通过主从复制来实现。所以说主从复制是读写分离的基础

注意:MySQL-proxy它能实现读写语句的区分主要依靠的是内部一个lua脚本(能实现读写语句的判断)。

注意:如果只在主服务器(写服务器)上完成数据的写操作话;这个时候从服务器上没有执行写操作,是没有数据的。这个时候需要使用另外一个技术来实现主从服务器的数据一致性,这个技术叫做 主从复制技术。所以说主从复制是读写分离的基础。

优点: mysql proxy是一个处于你的client和mysql server端之间的简单程序,它可以监听、分析和改变它们的通信。它使用灵活,没有限制,常见用途:负载均衡,故障、查询分析,查询过滤和修改等等。

一、3台机器安装mysql

先把所有机器的防火墙全部关闭

# systemctl stop firewalld
# systemctl disable firewalld
# getenforce 
Disabled
# wget http://mirrors.sohu.com/mysql/MySQL-5.6/mysql-5.6.35-linux-glibc2.5-x86_64.tar.gz

# tar zxvf mysql-5.6.35-linux-glibc2.5-x86_64.tar.gz

# mv mysql-5.6.35-linux-glibc2.5-x86_64 /usr/local/mysql

# cd /usr/local/mysql

# useradd mysql

# mkdir -p /data/mysql

# chown -R mysql:mysql /data/mysql

# yum -y install libaio*

# yum -y install openssl-devel

# ./scripts/mysql_install_db --user=mysql --datadir=/data/mysql

# echo $? 

# cp support-files/my-default.cnf /etc/my.cnf

# cp support-files/mysql.server /etc/init.d/mysqld

# vi /etc/init.d/mysqld 

vim编辑下面两行basedir和datadir配置
basedir=/usr/local/mysql
datadir=/data/mysql

# chmod 755 /etc/init.d/mysqld

# chkconfig --add mysqld

# chkconfig --list

# /etc/init.d/mysqld start

# ps aux |grep mysql

# netstat -lntp |grep mysql

# export PATH=$PATH:/usr/local/mysql/bin/

# vim /etc/profile

# source /etc/profile

# mysql -uroot

二、配置基本信息

主zhdy-02(59.130)

vim /etc/my.cnf

server-id=130

log_bin=zhdy02

datadir=/data/mysql
从zhdy-06(59.135)

vim /etc/my.cnf

server-id=135

datadir=/data/mysql
从zhdy-07(59.136)

vim /etc/my.cnf

server-id=136

datadir=/data/mysql

3台均需要重启服务:

# /etc/init.d/mysqld restart

三、mysql主从授权

主zhdy-02(59.130)
mysql> grant replication slave on *.* to 'repl'@'192.168.59.135' identified by 'zhangduanya';
Query OK, 0 rows affected (0.00 sec)

mysql> grant replication slave on *.* to 'repl'@'192.168.59.136' identified by 'zhangduanya';
Query OK, 0 rows affected (0.00 sec)

mysql>  show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| zhdy02.000001 |      542 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
从zhdy-06(59.135)
mysql> change master to master_host='192.168.59.130', master_user='repl', master_password='zhangduanya', master_log_file='zhdy02.000001', master_log_pos=542;
Query OK, 0 rows affected, 2 warnings (0.03 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
从zhdy-07(59.136)
mysql> change master to master_host='192.168.59.130', master_user='repl', master_password='zhangduanya', master_log_file='zhdy02.000001', master_log_pos=542;
Query OK, 0 rows affected, 2 warnings (0.03 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

直到出现两个“yes”即可!

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

错误:

2017-09-26 16:13:34 4681 [ERROR] Slave I/O: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work. Error_code: 1593

原因分析:

mysql 5.6的复制引入了uuid的概念,各个复制结构中的server_uuid得保证不一样,但是查看到直接copy data文件夹后server_uuid是相同的,

show variables like '%server_uuid%';

解决方法:

找到data文件夹下的auto.cnf文件,修改里面的uuid值,保证各个db的uuid不一样,重启db即可!!


四、主mysql(59.130)配置Mysql-proxy

4.1 下载

cd /usr/local/src
wget https://cdn.mysql.com/archives/mysql-proxy/mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz

4.2 安装并配置环境变量

tar zxvf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz
#因为是二进制包,所以解压后直接移动到指定位置即可
mv mysql-proxy-0.8.5-linux-el6-x86-64bit   /usr/local/mysql-proxy
#配置环境变量
vim  ~/.bash_profile
#加入以下内容
export PATH=$PATH:/usr/local/mysql-proxy/bin
source ~/.bash_profile

4.3 配置mysql-proxy

mysql-proxy自带读写分离脚本,我们还需要修改一下

cp /usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua /usr/local/mysql-proxy/lib/mysql-proxy/lua/

修改读写分离脚本:
vim  /usr/local/mysql-proxy/lib/mysql-proxy/lua/rw-splitting.lua


--- config
--
-- connection pool
if not proxy.global.config.rwsplit then
        proxy.global.config.rwsplit = {
                min_idle_connections = 1,
                max_idle_connections = 1,

                is_debug = false
        }
end
  • 默认最小4个(最大8个)以上客户端连接才会实现读写分离,改为最小1个最大1个,便于读写分离的测试
  • 在读写分离测试时,需要开启多个窗口连接mysql-proxy,这是因为mysql-proxy会检查客户端连接,当连接没有超过min_idle_connections预设值时,不会进行读写分离,即查询操作会发生在master上。

4.4 设置LUA_PATH变量

在/etc/profile中添加LUA_PATH变量的值

vim  /etc/profile
#加入以下内容
LUA_PATH="/usr/local/mysql-proxy/lib/mysql-proxy/lua/?.lua"
export  LUA_PATH

执行source /etc/profile

该变量可以通过mysql-proxy -V查看

[[email protected]02 src]# mysql-proxy -V
mysql-proxy 0.8.5
  chassis: 0.8.5
  glib2: 2.16.6
  libevent: 2.0.21-stable
  LUA: Lua 5.1.4
    package.path: /usr/local/mysql-proxy/lib/mysql-proxy/lua/?.lua;/usr/local/mysql-proxy/lib/mysql-proxy/lua/?.lua
    package.cpath: /usr/local/mysql-proxy/lib/mysql-proxy/lua/?.so;
-- modules
  proxy: 0.8.5

帮助:

参数

作用

–proxy-address

指定代理服务监听的地址和端口(默认是4040),简写为-P

–admin-address

指定管理主机地址和端口(默认是4041)

–proxy-backend-addresses

指定后端mysql服务器的地址和端口(master),简写为-b

–proxy-read-only-backend-addresses

指定后端只读mysql服务器的地址和端口(slave),缩为-r

–proxy-lua-script

指定mysql代理功能的Lua脚本文件

–daemon

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

–defaults-file

指定配置文件路径

–log-file

指定日志文件路径

–log-level

指定日志级别有error,warning,info,message,debug

–user

指定运行mysql-proxy进程的用户

–admin-username

指定登录到mysql-proxy管理界面的用户名

–admin-password

指定登录到mysql-proxy管理界面的用户密码

–admin-lua-script=script-file

管理模块的lua脚本文件路径(创建管理接口)

–plugins

加载插件(admin,proxy)

–proxy-lua-script

指定读写分离脚本位置

–admin-lua-script

指定管理界面脚本位置

4.5 启动MYSQL-PROXY

启动时,我们需要什么功能就指定相应参数:

mysql-proxy --daemon --log-level=debug \
--log-file=/var/log/mysql-proxy.log \
--plugins=proxy -b 192.168.59.130:3306 -r 192.168.59.135:3306 -r 192.168.59.136:3306 \
--proxy-lua-script=/usr/local/mysql-proxy/lib/mysql-proxy/lua/rw-splitting.lua \
--plugins=admin --admin-username=admin \
--admin-password=admin \
--admin-lua-script=/usr/local/mysql-proxy/lib/mysql-proxy/lua/admin.lua
  • 需要指定plugins=proxy才能开启代理功能,同理,需要指定plugins=admin才能使用管理功能。

4.6 查看端口

4.7 查看启动日志

[[email protected] src]# tail -f /var/log/mysql-proxy.log 
2017-09-26 16:55:10: (critical) plugin proxy 0.8.5 started
2017-09-26 16:55:10: (critical) plugin admin 0.8.5 started
2017-09-26 16:55:10: (debug) max open file-descriptors = 1024
2017-09-26 16:55:10: (message) proxy listening on port :4040
2017-09-26 16:55:10: (message) added read/write backend: 192.168.59.130:3306
2017-09-26 16:55:10: (message) added read-only backend: 192.168.59.135:3306
2017-09-26 16:55:10: (message) added read-only backend: 192.168.59.136:3306
2017-09-26 16:55:10: (message) admin-server listening on port :4041

4.8 停止MYSQL-PROXY

ps aux |grep "mysql-proxy"
killall  -9  mysql-proxy

五、读写分离测试

5.1、创建并授权MYSQL-PROXY登录用户

登录master 和 slave,执行以下sql语句

mysql  -uroot 

> grant all on *.* to 'proxy'@'%' identified by 'zhangduanya';

> flush privileges;

5.2、登录MYSQL-PROXY管理后台查看状态

查看master和slave的状态

mysql -uadmin  -padmin -h192.168.59.130 -P4041

mysql> SELECT * FROM backends;
+-------------+---------------------+---------+------+------+-------------------+
| backend_ndx | address             | state   | type | uuid | connected_clients |
+-------------+---------------------+---------+------+------+-------------------+
|           1 | 192.168.59.130:3306 | unknown | rw   | NULL |                 0 |
|           2 | 192.168.59.135:3306 | unknown | ro   | NULL |                 0 |
|           3 | 192.168.59.136:3306 | unknown | ro   | NULL |                 0 |
+-------------+---------------------+---------+------+------+-------------------+
3 rows in set (0.00 sec)
  • 之所以是unknown状态,是因为我们还没有客户端连接,我们多开几个会话后,重新查看状态。

此时,我们的服务已经搭建,接下来我们来进行测试

六、总结

在上述环境中,mysql-master存在单点故障。如果在可用性要求较高的场合,单点隐患是绝对不允许的。为了避免mysql-proxy单点隐患有两种方法;

一种方法是:mysql-proxy配合keepalived做双机。

另一种方法是:为了避免mysql-master单点故障可以使用DRBD+heartbear做双机。

避免mysql-slave单点故障增加多台mysql-slave即可,因为mysql-proxy会自动屏蔽后端发生故障的mysql-slave。

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏运维

升级linux内核到3.0.0

wget http://www.kernel.org/pub/linux/kernel/v3.0/linux-3.0.tar.bz2

27140
来自专栏程序员同行者

开启mysql慢查询

14920
来自专栏linux系统运维

限定某个目录禁止解析php,限制user_agent, php相关配置

40550
来自专栏陈树义

MySQL用户管理:添加用户、授权、删除用户

添加用户 以root用户登录数据库,运行以下命令: create user zhangsan identified by 'zhangsan'; 上面的命令创...

51950
来自专栏云计算教程系列

如何备份你的MySQL数据库

数据库通常会在存储一些有价值的信息。因此,在发生事故或硬件故障时,必须具有可靠的备份以防止数据丢失。

47040
来自专栏xingoo, 一个梦想做发明家的程序员

Winodws安装系统时,通过安装磁盘进行分区

  今天使用一个系统盘安装的时候,很奇怪,分区总是分出来一个系统磁盘,一个MBR,剩下的只能分主分区。   这样就导致我在进行windows激活时,激活工具都找...

26460
来自专栏fixzd

redis系列:主从复制

这篇文章主要讲述Redis的主从复制功能。会依次从环境搭建、功能测试和原理分析几个方面进行介绍。

15040
来自专栏Laoqi's Linux运维专列

Mysql常用基础指令

一、设置更改mysql的root密码 1.1 初次使用mysql的密码为空 /usr/local/mysql/bin/mysql -uroot 但是这样极度的不...

36550
来自专栏ml

linux 下mysql的安装,并设置必要的密码

首先,我使用的是redhat linux ,版本号为: 1 [root@localhost init.d]# cat /proc/version 2 Linux...

542100
来自专栏开发技术

mysql5.7.18的安装与主从复制

    # tar -zxvf mysql-5.7.18-linux-glibc2.5-i686.tar.gz -C /usr/local

13130

扫码关注云+社区

领取腾讯云代金券