MySQL主从复制与读写分离

MySQL主从复制与读写分离

案例概述

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

一、读写分离概述

1. 什么是读写分离？
   读写分离，基本的原理是让主数据库处理事务性增、改、删操作（INSERT、UPDATE、DELETE），而从数据库处理SELECT查询操作。
   数据库复制被用来把事务性操作导致的变更同步到集群中的从数据库。
   2.为什么要读写分离呢？
   因为数据库的“写”（写10000条数据可能要3分钟）操作是比较耗时的。
   但是数据库的“读”（读10000条数据可能只要5秒钟）。
   所以读写分离，解决的是，数据库的写入，影响了查询的效率。
   3.什么时候要读写分离？
   数据库不一定要读写分离，如果程序使用数据库较多时，而更新少，查询多的情况下会考虑使用。
   利用数据库主从同步，再通过读写分离可以分担数据库压力，提高性能。
   4.主从复制与读写分离
   在实际的生产环境中，对数据库的读和写都在同一个数据库服务器中，是不能满足实际需求的。无论是在安全性、高可用性还是高并发等各个方面都是完全不能满足实际需求的。
   因此，通过主从复制的方式来同步数据，再通过读写分离来提升数据库的并发负载能力。
   有点类似于rsync，但是不同的是rsync是对磁盘文件做备份，而mysql主从复制是对数据库中的数据、语句做备份。
   5.mysq支持的复制类型
   （1）STATEMENT：基于语句的复制。在服务器上执行sql语句，在从服务器上执行同样的语句，mysql默认采用基于语句的复制，执行效率高。
   （2）ROW：基于行的复制。把改变的内容复制过去，而不是把命令在从服务器上执行一遍。
   （3）MIXED：混合类型的复制。默认采用基于语句的复制，一旦发现基于语句无法精确复制时，就会采用基于行的复制。
   6.主从复制的工作过程
   （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线程将进入睡眠状态，等待下一次被唤醒。

注：

中继日志通常会位于 OS 缓存中，所以中继日志的开销很小。
复制过程有一个很重要的限制，即复制在 Slave上是串行化的，也就是说 Master上的并行更新操作不能在 Slave上并行操作。

二、实际操作

2.1配置环境

master服务器： 192.168.35.6
slave1服务器： 192.168.35.7
slave1服务器： 192.168.35.8
Amoeba服务器： 192.168.35.9   
客户端 服务器： 192.168.35.9
1
2
3
4
5

2.2初始化环境

#五台服务器上都初始化
systemctl stop firewalld
setenforce 0
1
2
3

2.3搭建mysql主从复制

2.3.1搭建时间同步（主服务器：192.168.35.6）

1.#安装时间同步服务器
yum install ntp -y
 
2.#修改配置文件
vim /etc/ntp.conf  
server 127.127.35.0   #设置本地时钟源（35是自己的网段）
fudge 127.127.35.0 stratum 8  #设置时间层级为8 限制在15 以内
 
3.#开启服务
service ntpd start

4.#同步阿里云时间
ntpdate ntp.aliyun.com

5.改时间格式date -s 12:22:22
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

2.3.2搭建时间同步（从服务器：192.168.35.7、 192.168.35.8）

1.#安装时间同步服务器、同步服务
yum install ntp -y
yum install ntpdate -y
 
2. #开启服务
service ntpd start
 
3. #执行同步
/usr/sbin/ntpdate 192.168.35.6
或者 ntpdate 192.168.35.6
 
4.#计划定时任务
crontab -e
*/30 * * * *  /usr/sbin/ntpdate 192.168.35.6

5.#重新启动计划任务
systemctl restart crond
###########slave2:192.168.35.8与以上操作相同######
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

2.3.3配置主服务器（192.168.35.6）

1. #开启二进制日志
vim /etc/my.cnf
 
log-bin=master-bin        #开启二进制日志
binlog_format=MIXED       #二进制日志格式
log-slave-updates=true    #开启从服务器同步
 
2. #重启服务
systemctl restart mysqld.service 
 
3. #登入mysql，给从服务器在网段授权
mysql -uroot -p123123
grant replication slave on *.* to 'myslave'@'192.168.35.%' identified by '123456';
 
#刷新数据库
flush privileges;
 
#查看主服务器二进制文件
show master status;
开启二进制日志
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

1.开启二进制日志

2.重启服务

3.#登入mysql，给从服务器在网段授权

2.3.4配置从服务器（192.168.35.7、192.168.35.8）

1.#开启中继日志。
vim /etc/my.cnf
server-id = 2 #slave1和slave2的id不能相同，我slave2设置的3
relay-log=relay-log-bin
relay-log-index=slave-relay-bin.index
 
2.#重启服务
systemctl restart mysqld.service 
 
3. #登入mysql，配置同步注意master_log_file和master_log_pos的值要和master查询的一致
mysql -uroot -p123456
change master to master_host='192.168.35.6',master_user='myslave',master_password='123456',master_log_file='master-bin.000001',master_log_pos=603;
 
4.#启动同步，如果报错，执行restart slave试试
start slave;
show slave status\G;
##以下两个必须要是YES
#Slave_IO_Running: Yes
#Slave_SQL_Running: Yes
 
 
#########slave2：192.168.35.8与以上操作相同######
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

1.开启中继日志

2.重启服务

3.#登入mysql，配置同步注意master_log_file和master_log_pos的值要和master查询的一致

4.#启动同步，如果报错，执行restart slave试试

2.3.5验证主从同步

#在主服务器上创建一个库
create database ky20;
 
#在从服务器上查看
show databases;
1
2
3
4
5

2.4搭建Amoeba实现读写分离（192.168.35.9）

2.4.1安装JAVA环境

1.#下载安装包：jdk-6u14-linux-x64.bin、amoeba-mysql-binary-2.2.0.tar.gz 
cd /opt
 
2.#把jdk复制到/usr/local下
cp jdk-6u14-linux-x64.bin /usr/local/
 
3.#赋予jdk权限并执行
chmod +x /usr/local/jdk-6u14-linux-x64.bin
cd /usr/local/
./jdk-6u14-linux-x64.bin  #一路回车到底，最后输入yes 自动安装
 
4.#jdk改个名字
mv jdk1.6.0_14/ jdk1.6
 
5.#配置环境并刷新
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
 
source /etc/profile      #刷新配置文件
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

1.#下载安装包：jdk-6u14-linux-x64.bin、amoeba-mysql-binary-2.2.0.tar.gz

2.#把jdk复制到/usr/local下

3.#赋予jdk权限并执行

4.执行jdk，最后输入yes，接着回车

5…#jdk改个名字

6.#配置环境并刷新

2.4.2配置amoeba

############## 安装amoeba ###########
 
1.#在/usr/local目录下创建amoeba目录
mkdir /usr/local/amoeba
 
2.#切换至opt解压amoeba
cd /opt/
tar zxvf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba
 
cd /usr/local/amoeba 切换至目录查看
 
 
3.#给目录/usr/local/amoeba赋予执行权限
chmod -R 755 /usr/local/amoeba/
 
4.#运行amoeba
/usr/local/amoeba/bin/amoeba
如显示amoeba start|stop 说明安装成功

 
 
###########配置 Amoeba读写分离 ####
5.#先在Master、slave1、slave2的mysql上开放权限给 Amoeba 访问
grant all on *.* to test@'192.168.35.%' identified by '123456';
flush privileges;
 
6.#备份amoeba配置
cd /usr/local/amoeba/conf/
cp amoeba.xml amoeba.xml.bak
cp dbServers.xml dbServers.xml.bak
 
7.#修改amoeba配置
vim amoeba.xml
30 amoeba
#设置登录用户名
32123123
#设置密码
 
115master
#设置默认池为master
118master
#设置写池
119slaves
#设置读池
 
vim dbServers.xml 
23 
#23行注释
26test
#设置登录用户
28 
#删除
29123123
#解决28注释，添加密码
 
45
#服务池名
48192.168.35.6
#添加地址
 
52
55192.168.35.7
复制6行 添加另一从节点
59
62192.168.35.8
 
66
#定义池名
72slave1,slave2
#写上从节点名
 
8.#启动amoeba，并测试
amoeba start
netstat -ntap |grep java
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75

1.#在/usr/local目录下创建amoeba目录

2.#切换至opt解压amoeba

3.#给目录/usr/local/amoeba赋予执行权限

4.#运行amoeba

5.#先在Master、slave1、slave2的mysql上开放权限给 Amoeba 访问

2.4.3在客户端测试（192.168.35.9）

1.#安装mariadb
yum install mariadb mariadb-server.x86_64 -y
 
2.#登入并查看数据库
mysql -uamoeba -p123456 -h 192.168.35.9 -P8066
 
 
3.#测试同步
##在主服务器服务器上新建表
use test1;
create table info(id int(10),name char(40));
show tables;
 
##在客户机上，插入数据会同步到所有数据库中
 use test1;
 insert into info values(1,'小明');
 
##在主从服务器上查看
 
4.#测试读写分离
#停止slave1和slave2的slave同步功能
stop slave;
 
#在master、slave1和slave2上插入数据
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

1.#安装mariadb

2.#登入并查看数据库

3.测试

在主服务器上新建一个表

4.在客户机上，插入的数据也会同步到数据库中

5.在主服务器上查看

6.测试读写分离

7.在master上插入数据

8.在从服务器上插入数据

9.在客户端查看只能看到slave1的数据或者slave2的数据，说明客户端是以轮询的方式查看slave内容的

10.在客户端插入数数据，但是看不到，因为从服务器上关闭了slave的同步

11.但是可以在主服务器上可以看到数据

12.从服务器上看不到数据

uso-1663206859816)]

8.在从服务器上插入数据

[外链图片转存中…(img-z6RZBwtW-1663206859816)]

[外链图片转存中…(img-PS50HgDq-1663206859816)]

9.在客户端查看只能看到slave1的数据或者slave2的数据，说明客户端是以轮询的方式查看slave内容的

[外链图片转存中…(img-gjRWfjA0-1663206859816)]

10.在客户端插入数数据，但是看不到，因为从服务器上关闭了slave的同步

[外链图片转存中…(img-oregci43-1663206859817)]

11.但是可以在主服务器上可以看到数据

[外链图片转存中…(img-6MhB9Tgv-1663206859817)]

12.从服务器上看不到数据

[外链图片转存中…(img-XyhzXa1K-1663206859817)]

在从服务器上看不到客户端创建的数据，说明读写分离成功。