您的位置: 首页  >  文章  >  Mysql 读写分离

Mysql 读写分离

分类: 文章 • 2024-03-06 16:58:44

一、介绍

1.什么是读写分离

读写分离的基本原理是将数据库读和写操作分散到不同的节点上。Mysql 的读写分离需要建立在主从复制基础之上，Master 数据库处理事务性增、改、删操作（INSERT、UPDATE、DELETE），而 Slave 数据库处理SELECT查询操作，Master 数据库的写操作导致的变更通过主从复制架构同步到集群中的 Slave 数据库中。

2.应用场景

因为数据库的 "写" 操作耗时远远大于 "读"操作，读写分离可以增大数据库访问的并发量，从而缓解数据库的压力。

当然数据库不一定要读写分离。如果程序使用数据库较多时，在更新少，查询多的情况下会考虑使用，减少数据库压力，提高性能。当然，数据库也有其它优化方案，分布式数据库、负载均衡、搜索引擎、增加缓存服务器等，都是解决方法。

3.复制延迟

读写分离建立在主从复制的基础之上，当有写操作到达 Master 时，Mater 与 Slave 之间的数据同步，延迟可能达到 1 秒，如果有大量数据同步，延迟 1 分钟也是有可能的。复制延迟会带来一个问题：如果业务服务器将数据写入到数据库 Master 后，立刻（1 秒内）进行读取，此时读操作访问的是 Slave，由于数据还未同步过来，业务上就可能出现问题。例如，用户刚注册完后立刻登录，服务器提示他 "你还没有注册"，而用户明明刚才已经注册成功了。

解决主从复制延迟有几种常见的方法：

1）写操作后的读操作指定发给 Master

例如，注册账号完成后，登录时读取账号的读操作也发给 Master。

2）读取 Slave 失败后再读一次 Master

这就是通常所说的 "二次读取"，二次读取和业务无绑定，只需要对底层数据库访问的 API 进行封装即可，实现代价较小，不足之处在于如果有很多二次读取，将大大增加 Master 的读操作压力。例如，黑客暴力**账号，会导致大量的二次读取操作，Master 可能顶不住读操作的压力从而崩溃。

3）关键业务读写操作全部指向 Master，非关键业务采用读写分离

例如，对于一个用户管理系统来说，注册 + 登录的业务读写操作全部访问 Master，用户的介绍、爱好、等级等业务，可以采用读写分离，因为即使用户改了自己的自我介绍，在查询时却看到了自我介绍还是旧的，业务影响与不能登录相比就小很多，还可以忍受。

4.实现方式

将读和写操作区分开来，然后访问不同的数据库服务器，一般有两种方式：程序代码封装和中间件封装。

程序代码封装指在代码中抽象一个数据访问层，实现读写操作分离和数据库服务器连接的管理。

中间件封装指的是独立一套系统出来，实现读写操作分离和数据库服务器连接的管理。中间件对业务服务器提供 SQL 兼容的协议，业务服务器无须自己进行读写分离。对于业务服务器来说，访问中间件和访问数据库没有区别，事实上在业务服务器看来，中间件就是一个数据库服务器。其基本架构是：

Mysql 读写分离

下面使用 mysql-proxy 来实现 mysql 的读写分离；

二、部署

1.基本环境

    master 服务器：Centos-7.6.1810、MariaDB-5.5.60、192.168.3.29
 
    slave 服务器：Centos-7.6.1810、MariaDB-5.5.60、192.168.3.30

    mysql-proxy 服务器：Centos-7.6.1810、lua-5.3.5、mysql—proxy-0.8.5、192.168.3.28

基于上篇的已完成的主从复制架构，在此基础上，配置 mysql-proxy 区分读和写的调度即可；

2.安装 Lua

Lua 是一个小巧的脚本语言。Lua 由标准 C 编写而成，代码简洁优美，几乎在所有操作系统和平台上都可以编译，运行。一个完整的 Lua 解释器不过 200k，在目前所有脚本引擎中，Lua 的速度是最快的。这一切都决定了 Lua 是作为嵌入式脚本的最佳选择。

下载地址：http://www.lua.org/download.html

1）获取 Lua 部署包

[[email protected] ~]# curl -R -O http://www.lua.org/ftp/lua-5.3.5.tar.gz
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100  296k  100  296k    0     0   182k      0  0:00:01  0:00:01 --:--:--  182k
[[email protected] ~]# ls
lua-5.3.5.tar.gz

2）安装 Lua 需要的依赖包

[[email protected] ~]# yum -y install gcc* gcc-c++* autoconf* automake* zlib* libxml* ncurses-devel* libmcrypt* libtool* flex* pkgconfig* libevent* glib* readline-devel
...

3）编译安装 Lua

[[email protected] ~]# tar -zxvf lua-5.3.5.tar.gz  -C /usr/local
...
[[email protected] ~]# cd /usr/local/lua-5.3.5
[[email protected] lua-5.3.5]# make linux
...
[[email protected] lua-5.3.5]# make install
...
[[email protected] lua-5.3.5]# export LUA_CFLAGS="-I/usr/local/include" LUA_LIBS="-L/usr/local/lib -llua -ldl" LDFLAGS="-lm"

3.安装 mysql-proxy

1）获取安装包

[[email protected] ~]# wget https://cdn.mysql.com/archives/mysql-proxy/mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz
[[email protected] ~]# ls
lua-5.3.5.tar.gz  mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz

2）解压即可

[[email protected] ~]# tar -zxvf mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz -C /usr/local
...
[[email protected] ~]# cd /usr/local
[[email protected] local]# mv mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit/ mysql-proxy

4.配置读写分离

1）修改 Lua 脚本

[[email protected] mysql-proxy]# cp /usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua ./
[[email protected] mysql-proxy]# vim rw-splitting.lua
## 修改 min_idle_connections 和 max_idle_connections 值为1

--- 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

2）启动mysql-proxy，并指明启动参数（Mater 地址，Slave 地址、Lua脚本路径）

[[email protected] mysql-proxy]# cd bin
[[email protected] bin]# ./mysql-proxy --proxy-read-only-backend-addresses=192.168.3.30:3306 --proxy-backend-addresses=192.168.3.29:3306 --proxy-lua-script=/usr/local/mysql-proxy/rw-splitting.lua &

3）登录 Mater 数据库，创建读写分离的数据库链接账户

[[email protected] ~]# mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 7
Server version: 5.5.60-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> grant all on *.* to 'proxy'@'192.168.3.28' identified by 'proxy';   
Query OK, 0 rows affected (0.01 sec)

5.测试

可使用任意 mysql 数据库客户端登录 [email protected] 账户，进行读写分离测试；

下面在 windows 上使用 Navicat Premium 测试：

1）登录

Mysql 读写分离

 连接名 随便起，端口 选4040（ mysql-proxy 进程监听的端口）；

2）初始数据如下

Mysql 读写分离

3） 插入一条数据并查看

Mysql 读写分离

上面 insert 的语句 其实是在 Master 数据库中操作的，而 select 语句 是在 Slave 数据库完成的，只是我们还看不出效果，下面在 Slave 数据库中停止 slave 服务，然后再次测试。

4）登录 Slave 数据库，停止 slave 服务

[[email protected] ~]# mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 5.5.60-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> stop slave;
Query OK, 0 rows affected (0.01 sec)

5）再次插入数据并查看

Mysql 读写分离

插入数据成功；

Mysql 读写分离

查看表， 刚刚插入的数据不存在，是因为 insert 的操作，是在 Master 数据库中完成的，而select 的操作是在 Slave 数据库中进行的，由于刚刚我们停止了 Slave 数据库的 slave 服务，导致插入 Master 数据库的数据无法同步到 Slave 数据库中，所以查不到数据；

6）进一步验证

登录 Master 数据库，查看刚刚插入的数据存在；

[[email protected] ~]# mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 12
Server version: 5.5.60-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> use school;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [school]> select * from student;
+------+--------+------+
| id   | name   | age  |
+------+--------+------+
|    1 | syztoo |   19 |
|    2 | syz    |   22 |
|    3 | too    |   33 |
+------+--------+------+
3 rows in set (0.00 sec)

登录 Slave 数据库，查看刚刚插入的数据不存在：

[[email protected] ~]# mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 13
Server version: 5.5.60-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> use school;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [school]> select * from student;
+------+--------+------+
| id   | name   | age  |
+------+--------+------+
|    1 | syztoo |   19 |
|    2 | syz    |   22 |
+------+--------+------+
2 rows in set (0.00 sec)

查看 Slave 状态未运行：

MariaDB [school]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.168.3.29
                  Master_User: slave30
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 3034
               Relay_Log_File: mariadb-relay-bin.000007
                Relay_Log_Pos: 1711
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: No
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 3034
              Relay_Log_Space: 3433
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 29
1 row in set (0.00 sec)

开启 slave 服务，再次查看数据已存在：

MariaDB [school]> start slave;
Query OK, 0 rows affected (0.00 sec)

MariaDB [school]> select * from student;
+------+--------+------+
| id   | name   | age  |
+------+--------+------+
|    1 | syztoo |   19 |
|    2 | syz    |   22 |
|    3 | too    |   33 |
+------+--------+------+
3 rows in set (0.00 sec)

Mysql 读写分离

基于 Mariadb 的 读写分离部署成功！！！

 

相关推荐