MySQL主从复制与读写分离

目录

一、概述

二、主从复制原理

三、MySQL主从复制的工作过程

1.两日志

2.三线程

四、主从复制 

1.主从服务器时间同步

(1)master服务器配置

2.配置主从同步

4.测试

五、读写分离 

1.原理

2.实现方式

3.步骤

(1)安装 Java 环境

(2)重命名

(3)添加环境变量

(4)加载

(5)查看版本

(6)安装 Amoeba软件

(7)修改主配置文件

(8)修改数据库配置文件

4.测试

(1)下载 mariadb 客户端

(2)测试一

(3)测试二

(4)测试三

---

一、概述

1.在企业应用中，成熟的业务通常数据量都比较大。
2.单台MySQL在安全性、 高可用性和高并发方面都无法满足实际的需求。
3.配置多台主从数据库服务器以实现读写分离。

二、主从复制原理

1.MySQL主从复制原理
2.MySQL的 复制类型
3.基于语句的复制(STATEMENT, MySQL默认类型)
4.基于行的复制(ROW)
5.混合类型的复制(MIXED)

三、MySQL主从复制的工作过程

1.两日志

(1)二进制日志：master
(2)中继日志：slaves
二进制日志→复制到中继日志

2.三线程

(1)master上: dump 线程
(2)slave上: I/O 线程
(3)SQL线程

(1)dump 线程:
1)监听本地二进制日志的更新。
2)记录I/o线程对应的slave位置。
3)同步二进制日志更新内容给I/0线程。

(2)I/O线程:
1)监听master的dump线程。
2)将slave信息发送给master.从服务器位置、日志的position (记录位置)，超时时间。
3)接收master的dump线程传递过来的更新信息。
4)写入relay-log中。

(3)SQL线程:
1)监听中继日志。
2)将中继日志中的更新内容执行到自己的数据库中(保证从库与主库执行相同操作)。

四、主从复制 

1.主从服务器时间同步

(1)master服务器配置

1)安装ntp、修改配置文件

yum install ntp -y
vim /etc/ntp.conf
yum -y install ntpdate ntp      安装ntp软件
ntpdate ntp.aliyun.com          时间同步
vi /etc/ntp.conf                编辑配置文件

fudge ip地址 stratum 10         设置本机的时间层级为10级，0级表示时间层级为0级，是向其他服务器 
                                提供时问同步源的意思，不要设置为0级。

server ip地址                   设置本机为时间同步源

2)开启NTP服务、关闭防火墙和增强性安全功能 

systemctl start ntpd
systemctl stop firewalld.service
setenforce 0

3)两台slave服务器配置

①安装ntp、ntpdate服务
yum install ntp ntpdate -y
②开启ntp服务，关闭防火墙、增强性安全功能
systemctl start ntpd
systemctl stop firewalld. service
setenforce 0
③时间同步master服务器
ntpdate ip地址

两台slave服务器配置相同
master服务器同步阿里云时钟服务器

ntpdate ntp.aliyun.com
ntpdate ip地址
crontable -e
*/10****/usr/sbin/ntpdate ip地址

  

2.配置主从同步

(1)master服务器修改配置文件

vim /etc/my.cnf
在mysqld模块下修改一下内容

开启二进制日志文件(之后生成的日志名为master-bin)
1og-bin=master-bin

开启从服务器日志同步
log_slave-updates=true

主服务器id为11 (不可重复)
server_id=11

 重启服务

service mysqld restart

配置规则

mysql -uroot -p
grant replication slave on *.* to 'myslave'@'ip地址' identified by '密码';

刷新权限表

flush privileges;

规则解析: grant replication slave on *.* to 'myslave'@'ip地址' identified by '密码';
给从服务器提权，允许使用slave的身份复制master的所有数据库的所有表，并指定密码

查看master数据库状态

show master status;

以上可见产生了master-bin.000001日志文件，定位为603

从服务器需要定位到此处进行复制

从服务器配置

vim /etc/my.cnf

开启二进制日志文件
log-bin=master-bin

设置server id为22，slave2 为23
server_id = 22

从主服务器上同步日志文件记录到本地
relay-log=relay-log-bin

定义relay-log的位置和名称(index索引)
relay-log-index=slave-relay-bin.index

  

 开启从服务器功能

mysql -uroot -p

change master to
master_host='ip地址',master_user='myslave',master_password='123456',
master_log_file='master-bin.000001',master_log_pos=603;

start slave;

 

 

查看从服务器状态

show slave status \G;

同理、开启另一台从服务器同步
跳过一个错误，继续执行以下SQL

set global sql_slave_skip_counter=1 (跳过一个事务)

配置文件:

mysql_skip_error

4.测试

在主服务器上创建一个数据库

create database 数据库名;
show databases;

在两台从服务器上直接查看数据库列表

show databases;

以上，主从同步复制配置完成

  

 

 

五、读写分离 

1.原理

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

2.实现方式

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

② 基于中间代理层实现
       代理一般位于客户端和服务器之间，代理服务器接到客户端请求后通过判断后转发到后端数据库。
有以下代表性程序。
（1）MySQL-Proxy。MySQL-Proxy 为 MySQL 开源项目，通过其自带的 lua脚本进行SQL 判断。
（2）Atlas是由奇虎360的Web平台部基础架构团队开发维护的一个基于MySQL协议的数据中间层项目。它是在mysql-proxy 0.8.2版本的基础上，对其进行了优化，增加了一些新的功能特性。360内部使用Atlas运行的mysql业务，每天承载的读写请求数达几十亿条。支持事物以及存储过程。
（3）Amoeba。由陈思儒开发。该程序由Java语言进行开发，不支持事务和存储过程。

Amoeba：是一个以MySql为底层数据存储，并对应提供MySQL协议接口的proxy(代理)，外号变形虫。读取请求发送给从服务器时，采用轮询调度算法。
amoeba使用的java语言编写，配置文件为xml。
amoeba主要负责对外的一个代理IP。
访问这个IP时，发送的请求为“写”请求，则会转给主服务器。
当发送的请求为“读”时，会通过调度转发给从服务器，使用轮询算法，轮流分配给两台从服务器。
amoeba可以视为调度器，如果主服务器挂掉（单点故障），则会有MHA解决这个问题。

3.步骤

环境

master 192.168.22.128
slave1 192.168.22.228
slave2 192.168.22.126
amoeba 192.168.22.168
 

Amoeba

(1)安装 Java 环境

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			输入yes，键盘Enter

  

(2)重命名

mv jdk1.6.0_14/ /usr/local/jdk1.6

 (3)添加环境变量

vim /etc/profile
export JAVA_HOME=/usr/local/jdk1.6
export CLASSPATH=$CLASSPATH:$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

 (4)加载

source /etc/profile

 (5)查看版本

java -version

 (6)安装 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 说明安装成功

 配置 Amoeba读写分离，两个 Slave 读负载均衡

先在Master、Slave1、Slave2 的mysql上开放权限给 Amoeba 访问
grant all on *.* to 'test'@'192.168.22.%' identified by '123.com';

再回到amoeba服务器配置amoeba服务：
cd /usr/local/amoeba/conf/

cp amoeba.xml amoeba.xml.bak
vim amoeba.xml

  

(7)修改主配置文件

30行修改账户名(这里是client访问amoeba服务器时使用的账号)
<property name="user">amoeba</property>

32行修改账户密码(这里是数据库访问amoeba服务器时使用账号时用的密码)
<property name="password">123456</property>

115行修改默认池
<propertyname="defaultPoo1">master</propertv>

117行取消注释
取消下面的注释符号-->

118-119修改"读池"和"写池"
<propertyname="writePool">master</property> 
<propertyname="readPool">slaves</property> 

 

(8)修改数据库配置文件

cp dbServers.xml dbServers.xml.bak

vim dbServers.xml

23行注释
mysql schema：Mysql所有数据库信息（show databases）
<property name="schema">mysql</property>

26行，amoeba访问三台mysql数据库的账户和密码(账户为test)
<property name="user">test</property>

29行，修改访问数据库的密码
<property name="password">123.com</property>

45行，修改数据库主服务器名/地址
<dbServer name="master"	parent="abstractServer">	

48行，修改master服务器ip
<property name="ipAddress">192.168.22.128</property>

52行修改从服务器名
<dbServer name="slave1"  parent="abstractServer">	

55行修改从服务器地址
<property name="ipAddress">192.168.22.228</property>

紧接复制52-57行，粘贴，添加第二台服务器名
<dbServer name="slave2"	parent="abstractServer">	

修改第二台服务器IP
<property name="ipAddress">192.168.22.126</property>

66行，修改多个服务器池(multiPoo1)的名称(修改为slaves)<dbServer name="slaves" virtual-"true">

72行，添加两个从服务器的服务器名(slave1 slave2)<property name="poolNames">slave1,slave2</property>

/usr/local/amoeba/bin/amoeba start &
另开终端，查看是否启动 

netstat-natp | grep java
或者
netstat-natp | grep 8066

 

 

 

启动出现报错，检查配置文件

 

 4.测试

(1)下载 mariadb 客户端

yum -y install mariadb

systemctl stop firewalld
setenforce 0

远程登录 amoeba 服务器

mysql -u amoeba -p123456 -h 192.168.22.168 -P8066

(2)测试一

amoeba服务器是否关联后端mysql
客户端进入数据库创建表 
use 数据库名
show tables
create table 表名（id int(4) not null,name varchar(20));	

在三台mysq1服务器中查看是否有此表(amoeba关联) 
use 数据库名
show tables

(3)测试二

测试mysgl读写分离
2台从服务器关闭主从同步，测试amoeba读写分离 
stop slave;

客户端插入数据
insert into 表名(id,name,score) values(自定义)
master(处理写的任务) select* from 表名

slave(处理读的任务) select from 表名;
发现:从服务器看不到数据

(4)测试三

读写分离架构

        分别在slave1和salve2ky09数据库中插入不同数据，然后使用客户端进入，select查询，观测结果。

slave1
insert into test1 (id,name,score) values (2，'jizhua');	

slave2
insert into test1 (id.name,score) values (1,'shupian');	

client端
select*from test1

小结:"流量"被轮询读取