MySQL数据库运维之读写分离01

头像
夏日寒冰
    阅读 6 分钟
    5
    之前文章介绍了MySQL的主从同步复制，了解了主从同步的架构及搭建过程。主从同步一方面用来做数据库的数据备份，另外一个很重要的因素是用来解决网站的读写瓶颈，本篇文章将介绍MySQL读写分离的原理，架构，然后使用mysql-proxy中间件搭建一个简单的mysql读写分离集群架构！微信搜索公众号：”菜鸟封神记“，定期分享一线大厂常用技术干货。

    一、读写分离的含义
    简单来说，数据库的读写分离就是有多个数据库，这些数据库划分为读写库和只读库，读写库可以提供数据的读服务和写服务，只读库只能提供读服务。这种通过将数据库按照职责划分的架构，称为数据库的读写分离架构。

    二、读写分离作用
    1、安全，读写分离通常是给数据库连接方提供一个代理服务器的IP地址和端口，可以有效隐藏数据库的真实IP和端口
    2、将读写请求分担到不同的服务器上执行，让多个数据库各司其职，共同分担压力，能够有效提升数据库的读写性能
    3、扩展简单，当数据库读压力过大时，可以迅速扩展从库，提高数据库的读性能

    三、读写分离架构示意图
    图片描述

    四、常见的实现方式
    1、自研发程序实现代理中间件
    2、使用开源的数据库读写分离中间件，常见的有：MyCat，mysql-proxy

    五、使用mysql-proxy实现MySQL读写分离
    1、环境准备
    主库：192.168.0.4:3306（主机名：mysql-server01）
    从库：192.168.0.5:3306（主机名：mysql-server02）
    代理服务器：192.168.0.6:3306
    mysql版本：mysql-5.6.39
    mysql-proxy下载地址：https://downloads.mysql.com/a...
    2、搭建MySQL主从环境，之前文章详细介绍过，此处不再赘述，可参考文章：https://segmentfault.com/a/11...
    3、将mysql-proxy代理软件上传至代理服务器192.168.0.6上，然后解压重命名

    [root@mysql-proxy ~]# tar xf mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz 
[root@mysql-proxy ~]# mv mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit /usr/local/mysql-proxy-0.8.5

    4、配置mysql-proxy的环境变量，方便直接使用mysql-proxy命令

    #编辑/etc/profile文件
[root@mysql-proxy ~]# vim /etc/profile
#添加如下内容
    MYSQL_PROXY_HOME=/usr/local/mysql-proxy-0.8.5
    PATH=$PATH:$MYSQL_PROXY_HOME/bin
#使配置文件生效
[root@mysql-proxy ~]# source /etc/profile

    5、创建mysql-proxy的配置文件目录和日志目录

    [root@mysql-proxy ~]# mkdir -pv /usr/local/mysql-proxy-0.8.5/{logs,conf}

    6、编辑mysql-proxy配置文件，写入配置内容，然后保存退出

    [root@mysql-proxy ~]# vim /usr/local/mysql-proxy-0.8.5/conf/mysql-proxy.conf

#写入如下内容
[mysql-proxy]
plugins=admin,proxy
admin-username=admin
admin-password=admin
admin-lua-script=/usr/local/mysql-proxy-0.8.5/lib/mysql-proxy/lua/admin.lua
proxy-backend-addresses=192.168.0.4:3306
proxy-read-only-backend-addresses=192.168.0.5:3306
proxy-lua-script=/usr/local/mysql-proxy-0.8.5/share/doc/mysql-proxy/rw-splitting.lua
log-file=/usr/local/mysql-proxy-0.8.5/logs/mysql-proxy.log
log-level=debug
daemon=true
keepalive=true

    参数解释：

    plugins：要加载的插件，admin表示管理插件，可以查看所代理服务器的状态信息，proxy表示代理插件
admin-username：管理用户的用户名
admin-password：管理用户的密码
admin-lua-script：表示要加载的lua脚本，admin.lua是用来查看被代理服务器状态的脚本
proxy-backend-addresses：表示所代理读写库的ip地址和端口，用来配置主从中的主库，可读可写
proxy-read-only-backend-addresses：表示所代理读库的ip地址和端口，用来配置主从中的从库
rw-splitting.lua：该脚本是用来实现读写分离的脚本，mysql-proxy中自带，可以直接使用
log-file：表示mysql-proxy服务的日志文件路径
log-level：表示日志级别
daemon：表示是否以后台进程启动
keepalive：当mysql-proxy崩溃时，是否尝试重启

    注意：admin插件功能启动之后默认监听在TCP的4041端口，proxy插件默认监听在TCP的4040端口
    7、检查主从库的防火墙是否关闭，如果未关闭，也可以配置防火墙规则，使得MySQL代理服务器可以访问主从库上TCP的3306端口

    [root@mysql-server01 ~]# service iptables status
[root@mysql-server01 ~]# service iptables status

    8、开启mysql-proxy

    #启动mysql-proxy
[root@mysql-proxy ~]# mysql-proxy --defaults-file=/usr/local/mysql-proxy-0.8.5/conf/mysql-proxy.conf
#查看日志是否正常，如下日志输出表示启动正常
[root@mysql-proxy ~]# tail -f /usr/local/mysql-proxy-0.8.5/logs/mysql-proxy.log 
    2018-05-07 07:28:09: (critical) plugin proxy 0.8.5 started
    2018-05-07 07:28:09: (debug) max open file-descriptors = 1024
    2018-05-07 07:28:09: (message) admin-server listening on port :4041
    2018-05-07 07:28:09: (message) proxy listening on port :4040
    2018-05-07 07:28:09: (message) added read/write backend: 192.168.0.4:3306
    2018-05-07 07:28:09: (message) added read-only backend: 192.168.0.5:3306
#查看端口是否启动，4040端口和4041端口
[root@mysql-proxy ~]# netstat -tunlp
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address               Foreign Address             State       PID/Program name   
tcp        0      0 0.0.0.0:4040                0.0.0.0:*                   LISTEN      10095/mysql-proxy   
tcp        0      0 0.0.0.0:4041                0.0.0.0:*                   LISTEN      10095/mysql-proxy

    9、查看代理服务器上主从库的当前状态

    #使用管理用户连接代理，代理上的admin管理服务默认监听在tcp的4041端口
[root@mysql-proxy ~]# mysql -uadmin -padmin -P4041
#使用查看被代理服务器的状态
mysql> SELECT * FROM backends \G
*************************** 1. row ***************************
      backend_ndx: 1
          address: 192.168.0.4:3306
            state: up  #表示被代理服务器的状态，可能得值有：up,unkonwn,down
             type: rw
             uuid: NULL
connected_clients: 0
*************************** 2. row ***************************
      backend_ndx: 2
          address: 192.168.0.5:3306
            state: unknown #此时没有查询操作，所以状态可能为unkown
             type: ro
             uuid: NULL
connected_clients: 0
2 rows in set (0.00 sec)

    10、在主库上创建读写分离用户

    mysql> GRANT ALL ON *.* TO 'proxy'@'192.168.0.%' IDENTIFIED BY 'proxy';
mysql> FLUSH PRIVILEGES;

    11、可以看到上述的从库状态为unknown，表示未知状态，在主库上创建用于增删改查的账户，然后在代理服务器上执行建库、建表和插入数据操作，查看是否能够正常同步

    [root@mysql-proxy ~]# mysql -uproxy -pproxy -P4040
mysql> CREATE DATABASE proxy_db;
 Query OK, 1 row affected (0.13 sec)
#查看是否创建成功
mysql> SHOW DATABASES;
#在主库和从库中分别查看proxy_db库是否正常创建和同步，此处不演示，可直接登陆主从库查看

    12、上述测试正常之后，需要验证读和写是否分离。可以通过在主库和从库上执行建库和查询操作，然后在主从库上分别通过tcpdump抓包操作完成验证，tcpdump的抓包操作此处不做详解，可查阅资料
    （1）修改MySQL读写分离lua脚本中的如下内容，使其连接数很少时依然使用读写分离

    [root@mysql-proxy ~]# vim /usr/local/mysql-proxy-0.8.5/share/doc/mysql-proxy/rw-splitting.lua
    if not proxy.global.config.rwsplit then
            proxy.global.config.rwsplit = {
                    #以下两处将默认的值改为1，表示连接数为多少时使用读写分离
                    min_idle_connections = 1, 
                    max_idle_connections = 1,
                    is_debug = false
            }
    end

    （2）连接主库192.168.0.4，然后启动抓包操作，并连接代理服务器，然后执行create database操作

    #在主库上执行抓包操作
[root@mysql-server01 ~]# tcpdump -i eth0 -s0 -nn -XX tcp dst port 3306 and dst host 192.168.0.4
#连接代理服务器
[root@mysql-proxy ~]# mysql -uproxy -pproxy -P4040
#执行数据库创建操作
mysql> CREATE DATABASE test1;
#执行完成create操作之后，可以看到主服务器上打印出的类似如下数据包信息
    10:46:06.909236 IP 192.168.0.6.47684 > 192.168.0.4.3306: Flags [P.], seq 1552605453:1552605479,...
    ...
    10:46:06.911517 IP 192.168.0.5.48496 > 192.168.0.4.3306: Flags [.], ack 2392886444...
    ...
    10:46:06.911535 IP 192.168.0.6.47684 > 192.168.0.4.3306: Flags [.], ...
从上述的输出结果可以看出，执行DDL写操作时，操作的数据库为主库。
#在从库上执行抓包操作
[root@mysql-server02 ~]# tcpdump -i eth0 -s0 -nn -XX tcp dst port 3306 and dst host 192.168.0.5
#连接代理服务器
[root@mysql-proxy ~]# mysql -uproxy -pproxy -P4040
#执行查询操作
mysql> SELECT user FROM mysql.user;
#执行完成select操作之后，可以看到从服务器上打印出类似如下的数据包信息
    04:13:31.854981 IP 192.168.0.6.56506 > 192.168.0.5.3306: Flags 
    ...
    04:13:31.866803 IP 192.168.0.6.56506 > 192.168.0.5.3306: Flags [.], ack 3371...
    ...
    04:13:31.877291 IP 192.168.0.6.56506 > 192.168.0.5.3306: Flags [.], a...
    ...
#从上述的输出结果可以看出，执行DDL写操作时，操作的数据库为从库。

    （3）使用管理用户连接带来服务器，然后查看被代理主从服务器的当前状态

    [root@mysql-proxy ~]# mysql -uproxy -pproxy -P4040
#查询被代理服务器的当前状态
mysql> SELECT * FROM backends;
#此时可以看到两个被代理服务器的状态都为up，表示读写分离配置成功。

    至此，MySQL读写分离原理及使用mysql-proxy中间件实现的主从分离架构搭建过程介绍完毕，下篇文章将介绍另外一种常用的实现读写分离的中间件产品MyCat！欢迎评论转发！

    后续更多文章将更新在个人小站上，欢迎查看。

    另外提供一些优秀的IT视频资料，可免费下载！如需要请查看https://www.592xuexi.com

    mysql
    夏日寒冰
    318 声望85 粉丝

    忠实的技术爱好者，追求极致，喜欢总结一些自己用过的技术点，与他人交流分享。

    « 上一篇
    MySQL数据库运维之数据恢复
    下一篇 »
    MySQL数据库运维之读写分离02

    引用和评论

    推荐阅读
    头像
    面试常问的Spring源码，出maven版本了

    夏日寒冰阅读 526

    头像
    在Java程序中监听mysql的binlog

    huan1993赞 7阅读 1.2k

    头像
    Mysql数据库sql优化案例

    杜若赞 4阅读 1.9k

    头像
    MySQL 亿级数据平滑迁移实战

    vivo互联网技术赞 2阅读 834

    头像
    MySQL间隙锁，next-key锁

    杜若赞 2阅读 9.8k评论 1

    头像
    高效工程师的七个习惯

    Bytebase赞 3阅读 406

    头像
    sqlmap注入语句学习，帮助你排查SQL注入漏洞

    TANKING赞 2阅读 450

    0 条评论
    得票最新