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

读写分离概述

什么是读写分离

读写分离，基本的原理是让主数据库处理事务性增、改、删操作( INSERT、UPDATE、 DELETE) ，而从数据库处理SELECT查询操作。数据库复制被用来把事务性操作导致的变更同步到集群中的从数据库。

为什么要读写分离

因为数据库的“写”(写10000条数据可能要3分钟)操作是比较耗时的。

但是数据库的"读”(读10000条数据可能只要5秒钟)。

所以读写分离，解决的是，数据库的写入，影响了查询的效率。

什么时候要读写分离

• 数据库不一定要读写分离，如果程序使用数据库较多时，而更新少，查询多的情况下会考虑使用。
• 利用数据库主从同步，再通过读写分离可以分担数据库压力，提高性能。

MySQL主从复制与读写分离

主从复制的概念

• 在实际的生产环境中，对数据库的读和写都在同一个数据库服务器中，是不能满足实际需求的。无论是在安全性、高可用性还是高并发等各个方面都是完全不能满足实际需求的。

• 因此，通过主从复制的方式来同步数据，再通过读写分离来提升数据库的并发负载能力。有点类似于rsync（数据镜像备份工具），但是不同的是rsync是对磁盘文件做备份，而mysql主从复制是对数据库中的数据、语句做备份，即二进制日志文件中的数据进行相关备份，并不是对文件进行备份。

MySQL支持的复制类型

• statement：基于语句的复制。在服务器上执行sql语句，在从服务器上执行同样的语句，mysql默认采用基于语句的复制，执行效率高。缺点是，高并发高负载时，容易导致基于语句复制的顺序会有偏差，从而导致数据的丢失。
• row：基于行的复制。把改变的内容复制过去，而不是把命令在从服务器上执行一遍。复制的时候精确度较高，但效率较低、日志文件占用空间较大。
• mixed：混合类型的复制。默认采用基于语句的复制，一旦发现基于语句无法精确复制时，就会采用基于行的复制。

主从复制的工作过程

1. master节点将数据的改变记录成二进制日志（bin log），当Master上的数据发生改变时，则将其写入二进制日志中。
2. Slave节点会在一定时间间隔内对Master的二进制日志进行探测其是否发生改变，如果发生改变，则开始一个I/O线程请求Master的二进制事件。
3. 同时Master节点为每个I/O线程启动一个dump线程，用于向其发送二进制事件，并保存至Slave节点本地的中继日志（Relay log），Slave节点将会开启SQL线程，从中继日志中读取二进制日志，在本地重放，即解析成sql语句注意执行，使得其数据和Master节点的保持一致，最后I/O线程和SQL线程将进入睡眠状态，等待下一次被唤醒。

主从复制示例

保证MySQL主从服务器时间同步

yum install -y ntp ntpdate      #下载安装时间同步工具ntp ntpdate
# 如果是图形化界面的linux系统，系统一般自带此工具，可以使用rpm -q ntp ntpdate查看
# 通常我们实现主从时间同步都是由ntp服务来实现的

# 对接本地源时间同步
vim /etc/ntp.conf 
# 末行添加以下内容
server 127.127.142.0            # 设置本地是时钟源，注意修改为网段，若本机网段为192.168.142.0，则本地时钟源为127.127.142.0
fudge 127.127.142.0 stratum 8   # 设置时间层级为8（限制在15以内）

# 对接在线源时间同步
ntpdate ntp.aliyun.com


# 配置完成后开启服务（以上两种时间同步方式均可）
service ntpd start             #开启ntp服务
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

yum install -y ntp ntpdate      #下载安装时间同步工具ntp ntpdate

service ntpd start                #开启ntp服务
/usr/sbin/ntpdate 192.168.142.70  #时间同步跟随主服务器的IP地址

crontab -e               #设置周期性计划任务
*/30 * * * * /usr/sbin/ntpdate 192.168.142.70
# 每天每隔半个小时，进行一次时间同步跟随主服务器
1
2
3
4
5
6
7
8

主节点服务器配置

vim /etc/my.cnf          #mysql主配置文件
server-id = 1            #每个MySQL集群中的唯一标识，一定不能设置相同数字
log-bin = mysql-bin      #添加，主服务器开启二进制日志，
binlog_format = MIXED    #设置二进制日志存储的模式
log-slave-updates = true #添加，允许slave从master复制数据时可以写入到自己的二进制日志
expire_logs_days = 7     #设置二进制日志文件过期时间，默认值为0，表示logs不过期
max_binlog_size = 500M   #设置二进制日志大小的限制，限制为500M，若超出给定值，日志会发生滚动（关闭当前文件，重新打开一个新的日志文件），默认值为1GB

# 配置完成后重启mysql服务
systemctl restart mysqld
1
2
3
4
5
6
7
8
9
10

mysql -u root -pabc123  # 进入主服务器数据库

# 给从节点服务器授权，允许主从复制的权限（replication slave），从192.168.142.0/24这个网段进行连接
grant replication slave on *.* to 'myslave'@'192.168.142.%' identified by '123456';
flush privileges;       # 刷新权限
show master status;     # 查看记录的参数（二进制日志名称，二进制日志的复制数据的位置点
1
2
3
4
5
6

从节点服务器设置

vim /etc/my.cnf
server-id = 2           # 设置id标识与Master不一致即可，两个Slave的id标识也不能相同
relay-log=relay-log-bin # 添加，开启中继日志，将主服务器上同步日志文件记录到本地
relay-log-index=slave-relay-bin.index # 添加，定义中继日志文件的位置和名称，一般和relay-log在同一目录
relay_log_recovery = 1  # 选配项

# 配置完成后重启mysql服务
systemctl restart mysqld
1
2
3
4
5
6
7
8

mysql -u root -pabc123  # 进入从服务器数据库


change master to master_host='192.168.142.70',master_user='myslave',master_password='123456',master_log_file='mysql-bin.000005',master_log_pos=604;
# 配置同步，注意master_log_file和master_log_pos的值要与Master主节点上查询的一致

start slave;           #启动同步，如有报错执行 reset slave;
show slave status\G    #查看slave状态（用于确保IO及SQL线程都是Yes，即同步正常）

slave状态如下为正常
Slave_IO_Running：Yes   #负责与主节点的IO通信
Slave_SQL_Running：Yes  #负责自己的slave mysql进程
1
2
3
4
5
6
7
8
9
10
11
12

扩展：一般状态显示Slave_IO_Running：No的可能性

1. 网络不通
2. my.cnf配置有问题
3. 密码、file文件名、pos偏移量不对
4. 防火墙没有关闭

验证

主节点创建新数据


从节点验证是否存在新数据

MySQL读写分离

MySQL读写分离原理

• 只在主服务器上写，只在从服务器上读

• 主数据库处理事务性操作，从数据库处理select查询

• 数据库复制用于将事务性操作的变更数据同步到集群中的从数据库

• 读写分离方案

  • 基于程序代码内部实现

  • 基于中间代理层实现

    • MySQL-Proxy
    • Amoeba

读写分离分类

基于程序代码内部实现

• 在代码中根据select、 insert 进行路由分类这类方法也是目前生产环境应用最广泛的。
• 优点是性能较好，因为在程序代码中实现，不需要增加额外的设备为硬件开支;缺点是需要开发人员来实现，运维人员无从下手。
• 但是并不是所有的应用都适合在程序代码中实现读写分离，像一-些大型复杂的Java应用，如果在程序代码中实现读写分离对代码改动就较大。

基于中间代理层实现

• 代理一般位于客户端和服务器之间，代理服务器接到客户端请求后通过判断后转发到后端数据库，有以下代表性程序。
• MySQL-Proxy：MySQL-Proxy为MySQL 开源项目，通过其自带的lua 脚本进行SQL判断。
• Atlas：是由奇虎360的web平台部基础架构团队开发维护的一-个基于MySQL协议的数据中间层项目。它是在mysql-proxy0.8.2版本的基础上，对其进行了优化，增加了- -些新的功能特性。360内部使用Atlas运行的mysql业务， 每天承载的读写请求数达几十亿条。支持事务以及存储过程。
• Amoeba：由陈思儒开发，作者曾就职于阿里巴巴。该程序由Java语言进行开发，阿里巴巴将其用于生产环境。但是它不支持事务和存储过程。
• Mycat：是一款流行的基于Java语言编写的数据库中间件，是一个实现了MySql协议的服务器，其核心功能是分库分表。配合数据库的主从模式，还可以实现读写分离。

由于使用MySQL Proxy 需要写大量的Lua脚本，这些Lua并不是现成的，而是需要自己去写。这对于并不熟悉MySQL Proxy内置变量和MySQL Protocol的人来说是非常困难的。

Amoeba是一个非常容易使用、可移植性非常强的软件。因此它在生产环境中被广泛应用于数据库的代理层。

读写分离示例

Amoeba搭建
除了之前配置的主从复制，再准备一台普通的主机进行amoeba的搭建，因为 Amoeba 基于是 jdk1.5 开发的，所以官方推荐使用 jdk1.5 或 1.6 版本，高版本不建议使用。

将jdk-6u14-linux-x64.bin 和 amoeba-mysql-binary-2.2.0.tar.gz.0 上传到/opt目录下。
cd /opt
cp jdk-6u14-linux-x64.bin /usr/local/    

cd /usr/local/
chmod +x jdk-6u14-linux-x64.bin

./jdk-6u14-linux-x64.bin          
#执行jdk工具包，使用空格到最下方，填入yes进行安装
1
2
3
4
5
6
7
8
9

# 执行jdk完成后，会生成一个同名软件包，这是去修改环境变量
vim /etc/profile
#末行添加以下内容
 
export JAVA_HOME=/usr/local/jdk1.6.0_14            # java的工作目录
export JRE_HOME=$JAVA_HOME/jre                     
export CLASSPATH=.:$JAVA_HOME/lib:$JRE_HOME/lib    # java工具包目录
export PATH=$JAVA_HOME/bin:$JRE_HOME/bin:$PATH     # 加入环境变量中

# 保存后重新加载环境变量
source /etc/profile

java -version   # 验证jdk版本是否改变
1
2
3
4
5
6
7
8
9
10
11
12
13

##安装Amoeba##
# 到达/usr/local/目录下，自行创建一个amoeba目录
cd /opt
tar xf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/
# 将软件包解压到新建的amoeba下

# 将解压后的amoeba目录下的lib，添加到环境变量中，使系统可识别，可以直接使用amoeba
vim /etc/profile
# 添加以下一行内容
AMOEBA_HOME=/usr/local/amoeba
# 修改环境变量行
export PATH=$JAVA_HOME/bin:$JRE_HOME/bin:$AMOEBA_HOME/bin:$PATH

修改结束后重新加载
source /etc/profile

/usr/local/amoeba/bin/amoeba   # 使用绝对路径运行amoeba
amoeba start|stop              # 出现该字样，代表amoeba正常可以使用
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

Amoeba服务器配置

# 在一主两从的三台服务器中，分别对amoeba服务器进行授权
grant all on *.* to 'amoeba'@'192.168.142.%' identified by '123456';

## Amoeba服务器的配置文件修改 ##
cd /usr/local/amoeba/conf
# 该目录下有个amoeba.xml文件为amoeba相关服务的配置文件
vim amoeba.xml                #修改该配置文件，建议修改前先进行备份
--115行修改server1为master--  #默认服务器设置为master
--118行修改server1为master--  #可写服务器设置为master
--119行修改server1为slaves--  #可读服务器设置为slaves
# 设置完成后取消117，120行的箭头注释
# 以上服务器设置操作，只是确定服务器的地址池名称，并没有确定地址池的内容，所以还没有指定相关主机。
1
2
3
4
5
6
7
8
9
10
11
12

## Amoeba服务器的地址池配置修改 ##
vim /usr/local/amoeba/conf/dbServers.xml 
--注释22-24行--    # 注释默认数据库test字样
--修改26行--
将root字段改为amoeba            
#由于我们之前设置每台MySQL服务器的登录用户为amoeba，所以这里要将默认的root改为amoeba
--取消28-30行注释，开启密码验证--
将密码password字段修改为123456
--修改45-72行内容，设置地址池，操作步骤如下图--
1
2
3
4
5
6
7
8
9

#配置完成后，启动amoeba
/usr/local/amoeba/bin/amoeba start &
netstat -lnpt | grep :8066     #查看8066端口是否已经被启用
1
2
3

验证读写分离

# 使用一台装有mysql的主机使用远程登录登录Amoeba服务器
mysql -u zhangsan -pabc123 -h 192.168.142.60 -P 8066
#使用zhangsan用户并指定端口号8066登录Amoeba服务器
1
2
3

客户机接入Amoeba服务器写入数据
客户机写入数据：

主节点服务器内容显示：

从节点服务器1内容显示：

从节点服务器2内容显示：

在主从复制运行时，向Amoeba进行数据的更改，先将修改数据发送给主服务器，再同步到从服务器。

关闭同步后客户机写入数据

客户机写入数据：

主服务器内容显示：

两个从节点服务器内容显示：

在关闭主从复制同步后，客户机向Amoeba服务器写入数据，Amoeba服务器会将更新数据交给主节点服务器，但是本身不会显示，主节点服务器数据将会被更新

关闭同步后两个从节点服务器分别写入数据

从节点服务器1写入新数据：

从节点服务器2写入新数据：

客户机向Amoeba服务器查询数据：

在关闭同步后，两台从节点服务器分别写入不同数据，Amoeba服务器可以依次进行从服务器的数据读取，可以依次显示两个从节点服务器的不同内容，起到轮询的效果。

读写分离总结

• 在我们实验主从复制之前，要确保我们主从的数据是相同的，并且不是克隆或者镜像生成的两个数据库，否则会导致主从复制失败，原因是主从的uuid(UUID 是 通用唯一识别码)相同。
• 若主节点服务器在做主从复制前，数据库内容与从节点服务器数据库内容不一致，会导致客户机连接Amoeba服务器展现的库表内容不一致，但是Amoeba只能查询已经进行同步的数据（即从库同步的数据），在同步之前的数据，无法进行增删改查的操作。