【MySQL】主从复制（两台服务器）

概述

主从复制是将主数据库的DDL（数据定义语句）和 DML（数据操作语句）操作通过二进制日志（binlog）传到从库服务器中，然后在从库上对这些日志重新执行（也叫重做），从而使得从库和主库的数据保持同步。主库通常称为Master，从库通常称为Slave。

优点

1、主库发生问题，可以快速切换到从库提供服务。

2、实现读写分离，降低主库的访问压力。

3、可以在从库中执行备份，以避免备份期间影响主库服务。

原理

        当从服务器连接到主服务器时，从服务器会启动一个I/O线程，该线程会连接到主服务器，并请求复制主服务器上的二进制日志。I/O线程会将二进制日志的内容读取到本地的中继日志（relay log）中，并保存在本地磁盘上。

        I/O线程的主要任务是从主服务器上读取二进制日志，并将其写入从服务器的中继日志。I/O线程的工作方式如下：

1. 从服务器向主服务器发送一个请求，请求复制主服务器上的二进制日志。

2. 主服务器接收到请求后，将二进制日志的内容发送给从服务器。

3. I/O线程在从服务器上读取二进制日志，并将其写入中继日志。

4. 从服务器周期性地向主服务器发送心跳包，以确保主从服务器之间的连接正常。

5. 如果主服务器发生故障，从服务器可以自动切换到另一个主服务器（即主服务器的备份服务器）。

        当I/O线程将二进制日志写入中继日志后，SQL线程会读取中继日志中的内容，并执行其中的SQL语句。SQL线程的主要任务是将中继日志中的SQL语句执行到从服务器上的数据库中。SQL线程的工作方式如下：

1. SQL线程从中继日志中读取SQL语句。

2. SQL线程执行SQL语句，并将结果写入从服务器上的数据库中。

3. SQL线程周期性地向主服务器发送心跳包，以确保主从服务器之间的连接正常。

4. 如果主服务器发生故障，从服务器可以自动切换到另一个主服务器（即主服务器的备份服务器）。

        通过I/O线程和SQL线程的协作，从服务器可以将主服务器上的数据复制到本地，并保持与主服务器上的数据一致。

主库配置

开放端口/关闭防火墙

首先在服务器上均需要开放MySQL的端口，为了让别的服务器进行访问。这里有两种方法：

第一种：开放指定端口，并且重新加载防火墙

firewall-cmd --zone=public --add-port=3306/tcp -permanent
firewall-cmd -reload

第二种：关闭防火墙，并且禁止开机自启

systemctl stop firewalld
systemctl disable firewalld

修改配置文件

vim /etc/my.cnf

# 服务id 用来保证整个集群环境的唯一性 取值范围[1,2^32 - 1] 默认为1
server-id=1
# 是否可读：0可读可写 1只读
read-only=0
# 二进制文件的命名
log-bin=master-bin
# 二进制索引文件的命名
log-bin-index=master-bin.index
# 忽略的数据 表示不需要同步的数据库
# binlog-ignore-db=mysql
# 指定同步的数据库
# binlog-do-db=test

重启MySQL

systemctl restart mysqld

创建账号

必须先登录MySQL，在里面执行代码

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

查询binlog坐标

这也是在MySQL服务中的代码

SHOW MASTER STATUS;

表中会有五个字段

File：从哪个日志文件开始推送日志文件

Position：从哪个位置开始推送日志

Binlog_Do_DB：指定需要同步的数据库

Binlog_Ignore_DB：指定不需要同步的数据库

Executed_Gtid_set

从库配置

修改配置文件

vim /etc/my.cnf

server-id=2
read-only=1
relay-bin=slave-relay-bin
relay-bin-index=slave-bin.index

重启MySQL

systemctl restart mysqld

设置主库

登录MySQL，若MySQL版本大于等于8.0.23则执行

CHANGE REPLICATION SOURCE TO SOURCE_HOST='主库IP',SOURCE_USER='主库用户名',SOURCE_PASSWORD="主库密码",SOURCE_LOG_FILE='binlog文件名',SOURCE_LOG_POS='binlog文件位置'

MySQL版本小于8.0.23执行

CHANGE MASTER TO MASTER_HOST='主库IP',MASTER_USER='主库用户名',MASTER_PASSWORD="主库密码",MASTER_LOG_FILE='binlog文件名',MASTER_LOG_POS='binlog文件位置'

注意：关于binlog的文件名与文件位置在主库配置的最后一步中可以查看：

SHOW MASTER STATUS;

执行同步

MySQL版本8.0.22之前

start slave;

MySQL版本8.0.22之后

start replica;

查看主从同步状态

 MySQL版本8.0.22之前

show slave status;

MySQL版本8.0.22之后

show replica status;