MySQL主從復(fù)制的兩種方式詳解

更新時間：2025年09月10日 10:43:42 作者：ZZDICT

主從復(fù)制是指將主數(shù)據(jù)庫的?DDL?和?DML?操作通過二進(jìn)制日志傳到從庫服務(wù)器中,然后在從庫上對這?些日志重新執(zhí)行（也叫重做）,從而使得從庫和主庫的數(shù)據(jù)保持同步,本文給大家詳細(xì)介紹了MySQL主從復(fù)制的兩種方式,需要的朋友可以參考下

概述

主從復(fù)制是指將主數(shù)據(jù)庫的 DDL 和 DML 操作通過二進(jìn)制日志傳到從庫服務(wù)器中，然后在從庫上對這些日志重新執(zhí)行（也叫重做），從而使得從庫和主庫的數(shù)據(jù)保持同步。

MySQL支持一臺主庫同時向多臺從庫進(jìn)行復(fù)制，從庫同時也可以作為其他從服務(wù)器的主庫，實現(xiàn)鏈狀復(fù)制。

MySQL 復(fù)制的優(yōu)點(diǎn)主要包含以下三個方面：

1. 主庫出現(xiàn)問題，可以快速切換到從庫提供服務(wù)。

2. 實現(xiàn)讀寫分離，降低主庫的訪問壓力。

3. 可以在從庫中執(zhí)行備份，以避免備份期間影響主庫服務(wù)。

主從復(fù)制原理

MySQL主從復(fù)制的核心就是二進(jìn)制日志，具體的過程如下：

從上圖來看，復(fù)制分成三步：

1. Master 主庫在事務(wù)提交時，會把數(shù)據(jù)變更記錄在二進(jìn)制日志文件 Binlog 中。

2. 從庫讀取主庫的二進(jìn)制日志文件 Binlog ，寫入到從庫的中繼日志 Relay Log 。

3. slave重做中繼日志中的事件，將改變反映它自己的數(shù)據(jù)。

環(huán)境準(zhǔn)備

準(zhǔn)備兩臺虛擬機(jī)，進(jìn)行更換阿里鏡像源，關(guān)閉防火墻和selinux，進(jìn)行時間同步，固定IP等基礎(chǔ)操作

主機(jī)名	角色	IP	系統(tǒng)	版本
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 "=====系統(tǒng)環(huán)境初始化腳本====="
sleep 3
echo "——>>> 關(guān)閉防火墻與SELinux <<<——"
sleep 3
systemctl stop firewalld
systemctl disable firewalld &> /dev/null
setenforce 0
sed -i '/SELINUX/{s/enforcing/disabled/}' /etc/selinux/config
 
echo "——>>> 創(chuàng)建阿里倉庫 <<<——"
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 "——>>> 設(shè)置時區(qū)并同步時間 <<<——"
sleep 3
timedatectl set-timezone Asia/Shanghai
yum -y install chrony
systemctl start chronyd
systemctl enable chronyd
reboot

給兩臺主機(jī)下載MySQL，并給root用戶設(shè)置了簡單密碼為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'

基于二進(jìn)制日志的復(fù)制

這是最常見的主從復(fù)制模式。在這種模式下，主服務(wù)器將所有更改（如插入、更新、刪除）記錄到二進(jìn)制日志（binary log）中。從服務(wù)器讀取這些日志并將相同的更改應(yīng)用到自己的數(shù)據(jù)上。

配置master

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

# 追加如下配置 

server-id = 1            # 服務(wù)器唯一標(biāo)識，每個服務(wù)器在復(fù)制環(huán)境中應(yīng)有唯一的ID，用于標(biāo)識不同的復(fù)制實例

log-bin = mysql-bin      # 啟用二進(jìn)制日志，指定二進(jìn)制日志文件的基名，MySQL會在此基名后添加數(shù)字和擴(kuò)展名來創(chuàng)建日志文件

binlog-format = ROW      # 設(shè)置二進(jìn)制日志格式為ROW，記錄每一行數(shù)據(jù)的變化，有助于減少數(shù)據(jù)不一致的風(fēng)險，也便于從庫的并行復(fù)制

expire_logs_days = 10    # 二進(jìn)制日志保留時間，設(shè)置二進(jìn)制日志文件的自動清理周期，這里是10天，根據(jù)數(shù)據(jù)保留和備份策略調(diào)整

read-only=0             # 設(shè)置服務(wù)器是否為只讀模式，0表示關(guān)閉只讀模式，允許所有用戶進(jìn)行寫操作，包括非超級用戶

# binlog-ignore-db =      # 指定不需要復(fù)制的數(shù)據(jù)庫，即不記錄這些數(shù)據(jù)庫的變更到二進(jìn)制日志中，多個數(shù)據(jù)庫用逗號分隔

# binlog_do_db =          # 指定需要復(fù)制的數(shù)據(jù)庫，即只記錄這些數(shù)據(jù)庫的變更到二進(jìn)制日志中，多個數(shù)據(jù)庫用逗號分隔
# 注意：binlog-ignore-db和binlog_do_db不要同時使用，以免產(chǎn)生沖突

重新啟動MySQL服務(wù)

systemctl restart mysqld

登錄MySQL，創(chuàng)建遠(yuǎn)程連接的賬號，并授予主從復(fù)制權(quán)限

[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結(jié)束
Your MySQL connection id is 8  # MySQL連接ID為8
Server version: 8.0.39 MySQL Community Server - GPL  # 服務(wù)器版本信息

Copyright (c) 2000, 2024, Oracle and/or its affiliates.  # 版權(quán)信息

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.  # Oracle是Oracle公司及其附屬公司的注冊商標(biāo)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.  # 輸入'help;'或'\h'獲取幫助，輸入'\c'清除當(dāng)前輸入的命令

mysql> CREATE USER 'itit'@'%' IDENTIFIED WITH mysql_native_password BY '123456';  # 創(chuàng)建用戶'itit'，允許從任何主機(jī)訪問，并設(shè)置密碼為'123456'
Query OK, 0 rows affected (0.01 sec)  # 命令執(zhí)行成功，沒有行受到影響

mysql> GRANT REPLICATION SLAVE ON *.* TO 'itit'@'%';  # 授予用戶'itit'從任何主機(jī)進(jìn)行復(fù)制的權(quán)限
Query OK, 0 rows affected (0.00 sec)  # 命令執(zhí)行成功，沒有行受到影響

mysql> show master status ;  # 顯示主服務(wù)器狀態(tài)，包括二進(jìn)制日志文件名和位置
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      660 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)  # 輸出結(jié)果，顯示當(dāng)前使用的二進(jìn)制日志文件和位置

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

查詢的字段解釋：

File：當(dāng)前正在寫入的二進(jìn)制日志文件名。對于新的復(fù)制關(guān)系，從庫需要從這個文件開始復(fù)制。
Position：在當(dāng)前二進(jìn)制日志文件中的位置（偏移量）。從庫將從這個位置開始復(fù)制。
Binlog_Do_DB：指定需要復(fù)制的數(shù)據(jù)庫。如果配置了binlog_do_db參數(shù)，這里會顯示相應(yīng)的數(shù)據(jù)庫。如果沒有設(shè)置，則該字段為空。
Binlog_Ignore_DB：指定不需要復(fù)制的數(shù)據(jù)庫。如果配置了binlog_ignore_db參數(shù)，這里會顯示相應(yīng)的數(shù)據(jù)庫。如果沒有設(shè)置，則該字段為空。
Executed_Gtid_Set：已經(jīng)執(zhí)行的GTID集合。在GTID復(fù)制模式下，主庫會記錄每個事務(wù)的GTID。這個字段顯示了所有已經(jīng)執(zhí)行的事務(wù)的GTID集合。如果服務(wù)器沒有啟用GTID模式，則此字段為空。

配置slave

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

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

# 從庫的唯一標(biāo)識，與主庫和其他從庫不同
server-id = 2

# 啟用二進(jìn)制日志，以便從庫可以作為其他從庫的主庫
log-bin = mysql-bin

# 設(shè)置二進(jìn)制日志格式為ROW，有助于減少數(shù)據(jù)不一致的風(fēng)險
binlog-format = ROW

# 設(shè)置從庫為只讀模式，防止在從庫上直接寫入數(shù)據(jù)導(dǎo)致的數(shù)據(jù)不一致
read-only = 1

# 設(shè)置服務(wù)器為只讀模式，超級管理員也無法執(zhí)行寫操作（可選操作）
#super-read-only=1

# 注：從庫開啟二進(jìn)制文件是可以記錄從庫的數(shù)據(jù)操作的記錄，也是可選操作，
#如果不開，從庫就不會記錄自己這個庫的操作。

重新啟動MySQL服務(wù)

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 的主從復(fù)制相關(guān)的語法經(jīng)歷了一些變化，特別是 CHANGE MASTER TO 語句的重命名為 CHANGE REPLICATION SOURCE TO。這種變化是為了統(tǒng)一復(fù)制源的概念并改善語法的一致性。下面是你提供的命令在 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

啟動復(fù)制的命令

在 MySQL 8.0.23 及之后版本中

啟動復(fù)制進(jìn)程：使用 START REPLICA 命令來啟動復(fù)制進(jìn)程：

START REPLICA;

停止復(fù)制進(jìn)程：使用 STOP REPLICA 命令來停止復(fù)制進(jìn)程：

STOP REPLICA;

查看復(fù)制狀態(tài)：使用 SHOW REPLICA STATUS 命令查看復(fù)制狀態(tài)：

SHOW REPLICA STATUS\G;

查看復(fù)制進(jìn)程的線程狀態(tài)：使用 SHOW PROCESSLIST 來查看正在運(yùn)行的線程，包括復(fù)制線程：

SHOW PROCESSLIST;

在 MySQL 8.0.22 及之前版本中

啟動復(fù)制進(jìn)程：使用 START SLAVE 命令來啟動復(fù)制進(jìn)程：

START SLAVE;

停止復(fù)制進(jìn)程：使用 STOP SLAVE 命令來停止復(fù)制進(jìn)程：

STOP SLAVE;

查看復(fù)制狀態(tài)：使用 SHOW SLAVE STATUS 命令查看復(fù)制狀態(tài)：

SHOW SLAVE STATUS\G;

查看復(fù)制進(jìn)程的線程狀態(tài)：使用 SHOW PROCESSLIST 來查看正在運(yùn)行的線程，包括復(fù)制線程：

SHOW PROCESSLIST;

測試

當(dāng)前主庫的內(nèi)容

[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)

當(dāng)前從庫的內(nèi)容

[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)

在主庫執(zhí)行一系列sql操作

-- 創(chuàng)建數(shù)據(jù)庫，如果數(shù)據(jù)庫不存在
CREATE DATABASE IF NOT EXISTS db01;

-- 切換到剛剛創(chuàng)建的數(shù)據(jù)庫
USE db01;

-- 創(chuàng)建用戶表
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;

-- 插入數(shù)據(jù)
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方式進(jìn)行主從同步

GTID（全局事務(wù)標(biāo)識符）主從復(fù)制是 MySQL 提供的一種復(fù)制方式，它增強(qiáng)了復(fù)制的管理和恢復(fù)能力。GTID 復(fù)制使用全局唯一的事務(wù)標(biāo)識符來追蹤和管理事務(wù)的復(fù)制，從而簡化了主從服務(wù)器之間的數(shù)據(jù)同步和故障恢復(fù)。以下是關(guān)于 GTID 主從復(fù)制的詳細(xì)介紹，包括其工作原理、配置步驟和優(yōu)缺點(diǎn)。

工作原理

全局事務(wù)標(biāo)識符（GTID）：

GTID 是一個唯一標(biāo)識符，分配給每個事務(wù)。它由主服務(wù)器生成，并在二進(jìn)制日志中記錄。
格式通常是 UUID:事務(wù)ID，其中 UUID 是主服務(wù)器的唯一標(biāo)識，事務(wù)ID 是事務(wù)的序列號。

主服務(wù)器：

主服務(wù)器將每個事務(wù)記錄到二進(jìn)制日志中，并為每個事務(wù)分配一個 GTID。
主服務(wù)器的 GTID 集合表示已提交的事務(wù)的完整列表。

從服務(wù)器：

從服務(wù)器從主服務(wù)器獲取二進(jìn)制日志，并應(yīng)用其中的事務(wù)。
從服務(wù)器維護(hù)一個 GTID 集合，記錄已經(jīng)應(yīng)用的事務(wù)。

復(fù)制過程：

從服務(wù)器請求主服務(wù)器的 GTID 事務(wù)。
主服務(wù)器傳輸含 GTID 的二進(jìn)制日志到從服務(wù)器。
從服務(wù)器應(yīng)用這些事務(wù)，并更新其 GTID 集合。

自動故障恢復(fù)：

在故障恢復(fù)過程中，從服務(wù)器可以自動重新同步主服務(wù)器的事務(wù)，因為 GTID 提供了唯一的事務(wù)標(biāo)識，無需手動指定日志位置。

首先恢復(fù)原先的初始環(huán)境，進(jìn)行操作，避免上面做過的干擾。

配置master

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

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

server-id：唯一標(biāo)識符，用于區(qū)分主從服務(wù)器。
log-bin：啟用二進(jìn)制日志。
enforce-gtid-consistency：確保所有事務(wù)具有一致的 GTID。
gtid-mode：啟用 GTID。
binlog_format：指定二進(jìn)制日志格式為 ROW，這是 GTID 復(fù)制的要求。

重新啟動 MySQL 服務(wù)以應(yīng)用這些更改

sudo systemctl restart mysqld

創(chuàng)建復(fù)制用戶：

在主服務(wù)器上創(chuàng)建一個用于復(fù)制的用戶：

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

獲取主服務(wù)器狀態(tài)：

運(yùn)行以下命令來獲取主服務(wù)器的當(dāng)前狀態(tài)：

SHOW MASTER STATUS;

配置slave

修改從服務(wù)器配置文件：

編輯從服務(wù)器的 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：唯一標(biāo)識符，用于區(qū)分從服務(wù)器。
enforce-gtid-consistency：確保所有事務(wù)具有一致的 GTID。
gtid-mode：啟用 GTID。
log-bin：啟用二進(jìn)制日志。
relay-log：指定中繼日志文件。

重新啟動 MySQL 服務(wù)以應(yīng)用這些更改

sudo systemctl restart mysqld

配置復(fù)制：

在從服務(wù)器上運(yùn)行以下 SQL 命令來配置復(fù)制：

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

MASTER_HOST：主服務(wù)器的 IP 地址或主機(jī)名。
MASTER_USER 和 MASTER_PASSWORD：用于復(fù)制的用戶和密碼。
MASTER_AUTO_POSITION=1：啟用 GTID 自動定位。

啟動復(fù)制進(jìn)程：

START SLAVE;

檢查復(fù)制狀態(tài)：

使用以下命令來檢查復(fù)制是否正常運(yùn)行：

SHOW SLAVE STATUS\G

確保 Slave_IO_Running 和 Slave_SQL_Running 兩個字段的值都為 Yes，并且 Last_Error 字段為空，這表明從服務(wù)器正在正常復(fù)制主服務(wù)器上的數(shù)據(jù)。

測試

在主庫中執(zhí)行

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;

以上就是MySQL主從復(fù)制的兩種方式詳解的詳細(xì)內(nèi)容，更多關(guān)于MySQL主從復(fù)制的資料請關(guān)注腳本之家其它相關(guān)文章！

您可能感興趣的文章:

最新国产好看的视频,伊人天堂AV在线,国产Aaaaaa视频,蜜臀视频在线观看一区,人妻av色图,密臀久久久精品影片,青青视频免费观看毛片,久草在线观看视,国产三级精品色情在线

MySQL主從復(fù)制的兩種方式詳解

目錄

概述

主從復(fù)制原理

環(huán)境準(zhǔn)備

基于二進(jìn)制日志的復(fù)制

配置master

配置slave

啟動復(fù)制的命令

測試

Gtid方式進(jìn)行主從同步

工作原理

配置master

配置slave

測試

相關(guān)文章

最新評論

大家感興趣的內(nèi)容

最近更新的內(nèi)容

常用在線小工具