MySQL 主从复制与读写分离详解

在实际生产环境中，如果对数据库的读、写都在同一个数据库服务器中操作，无论是在安全性、高可用性还是高并发等各个方面都是完全不能满足实际需求的。那么在实际生产环境中是如何解决这个问题？这就需要提到两个对于mysql数据库特别重要的技术--主从复制和读写分离。

实际环境中，一台MySQL主服务器带两台MySQL从服务器做数据复制，前端应用在进行数据库写操作时，对主服务器进行操作；在进行数据库读操作时，对两台从服务器进行操作，这样大大减轻了主服务器的压力。如图所示：

一、MySQL主从复制原理

MySQL的主从复制和MySQL的读写分离两者有必然的联系，首先要部署主从复制，只有主从复制完成了，才能在此基础上进行数据的读写分离

1）MySQL支持的复制类型

（1）基于语句的复制：在主服务器上执行的SQL语句，在从服务器上执行同样的语句。MySQL默认采用基于语句的复制，效率比较高。但是必须开启二进制日志功能；
（2）基于行的复制：把改变的内容复制过去，而不是把命令在从服务器上执行一遍；
（3）混合类型的复制：默认采用基于语句的复制，一旦发现基于语句无法精确复制时，就会采用基于行的复制；

2）复制的工作过程

MySQL复制的工作过程如图所示：

MySQL复制的工作过程详细介绍：
1.在每个事物更新数据完成之前，Master在二进制日志记录这些变化。写入二进制日志完成后，Master通知存储引擎提交事务；
2.Slave将Master的Binary log（二进制日志）复制到其Relay log（中继日志）。首先Slave开始一个工作进程--I/O线程，I/O线程在Master上打开一个普通的连接，然后开始Binlog dump process（二进制日志转储过程）。Binlog dump process从Master的二进制日志中读取事件，如果已经跟上Master，它就会睡眠并等待Master产生新的事件。I/O线程将这些时间写入中继日志；
3.SQL slave thread（SQL从线程）处理该过程的最后一步。SQL线程从中继日志中读取事件，并重放其中的事件而更新Slave的数据，使其与Master中的数据一致，只要该线程与I/O线程保持一致，中继日志通常会位于OS的缓存中，所以中继日志的开销很小；

复制过程有一个很重要的限制，即复制在Slave上是串行化的，也就是说Master上的并行更新操作不能在Slave上并行操作。

二、MySQL读写分离原理

简单来说，读写分离就是只在主服务器上写、只在从服务器上读。基本的原理是让主数据库处理事务性查询，而从数据库处理select查询。数据库复制被用来把事务性查询导致的变更同步到群集中的从数据库。如图所示：

目前常见的MySQL读写分离分为两种：

1）基于程序代码内部实现

在代码中根据select、insert进行路由分类，这类方法也是目前生产环境应用最广泛的。优点是性能较好，因为在程序代码中实现，不需要增加额外的设备作为硬件开发，缺点是需要开发人员来实现，运维人员无从下手；

2）基于中间代理层实现

代理一般位于客户端和服务器之间，代理服务器接到客户端的请求后通过判断后转发到后端数据库，有两个代表性程序：
（1）MySQL-Proxy：为MySQL开源项目，通过其自带的lua脚本进行SQL判断，虽然是MySQL官方产品。但是MySQL官方并不建议将MySQL-Proxy用到生产环境中；
（2）Amoeba（变形虫）：由陈思儒开发，其层就职于阿里巴巴。该程序用Java语言进行开发，阿里巴巴将其用于生产环境。它不支持事务和存储过程；

经过上述简单的比较，通过程序代码实现MySQL读写分离自然是一个不错的选择，但是并不是所有的应用都适合在程序代码中实现读写分离。例如：一些大型复杂Java应用，如果在程序代码中实现分离对代码改动较大。所以，像这种代行复杂的应用一般会考虑使用代理层来实现。

三、搭建MySQL主从复制

实验拓补如下：

大致步骤分为：
（1）MySQL主服务器建立时间同步；
（2）MySQL从服务器进行时间同步；
（3）关闭防火墙；
（4）安装MySQL数据库；
（5）配置MySQL主服务器；
（6）配置MySQL从服务器；
（7）验证主从复制效果；

（1）MySQL主服务器建立时间同步

[root@localhost ~]# yum -y install ntp             //安装NTP[root@localhost ~]# vim /etc/ntp.conf              //编写ntp服务的配置文件                     ……………………                     //省略部分内容，填写以下内容server 127.127.1.0                                        //需要同步上层服务器（本身）fudge 127.127.1.0 stratum 8                         //同步上层服务器的层数（大小不能超过或等于16）[root@localhost ~]# systemctl restart ntpd   //重启ntp服务

（2）MySQL从服务器进行时间同步

两台从服务器上都要进行时间同步！

[root@localhost ~]# yum -y install ntpdate[root@localhost ~]# ntpdate 192.168.1.1//安装ntpdate工具，并指定ntp服务器实现同步

（3）关闭防火墙（或开启相应端口）

由于实验环境，这里就采用强制关闭防火墙了。每台服务器上都关闭防火墙！

[root@localhost ~]# systemctl stop firewalld//关闭防火墙

（4）安装MySQL数据库

安装MySQL数据库可以参考博文：编译安装MySQL数据库系统

安装完成之后，启动MySQL数据库

[root@localhost ~]# systemctl start mysqld//启动MySQL数据库[root@localhost ~]# netstat -anpt | grep mysqldtcp6       0      0 :::3306                 :::*                    LISTEN      3263/mysqld       //mysql数据库的监听端口是TCP/3306，确认一下！  [root@localhost ~]# mysqladmin -u root password '123456'//给MySQL数据库管理员"root"设置密码

主、从服务器都需要安装MySQL数据库、并启动、设置密码 ！！！

（5）配置MySQL主服务器

[root@localhost ~]# vim /etc/my.cnf//编写MySQL数据库的配置文件                        ……………………                     //省略部分内容，填写以下内容server_id = 11//服务器ID号，自定义但不可以与别的MySQL服务器冲突log_bin = master-bin//定义bin-log的文件名（默认保存在数据目录下）log-slave-updates = true//开启从服务器自动备份[root@localhost ~]# systemctl restart mysqld//重启MySQL数据库[root@localhost ~]# mysql -u root -pEnter password: //登录MySQL数据库mysql> grant replication slave on *.* to 'myslave'@'192.168.1.%' identified by '123456';Query OK, 0 rows affected (0.00 sec)//授权用户myslave，密码是123456，在192.168.1.0网段的所有MySQL服务器上对所有数据库、表具有复制的权限mysql> flush privileges;Query OK, 0 rows affected (0.00 sec)//刷新权限mysql> show master status;//查看主服务器的状态+-------------------+----------+--------------+------------------+-------------------+| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+-------------------+----------+--------------+------------------+-------------------+| master-bin.000001 |      410 |              |                  |                   |+-------------------+----------+--------------+------------------+-------------------+1 row in set (0.00 sec)//File列显示日志名，Position列显示偏移量

（6）配置MySQL从服务器

两台从服务器的配置几乎一样！

[root@localhost ~]# vim /etc/my.cnf//编写MySQL数据库的配置文件                            ……………………                     //省略部分内容，填写以下内容server_id = 22//服务器ID号，自定义但不可以与别的MySQL服务器冲突relay-log = relay-log-bin//定义中继日志文件名称和路径（默认保存在数据目录下）relay-log-index = slave-relay-bin.index//定义中继日志索引文件名（记录很多的中继日志文件）//默认情况，中继日志使用host_name-relay-bin.nnnnnn形式的文件名，其中host_name是从服务器主机名，nnnnnn是序 列号。//用连续序列号来创建连续中继日志文件，从000001开始。从服务器跟踪索引文件中目前正使用的中继日志。 默认中继日志索引文件名为host_name-relay-bin.index。//默认情况，在从服务器的数据目录中创建这些文件。[root@localhost ~]# systemctl restart mysqld//重启MySQL数据库[root@localhost ~]# mysql -u root -pEnter password: //登录MySQL数据库 mysql> change master to master_host='192.168.1.1',master_user='myslave',-> master_password='123456',master_log_file='master-bin.000001',master_log_pos=410;Query OK, 0 rows affected, 2 warnings (0.02 sec)//指定主服务的IP地址、登录主服务器的用户名、密码、指定同步的日志、偏移量mysql> start slave;Query OK, 0 rows affected (0.00 sec)//启动同步mysql> show slave status\G;//查看Slave状态，确保以下两项为"Yes"                            ……………………                     //省略部分内容，填写以下内容             Slave_IO_Running: Yes                                          //I/O线程启动            Slave_SQL_Running: Yes                                       //SQL线程启动                                 ……………………                     //省略部分内容，填写以下内容

两台从服务器配置完毕后！即可进行测试！

（7）验证主从复制效果

①在主、从服务器上登录MySQL数据库，查看数据库；

[root@localhost ~]# mysql -u root -pEnter password: mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || test               |+--------------------+4 rows in set (0.10 sec)

②在主服务器上创建新的数据库；

mysql> create database qq;Query OK, 1 row affected (0.00 sec)

③在主、从服务器上分别查看数据库

mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || qq                 || test               |+--------------------+5 rows in set (0.00 sec)

显示数据库相同，则表示主从复制成功！

四、搭建MySQL读写分离

搭建MySQL读写分离的基础就是MySQL主从复制，以下所有操作都是基于mysql主从复制的基础上。

搭建MySQL读写分离使用的软件是--Amoeba软件。简单介绍一下：

Amoeba项目开源框架于2008年发布一款Amoeba for MySQL软件。这个软件致力于MySQL的分布式数据库的前端代理曾，它主要为应用层访问MySQL的时候充当SQL路由功能，并且具有负载均衡、高可用性、SQL过滤、读写分离、可路由相关到目标数据库，可并发请求多台数据库。通过Amoeba能够完成多数据源的高可用、负载均衡、数据切片的功能，目前Amoeba已在很多企业的生产线上使用，其版本可在官网上自行下载。

（1）在Amoeba服务器上安装Java环境

注意：Amoeba软件千万不要安装在任意mysql服务器上！

因为Amoeba是基于jdk1.5开发的，所以官方推荐使用jdk1.5或1.6版本。高版本不建议使用！查看Centos 7系统默认的Java环境：

[root@localhost ~]# java -versionopenjdk version "1.8.0_102"OpenJDK Runtime Environment (build 1.8.0_102-b14)OpenJDK 64-Bit Server VM (build 25.102-b14, mixed mode)

所以，还需要重新部署一下java环境，使用的软件包网盘链接：https://pan.baidu.com/s/1sHwbvlIZ2VDLb_qw-b_z4w
提取码：rs1x
操作如下：

[root@localhost ~]# cp jdk-6u14-linux-x64.bin /usr/local[root@localhost ~]# chmod +x /usr/local/jdk-6u14-linux-x64.bin [root@localhost ~]# cd /usr/local[root@localhost local]# ./jdk-6u14-linux-x64.bin                  //根据提示输入yes和回车键[root@localhost local]# mv jdk1.6.0_14/ jdk1.6                   //对jdk的安装目录进行重命名[root@localhost ~]# vim /etc/profile                                     //编写全局环境变量配置文件                                ……………………                               //省略部分内容，填写以下内容export  JAVA_HOME=/usr/local/jdk1.6export CLASSPATH=$JAVA_HOME/lib:$JAVA_HOME/jre/libexport PATH=$JAVA_HOME/lib:$JAVA_HOME/jre/bin:$PATH:$HOME/binexport AMOEBA_HOME=/usr/local/amoeba/export PATH=$PATH:$AMOEBA_HOME/bin[root@localhost ~]# source /etc/profile[root@localhost ~]# java -versionjava version "1.6.0_14"Java(TM) SE Runtime Environment (build 1.6.0_14-b08)Java HotSpot(TM) 64-Bit Server VM (build 14.0-b16, mixed mode)//目前系统的Java环境是1.6版本的，可以使用！

（2）安装并配置Amoeba软件

Amoeba软件的网盘链接：https://pan.baidu.com/s/1m-gBbCW4SxTt4b5NvQngQQ
提取码：w93m
操作如下：

[root@localhost ~]# mkdir /usr/local/amoeba[root@localhost ~]# tar zxf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/[root@localhost ~]# chmod -R 755 /usr/local/amoeba/[root@localhost ~]# /usr/local/amoeba/bin/amoebaamoeba start|stop//出现这些内容，则表示安装amoeba安装成功

（3）配置Amoeba读写分离，两个Slave实现读负载均衡

①Master、Slave1、Slave2中都要开放权限给Amoeba访问，命令如下：

mysql> grant all on *.* to 'test'@'192.168.1.%' identified by '123.com';Query OK, 0 rows affected (0.00 sec)

②编辑amoeba.xml配置文件。此配置文件主要定义主从服务器地址池。

[root@localhost ~]# vim /usr/local/amoeba/conf/amoeba.xml                     ……………………                 //由于内容较多，编辑的每条内容都添加了实际的行号30                                         amoeba31                                         32                                         123456//指定客户机访问amoeba服务器时的账号、密码！115                 master116 117                 master118                 slaves//注意取消原本的注释信息                 

③编辑dbServer.xml配置文件

[root@localhost ~]# vim /usr/local/amoeba/conf/dbServers.xml                          ……………………                 //由于内容较多，编辑的每条内容都添加了实际的行号 26                         test 27  28                         123.com   //指定amoeba服务器访问mysql数据库使用的账号密码 43          44                  45                          46                         i192.168.1.1 47                  48          49          50          51                  52                          53                         192.168.1.2 54                  55          56          57                  58                          59                         192.168.1.3 60                  61          //slave2配置需要复制salve1的配置信息（行号可能与实际不符）                         62  63          64                  65                          66                         1 67  68                          69                         slave1,slave2 70                  71         

④确认无误后，启动Amoeba软件，其默认端口为TCP 8066

[root@localhost ~]# /usr/local/amoeba/bin/amoeba start &[root@localhost ~]# netstat -anpt | grep 8066tcp6       0      0 :::8066                 :::*                    LISTEN      3799/java         

（5）测试

由于实验环境，我们测试一下读写分离的效果，实际环境，轻易不要测试！！！

①在客户端安装mysql工具，并通过代理访问mysql，命令如下：

[root@localhost ~]# yum -y install mariadb//注意，Centos 7系统开始，自带的数据库叫mariadb，不再是mysql[root@localhost ~]# mysql -u amoeba -h 192.168.1.4 -P 8066 -pEnter password:   MySQL [(none)]> //表示访问成功

②在MySQL主服务器上创建一个表同步到从服务器上

mysql> use qq;Database changedmysql> create table q1 (id int);Query OK, 0 rows affected (0.10 sec)

分别在两台从服务器上停止同步，执行如下命令：

mysql> stop slave;Query OK, 0 rows affected (0.01 sec)

在主服务器上插入数据：

mysql> insert into q1 values ('1');Query OK, 1 row affected (0.00 sec)

③两台从服务器上同步了表，手动分别插入一条数据
第一台从服务器

mysql> use qq;Database changedmysql>  insert into q1 values ('2');Query OK, 1 row affected (0.00 sec)

第二台从服务器

mysql> use qq;Database changedmysql>  insert into q1 values ('3');Query OK, 1 row affected (0.00 sec)

④测试读操作
在客户机上第一次查询结果：

MySQL [(none)]> select * from qq.q1;+------+| id   |+------+|    3 |+------+1 row in set (0.00 sec)

在客户机上第二次查询结果：

MySQL [(none)]> select * from qq.q1;+------+| id   |+------+|    2 |+------+1 row in set (0.00 sec)

永远是这两种结果！！！
这样实现了读数据的负载均衡，而且效果很明显！

⑤测试写操作
在客户端上插入一条数据，但是在客户端查询不到！

MySQL [qq]> insert into q1 values ('4');    Query OK, 1 row affected (0.00 sec)

但在主服务器上可以查询到！

mysql> select * from q1;+------+| id   |+------+|    1 ||    4 |+------+2 rows in set (0.00 sec)

由此验证，已经实现了MySQL读写分离，目前所有的写在主服务器上，避免了数据的不同步；所有的读操作都分摊给了从服务器，用来分担数据库的压力！

-------- 本文至此结束，感谢阅读 --------