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版本了

    夏日寒冰阅读 558

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

    huan1993赞 11阅读 2k

    头像
    Mysql数据库sql优化案例

    杜若赞 5阅读 2.1k

    头像
    MySQL 回收表碎片实践教程

    MySQL技术赞 2阅读 321

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

    Bytebase赞 3阅读 640

    头像
    Mysql梳理1

    cashew赞 1阅读 2.1k

    头像
    mysql梳理2

    cashew赞 1阅读 1.5k

    0 条评论
    得票最新