MySQL 主从复制的两种方式详解

目录

概述

主从复制原理

环境准备

基于二进制日志的复制

配置master

配置slave

启动复制的命令

测试

Gtid方式进行主从同步

工作原理

配置master

配置slave

测试 

---

概述

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

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

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

        1. 主库出现问题，可以快速切换到从库提供服务。

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

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

主从复制原理

MySQL主从复制的核心就是 二进制日志，具体的过程如下： 

从上图来看，复制分成三步：

        1. Master 主库在事务提交时，会把数据变更记录在二进制日志文件 Binlog 中。

        2. 从库读取主库的二进制日志文件 Binlog ，写入到从库的中继日志 Relay Log 。

        3. slave重做中继日志中的事件，将改变反映它自己的数据。

环境准备

准备两台虚拟机，进行更换阿里镜像源，关闭防火墙和selinux，进行时间同步，固定IP等基础操作

主机名	角色	IP	系统	版本
master	主库	192.168.226.100	Centos7-2009-mini	MySQL：8.0.39
slave	从库	192.168.226.101	Centos7-2009-mini	MySQL：8.0.39

 初始化脚本，其中固定IP可选操作。

#!/bin/bash
echo "=====系统环境初始化脚本====="
sleep 3
echo "——>>> 关闭防火墙与SELinux <<<——"
sleep 3
systemctl stop firewalld
systemctl disable firewalld &> /dev/null
setenforce 0
sed -i '/SELINUX/{s/enforcing/disabled/}' /etc/selinux/config
 
echo "——>>> 创建阿里仓库 <<<——"
sleep 3
rm -rf /etc/yum.repos.d/*
curl -o /etc/yum.repos.d/CentOS-Base.repo https://mirrors.aliyun.com/repo/Centos-7.repo 
yum -y install wget
wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-7.repo  
 
 
echo "——>>> 设置时区并同步时间 <<<——"
sleep 3
timedatectl set-timezone Asia/Shanghai
yum -y install chrony
systemctl start chronyd
systemctl enable chronyd
reboot

 给两台主机下载MySQL，并给root用户设置了简单密码为1234

sudo yum remove mysql-server -y && sudo yum autoremove -y
sudo yum remove *mysql* -y
sudo rm -rf /var/lib/mysql/ 
sudo rm -rf /etc/mysql/ 

yum install -y yum-utils > /dev/null
yum install -y https://dev.mysql.com/get/mysql80-community-release-el7-11.noarch.rpm > /dev/null
yum-config-manager --enable mysql80-community > /dev/null
yum-config-manager --disable mysql57-community > /dev/null
yum install -y mysql-server
systemctl start mysqld && systemctl enable mysqld
mysqladmin -p"`awk '/temporary password/{p=$NF}END{print p}' /var/log/mysqld.log`" password 'TianPFh@123'
mysql -p'TianPFh@123' -e "UNINSTALL COMPONENT 'file://component_validate_password'"
mysqladmin -p'TianPFh@123' password '1234'

基于二进制日志的复制

这是最常见的主从复制模式。在这种模式下，主服务器将所有更改（如插入、更新、删除）记录到二进制日志（binary log）中。从服务器读取这些日志并将相同的更改应用到自己的数据上。

配置master

修改配置文件 /etc/my.cnf，在配置文件末尾追加即可。

# 追加如下配置 

server-id = 1            # 服务器唯一标识，每个服务器在复制环境中应有唯一的ID，用于标识不同的复制实例

log-bin = mysql-bin      # 启用二进制日志，指定二进制日志文件的基名，MySQL会在此基名后添加数字和扩展名来创建日志文件

binlog-format = ROW      # 设置二进制日志格式为ROW，记录每一行数据的变化，有助于减少数据不一致的风险，也便于从库的并行复制

expire_logs_days = 10    # 二进制日志保留时间，设置二进制日志文件的自动清理周期，这里是10天，根据数据保留和备份策略调整

read-only=0             # 设置服务器是否为只读模式，0表示关闭只读模式，允许所有用户进行写操作，包括非超级用户

# binlog-ignore-db =      # 指定不需要复制的数据库，即不记录这些数据库的变更到二进制日志中，多个数据库用逗号分隔

# binlog_do_db =          # 指定需要复制的数据库，即只记录这些数据库的变更到二进制日志中，多个数据库用逗号分隔
# 注意：binlog-ignore-db和binlog_do_db不要同时使用，以免产生冲突

重新启动MySQL服务

systemctl restart mysqld

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

[root@master ~]# mysql -uroot -p1234  # 使用root用户登录MySQL，密码是1234
mysql: [Warning] Using a password on the command line interface can be insecure.  # 警告：在命令行界面使用密码可能不安全
Welcome to the MySQL monitor.  Commands end with ; or \g.  # 欢迎信息，命令以;或\g结束
Your MySQL connection id is 8  # MySQL连接ID为8
Server version: 8.0.39 MySQL Community Server - GPL  # 服务器版本信息

Copyright (c) 2000, 2024, Oracle and/or its affiliates.  # 版权信息

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.  # Oracle是Oracle公司及其附属公司的注册商标

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.  # 输入'help;'或'\h'获取帮助，输入'\c'清除当前输入的命令

mysql> CREATE USER 'itit'@'%' IDENTIFIED WITH mysql_native_password BY '123456';  # 创建用户'itit'，允许从任何主机访问，并设置密码为'123456'
Query OK, 0 rows affected (0.01 sec)  # 命令执行成功，没有行受到影响

mysql> GRANT REPLICATION SLAVE ON *.* TO 'itit'@'%';  # 授予用户'itit'从任何主机进行复制的权限
Query OK, 0 rows affected (0.00 sec)  # 命令执行成功，没有行受到影响

mysql> show master status ;  # 显示主服务器状态，包括二进制日志文件名和位置
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      660 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)  # 输出结果，显示当前使用的二进制日志文件和位置

mysql> exit  # 退出MySQL命令行
Bye  # MySQL命令行退出信息

查询的字段解释：

• File：当前正在写入的二进制日志文件名。对于新的复制关系，从库需要从这个文件开始复制。

• Position：在当前二进制日志文件中的位置（偏移量）。从库将从这个位置开始复制。

• Binlog_Do_DB：指定需要复制的数据库。如果配置了binlog_do_db参数，这里会显示相应的数据库。如果没有设置，则该字段为空。

• Binlog_Ignore_DB：指定不需要复制的数据库。如果配置了binlog_ignore_db参数，这里会显示相应的数据库。如果没有设置，则该字段为空。

• Executed_Gtid_Set：已经执行的GTID集合。在GTID复制模式下，主库会记录每个事务的GTID。这个字段显示了所有已经执行的事务的GTID集合。如果服务器没有启用GTID模式，则此字段为空。

配置slave

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

# 在文件中追加下述配置即可

# 从库的唯一标识，与主库和其他从库不同
server-id = 2

# 启用二进制日志，以便从库可以作为其他从库的主库
log-bin = mysql-bin

# 设置二进制日志格式为ROW，有助于减少数据不一致的风险
binlog-format = ROW

# 设置从库为只读模式，防止在从库上直接写入数据导致的数据不一致
read-only = 1

# 设置服务器为只读模式，超级管理员也无法执行写操作（可选操作）
#super-read-only=1

# 注：从库开启二进制文件是可以记录从库的数据操作的记录，也是可选操作，
#如果不开，从库就不会记录自己这个库的操作。

重新启动MySQL服务

systemctl restart mysqld

登录从库的MySQL，配置从库以连接到主库

CHANGE REPLICATION SOURCE TO
    SOURCE_HOST='192.168.226.100',
    SOURCE_USER='itit',
    SOURCE_PASSWORD='123456',
    SOURCE_LOG_FILE='mysql-bin.000001',
    SOURCE_LOG_POS=660;

在 MySQL 8.0.23之前的版本中，MySQL 的主从复制相关的语法经历了一些变化，特别是 CHANGE MASTER TO 语句的重命名为 CHANGE REPLICATION SOURCE TO。这种变化是为了统一复制源的概念并改善语法的一致性。下面是你提供的命令在 MySQL 8.0.23 之前版本中的正确语法：

CHANGE MASTER TO 
    MASTER_HOST='192.168.226.100',
    MASTER_USER='itit', 
    MASTER_PASSWORD='123456', 
    MASTER_LOG_FILE='mysql-bin.000001', 
    MASTER_LOG_POS=660;

• MySQL 8.0.23 及更高版本：使用 CHANGE REPLICATION SOURCE TO。
• MySQL 8.0.22 及更早版本：使用 CHANGE MASTER TO。

MySQL 8.0.23 及更高版本	含义	MySQL 8.0.22 及更早版本
SOURCE_HOST	主库IP地址	MASTER_HOST
SOURCE_USER	连接主库的用户名	MASTER_USER
SOURCE_PASSWORD	连接主库的密码	MASTER_PASSWORD
SOURCE_LOG_FILE	连接主库的密码	MASTER_LOG_FILE
SOURCE_LOG_POS	binlog日志文件位置	MASTER_LOG_POS

启动复制的命令

在 MySQL 8.0.23 及之后版本中

1. 启动复制进程： 使用 START REPLICA 命令来启动复制进程：

   START REPLICA;

2. 停止复制进程： 使用 STOP REPLICA 命令来停止复制进程：

   STOP REPLICA;

3. 查看复制状态： 使用 SHOW REPLICA STATUS 命令查看复制状态：

   SHOW REPLICA STATUS\G;

4. 查看复制进程的线程状态： 使用 SHOW PROCESSLIST 来查看正在运行的线程，包括复制线程：

   SHOW PROCESSLIST;

在 MySQL 8.0.22 及之前版本中

1. 启动复制进程： 使用 START SLAVE 命令来启动复制进程：

   START SLAVE;

2. 停止复制进程： 使用 STOP SLAVE 命令来停止复制进程：

   STOP SLAVE;

3. 查看复制状态： 使用 SHOW SLAVE STATUS 命令查看复制状态：

   SHOW SLAVE STATUS\G;

4. 查看复制进程的线程状态： 使用 SHOW PROCESSLIST 来查看正在运行的线程，包括复制线程：

   SHOW PROCESSLIST;

测试

当前主库的内容

[root@master ~]# mysql -p1234
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.39 MySQL Community Server - GPL

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.04 sec)

当前从库的内容

[root@slave ~]# mysql -p1234
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 8.0.39 MySQL Community Server - GPL

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.02 sec)

在主库执行一系列sql操作

-- 创建数据库，如果数据库不存在
CREATE DATABASE IF NOT EXISTS db01;

-- 切换到刚刚创建的数据库
USE db01;

-- 创建用户表
CREATE TABLE tb_user (
    id INT(11) AUTO_INCREMENT PRIMARY KEY NOT NULL,
    name VARCHAR(50) NOT NULL,
    sex VARCHAR(1)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 插入数据
INSERT INTO tb_user (name, sex) VALUES
('Tom', '1'),
('Trigger', '0'),
('Dawn', '1');

来到从库查看

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| db01               |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use db01;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------+
| Tables_in_db01 |
+----------------+
| tb_user        |
+----------------+
1 row in set (0.00 sec)

mysql> select * from tb_user;
+----+---------+------+
| id | name    | sex  |
+----+---------+------+
|  1 | Tom     | 1    |
|  2 | Trigger | 0    |
|  3 | Dawn    | 1    |
+----+---------+------+
3 rows in set (0.00 sec)

Gtid方式进行主从同步

GTID（全局事务标识符）主从复制是 MySQL 提供的一种复制方式，它增强了复制的管理和恢复能力。GTID 复制使用全局唯一的事务标识符来追踪和管理事务的复制，从而简化了主从服务器之间的数据同步和故障恢复。以下是关于 GTID 主从复制的详细介绍，包括其工作原理、配置步骤和优缺点。

工作原理

全局事务标识符（GTID）：

• GTID 是一个唯一标识符，分配给每个事务。它由主服务器生成，并在二进制日志中记录。
• 格式通常是 UUID:事务ID，其中 UUID 是主服务器的唯一标识，事务ID 是事务的序列号。

主服务器：

• 主服务器将每个事务记录到二进制日志中，并为每个事务分配一个 GTID。
• 主服务器的 GTID 集合表示已提交的事务的完整列表。

从服务器：

• 从服务器从主服务器获取二进制日志，并应用其中的事务。
• 从服务器维护一个 GTID 集合，记录已经应用的事务。

复制过程：

• 从服务器请求主服务器的 GTID 事务。
• 主服务器传输含 GTID 的二进制日志到从服务器。
• 从服务器应用这些事务，并更新其 GTID 集合。

自动故障恢复：

• 在故障恢复过程中，从服务器可以自动重新同步主服务器的事务，因为 GTID 提供了唯一的事务标识，无需手动指定日志位置。

首先恢复原先的初始环境，进行操作，避免上面做过的干扰。

配置master

修改配置文件 /etc/my.cnf，在配置文件末尾追加即可。

server-id = 1
log-bin = mysql-bin
enforce-gtid-consistency = ON
gtid-mode = ON
binlog_format = ROW

• server-id：唯一标识符，用于区分主从服务器。
• log-bin：启用二进制日志。
• enforce-gtid-consistency：确保所有事务具有一致的 GTID。
• gtid-mode：启用 GTID。
• binlog_format：指定二进制日志格式为 ROW，这是 GTID 复制的要求。

重新启动 MySQL 服务以应用这些更改

sudo systemctl restart mysqld

创建复制用户：

在主服务器上创建一个用于复制的用户：

CREATE USER 'replica'@'%' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE ON *.* TO 'replica'@'%';
ALTER USER 'replica'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
FLUSH PRIVILEGES;

获取主服务器状态：

运行以下命令来获取主服务器的当前状态：

SHOW MASTER STATUS;

配置slave

修改从服务器配置文件：

编辑从服务器的 MySQL 配置文件 my.cnf。

在 [mysqld] 部分添加以下配置：

server-id = 2
enforce-gtid-consistency = ON
gtid-mode = ON
log-bin = mysql-bin
relay-log = mysql-relay-bin

• server-id：唯一标识符，用于区分从服务器。
• enforce-gtid-consistency：确保所有事务具有一致的 GTID。
• gtid-mode：启用 GTID。
• log-bin：启用二进制日志。
• relay-log：指定中继日志文件。

 重新启动 MySQL 服务以应用这些更改

sudo systemctl restart mysqld

配置复制：

在从服务器上运行以下 SQL 命令来配置复制：

CHANGE MASTER TO
  MASTER_HOST='192.168.226.100',
  MASTER_USER='replica',
  MASTER_PASSWORD='123456',
  MASTER_AUTO_POSITION=1;

• MASTER_HOST：主服务器的 IP 地址或主机名。
• MASTER_USER 和 MASTER_PASSWORD：用于复制的用户和密码。
• MASTER_AUTO_POSITION=1：启用 GTID 自动定位。

启动复制进程：

START SLAVE;

检查复制状态：

使用以下命令来检查复制是否正常运行：

SHOW SLAVE STATUS\G

确保 Slave_IO_Running 和 Slave_SQL_Running 两个字段的值都为 Yes，并且 Last_Error 字段为空，这表明从服务器正在正常复制主服务器上的数据。

测试 

在主库中执行

CREATE DATABASE test_db;
USE test_db;
CREATE TABLE test_table (id INT PRIMARY KEY, value VARCHAR(255));
INSERT INTO test_table (id, value) VALUES (1, 'Test Value 1');
INSERT INTO test_table (id, value) VALUES (2, 'Test Value 2');
INSERT INTO test_table (id, value) VALUES (3, 'Test Value 3');
COMMIT;
SHOW MASTER STATUS;

在从库中查看

SHOW DATABASES;
USE test_db;
SELECT * FROM test_table;
SHOW SLAVE STATUS\G;