MYSQL 主从复制 --- binlog

短命女 2024-04-21 19:05 54阅读 0赞

### 一个MYSQL数据库存在的问题 ###

在谈主从复制之前，应该都会有一个疑问，那么就是一个MYSQL数据库存在的问题呢？

> 1.  读和写所有压力都由一台数据库承担，压力大
> 2.  数据库服务器磁盘损坏则数据丢失，单点故障

为了解决我们可以使用MYSQL的主从复制处理，那么什么是主从复制呢？

### 主从复制的概念 ###

MySQL主从复制是一个**异步**的复制过程，底层是基于Mysql数据库自带的**二进制日志 binlog** 功能。简单的说，就是一台或多台MySQL数据库（slave，即**从库**）从另一台MySQL数据库（master，即**主库**）进行日志的复制，然后再解析日志并应用到自身，最终实现 **从库** 的数据和 **主库** 的数据保持一致。

> BINLOG：记录了所有的 DDL（数据定义语言）语句和 DML（数据操纵语言）语句，但是不包括数据查询语句。此日志对于灾难时的数据恢复起着极其重要的作用，MySQL的主从复制， 就是通过该binlog实现的。默认MySQL是未开启该日志的。更详细的binlog可以阅读[MYSQL日志][MYSQL]文章

### 主从复制实现的过程 ###

我们从主从复制的概念中知道它主要是依赖于binlog 日志，而且这个过程是异步的，即主库上执行事务操作线程不会等待复制binlog的线程完成。如下图：

![img][]MYSQL主从复制过程

从图中可以知道，MYSQL复制过程分为三步：

> 1.  **写入 Binlog**：主库写 binlog 日志，提交事务，并更新本地存储数据
> 2.  **同步 Binlog**：把 binlog 复制到所有从库上，每个从库把 binlog 写到暂存日志中
> 3.  **回放 Binlog**：回放 binlog，并更新存储引擎中的数据

上图更详细的解释如下：

> 1.  MySQL 主库在收到客户端提交事务的请求之后，会先写入 binlog，再提交事务，更新存储引擎中的数据，事务提交完成后，返回给客户端“操作成功”的响应
> 2.  从库会创建一个专门的 I/O 线程，连接主库的 log dump 线程，来接收主库的 binlog 日志，再把 binlog 信息写入 relay log 的中继日志里，再返回给主库“复制成功”的响应
> 3.  从库会创建一个用于回放 binlog 的线程，去读 relay log 中继日志，然后回放 binlog 更新存储引擎中的数据，最终实现主从的数据一致性

这要实现了主从复制过程，那么我们经常使用到的有哪些场景呢？比如读写分离。即写数据时只写主库，在读数据时只读从库，这样即使写请求会锁表或者锁记录，也不会影响读请求的执行。如下图：

![img][img 1]MYSQL主从架构的读写分离

到这里基本知道了什么是主从复制了，那么在使用过程中是不是从库可以无限多呢？

> 答案：当然不是的。因为从库数量增加，从库连接上来的 I/O 线程也比较多，**主库也要创建同样多的 log dump 线程来处理复制的请求，对主库资源消耗比较高，同时还受限于主库的网络带宽**。所以，一般个主库跟 2～3 个从库（1 主 2 从 1 备主），这就是一主多从的 MySQL 集群结构

### MySQL 集群结构 ###

> 1.  主从(Master-Slaves)
> 2.  主主(Master-Master)
> 3.  主从从 … (Master-Slaves-Slaves…)
> 4.  主主从 (Master - Master - Slaves)

#### 常规复制架构—主从(Master-Slaves) ####

在实际应用场景中，MySQL 复制 90% 以上都是一个 Master 复制到一个或者多个 Slave 的架构模式，主要用于读压力比较大的应用的数据库端廉价扩展解决方案。如下图：

![img][img 2]  
主从 (Master-Slaves)

一个 Master 复制多个 Slave 的架构实施非常简单，多个 Slave 和单个 Slave 的实施 并没有实质性的区别。在 Master 端并不 Care 有多少个 Slave 连上了自己，只要有 Slave 的 IO 线程通过了连接认证，向他请求指定位置之后的 Binary Log 信息，他就会按照该 IO 线程的要求，读取自己的 Binary Log 信息，返回给 Slave 的 IO 线程。

#### Dual Master 复制架构 —主主(Master - Master) ####

**双主(Dual Master)复制架构**适用于DBA做维护时需要主从切换的场景，通过双主复制架构避免了重复搭建从库的麻烦，双主复制架构如下图所示：

![img][img 3]  
主主 (Master - Master)

主库Master1和Master互为主从，所有客户端的写请求都访问主库Master1或Master2。加入DBA需要做日常维护操作，为了避免影响服务，需进行一下操作：

>  *  首先，在Master1库上停止Slave线程(STOP SLAVE)，避免后续对Master2库的维护操作操作被实时复制到Master1库上对服务造成影响。
>  *  其次，在Master2库上停止Slave线程(STOP SLAVE)，开始日常维护操作，例如修改varchar字段从长度10增加到200。
>  *  然后，在Master2库上完成维护操作之后，打开Master2库上的Slave线程(STRART SLAVE),让Master2的数据和Master1库同步，同步完成后，把应用的写操作切换到Master2库上。
>  *  最后，确认Master1库上没有应用访问后，打开Master1的Slave线程(START SLAVE)即可

通过双主复制架构能够大大减轻一主多从架构下对主库进行维护带来的额外搭建从库的工作。可以配合一个第三方的工具，比如keepalived 轻松做到 IP 的漂移，停机维护也不会影响写操作。

#### 级联复制架构 —主从从 … (Master - Slaves - Slaves …) ####

如果读压力加大，就需要更多的 slave 来解决，但是如果slave的复制全部从 master 复制，势必会加大 master 的复制IO的压力，所以就出现了级联复制，减轻 master 压力。如下图：

![img][img 4]  
主从从 … (Master - Slaves - Slaves …)

但是，这个框架有一个缺点就是slave 延迟更加大了

#### Dual Master与级联复制结合架构 —主主从 (Master - Master - Slaves) ####

级联复制在一定程度上面确实解决了 Master 因为所附属的 Slave 过多而成为瓶颈的问题，但是他并不能解决人工维护和出现异常需要切换后可能存在重新搭建 Replication的问题。如下图：

![img][img 5]  
主主从 (Master - Master - Slaves)

这样就解决了单点 master 的问题，解决了slave 级联延迟的问题。

### 主从复制有哪些模型 ###

主从复制模型是通过sync\_binlog参数（具体可以阅读[MYSQL日志][MYSQL]）来控制的，主要有三种：

>  *  **同步复制**：MySQL 主库提交事务的线程要等待所有从库的复制成功响应，才返回客户端结果。这种方式在实际项目中，基本上没法用，原因有两个：一是性能很差，因为要复制到所有节点才返回响应；二是可用性也很差，主库和所有从库任何一个数据库出问题，都会影响业务。
>  *  **异步复制**（默认模型）：MySQL 主库提交事务的线程并不会等待 binlog 同步到各从库，就返回客户端结果。这种模式一旦主库宕机，数据就会发生丢失。
>  *  **半同步复制**：MySQL 5.7 版本之后增加的一种复制方式，介于两者之间，事务线程不用等待所有的从库复制成功响应，只要一部分复制成功响应回来就行，比如一主二从的集群，只要数据成功复制到任意一个从库上，主库的事务线程就可以返回给客户端。这种**半同步复制的方式，兼顾了异步复制和同步复制的优点，即使出现主库宕机，至少还有一个从库有最新的数据，不存在数据丢失的风险**。

### 主从复制解决的问题 ###

> 数据分布 (Data distribution )
> 
> 负载平衡(load balancing)
> 
> 备份(Backups)
> 
> 高可用性和容错行 High availability and failover

### 主从复制实验搭建 ###

#### **配置主从复制步骤** ####

**Master数据库：**

> 1.  安装数据库;
> 2.  修改数据库配置文件，指明 server\_id，开启二进制日志(log-bin);
> 3.  启动数据库，查看当前是哪个日志，position 号是多少;
> 4.  登录数据库，授权数据复制用户(IP 地址为从机 IP 地址，如果是双向主从，这里的 还需要授权本机的 IP 地址，此时自己的 IP 地址就是从 IP 地址);
> 5.  备份数据库(记得加锁和解锁);
> 6.  传送备份数据到 Slave 上;
> 7.  启动数据库;
> 
> 以上步骤，为单向主从搭建成功，想搭建双向主从需要的步骤：
> 
> a. 登录数据库，指定 Master 的地址、用户、密码等信息(此步仅双向主从时需要);
> 
> b. 开启同步，查看状态;

**Slave数据库：**

> 1.  安装数据库;
> 2.  修改数据库配置文件，指明 server\_id(如果是搭建双向主从的话，也要开启二进制 日志 log-bin);
> 3.  启动数据库，还原备份;
> 4.  查看当前是哪个日志，position 号是多少(单向主从此步不需要，双向主从需要);
> 5.  指定 Master 的地址、用户、密码等信息;
> 6.  开启同步，查看状态

#### 例子 ####

1.准备工作

准备两台机器，并安装好mysql，服务器信息如下：

![img][img 6]

1.  防火墙

如果这两天机器有防火墙，并开启了防火墙，那么就要开发对应的端口，如下：

> firewall-cmd --zone=public --add-port=3306/tcp --permanent --添加端口
> 
> firewall-cmd --zone=public --list-ports --查看开放的端口

防火墙更加详细的操作可以阅读[LINUX防火墙][LINUX]文章。

1.  启动mysql服务

把这两台mysql服务启动起来，如下：

> systemctl start mysqld 或者 systemctl start mysql

然后验证是否启动成功，如下图启动成功：

![img][img 7]

注意事项

> 在搭建Mysql主从架构过程中，由于从服务器是克隆的主服务器系统，导致主从mysql uuid相同,解决办法，修改其中一台服务器的server-uuid，并保证server-uuid的格式正确，修改完成之后重启Mysql服务就可以了。
> 
> 在修改配置文件之前，先登录Mysql客户端查看uuid，把返回的uuid复制，放到要修改的配置文件即可

![img][img 8]

查到uuid之后，修改uuid配置文件：

> \# vim /data/mysql/auto.cnf //如过找不到 find -name auto.cnf 找一下路径，这个文件一般在数据目录下
> 
> \[auto\]
> 
> server-uuid=267170ea-5f41-11ed-93db-000c29936244
> 
> \# 按照这个16进制格式，修改server-uuid，重启mysql即可

1.  主库配置

在mysql配置文件最下面增加配置:

> log-bin=mysql-bin \#\[必须\]启用二进制日志  
> server-id=1 \#\[必须\]服务器唯一ID(唯一即可)

> ![img][img 9]

保存配置文件之后，**重启主库Mysql服务：**

> systemctl restart mysqld 或者 systemctl restart mysql

然后登陆主库mysql服务，接着通过下面指令查看配置文件是否修改成功：

![img][img 10]

从上图可以看出已经修改成功，接着创建用户并授权：

> GRANT REPLICATION SLAVE ON *.* to ‘ian’@‘%’ identified by ‘mysqltest’;
> 
> 命令解析：
> 
> ian：是用户名字
> 
> mysqltest： 是密码
> 
> 并且给ian用户授予REPLICATION SLAVE权限。常用于建立复制时所需要用到的用户权限，也就是slave必须被master授权具有该权限的用户，才能通过该用户复制。

创建完用户和授权之后，查看master同步状态，主要是那个文件开始和开始的位置：

> show master status;

![img][img 11]

为什么要知道这两个值呢？因为在从库的时候要用到。到这里主库已经配置好了。

1.  从库配置

修改从库配置文件：

> server-id=12 \#\[必须\]服务器唯一ID 保证唯一 需要主和从唯一就可  
> relay-log=relay-log  
> relay-log-index=relay-log.index

![img][img 12]

保存配置文件，然后重启从库数据库：

> systemctl restart mysqld 或者 systemctl restart mysql

登录从库数据库，然后查看配置是否修改成功：

![img][img 13]

修改成功之后，在从库中设置主库地址和同步位置：

> change master to master\_host=‘192.168.0.117’,master\_user=‘ian’,master\_password=‘mysqltest’,master\_log\_file=‘mysql-bin.000008’,master\_log\_pos=23758;
> 
> 参数说明：
> 
> A. master\_host : 主库的IP地址
> 
> B. master\_user : 访问主库进行主从复制的用户名(上面在主库创建的)
> 
> C. master\_password : 访问主库进行主从复制的用户名对应的密码
> 
> D. master\_log\_file : 从哪个日志文件开始同步(上述查询master状态中展示的有)
> 
> E. master\_log\_pos : 从指定日志文件的哪个位置开始同步(上述查询master状态中展示的有)

然后查看从库数据库的状态：

> show slave status \\G;
> 
> \\G : 在MySQL的sql语句后加上\\G，表示将查询结果进行按列打印，可以使每个字段打印到单独的行。即将查到的结构旋转90度变成纵向；

![img][img 14]

1.  测试

我们在主库中创建一个数据库test4，如下图：

![img][img 15]

然后我们在到从库中查看，可以知道：

![img][img 16]

然后我们可以在这个数据库里面添加一些测试数据，如添加一个user表，然后在表中添加一行数据，一样可以在从库中查看得到，那么我们就配置完成了。

问题1：如果Mysql的远程连接不上，出现下面问题

![img][img 17]

答案：在mysql配置中添加skip-grant-tables，并屏蔽掉bind\_address配置项

问题2：MySQL主从复制，启动slave时，出现下面报错：

> mysql> start slave;  
> ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository

![img][img 18] 可以看到报错，原来是找不到./server246-relay-bin.index文件，找到原因所在了，由于我使用的是冷备份文件恢复的实例，在mysql库中的slave\_relay\_log\_info表中依然保留之前relay\_log的信息，所以导致启动slave报错。

mysql提供了工具用来删除记录：

> slave reset；

slave reset执行候做了这样几件事：

> 1、删除slave\_master\_info ，slave\_relay\_log\_info两个表中数据；  
> 2、删除所有relay log文件，并重新创建新的relay log文件；  
> 3、不会改变gtid\_executed 或者 gtid\_purged的值

> mysql> reset slave;  
> Query OK, 0 rows affected (0.01 sec)
> 
> mysql> change master to …
> 
> mysql> start slave;  
> Query OK, 0 rows affected (0.00 sec)

这样slave 就可以启动了。

[MYSQL]: https://blog.csdn.net/sanylove/article/details/127365221
[img]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/21/6528937afb944211b9e8aa72e7b01559.png
[img 1]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/21/d696d97bd9744f70b2898dc647e8bb01.png
[img 2]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/21/99ff7d86074f46fbb00469bbd1372add.png
[img 3]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/21/54a0e6ba5ffc43c29e8bf6b915d9d3e0.png
[img 4]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/21/f580256484ae40b1bd7b5640e7997742.png
[img 5]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/21/3fcf0e24be914d00bbb19edb80d81bbb.png
[img 6]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/21/0bdc45b2af84476d8da16d45d28d4320.png
[LINUX]: https://blog.csdn.net/sanylove/article/details/127751698
[img 7]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/21/483b5291bb1a49c2a5c410cd4d5e6913.png
[img 8]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/21/53c64f7e77d24f84aa6520b32a8f6189.png
[img 9]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/21/c60d2bbe1d1f4727b6971b0a4c369ead.png
[img 10]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/21/01a203f0cae4450a827c79b39258afe9.png
[img 11]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/21/cf86c774ce9345f681dfea2970ea2310.png
[img 12]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/21/3c3391dc680249eb88af3ac7711185b9.png
[img 13]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/21/c2de1df031714cd09607ef4250df457b.png
[img 14]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/21/288b3e27592e404aae96eaa65b9fc955.png
[img 15]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/21/7e8192d0dbae4f6888655b68f4746498.png
[img 16]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/21/0a92fa195f624fb9bc0e5bf78e6b078b.png
[img 17]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/21/62b0fa12b799431798fdc1662699a258.png
[img 18]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/21/ba5ba3b792a14ce6b5685c54d1883f80.png