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

前言

在企业实际应用中,成熟的业务通常数据量都比较大,而单台MySQL服务器在安全性、高可用性和高并发方面都无法满足实际的需求,我们可以在多台MySQL服务器(Master-Slave)部署 主从复制来实现同步数据,再通过 读写分离来提升数据库的并发负载能力。有点类似于rsync,但是不同的是rsync是对磁盘文件做备份,而mysql主从复制是对数据库中的数据、语句做备份。

一、相关概述

主从复制:主数据库(Master)发送更新事件到从数据库(Slave),从数据库读取更新记录,并执行更新记录,使得从数据库的内容与主数据库保持一致。

(一)MySQL 支持的复制类型

  • 基于语句的复制(STATEMENT)。在主库上执行的 SQL 语句,在从库上执行同样的语句。MySQL 默认采用基于语句的复制,效率比较高。
  • 基于行的复制(ROW)。把改变的内容复制过去,而不是把命令在从库上执行一遍。
  • 混合类型的复制(MIXED)。默认采用基于语句的复制,一旦发现基于语句无法精确复制时,就会采用基于行的复制。

(二)MySQL主从复制的工作过程

1.Master 服务器保存记录到二进制日志

  • MySQL主库上进行的增、删、改的数据更新,都会按顺序写入到自己的二进制日志(Binary log)当中

2.Slave 服务器复制Master 服务器的日志

  • 然后MySQL从库开始一个I/O线程连接主库,读取主库的二进制日志,备份到从服务器的==中继日志(Relay log)==当中。如果已经跟上主库,它会睡眠并等待Master 产生新的事件,I/O线程将这些事件写入中继日志

3.Slave 服务器重放复制过来的日志

  • 然后从库打开SQL线程,SQL线程读取I/O线程写入的中继日志,并且根据中继日志的内容更新从库的数据,使其与主库中的数据一致

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

二、读写分离

(1)读写分离的概念

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

(2)读写分离的作用

  • 因为数据库的"写"(写10000条数据可能要3分钟)操作是比较耗时的。但是数据库的"读"(读10000条数据可能只要5秒钟)。
  • 所以读写分离,解决的是,数据库的写入,影响了查询的效率。注意:数据库不一定要读写分离,如果程序使用数据库较多时,而更新少,查询多的情况下会考虑使用。利用数据库主从同步,再通过读写分离可以分担数据库压力,提高性能

(3)MySQL 读写分离原理

  • 读写分离就是只在主服务器上写,只在从服务器上读
  • 基本的原理是让主数据库处理事务性查询,而从数据库处理 select 查询
  • 数据库复制被用来把主数据库上事务性查询导致的变更同步到集群中的从数据库

(4)常见的 MySQL 读写分离

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

1.在代码中根据 select、insert 进行路由分类,这类方法也是目前生产环境应用最广泛的。

2.优缺点:

  • 优点是性能较好,因为在程序代码中实现,不需要增加额外的设备为硬件开支;
  • 缺点是需要开发人员来实现,运维人员无从下手。

3.并不是所有的应用都适合在程序代码中实现读写分离,像一些大型复杂的Java应用,如果在程序代码中实现读写分离对代码改动就较大。

2)基于中间代理层实现

1.代理一般位于客户端和服务器之间,代理服务器接到客户端请求后通过判断后转发到后端数据库,有以下代表性程序:

  • MySQL-Proxy:MySQL-Proxy 为 MySQL 开源项目,通过其自带的 lua 脚本进行SQL 判断。
  • Atlas:是由奇虎360的Web平台部基础架构团队开发维护的一个基于MySQL协议的数据中间层项目。它是在mysql-proxy 0.8.2版本的基础上,对其进行了优化,增加了一些新的功能特性。360内部使用Atlas运行的mysql业务,每天承载的读写请求数达几十亿条。支持事物以及存储过程。
  • Amoeba:由陈思儒开发,作者曾就职于阿里巴巴。该程序由Java语言进行开发,阿里巴巴将其用于生产环境。但是它不支持事务和存储过程。

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

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

三、MySQL主从复制实验部署

需要的相关软降包
amoeba-mysql-binary-2.2.0.tar.gz
jdk-6u14-linux-x64.bin
mysql压缩包

(1)主从复制实验步骤及准备

实验步骤
第一步:客户端client访问代理服务器amoeba
第二步:代理服务器读写判断
写操作:写入到主服务器
第三步:主服务器将增删改写入自己二进制日志
第四步:从服务器将主服务器的二进制日志同步至自己中继日志
第五步:从服务器重放中继日志到数据库中
读操作:直接访问从服务器
最终结果:降低负载,起到负载均衡作用
主机 操作系统 IP地址 所需工具/软件/安装包

Amoeba CentOS7  192.168.71.10 jdk-6u14-linux-x64.bin、amoeba-mysql-binary-2.2.0.tar.gz
Master CentOS7 192.168.71.12 ntp 、 mysql-boost-5.7.20.tar.gz
Slave1 CentOS7 192.168.71.13 ntp 、ntpdate 、 mysql-boost-5.7.20.tar.gz
Slave2 CentOS7 192.168.71.14 ntp 、ntpdate 、mysql-boost-5.7.20.tar.gz
客户端 CentOS7 192.168.71.15

1.关闭防火墙及安全机制

四台服务器都要关闭

systemctl stop firewalld
systemctl disable firewalld
setenforce 0

2.安装时间同步服务ntp

(1)主服务器

yum -y install ntp

vim /etc/ntp.conf
##---------末尾添加---------
server 127.127.71.0           #设置本地是时钟源,注意修改网段
fudge 127.127.71.0 stratum 8  #设置时间层级为8(限制在15内)

service ntpd start

(2)两个从服务器

yum -y install ntp ntpdate

service ntpd start

/usr/sbin/ntpdate 192.168.71.12  #进行时间同步,指向Master服务器IP

crontab -e     #设置计划任务每个半个小时同步一次时间
*/30 * * * * /usr/sbin/ntpdate 192.168.71.12


3.主服务器的mysql配置

vim /etc/my.cnf
server-id = 1
log-bin=master-bin       #添加,主服务器开启二进制日志
log-slave-updates=true   #添加,允许从服务器更新二进制日志

systemctl restart mysqld

mysql -u root -p
grant replication slave on *.* to 'myslave'@'192.168.71.%' identified by '123'; #对从服务器提权
#grant 授权
#replication 复制
#允许网段为192.168.71的使用密码123对所有表库都可以复制

flush privileges;

show master status;
#File 列显示日志名,Fosition 列显示偏移量

4.从服务器的mysql配置

Slave1服务器:192.168.163.13
Slave2服务器:192.168.163.14

1)修改配置文件

vim /etc/my.cnf
#修改,注意id与Master的不同,两个Slave的id也要不同
server-id = 2

#添加,开启中继日志,从主服务器上同步日志文件记录到本地
relay-log=relay-log-bin

#添加,定义中继日志文件的位置和名称
relay-log-index=slave-relay-bin.index

systemctl restart mysqld

slave1

slave2

2)从服务器 对数据库进行操作

mysql -u root - p123
change master to master_host='192.168.71.12',master_user='myslave',master_password='123',master_log_file='master-bin.000002',master_log_pos=306; #show master status;所输出的二进制文件和位置点一样
     #配置同步,注意master_log_file 和master_log_pos的值要与Master查询的一致

start slave;                     #启动同步,如有报错执行reset slave;
show slave status\G;             #查看Slave 状态
//确保 IO 和 SQL 线程都是Yes,代表同步正常。
Slave_IO_Running:Yes            #负责与主机的io通信
Slave_SQL_Running:Yes           #负责自己的slave mysql进程




5.验证结果

主服务器上创建一个库验,在从服务器上查看

四、MySQL读写分离实验

Amoeba服务器(192.168.153.30)
安装Java 环境
因为Amoeba 基于是jdk1.5 开发的,所以官方推荐使用jdk1.5或1.6版本,高版本不建议使用。

1)创建jdk java环境

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

cp jdk-6u14-linux-x64.bin /usr/local/
cd /usr/local/
chmod +x jdk-6u14-linux-x64.bin
./jdk-6u14-linux-x64.bin
//按yes,按enter

mv jdk1.6.0_14/ /usr/1ocal/jdk1.6

vim /etc/profile
export JAVA_HOME=/usr/local/jdk1.6
export CLASSPATH=SCLASSPATH:$JAVA_HOME/lib:$JAVA_HOME/jre/lib
export PATH=$JAVA_HOME/lib:$JAVA_HOME/jre/bin/:$PATH:$HOME/bin
export AMOEBA_HOME=/usr/local/amoeba
export PATH=$PATH:$AMOEBA_HOME/bin

source /etc/profile
java -version



2)安装 Amoeba软件

mkdir /usr/local/amoeba
tar zxvf /opt/amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/
chmod -R 755 /usr/local/amoeba/
/usr/local/amoeba/bin/amoeba
//如显示amoeba start|stop 说明安装成功

3)在主从服务器的mysql上授权

Master服务器:192.168.71.12
Slave1服务器:192.168.71.13
Slave2服务器:192.168.71.14

先在Master、Slave1、Slave2 的mysql上开放权限给 Amoeba 访问

grant all on *.* to 'test'@'192.168.163.%' identified by 'abc123';

4)配置amoeba服务在阿米巴上配置

cd /usr/local/amoeba/conf/

cp amoeba.xml amoeba.xml.bak     #对配置文件备份
vim amoeba.xml                   #修改amoeba配置文件

<property name="user">amoeba</property>          #30行修改

<property name="password">abc123</property>      #32行修改 客户端连接amoeba服务器的密码

<property.name="defaultPool">master</property>   #115行修改 设置默认池

<property name="writePool">master</property>     #117行去掉注释 设置master为写池
<property name="readPool">slaves</property>		 #slaves 为读池

cp dbServers.xml dbServers.xml.bak
vim dbServers.xml                     #修改数据库配置文件

<!-- <property name="schema"> test</property> -->      #23行,注释掉   作用: 默认进入test库以防mysql 中没有test库时,会报错

<property name="user">test</property>                  #26行,修改主从服务器上提权的用户和密码

<property.name-"password">123</property>           #28-30行,去掉注释

<dbServer name= "master" parent="abstractServer">      #45行,修改,设置主服务器的名Master

<property name= "ipAddress">192.168.71.12</property>  #48行,修改,设置主服务器的地址

<dbServer name="slave1"  parent="abstractServer">      #52行,修改,设置从服务器的名slave1

<property.name="ipAddress">192.168.71.13</property>   #55行,修改,设置从服务器1的地址

<dbServer name="slave2 " parent="abstractserver">      #59行,复制上面6行粘贴,设置从服务器2的名slave2和地址
<property, name="ipAddress">192.168.71.14</property>

<dbServer name="slaves" virtual="true">                #65行,修改

<property name="poolNames">slave1,slave2</property>    #71行,修改

/usr/local/amoeba/bin/amoeba start&      #启动Amoeba软件,按ctrl+c 返回
netstat -anpt | grep java               #查看8066端口是否开启,默认端口为TCP 8066


5)测试读写分离

在客户端服务器上测试

yum install -y mysql mysql-server #快速安装mysql,也可以选择编译安装

mysql -u amoeba -pabc123 -h 192.168.71.20 -P8066

主从服务 v器上都可以看到该表

通过amoeba服务器代理访问mysql ,在通过客户端连接mysql后写入的数据只有主服务会记录,然后同步给从服务器

stop slave;                                    #关闭同步写入数据
use school;
//在slave1上:
insert into test1 values('1','slave1');

//在slave2上:
insert into test1 values('2','slave2');

在主服务器上插入数据

insert into test1 values('3','master');

在客户端中向表插入数据

//在客户端服务器上:
use school;
select * from test1;    //客户端会分别向slave1和s1ave2读取数据,显示的只有在两个从服务器上添加的数据,没有在主服务器上添加的数据

insert into test1 values('4','climet');   //只有主服务器上有此数据

在主服务器上查看数据

同时开启两个从服务器,查看表格记录

到此这篇关于详解MySQL主从复制及读写分离的文章就介绍到这了,更多相关MySQL主从复制及读写分离内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

(0)

相关推荐

  • 详解MySQL的主从复制、读写分离、备份恢复

    一.MySQL主从复制 1.简介 我们为什么要用主从复制? 主从复制目的: 可以做数据库的实时备份,保证数据的完整性: 可做读写分离,主服务器只管写,从服务器只管读,这样可以提升整体性能. 原理图: 从上图可以看出,同步是靠log文件同步读写完成的. 2.更改配置文件 两天机器都操作,确保 server-id 要不同,通常主ID要小于从ID.一定注意. # 3306和3307分别代表2台机器 # 打开log-bin,并使server-id不一样 #vim /data/3306/my.cnf lo

  • mysql主从复制读写分离的配置方法详解

    一.说明 前面我们说了mysql的安装配置,mysql语句使用以及备份恢复mysql数据;本次要介绍的是mysql的主从复制,读写分离;及高可用MHA; 环境如下: master:CentOS7_x64 mysql5.721 172.16.3.175 db1 slave1:CentOS7_x64 mysql5.7.21 172.16.3.235 db2 slave2:CentOS7_x64 mysql5.7.21 172.16.3.235 db3 proxysql/MHA:CentOS7_x64

  • CentOS服务器平台搭建mysql主从复制与读写分离的方法

    本文实例讲述了CentOS服务器搭建mysql主从复制与读写分离的方法.分享给大家供大家参考,具体如下: mysql 主从复制的优点: ① 如果主服务器出现问题, 可以快速切换到从服务器提供的服务,保证高可用性 ② 可以在从服务器上执行查询操作, 降低主服务器的访问压力 ③ 可以在从服务器上执行备份, 以避免备份期间影响主服务器的服务 注意事项: ① server-id必须唯一,一般使用ip的后三位 ② 从库Slave_IO_Running:NO 可能原因:帐号无权限操作 ③ Can't exe

  • MySQL5.6 Replication主从复制(读写分离) 配置完整版

    MySQL5.6主从复制(读写分离)教程 1.MySQL5.6开始主从复制有两种方式: 基于日志(binlog): 基于GTID(全局事务标示符). 需要注意的是:GTID方式不支持临时表!所以如果你的业务系统要用到临时表的话就不要考虑这种方式了,至少目前最新版本MySQL5.6.12的GTID复制还是不支持临时表的. 所以本教程主要是告诉大家如何通过日志(binlog)方式做主从复制! 2.MySQL官方提供的MySQL Replication教程: http://dev.mysql.com/

  • Mysql主从复制与读写分离图文详解

    文章思维导图 为什么使用主从复制.读写分离 主从复制.读写分离一般是一起使用的.目的很简单,就是为了提高数据库的并发性能. 你想,假设是单机,读写都在一台MySQL上面完成,性能肯定不高. 如果有三台MySQL,一台mater只负责写操作,两台salve只负责读操作,性能不就能大大提高了吗? 所以主从复制.读写分离就是为了数据库能支持更大的并发. 随着业务量的扩展.如果是单机部署的MySQL,会导致I/O频率过高. 采用主从复制.读写分离可以提高数据库的可用性. 主从复制的原理 ①当Master

  • 详解MySQL主从复制读写分离搭建

    MySQL主从设置 MySQL主从复制,读写分离的设置非常简单: 修改配置my.cnf文件 master 和 slave设置的差不多: [mysqld] log-bin=mysql-bin server-id=222 log-bin=mysql-bin的意思是:启用二进制日志. server-id=222的意思是设置了服务器的唯一ID,默认是1,一般取IP最后一段,可以写成别的,只要不和其他mysql服务器重复就好. 这里,有的MySQL默认的my.cnf文件引用了/etc/mysql/conf

  • MySQL主从复制与读写分离原理及用法详解

    本文实例讲述了MySQL主从复制与读写分离原理及用法.分享给大家供大家参考,具体如下: 主从复制 概念 影响MySQL-A数据库的操作,在数据库执行后,都会写入本地的日志系统A中. 假设,实时的将变化了的日志系统中的数据库事件操作,在MYSQL-A的3306端口,通过网络发给MYSQL-B. MYSQL-B收到后,写入本地日志系统B,然后一条条的将数据库事件在数据库中完成. 那么,MYSQL-A的变化,MYSQL-B也会变化,这样就是所谓的MYSQL的复制,即MYSQL replication.

  • 详解MySQL主从复制及读写分离

    前言 在企业实际应用中,成熟的业务通常数据量都比较大,而单台MySQL服务器在安全性.高可用性和高并发方面都无法满足实际的需求,我们可以在多台MySQL服务器(Master-Slave)部署 主从复制来实现同步数据,再通过 读写分离来提升数据库的并发负载能力.有点类似于rsync,但是不同的是rsync是对磁盘文件做备份,而mysql主从复制是对数据库中的数据.语句做备份. 一.相关概述 主从复制:主数据库(Master)发送更新事件到从数据库(Slave),从数据库读取更新记录,并执行更新记录

  • 一篇文章看懂MySQL主从复制与读写分离

    目录 引言 一.MySQL主从复制 1.MySQL的复制类型 2.MySQL主从复制的原理 3.MySQL主从复制延迟 二.MySQL读写分离 1.常见的 MySQL 读写分离分 2.MySQL 读写分离原理 三.MySQL主从复制与读写分离配置步骤 1.搭建环境 2.解决需要 3.准备阶段(关闭防火墙及控制访问机制) 4.搭建MySQL主从复制 5.搭建MySQL读写分离步骤 6.测试测试读写分离 总结 引言 企业中的业务通常数据量都比较大,而单台数据库在数据存储.安全性和高并发方面都无法满足

  • 详解MySQL主从复制实战 - 基于日志点的复制

    基于日志点的复制 1.在主库与从库上建立专用的复制账号 MariaDB [employees]> create user 'repl'@'172.%' identified by '123456'; 注意在生产上的密码必须依照相关规范以达到一定的密码强度, 并且规定在从库上的特定网段上才能访问主库 2.在主库与从库上授予复制权限 MariaDB [employees]> grant replication slave on *.* to 'repl'@'172.%'; 3.配置主库 注意启用二

  • 详解MySQL主从复制实战 - 基于GTID的复制

     基于GTID的复制 简介 基于GTID的复制是MySQL 5.6后新增的复制方式. GTID (global transaction identifier) 即全局事务ID, 保证了在每个在主库上提交的事务在集群中有一个唯一的ID. 在原来基于日志的复制中, 从库需要告知主库要从哪个偏移量进行增量同步, 如果指定错误会造成数据的遗漏, 从而造成数据的不一致. 而基于GTID的复制中, 从库会告知主库已经执行的事务的GTID的值, 然后主库会将所有未执行的事务的GTID的列表返回给从库. 并且可

  • MySQL数据库主从复制与读写分离

    目录 一.主从复制 主从复制三线程 主从复制的过程: 主从复制的策略: 主从复制高延迟 二.读写分离 读写分离概念 读写分离原因与场景 总结 一.主从复制        主从复制:在实际的生产中,为了解决Mysql的单点故障以及提高MySQL的整体服务性能,一般都会采用主从复制.即:对数据库中的数据.语句做备份. 主从复制三线程        Mysql的主从复制中主要有三个线程:master(binlog dump thread).slave(I/O thread .SQL thread),M

  • Mysql数据库的主从复制与读写分离精讲教程

    目录 前言 一.MySQL主从复制 1.支持的复制类型 2.主从复制的工作过程是基于日志 3.请求方式 4.主从复制的原理 5.MySQL集群和主从复制分别适合在什么场景下使用 6.为什么使用主从复制.读写分离 7.用途及条件 8.mysql主从复制存在的问题 9.MySQL主从复制延迟 二.主从复制的形式 三.读写分离 1.原理 2.为什么要读写分离呢? 3.什么时候要读写分离? 5.目前较为常见的MySQL读写分离 四.案例实施 1.案例环境 2.实验思路(解决需求) 3.准备 4.搭建My

随机推荐