MySQL主从复制与读写分离

MySQL主从复制与读写分离

1.MySQL主从复制与读写分离原理

1.1MySQL主从复制原理

主从复制的工作过程

1、Master节点将数据的改变记录成二进制日志(Binary log)，当Master上的数据发生改变时，则将其改变写入二进制日志中

2、SLAVE节点会开启I/O线程，用于监听master的二进制日志的更新，一旦发现更新内容，则向master的dump线程发出同步请求

3、master的dump线程在接收到SLAVE的I/O请求后，会读取二进制日志文件中更新的数据，并发送给slave的I/O线程

4、SLAVE的I/O线程接收到数据后，会保存在slave节点的中继日志中

5、同时，slave节点中的SQL线程，会读取中继日志中的数据，更新在本地的mysql数据库中

6、最终，完成slave复制master数据，达到主从同步的效果

1.2主从复制类型

• 基于语句的复制(STATEMENT, MySQL默认 类型)
• 基于行的复制(ROW)
• 混合类型的复制(MIXED)

1.3MySQL主从复制方式

• 异步复制
  默认同步方式是异步复制。主库将更新写入Binlog日志文件后，不需要等待数据更新是否已经复制到从库中，就可以继续处理更多的请求。Master将事件写入binlog，但并不知道Slave是否或何时已经接收且已处理。在异步复制的机制的情况下，如果Master宕机，事务在Master上已提交，但很可能这些事务没有传到任何的Slave上。假设有Master->Salve故障转移的机制，此时Slave也可能会丢失事务。MySQL复制默认是异步复制，异步复制提供了最佳性能。
• 同步复制
  主库将更新写入Binlog日志文件后，需要等待数据更新已经复制到从库中，并且已经在从库执行成功，然后才能返回继续处理其它的请求。同步复制提供了最佳安全性，保证数据安全，数据不会丢失，但对性能有一定的影响。
• 半同步复制
  主库提交更新写入二进制日志文件后，等待数据更新写入了从服务器中继日志中，然后才能再继续处理其它请求。该功能确保至少有1个从库接收完主库传递过来的binlog内容已经写入到自己的relay log里面了，才会通知主库上面的等待线程，该操作完毕。
  半同步复制，是最佳安全性与最佳性能之间的一个折中。
  MySQL 5.5版本之后引入了半同步复制功能，主从服务器必须安装半同步复制插件，才能开启该复制功能。如果等待超时，超过rpl_semi_sync_master_timeout参数设置时间（默认值为10000，表示10秒），则关闭半同步复制，并自动转换为异步复制模式。当master dump线程发送完一个事务的所有事件之后，如果在rpl_semi_sync_master_timeout内，收到了从库的响应，则主从又重新恢复为增强半同步复制。
  ACK (Acknowledge character）即是确认字符

1.4MySQL读写分离原理

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

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

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

• 读写分离方案

  • 基于程序代码内部实现

  • 基于中间代理层实现：MySQL-Proxy、Amoeba

2.主从复制搭建

2.1环境

Master：192.168.36.10

Slave1：192.168.36.20

Slave2：192.168.36.30
1
2
3
4
5

#三台虚拟机全都关闭防火墙和核心防护
systemctl stop firewalld
setenforce 0
1
2
3

2.2主从服务器时间同步

主服务器

#安装时间同步ntp服务
[root@master ~]# yum install -y ntp

#时间同步（可不做）
[root@master ~]# ntpdate ntp.aliyun.com
16 Aug 18:29:11 ntpdate[6588]: the NTP socket is in use, exiting

#修改配置文件
[root@master ~]# vim /etc/ntp.conf
#添加内容

#设置本地为是时钟源
server 127.127.36.0

#网段固定格式127.127.+自己的网段号，stratum时间层级为8（限制在15内）
fudge 127.127.36.0 stratum 8

#启动服务
[root@master ~]# systemctl start ntpd
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

从服务器

#从服务器slave1
[root@slave1 ~]# yum install -y ntp
[root@slave1 ~]# ntpdate 192.168.36.10
17 Aug 13:49:56 ntpdate[2096]: adjust time server 192.168.36.10 offset -0.010059 sec
[root@slave1 ~]# service ntpd start


#从服务器slave2
[root@slave2 ~]# yum install -y ntp
[root@slave2 ~]# ntpdate 192.168.36.10
17 Aug 13:49:46 ntpdate[2104]: adjust time server 192.168.36.10 offset -0.011275 sec
[root@slave2 ~]# service ntpd start
1
2
3
4
5
6
7
8
9
10
11
12

2.3主服务器配置

[root@master ~]# vim /etc/my.cnf
#添加内容
server-id = 1
log-bin=master-bin       #添加主服务器
binlog_format = MIXED   #开启二进日志
log-slave-updates=true  #允许同步更新

#重启服务
[root@master ~]# systemctl restart mysqld
1
2
3
4
5
6
7
8
9

#进入mysql对从服务器进行授权
[root@master ~]# mysql -uroot -p123456
mysql> GRANT REPLICATION SLAVE ON *.* TO 'myslave'@'192.168.36.%' IDENTIFIED BY '123456';

#刷新
mysql> flush privileges;

#查看主数据配置信息
mysql> show master status;
1
2
3
4
5
6
7
8
9

2.4从服务器配置

#从服务器slave1
[root@slave1 ~]# vim /etc/my.cnf
#添加内容

server-id = 2       #id改为2，slave2改为3
relay-log=relay-log-bin   #开启中继日志
relay-log-index=slave-relay-bin.index   #定义中继日志文件的位置和名称
relay_log_recovery = 1

[root@slave1 ~]# systemctl restart mysqld
1
2
3
4
5
6
7
8
9
10

#slave1进入mysql
[root@slave1 ~]# mysql -uroot -p123456
change master to master_host='192.168.36.10',master_user='myslave',master_password='123456',master_log_file='master-bin.000001',master_log_pos=603;

#master-bin.000001 和 master_log_pos=603 就是之前主数据库配置文件查看的结果


#启动
mysql> start slave;

#查看slave状态
mysql> show slave status\G
1
2
3
4
5
6
7
8
9
10
11
12

显示两个yes就没有问题，slave2操作一样

2.5验证主从同步

#在主服务器上创一个库
mysql> create database slave_test;

#在从服务器查看
mysql> show databases;
1
2
3
4
5

3.搭建读写分离

3.1安装JAVA环境

再开一台虚拟机用于配置Amoeba，IP为192.168.36.40

将 jdk-6u14-linux-x64.bin 放在 /usr/local 目录下

[root@amoeba local]# chmod +x jdk-6u14-linux-x64.bin
[root@amoeba local]# ./jdk-6u14-linux-x64.bin
输入yes，回车
[root@amoeba local]# mv jdk1.6.0_14/ jdk1.6

[root@amoeba local]# 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

[root@amoeba local]# source /etc/profile

#查看java版本
[root@amoeba local]# java -version
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

3.2安装Amoeba

[root@amoeba ~]# mkdir /usr/local/amoeba
[root@amoeba ~]# cd /usr/local/amoeba
[root@amoeba amoeba]# tar zxvf /opt/amoeba-mysql-binary-2.2.0.tar.gz -C ./
[root@amoeba amoeba]# chmod -R 755 /usr/local/amoeba/
[root@amoeba amoeba]# ./bin/amoeba
amoeba start|stop
1
2
3
4
5
6

修改主配置文件

#在Master、slave1、Slave2的mysql上开放权限给Amoeba访问
grant all on * . * to test@'192.168.36.%' identified by '123456';
1
2

[root@amoeba amoeba]# cd conf
[root@amoeba conf]# ls
1
2

[root@amoeba conf]# vim amoeba.xml
#第30行，修改账户名，root改为amoeba
amoeba

#第32行，添加密码
123456

#第115行，修改默认池
master

#第117-120行
取消注释
#写池命名为master
master
#读池命名为slaves
slaves
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

amoeba.xml定义的是：

1.用户通过amoeba访问数据库时“登录amoeba代理的”账号、密码

2.定义amoeba代理的“读池”和“写池”的名称：slaves和master

PS：这个是第一个账号

修改数据库配置文件

[root@amoeba conf]# cp dbServers.xml dbServers.xml.bak
[root@amoeba conf]# vim dbServers.xml
#第23行
注释

#第26行，root改为test（自定义）
test

#第28-30行，28行保持注释，29行取消注释并添加密码

123456

#第45行，设置主服务器的名为master


#第48行，设置主服务器的地址
192.168.36.10

#第52行，设置从服务器1的名slave1


#第55行，设置从服务器1的地址
192.168.36.20

#第58行
复制52-57行内容，在58行粘贴并修改为从服务器2的名称和IP



192.168.36.30



#第65行，multiPool改为slaves


#第71行，server1,server2改为slave1,slave2
slave1,slave2

[root@amoeba conf]# cd ../
[root@amoeba amoeba]# cd bin
[root@amoeba bin]# ./amoeba start &
#启动Amoeba软件，按ctrl+c返回

[root@amoeba bin]# netstat -anpt | grep java
[root@amoeba bin]# netstat -anpt | grep 8066
#查看8066端口是否开启，默认端口为TCP8066
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

dbServers.xml主要定义了：

1.amoeba访问mysql数据库的用户和密码

2.定义了后端的mysql服务器具体的角色及其IP地址

3.定义了读池又哪些mysql成员

3.3测试读写分离

再开一台虚拟机，client端

[root@client ~]# yum install -y mariadb-server mariadb
[root@client ~]# systemctl start mariadb.service

[root@client ~]# mysql -u amoeba -p123456 -h 192.168.36.40 -P8066
1
2
3
4

在主服务器上创建一个表

mysql> use slave_test
mysql> create table test(id int(4),name char(10));
1
2

两台从服务器关闭slave，并插入数据

#slave1
mysql> stop slave;

mysql> use slave_test
mysql> insert into test values (1,'zhangsan');
1
2
3
4
5

#slave2
mysql> stop slave;

mysql> use slave_test
mysql> insert into test values (2,'lisi');
1
2
3
4
5

在主服务器上插入数据

mysql> insert into test values(3,'wangwu');
1

最后到客户端验证

因为设置了轮询，所以查询两次是分别从slave1和slave2查询到的结果，master插入的内容则查询不到