Mysql主从配置之读写分离篇

一、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=rootadmin-username=proxyadmin-password=123.comproxy-address=172.25.20.4:4000proxy-read-only-backend-addresses=172.25.20.3proxy-backend-addresses=172.25.20.2proxy-lua-script=/usr/local/mysql-proxy/lua/rw-splitting.luaadmin-lua-script=/usr/local/mysql-proxy/lua/admin-sql.lualog-file=/usr/local/mysql-proxy/logs/mysql-proxy.loglog-level=infodaemon=truekeepalive=true[root@server4 mysql-proxy]# chmod 660 /etc/mysql-proxy.cnf

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

[root@server4 mysql-proxy]# vim /usr/local/mysql-proxy/lua/rw-splitting.luaif 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 4000tcp        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 changedmysql> 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 existmysql> 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上查看：

##mastermysql> 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)##slavemysql> 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 existmysql> 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+asDFmysql> SELECT * FROM myuser.user;ERROR 1146 (42S02): Table 'myuser.user' doesn't existmysql> 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)