【MySql】 搭建主从复制

一、概述 

主从复制是指将主库的数据变更同步到从库，从而保证主库和从库数据一致，可用于数据备份、失败迁移、读写分离、降低单库读写压力等场景。对于面向大企业的B端产品而言，主从复制技术尤为重要。

1.1 什么是主从复制

主从复制是指将主库的 DDL 和 DML 操作通过二进制日志传到从库服务器中，然后在从库上对这些日志重新执行（也叫重做），从而使得从库和主库的数据保持一致。

MySQL 支持一台主库同时向多台从库进行复制，从库同时也可以作为其他从服务器的主库，实现链状复制。

MySQL 复制的优点，主要包含以下三个方面：

1. 实时灾备，用于故障切换：主库出现问题，可以快速切换到从库提供服务。
2. 读写分离，提供查询服务：实现读写分离，降低主库的访问压力。
3. 备份，避免影响业务：可以在从库中执行备份，以避免备份期间影响主库服务。

1.2 主从复制应用场景

1. 主库压力较大扛不住时，可通过主从复制把读压力分担一部分到从库上；
2. 业务高峰数据库连接数太多时，例如高峰的认证，只需要把这个业务分拆到从库上；
3. 低频复杂类且难以优化的 SQL（报表统计分析类的 SQL），专门在从库上执行；
4. 利用从库做高可用切换。

1.3 主从复制原理

如上图所示，主从复制的过程原理可分为三个部分：

1. 主库汇报数据变更记录在二进制日志文件 binlog 中：

a) 启动（Slave I/O Thread->master.info；Slave SQL Thread，Relay-log.info）；

b) 建立连接（slave I/O Thread->master.info；Master log Dump Thread->binlog ）。

2. 从库连接主库，读取 binlog 日志，并写入自身中继日志 Relay log：

a) 获取更新日志（Master log dump Thread->binlog）；

b) 传递更新日志（Master log dump Thread->binlog；slave I/O Thread->master.info）；

c) 保存更新日志（slave I/O Thread->master.info；slave Relay log）。

3. slave 重做中继日志，将改变反映它自身的数据：应用中继日志（Slave SQL Thread；relay-log.info；slave Relay Log）。

1.4 MySQL主从复制工作方式

1. master 记录二进制日志。在每个事务完成数据更新之前，master 在二进制日志记录这些改变。MySQL 将事务串行地写入二进制日志，完成后，master 通知存储引擎提交事务。

2. slave 将 master 的 binary log 拷贝到它自身的中继日志：

a) 首先，slave 开始一个工作线程：I/O 线程；

b) I/O 线程在 master 上打开一个普通的连接，然后开始 binlog dump process。binlog dump process 从 master 的二进制日志中读取事件（接收的单位是 event），如果已经跟上 master，它会睡眠并等待 master 产生新的事件；

c) I/O 线程将这些事件写入中继日志。

3. SQL slave 重做中继日志

a) Thread（SQL 从线程）是处理该过程的最后一步。SQL 线程从中继日志读取事件，并重放其中的事件（回放的单位也是 event）更新 slave 的数据，使其与 master 中的数据一致。只要该线程与 I/O 线程保持一致，中继日志通常会位于 OS 的缓存中，所以中继日志的开销很小；

b) 在 master 中也有一个工作线程：和其他 MySQL 的连接一样，slave 在 master 中打开一个连接也会使得 master 开始一个线程。

1.5 MySQL主从复制技术之GTID

GTID（Global Transaction Identifiers）是已提交事务的一个编号，是事务的全局唯一编号。GTID 和事务会记录到 binlog 中，用来标识事务。GTID 用于替代以前传统复制方法(binglog+position)，在MySQL 5.6.2 开始支持 GTID。

MySQL 支持 GTID 后，一个事务在集群中就不再孤单，在每一个节点中，如果存在具有相同标识符的情况，可以避免同一个事务，在同一节点中出现多次的情况。

GTID 的出现，最直接的效果就是，每一个事务在集群中具有了唯一性的意义，相对于行复制来讲，数据安全性更高，故障切换更简单。

二、环境搭建

2.1 环境准备

 
两台服务器准备好了后，我们还需要在这两台服务器中去对防火墙做一个处理，由于在这两台服务器中我们都需要去安装MySQL，MySQL默认端口号为3306，所以我们需要开启防火墙中的3306端口号，或者直接将防火墙关闭。
那么在我们测试学习阶段建议直接将防火墙关闭就行了，而我们在具体生产环境中，那么还是建议只开放指定端口即可。
在上述的两台服务器中分别安装好MySQL，并完成基础的初始化准备(安装、 密码配置等操作)工作，其中：

192.168.200.200 作为主服务器master
192.168.200.201 作为从服务器slave

主从模式也是通过mysql的binlog实现。

一、选择安装的 MySQL 版本

1. 首先判断是否要和公司其他已经安装好的 MySQL 保持版本一致。

2. 如果不是实验新功能性质，则不选择 development release，而是安装 General Availability（GA）release（代表稳定版本，可在生产系统使用）。

二、选择安装 MySQL 的方式

安装方式有2种：二进制安装包的方式(RPM/ZIP/Tar等)、源码方式(source code)。一般会选择二进制安装方式；如果有特殊需求，比如修改一部分源码或修改 MySQL 深层次的配置，则会选择源码方式。

三、二进制 Tar 包安装 MySQL

MySQL 安装步骤如下，参考Mysql的linux安装部署。

2.2 主要步骤

先在主mysql中配置 记录mydb2/mydb3库的操作日志到binlog日志文件中

1. – 主库写操作会按照配置记录到二进制文件中（binlog）
2. – 主库需要创建一个从账户并分配可以读取binlog日志的权限

在从mysql中配置中继日志文件，用来保存读取到的mysql主的 binlog 日志

1. – 从库可以开启主从复制，从指定的主库的binlog文件中加载日志缓存到自己的relaylog文件中，最后通过一个sql线程将relaylog文件中的日志replay到自己的库表中
2. – 从库需要使用主库提供的账号和主库的binlog文件建立连接

2.3 主库配置

改配置文件 /etc/my.cnf

#mysql 服务ID，保证整个集群环境中唯一，取值范围：1 – 2的32次方-1，默认为1
server-id=1
# 二进制日志名，默认binlog
log_bin = /var/log/mysql/mysql-bin.log

# 设置日志格式，默认值ROW。row（记录行数据）  statement（记录sql）  mixed（混合模式）
binlog_format=STATEMENT

#是否只读,1 代表当前服务器只读(不能做增删改操作), 0代表读写
read-only=0
#下面两个选项大家可以不设置，但可以了解下。我们这里不指定这两个参数，也就是说我们现在创建的所有数据库都需要同步。
# 设置需要复制的数据库，默认复制全部数据库
binlog-do-db=mydb2
binlog-do-db=mydb3
# 设置不需要复制的数据库
binlog-ignore-db=mydb4
#binlog-ignore-db=infomation_schema

重启MySQL服务器

systemctl restart mysqld

登录mysql，创建远程连接的账号，并授予主从复制权限

#创建itcast用户，并设置密码，该用户可在任意主机连接该MySQL服务
CREATE USER 'itcast'@'%' IDENTIFIED WITH mysql_native_password BY 'Root@123456';
#为 'itcast'@'%' 用户分配主从复制权限
GRANT REPLICATION SLAVE ON *.* TO 'itcast'@'%';

通过指令，查看二进制日志坐标

show master status;

字段含义说明：

file : 从哪个日志文件开始推送日志文件，即我们写到哪个日志文件了
position： 从哪个位置开始推送日志，稍后我们只需要从当前这个日志文件的这个位置往后再来同步就行了
binlog_ignore_db : 指定不需要同步的数据库

2.4 从库配置

1、修改配置文件 /etc/my.cnf

#mysql 服务ID，保证整个集群环境中唯一，取值范围：1 – 2^32-1，和主库不一样即可，并且在这个范围之内即可
server-id=2
#是否只读,1 代表只读, 0 代表读写。对于从库来说，我们只需要从从库中进行查询即可，不需要从从库中进行写入操作，所以直接把read-only设置为1就行了。
#PS：这个read-only=1，仅仅代表的是对普通用户是只读的，如果这个用户具有超级管理员super的权限，那么它也是可以进行读写的。如果想要禁用超级管理员的读写功能，此时我们也可以去设置一个参数：super-read-only=1(使用-和_都可以)
read-only=1
# 中继日志名，默认xxxxxxxxxxxx-relay-bin
#relay-log=relay-bin

2、重新启动MySQL服务

systemctl restart mysqld

 登录从数据库，设置主库配置

CHANGE REPLICATION SOURCE TO SOURCE_HOST='192.168.200.200', SOURCE_USER='itcast', SOURCE_PASSWORD='Root@123456', SOURCE_LOG_FILE='binlog.000004', SOURCE_LOG_POS=663;

SOURCE_HOST：原主机地址；

SOURCE_USER 原主机上对应的MySQL的用户名是什么；

SOURCE_PASSWORD：原主机密码是什么；

SOURCE_LOG_FILE：我从哪个二进制文件开始读；

SOURCE_LOG_POS：代表position，我要从这份日志中的哪个位置开始读

上述是8.0.23中的语法。如果mysql是 8.0.23 之前的版本，执行如下SQL：

CHANGE MASTER TO MASTER_HOST='192.168.200.200', MASTER_USER='itcast', MASTER_PASSWORD='Root@123456', MASTER_LOG_FILE='binlog.000004', MASTER_LOG_POS=663;

其实也就是参数名不同而已，在8.0.23之后也是兼容8.0.23之前的语法的。

开启同步操作

start replica; #8.0.22之后
start slave;  #8.0.22之前，新版本也是兼容老版本的

 查看主从同步状态

show replica status; #8.0.22之后
show slave status; #8.0.22之前，新版本也是兼容老版本的

查看 GTID 状态

show  variables like '%gtid%';