MySQL——主从复制

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

---

前言

---

一、MySQL主从复制

在实际生产环境中，面对巨大的并发量和数据读写压力，我们往往需要多台数据库服务器来提共服务，并且将读和写的的压力分担到不同的服务器上，以满足在安全性与高可用性上的需求。

二、主从复制原理

MySQL 的主从复制和 MySQL 的读写分离两者有着紧密联系，首先要部署主从复制，
只有主从复制完成了，才能在此基础上进行数据的读写分离。

三、MySQL支持的复制类型

（一）、基于语句的复制（STATEMENT）。

在主服务器上执行的 SQL 语句，在从服务器上执行同样的语句。MySQL 默认采用基于语句的复制，效率比较高。

（二）、基于行的复制（ROW）。

把改变的内容复制过去，而不是把命令在从服务器上执行一遍。

（三）、混合类型的复制（MIXED）。

默认采用基于语句的复制，一旦发现基于语句无法精确复制时，就会采用基于行的复制。

四、主从复制的工作过程

核心：两个日志，三个线程
两个日志：二进制日志、中继日志
三个线程：I/O、dump、sql

#主要原理：Master将数据保存在二进制日志中，I/O向dump发出同步请求，dump把数据发送给I/O线程，I/O写入本地的中继日志数据，同步到自己数据库中，完成同步。
1

- 主MySQL服务器做的增删改操作，都会写入自己的二进制日志（Binary log)；
- 然后从MySQL从服务器打开自己的I/O线程连接主服务器，进行读取主服务器的二进制日志；
- I/O去监听二进制日志，一旦有新的数据，会发起请求连接；
- 这时候会触发dump线程，dump thread响应请求，传送数据给I/O（dump线程要么处于等待，要么处于睡眠）；
- I/O接受到数据之后存放在中继日志；
- SQL thread线程会读取中继日志里的数据，存放到自己的服务器中。
1
2
3
4
5
6

1、在每个事务更新数据完成之前，Master在二进制日志(Binary log)记录这些改变。写入二进制日志完成后，Master通知存储引擎批交事务。
2、Slave 将Master复制到其中继日志(Relay log)，首先slave开始一个工作线程(I/O),I/O线程在Master上打开一个普通的连接，然后开始Binlog dump process。Binlog dump process从Master的二进制日志中读取事件，如果已经跟上Master,它会睡眠并等待Master产生新的事件，I/O线程将这些事件写入中继日志。
3、SQL slave thread(SQL从线程)处理该过程的最后一步，SQL线程从中继日志读取事件，并重放其中的事件而更新slave数据，使其与Master中的数据一致，只要该线程与I/O线程保持一致。

1
2
3
4

注:

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

五、配置主从复制

主服务器：192.168.116.10
从服务器1：192.168.116.20
从服务器2：192.168.116.30
1
2
3

（一）、进行服务器时间同步

#关闭防火墙
systemctl stop firewalld
setenforce 0

ntpdate ntp1.aliyun.com
1
2
3
4
5

（二）、配置主服务器

vim /etc/my.cnf

log-bin=master-bin        #开启二进制日志
binlog_format=MIXED       #二进制日志格式
log-slave-updates=true    #开启从服务器同步

systemctl restart mysqld.service

1
2
3
4
5
6
7
8

给从服务器授权
grant replication slave on *.* to 'myslave'@'192.168.116.%' identified by '123456';
#刷新
flush privileges;
#查看主服务器二进制文件
show master status;

1
2
3
4
5
6
7

（三）、配置从服务器1

vim /etc/my.cnf

server-id = 2           ##slave1和slave2的id不能相同
log-bin=master-bin
relay-log=relay-log-bin
relay-log-index=slave-relay-bin.index

systemctl restart mysqld.service

1
2
3
4
5
6
7
8
9

change master to master_host='192.168.116.10',master_user='myslave',master_password='123456',master_log_file='master-bin.000001',master_log_pos=604;

flush privileges;    #刷新
start slave;         #开启
show slave status\G  #查看

1
2
3
4
5
6

（四）、配置从服务器2

vim /etc/my.cnf

server-id = 3           ##同一集群的slave ID不能相同
log-bin=master-bin
relay-log=relay-log-bin
relay-log-index=slave-relay-bin.index

systemctl restart mysqld.service

1
2
3
4
5
6
7
8
9

change master to master_host='192.168.116.10',master_user='myslave',master_password='123456',master_log_file='master-bin.000001',master_log_pos=604;

flush privileges;    #刷新
start slave;         #开启
show slave status\G  #查看

1
2
3
4
5
6

（五）、验证

1、在主服务器中写入一些数据

2、在第一个从服务器上查看，数据已经同步过来

3、在第二个从服务器上查看，数据已经同步过来

---

总结

主从复制的优点

1、数据备份（Data Backup）：只是简单的对数据库进行备份，降低数据丢失的风险。
2、线下统计：用于报表等对数据时效性要求不高的场合。
3、负载均衡（Load Balance）、读写分离：主要用于在MySQL集群，解决单点故障或做故障切换；以降低单台服务器的负载和风险，如实现读写分离，可以使得服务器访问负荷比较均衡。
4、数据分发(Data DistributIOn)、灾备：主要用于多数据中心或异地备份，实现数据分发与同步。
5、高可用和数据容错(High Availability and Failover)：MySQL自带的健康监控和检测，根据配置的时间间隔，可以检测主库是否正常工作，一旦发现主库宕机或无法正常工作，就会选择到最好的一个备库上。
1
2
3
4
5
6
7