mysql主从架构搭建与维护详解

dougsu

墨衍会员 · AI 创作全网分发码龄4年

关注

原创最新推荐文章于 2026-04-17 10:58:26 发布 836 阅读 AI 写同款· GEO 优化 ›

Mysql部署全攻略——主从及主主同步搭建详细介绍如何实现mysql的主从同步，及简单介绍主主同步如何实现阅读详情

引言

本文是关于MySQL主从复制配置的文章。会深入了解如何搭建MySQL主从配置，这是一个强大的工具，可以为数据库操作提供更高的效率和安全性。从基本的环境准备，到主从服务器的配置，再到主从复制的优化与扩展，本文将全面覆盖。

1. 主从复制简介

1.1 什么是MySQL主从复制

MySQL主从复制是一种异步复制技术，用于将数据从一个MySQL数据库（主数据库）复制到一个或多个MySQL数据库（从数据库）。这种机制使得从数据库达到与主数据库相同的数据状态。主要的工作流程是，主数据库将数据更改事件写入二进制日志（binlog），然后从数据库读取并执行这些日志中的事件，从而保持与主数据库的同步。

1.2 主从配置的优点和用途

MySQL主从复制配置的优点和用途非常多元：

数据备份：通过主从复制，数据被复制到一个或多个从数据库中，提供了一种灾备方案，防止数据丢失。
读取性能提升：对于读取密集型应用，可以通过读取从数据库来分散读取负载，从而提升系统性能。
数据分布：可以将数据复制到地理位置分布的多个数据库中，为用户提供更快的本地访问服务。
实时分析和报告：从数据库可以用于复杂查询和报表生成，这样不会影响主数据库的性能。

2. MySQL主从复制原理

MySQL主从复制基于主数据库的二进制日志（binlog）。主数据库在二进制日志中记录所有的数据更改事件。从数据库有两个线程用于复制：IO线程和SQL线程。这些线程读取并执行二进制日志中的事件，使得从数据库达到与主数据库相同的数据状态。

2.1 工作流程

主从复制的工作流程主要包含以下步骤：

主数据库执行数据更改操作，如INSERT、UPDATE或DELETE。
主数据库将这些更改记录在二进制日志中。
从数据库的IO线程连接到主数据库，并读取二进制日志中的新事件。
从数据库的IO线程将这些事件写入到从数据库的中继日志。
从数据库的SQL线程读取中继日志，并执行其中的事件，使数据与主数据库同步。

2.2 数据同步方式

MySQL主从复制采用异步方式进行数据同步，也就是说，主数据库在二进制日志中记录事件后，不会等待从数据库执行事件，而是立即返回。从数据库自行负责获取并执行新事件。这种方式在大多数情况下可以提高性能，但在网络中断或从数据库故障的情况下可能会导致数据延迟或数据丢失。
还有一种半同步复制方式，在这种方式中，主数据库在执行更改后会等待至少一个从数据库收到更改事件后才返回。这种方式可以提高数据的可靠性，但可能会影响主数据库的性能。

3. 环境准备

在进行MySQL主从复制的配置之前，我们需要准备相应的环境。这包括满足硬件和软件的需求，选择合适的MySQL版本，以及正确地安装MySQL服务器。

3.1 硬件和软件需求

硬件需求：首先，你需要至少两台计算机（测试的时候可以使用创建两个虚拟机进行演示），一台作为主服务器，另一台或多台作为从服务器。这些计算机需要有足够的处理器速度、内存大小和硬盘空间，以支持MySQL服务器的运行和数据存储。
软件需求：每台计算机上都需要安装操作系统，如Linux、Windows或macOS。操作系统应该安装和更新到最新的稳定版本。此外，还需要一些基本的软件工具,例如mysql自带的客户端或者使用Navicat。

3.2 MySQL版本选择

选择合适的MySQL版本也是很重要的。不同版本的MySQL可能支持不同的功能，有不同的性能特性和问题。通常，我们推荐使用最新的稳定版本，因为这些版本包含了最新的功能和最近的安全修复。然而，在某些情况下，你可能需要使用特定版本的MySQL，以满足特定的需求或兼容性。本文将使用截至目前最新的版本（mysql8.0.33）进行搭建。

3.3 安装MySQL服务

安装2台装有mysql服务的服务器，mysql服务的安装请参考该教程：https://blog.csdn.net/dougsu/article/details/130816827

4.配置MySQL主服务器

在准备好硬件和软件环境后，我们需要配置MySQL的主服务器。这包括修改配置文件、创建复制账户，以及启动和测试主服务器。（下面我们会使用Navicat工具来充当客户端进行查询和操作）

4.1 配置文件修改

首先，我们需要修改MySQL的配置文件my.cnf（在Windows系统上是my.ini）。在**[mysqld]**段下，我们需要添加或修改以下配置项：

server-id=1
#开启binlog
log_bin=master-bin
log_bin-index=master-bin.index
binlog-format=MIXED

server-id：为每个MySQL服务器设置一个唯一的整数ID。
log-bin：启用二进制日志，并指定日志文件的名称。
log_bin-index：二进制日志文件索引文件的名称
binlog-format：指定二进制日志的格式，可以是STATEMENT、ROW或MIXED。

二进制日志的格式补充

STATEMENT：MySQL 会将每个执行的 SQL 语句记录到二进制日志中，二进制日志只包含执行的 SQL 语句，而不会记录具体的行级变更，适用于大多数情况，但在某些情况下可能会导致主从同步的一致性问题
ROW：MySQL 会将每个修改的行的变更信息记录到二进制日志中，二进制日志将包含实际的行级变更信息，而不仅仅是 SQL 语句，可以确保主从同步的精确一致性，但会产生更大的二进制日志文件
MIXED：MySQL 可以根据具体情况自动选择记录 SQL 语句或行级变更信息，对于大多数情况，MySQL 会使用 STATEMENT 格式记录 SQL 语句，但对于某些无法使用 SQL 语句准确重放的情况，MySQL 会使用 ROW 格式记录行级变更信息，所以这种格式结合了 STATEMENT 和 ROW 的优点，可以在不同情况下选择最合适的记录方式。

4.2 创建复制账户

在实际生产环境中，不建议直接使用root用户，而是创建一个拥有全部权限的用户来负责主从同步。使用以下SQL命令来创建复制账户reUser,密码是abc123

CREATE USER 'reUser'@'%' IDENTIFIED BY 'abc123';
GRANT REPLICATION SLAVE ON *.* TO 'reUser'@'%';
flush privileges;

创建好用户后可以使用下面的指令查询配置的权限，查询应该应包含权限：REPLICATION SLAVE

SHOW GRANTS FOR 'reUser'@'%';

注意：在搭建测试可以直接是用root账号，省略创建用户

4.3 启动和测试主服务器

修改配置后重启mysql服务
客户端执行SHOW VARIABLES LIKE 'server_id';,如果配置正常会显示
客户端执行show master status;,如果配置正常会显示

字段解释：

File：表示当前正在写入的二进制日志文件的名称。
Position：表示当前正在写入的二进制日志文件中的位置（字节偏移量）。
Binlog_Do_DB：表示为主服务器启用二进制日志记录的数据库列表，只记录指定的数据库操作。
Binlog_Ignore_DB：表示为主服务器启用二进制日志记录时要忽略的数据库列表，不记录指定的数据库操作。
Executed_Gtid_Set：表示已经在主服务器上执行的 GTID（Global Transaction Identifier）集合。GTID 是用于跟踪分布式事务的标识符。

注意：Binlog_Do_DB、Binlog_Ignore_DB 和 Executed_Gtid_Set 字段可能在某些情况下为空，具体取决于您的配置和使用情况。

5. 配置MySQL从服务器

配置MySQL从服务器的过程与配置主服务器相似，包括修改配置文件和启动测试服务器。

5.1 配置文件修改

需要修改MySQL的配置文件my.cnf（在Windows系统上是my.ini）。在**[mysqld]**段下，我们需要添加或修改以下配置项：

server-id=2
#打开MySQL中继日志
relay-log=slave-relay-bin
relay-log-index=slave-relay-bin.index
#打开从服务二进制日志
log-bin=mysql-bin
#使得更新的数据写进二进制日志中
log-slave-updates=1

server-id：为每个MySQL服务器设置一个唯一的整数ID
relay-log：指定了从服务器（Slave）中用于存储中继日志的文件名前缀
relay-log-index：指定了从服务器（Slave）中用于存储中继日志索引的文件名
log-bin：启用二进制日志，并指定日志文件的名称。
binlog-format：指定了从服务器是否将复制事件写入其自己的二进制日志，当设置为 1 时，从服务器会将接收到的主服务器的二进制日志事件写入自己的二进制日志中，即在中继日志和二进制日志中都会记录复制事件，该选项通常在主从服务器之间使用基于链式复制的配置中，用于实现级联复制和多级复制

5.2 启动和测试从服务器

修改配置后重启mysql服务
客户端执行SHOW VARIABLES LIKE 'server_id';,如果配置正常会显示 2

6. 建立主从连接

6.1 从服务器连接主服务器

在完成主服务器和从服务器的配置后，我们需要在从服务器上创建一个连接，指向主服务器，并启动复制进程。这需要使用MySQL的CHANGE MASTER TO命令，并提供主服务器的地址、复制账户的用户名和密码，以及二进制日志文件和位置，所以我的配置如下

CHANGE MASTER TO
MASTER_HOST='192.168.3.51',
MASTER_PORT=3306,
MASTER_USER='root',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='master-bin.000001',
MASTER_LOG_POS=157,
GET_MASTER_PUBLIC_KEY=1;

master_host：是主服务器的地址
replication_user和replication_password：是在主服务器上创建的复制账户的用户名和密码。
master_log_file和master_log_pos：是主服务器二进制日志的名称和位置，可以通过在主服务器上执行SHOW MASTER STATUS命令获取(参控前面的4.3章节)。

执行结果如下：
在这里插入图片描述

6.2 启动复制进程

在完成上述步骤后，复制进程尚未开始。为了启动复制进程，你需要在从服务器上执行以下命令：

START SLAVE;

在这里插入图片描述
启动复制后，从服务器会开始读取主服务器的二进制日志，并在本地执行这些日志中的事件，从而保持与主服务器的数据同步。
可以通过SHOW SLAVE STATUS命令查看复制的状态。

如果复制进程正在运行，Slave_IO_Running和Slave_SQL_Running状态都应显示为“YES”。
如果已经同步完成或者不需要同步，Master_Log_File和Read_Master_Log_Pos和4.3章节查询的结果相同。

查询复制信息也可以使用自带的客户端通过show slave status \G;访问，结果：
在这里插入图片描述

注意：如果是通过克隆已经安装好mysql的虚拟机，需要修改mysql根目录下 data/auto.cnf 文件里server-uuid的值，两个服务的值必须不一样，否则会报下面的错误

Fatal error: The replica I/O thread stops because source and replica have equal MySQL server UUIDs; these UUIDs must be different for replication to work.

7. 验证主从复制的工作

在配置好MySQL主从复制并启动复制进程后，我们需要验证复制是否正常工作。验证主从复制的常用方法是在主服务器上插入数据，然后在从服务器上检查是否收到了这些数据。

7.1 插入数据到主服务器进行验证

在主服务器上，我们可以创建一个新的数据库并插入一些数据。例如：

CREATE DATABASE mydb;
USE mydb;
CREATE TABLE user (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50));
INSERT INTO user(name) VALUES ('tom'), ('jerry'), ('dave');

主从查询

SELECT * FROM `user`

在这里插入图片描述
2. 从库查询

SELECT * FROM `user`

在这里插入图片描述
发现数据已经同步，在从库上查询最新的状态信息：
发现同步的偏移量Read_Master_Log_Pos: 1097已经修改

7.2 错误检查和问题解决

在配置和验证MySQL主从复制的过程中，可能会遇到一些问题。最常见的问题是从服务器无法连接到主服务器，或者从服务器无法读取或执行主服务器的二进制日志。

当遇到问题时，首先应该检查MySQL的错误日志，查看是否有任何相关的错误消息。此外，可以使用SHOW SLAVE STATUS命令查看从服务器的复制状态。这个命令的结果中包含了许多有用的信息，如主服务器的地址、复制账户的用户名、复制的错误号和错误消息等。

根据错误消息，可以确定问题的原因，并采取相应的解决措施。常见的解决措施包括：检查网络连接、确认账户的用户名和密码正确、确认主服务器的二进制日志可用等。

验证主从复制的工作是配置MySQL主从复制的一个重要步骤，也是保持主从复制正常运行的关键。只有当复制在正常工作时，才能确保数据的一致性和可用性。

8. 主从复制的维护和优化

在主从复制运行过程中，可能会出现一些问题，这些问题需要特定的处理和优化方案。

8.1 网络问题处理

网络问题可能是主从复制中最常见的故障。一旦网络连接不稳定，会直接影响到数据的同步。处理网络问题需要从多个角度出发，包括检查网络连接，网络设备状态，网络防火墙规则，以及MySQL服务器的监听端口等。在复杂的网络环境下，可能需要网络管理员的协助进行网络链路的调试和优化。

8.2 复制延迟问题

复制延迟，也就是从库同步主库数据的滞后，是影响主从复制效果的另一个重要因素。如果处理能力不足，或者网络带宽供不应求，都可能导致复制延迟。处理复制延迟可以从优化数据库操作、增强从库的硬件性能、优化网络带宽等方面进行。在某些情况下，可以考虑引入中间件，利用其数据分发能力减轻从库的压力。

8.3 主从切换

主从切换是高可用方案的重要环节，即在主库出现故障时，快速将从库提升为新的主库，保证服务的连续性。主从切换的过程需要事先进行充分的规划和测试，包括决定切换的触发条件、切换的具体步骤、业务在切换过程中的处理等。在进行主从切换时，还需要注意数据的一致性问题，避免在切换过程中出现数据丢失。

主从复制的维护和优化是保障数据一致性和服务高可用的重要手段，这需要数据库管理员具备扎实的专业知识和丰富的实战经验。

9. 高级主题和扩展

随着业务的发展，可能会遇到更复杂的需求，如多源复制、主主复制、自动切换和负载均衡等。

9.1 多源复制

在MySQL中，一个从服务器可以从多个主服务器复制数据，这被称为多源复制。多源复制可以提高数据可用性和读取性能。配置多源复制需要在从服务器上为每个主服务器创建一个复制通道，并为每个通道指定主服务器的详细信息。

9.2 主主复制

主主复制，也称为双向复制，是指两个服务器都是对方的主服务器和从服务器。主主复制可以提高数据的冗余性和可用性，但同时也增加了复制冲突的风险。为了避免冲突，需要我们仔细设计数据库的写入模式，例如，每个服务器只写入特定的数据。

9.3 自动切换和负载均衡

自动切换是指在主服务器发生故障时，自动将一个从服务器提升为新的主服务器。自动切换可以提高服务的可用性，但需要依赖额外的工具或服务，如MHA、MySQL Router等。
负载均衡是指将读取请求分发到多个从服务器，以提高读取性能。负载均衡可以通过硬件设备（如负载均衡器）、软件工具（如代理服务器）、或者在应用程序中实现。
高级主题和扩展需要操作者具有更深入的数据库知识，同时也需要更多的实践经验。在使用这些高级特性时，需要考虑到它们的优点和潜在的风险，以便做出最佳的决策。

10. 结论

本文详细讨论了MySQL的主从复制配置及其搭建，希望能帮助你理解和实施这一重要的数据库管理策略。文章从MySQL主从复制的基本概念和原理讲起，接着深入介绍了如何进行环境准备、主服务器和从服务器的配置，并如何建立主从连接。为了确保主从复制的顺利进行，我们也讨论了如何进行主从复制的验证，并解决可能遇到的问题。同时，文章也对主从复制的维护和优化，以及一些高级主题进行了阐述。希望通过本文，让你可以深入理解MySQL的主从复制，提升数据库管理能力。

收录于