Mysql主从配置之读写分离篇

最新推荐文章于 2021-06-08 17:40:12 发布

Running_free

最新推荐文章于 2021-06-08 17:40:12 发布

阅读量531

点赞数

分类专栏： linux运维文章标签： mysql 数据库数据存储主从复制读写分离

本文链接：https://blog.csdn.net/Running_free/article/details/78129467

版权

linux运维专栏收录该内容

84 篇文章 1 订阅

订阅专栏

一、MySQL 主从复制的几种方案

数据库读写分离对于大型系统或者访问量很高的互联网应用来说,是必不可少的一个重要功能。从数据库的角度来说,对于大多数应用来说,从集中到分布,最基本的一个需求不是数据存储的瓶颈,而是在于计算的瓶颈,即 SQL 查询的瓶颈,我们知道,正常情况下,Insert SQL 就是几十个毫秒的时间内写入完成,而系统中的大多数 Select SQL 则要几秒到几分钟才能有结果,很多复杂的 SQL,其消耗服务器 CPU 的能力超强,不亚于死循环的威力。在没有读写分离的系统上,很可能高峰时段的一些复杂 SQL 查询就导致数据库服务器 CPU爆表,系统陷入瘫痪,严重情况下可能导致数据库崩溃。因此,从保护数据库的角度来说,我们应该尽量避免没有主从复制机制的单节点数据库。
对于 MySQL 来说,标准的读写分离是主从模式,一个写节点 Master 后面跟着多个读节点,读节点的数量取决于系统的压力,通常是 1-3 个读节点的配置,如下图所示:
这里写图片描述

二、主从复制+读写分离

大型的电子商务系统一般都会使用一个Master数据库，多个Slave数据库的组合实现读写分离技术。Master库负责数据更新和实时数据查询，Slave库负责非实时数据查询。因为在实际的应用中，数据库都是读多写少（读取数据的频率高，更新数据的频率相对较少），而读取数据通常耗时比较长，占用数据库服务器的CPU较多，从而影响用户体验。我们通常的做法就是把查询从主库中抽取出来，采用多个从库，使用负载均衡，减轻每个从库的查询压力。

　　采用读写分离技术的目标：有效减轻Master库的压力，又可以把用户查询数据的请求分发到不同的Slave库，从而保证系统的健壮性。我们看下采用读写分离的过程：
　　
这里写图片描述
客户端通过master对数据库进行写操作，slave端进行读操作，并且具有主从复制，即slave会备份master的数据。这样读在slave端，写在master端，实现了读写分离，减轻了master的压力，提高并发负载。

三、mysql-proxy实现读写分离

1.环境描述：

操作系统：Red Hat Enterprise Linux Server release 6.5 (Santiago)
主服务器Master：172.25.20.2
从服务器Slave：172.25.20.3
调度服务器MySQL-Proxy：172.25.20.4
测试客户端 client：172.25.20.5（mysql）
mysql主从复制
此处省略，请参考笔者的另一篇博客《MySQL 5.7.19 主从复制实现与调优》
传送门：http://t.cn/R0WnDhC

2.安装mysql-proxy

在调度服务器（server4）上安装

实现读写分离是有lua脚本实现的，现在mysql-proxy里面已经集成，无需再安装

官网下载地址：https://downloads.mysql.com/archives/get/file/mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz

可找你需要的版本从：http://dev.mysql.com/downloads/mysql-proxy/

[root@server4 ~]# wget https://downloads.mysql.com/archives/get/file/mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz
[root@server4 ~]# tar -zxf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz 
[root@server4 ~]# mv mysql-proxy-0.8.5-linux-el6-x86-64bit /usr/local/mysql-proxy

3、配置mysql-proxy，创建主配置文件

[root@server4 ~]# cd /usr/local/mysql-proxy/
[root@server4 mysql-proxy]# mkdir lua
[root@server4 mysql-proxy]# mkdir logs
[root@server4 mysql-proxy]# cp share/doc/mysql-proxy/rw-splitting.lua ./lua/
[root@server4 mysql-proxy]# cp share/doc/mysql-proxy/admin-sql.lua ./lua

[root@server4 mysql-proxy]# vim /etc/mysql-proxy.cnf

[mysql-proxy]
user=root
admin-username=proxy
admin-password=123.com
proxy-address=172.25.20.4:4000
proxy-read-only-backend-addresses=172.25.20.3
proxy-backend-addresses=172.25.20.2
proxy-lua-script=/usr/local/mysql-proxy/lua/rw-splitting.lua
admin-lua-script=/usr/local/mysql-proxy/lua/admin-sql.lua
log-file=/usr/local/mysql-proxy/logs/mysql-proxy.log
log-level=info
daemon=true
keepalive=true

[root@server4 mysql-proxy]# chmod 660 /etc/mysql-proxy.cnf

4.修改读写分离配置文件

[root@server4 mysql-proxy]# vim /usr/local/mysql-proxy/lua/rw-splitting.lua

if not proxy.global.config.rwsplit then
        proxy.global.config.rwsplit = {
        --      min_idle_connections = 4,
        --      max_idle_connections = 8,
                min_idle_connections = 1,
                max_idle_connections = 1,

                is_debug = false
        }
end

5.启动mysql-proxy

[root@server4 mysql-proxy]# /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/etc/mysql-proxy.cnf
[root@server4 mysql-proxy]# netstat -tupln | grep 4000
tcp        0      0 172.25.20.4:4000            0.0.0.0:*                   LISTEN      1091/mysql-proxy 

##关闭mysql-proxy使用：killall -9 mysql-proxy

6.测试读写分离

在主服务器master（server2）上创建proxy用户用于mysql-proxy使用，从服务器也会同步这个操作

mysql> grant all on *.* to 'myproxy'@'172.25.20.4' identified by '1234+asDF';

使用客户端（server5）连接mysql-proxy

[root@server5 ~]# mysql -u myproxy -h 172.25.20.4 -P 4000 -p1234+asDF

创建数据库和表，这时的数据只写入主mysql，然后再同步从slave，可以先把slave的关了，看能不能写入，这里我就不测试了，下面测试下读的数据！

##先查看下客户端和master及slave数据库的内容，应该是相同的

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql> SELECT * FROM test.usertb;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 111      |
+----------+----------+
1 row in set (0.02 sec)

这里写图片描述

接下来进行写入数据测试

##客户端
mysql> CREATE DATABASE myuser;
Query OK, 1 row affected (0.00 sec)

mysql> USE myuser;
Database changed
mysql> CREATE TABLE user (number INT(10),name VARCHAR(255));
Query OK, 0 rows affected (0.08 sec)

mysql> insert into test values(01,'user1');
ERROR 1146 (42S02): Table 'myuser.test' doesn't exist
mysql> insert into user values(01,'user1');
Query OK, 1 row affected (0.00 sec)

mysql> insert into user values(02,'user2');
Query OK, 1 row affected (0.01 sec)

登陆主从mysq查看新写入的数据如下，

mysql> CREATE DATABASE myuser;
mysql> USE myuser;
mysql> CREATE TABLE user (number INT(10),name VARCHAR(255));
mysql> insert into user values(01,'user1');
mysql> insert into user values(02,'user2');

分别在master和slave上查看：

##master
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| myuser             |
| performance_schema |
| sys                |
| test               |
+--------------------+
6 rows in set (0.00 sec)

mysql> SELECT * FROM myuser.user;
+--------+-------+
| number | name  |
+--------+-------+
|      1 | user1 |
|      2 | user2 |
+--------+-------+
2 rows in set (0.00 sec)


##slave
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| myuser             |
| performance_schema |
| sys                |
| test               |
+--------------------+
6 rows in set (0.00 sec)

mysql> SELECT * FROM myuser.user;
+--------+-------+
| number | name  |
+--------+-------+
|      1 | user1 |
|      2 | user2 |
+--------+-------+
2 rows in set (0.00 sec)

这里写图片描述

好像数据没有同步到slave上，查看master的状态

mysql> show master status ;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000003 |     1100 | test         | mysql            | 42989d0d-a446-11e7-8d8d-525400140b3d:1-4 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

这里写图片描述

问题明白了，只同步test库的内容，我们修改配置似乎有点麻烦，那就在test库里作实验吧

##客户端（server5）
mysql> USE test;
mysql> CREATE TABLE user (number INT(10),name VARCHAR(255));
mysql> insert into user values(01,'user1');
mysql> insert into user values(02,'user2');

##master（server2）上

mysql> SELECT * FROM test.user;
+--------+-------+
| number | name  |
+--------+-------+
|      1 | user1 |
|      2 | user2 |
+--------+-------+
2 rows in set (0.00 sec)

##slave（server3）上
mysql>  SELECT * FROM test.user;
+--------+-------+
| number | name  |
+--------+-------+
|      1 | user1 |
|      2 | user2 |
+--------+-------+
2 rows in set (0.00 sec)

数据同步成功，顺便在客户端上查询下我们刚才写入的数据

mysql>  SELECT * FROM test.user;
+--------+-------+
| number | name  |
+--------+-------+
|      1 | user1 |
|      2 | user2 |
+--------+-------+
2 rows in set (0.00 sec)

这里写图片描述

顺便看看调度服务器（server4）的状态：
这里写图片描述

刚好还可以做个测试，由于我们刚才创建的数据库myuser和库里面的内容写入到了master上，但是没有同步到slave上，那么我们在客户端查询myuser数据库里面的内容，如果可以查询到的话，就说明查询也是走的master，那么我们的读写分离就没有成功，相反，如果查询不到，那么就可以说明查询走的是slave，而写入走的是master，读写分离配置成功。接下来我们一起见证奇迹：

mysql> SELECT * FROM myuser.user;
ERROR 1146 (42S02): Table 'myuser.user' doesn't exist
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| myuser             |
| performance_schema |
| sys                |
| test               |
+--------------------+
6 rows in set (0.00 sec)

果然如我们所料，查询不到，说明我们的读写分离配置成功了
这里写图片描述

这里写图片描述

换一台客户端再登陆到mysql-proxy，查询数据，结果一样，test库里的内容能正常查询，myuser库里的内容不可查询，说明我们配置成功，真正实现了读写分离的效果！

[root@foundation20 ~]# mysql -u myproxy -h 172.25.20.4 -P 4000 -p1234+asDF
mysql> SELECT * FROM myuser.user;
ERROR 1146 (42S02): Table 'myuser.user' doesn't exist
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql>  SELECT * FROM test.user;
+--------+-------+
| number | name  |
+--------+-------+
|      1 | user1 |
|      2 | user2 |
+--------+-------+
2 rows in set (0.00 sec)

Running_free

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
1
评论
Mysql主从配置之读写分离篇

一、MySQL 主从复制的几种方案数据库读写分离对于大型系统或者访问量很高的互联网应用来说,是必不可少的一个重要功能。从数据库的角度来说,对于大多数应用来说,从集中到分布,最基本的一个需求不是数据存储的瓶颈,而是在于计算的瓶颈,即 SQL 查询的瓶颈,我们知道,正常情况下,Insert SQL 就是几十个毫秒的时间内写入完成,而系统中的大多数 Select SQL 则要几秒到几分钟才能有结果,很多复
复制链接

扫一扫