【详解】MySQL双机双向热备份

举报
皮牙子抓饭 发表于 2025/07/21 20:53:50 2025/07/21
【摘要】 MySQL双机双向热备份在现代企业级应用中，数据的高可用性和灾难恢复能力是至关重要的。MySQL作为最流行的开源关系型数据库管理系统之一，提供了多种备份和恢复机制来确保数据的安全性。本文将详细介绍如何实现MySQL的双机双向热备份，以提高系统的可用性和数据的安全性。1. 环境准备1.1 硬件环境两台服务器（Server A 和 Server B），建议配置相同或相似。每台服务器至少有两个网络...

MySQL双机双向热备份

在现代企业级应用中，数据的高可用性和灾难恢复能力是至关重要的。MySQL作为最流行的开源关系型数据库管理系统之一，提供了多种备份和恢复机制来确保数据的安全性。本文将详细介绍如何实现MySQL的双机双向热备份，以提高系统的可用性和数据的安全性。

1. 环境准备

1.1 硬件环境

  • 两台服务器（Server A 和 Server B），建议配置相同或相似。
  • 每台服务器至少有两个网络接口，一个用于内部通信，一个用于外部访问。

1.2 软件环境

  • 操作系统：Linux（例如 CentOS 7）
  • 数据库：MySQL 5.7 或更高版本
  • 网络：确保两台服务器之间可以互相通信

2. 配置MySQL主从复制

2.1 安装MySQL

在两台服务器上安装MySQL。假设已经安装完成，版本为5.7。

2.2 配置Server A为主服务器

编辑MySQL配置文件 ​​/etc/my.cnf​​，添加以下内容：

[mysqld]
server-id=1
log-bin=mysql-bin
binlog-format=mixed

重启MySQL服务：

sudo systemctl restart mysqld

2.3 配置Server B为从服务器

编辑MySQL配置文件 ​​/etc/my.cnf​​，添加以下内容：

[mysqld]
server-id=2
log-bin=mysql-bin
binlog-format=mixed
relay-log=mysql-relay-bin
log-slave-updates=1
read-only=1

重启MySQL服务：

sudo systemctl restart mysqld

2.4 创建复制用户

在Server A上创建一个用于复制的用户：

CREATE USER 'repl'@'%' IDENTIFIED BY 'your_password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;

2.5 获取主服务器的二进制日志位置

在Server A上执行以下命令获取二进制日志文件名和位置：

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

记录下 ​​File​​ 和 ​​Position​​ 的值。

2.6 备份主服务器的数据

在Server A上备份数据：

mysqldump --all-databases --master-data=2 --single-transaction --routines --events --triggers > all_databases.sql

2.7 传输备份文件到从服务器

将备份文件传输到Server B：

scp all_databases.sql user@server_b:/path/to/backup/

2.8 在从服务器上恢复数据

在Server B上恢复数据：

mysql < /path/to/backup/all_databases.sql

2.9 配置从服务器

在Server B上配置从服务器：

CHANGE MASTER TO 
MASTER_HOST='server_a_ip', 
MASTER_USER='repl', 
MASTER_PASSWORD='your_password', 
MASTER_LOG_FILE='mysql-bin.000001', 
MASTER_LOG_POS=12345;

启动从服务器：

START SLAVE;

2.10 检查复制状态

在Server B上检查复制状态：

SHOW SLAVE STATUS\G

确保 ​​Slave_IO_Running​​ 和 ​​Slave_SQL_Running​​ 均为 ​​Yes​​。

3. 配置双向复制

3.1 在Server B上创建复制用户

在Server B上创建一个用于复制的用户：

CREATE USER 'repl'@'%' IDENTIFIED BY 'your_password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;

3.2 获取Server B的二进制日志位置

在Server B上执行以下命令获取二进制日志文件名和位置：

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

记录下 ​​File​​ 和 ​​Position​​ 的值。

3.3 配置Server A

在Server A上配置从服务器：

CHANGE MASTER TO 
MASTER_HOST='server_b_ip', 
MASTER_USER='repl', 
MASTER_PASSWORD='your_password', 
MASTER_LOG_FILE='mysql-bin.000001', 
MASTER_LOG_POS=12345;

启动从服务器：

START SLAVE;

3.4 检查复制状态

在Server A上检查复制状态：

SHOW SLAVE STATUS\G

确保 ​​Slave_IO_Running​​ 和 ​​Slave_SQL_Running​​ 均为 ​​Yes​​。

4. 测试双向复制

4.1 在Server A上插入数据

在Server A上插入一条测试数据：

USE test;
CREATE TABLE test_table (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100));
INSERT INTO test_table (name) VALUES ('Test Data');

4.2 在Server B上验证数据

在Server B上查询数据：

USE test;
SELECT * FROM test_table;

4.3 在Server B上插入数据

在Server B上插入一条测试数据：

INSERT INTO test_table (name) VALUES ('Test Data from B');

4.4 在Server A上验证数据

在Server A上查询数据：

SELECT * FROM test_table;

5. 注意事项

  • 双向复制可能会导致数据冲突，特别是在同一个表上同时进行写操作时。可以通过设置不同的数据库或表来避免冲突。
  • 定期检查复制状态，确保没有延迟或错误。
  • 使用监控工具（如Prometheus、Grafana）来监控MySQL的性能和复制状态。

通过以上步骤，您可以成功实现MySQL的双机双向热备份，从而提高系统的高可用性和数据的安全性。

下面是一个简单的示例，展示如何设置两台MySQL服务器之间的双向热备份。假设我们有两台服务器：Server A (192.168.1.10) 和 Server B (192.168.1.11)，每台服务器都运行着MySQL 5.7或更高版本。

步骤 1: 配置MySQL服务器

Server A (192.168.1.10)

编辑 MySQL 配置文件 ​​my.cnf​​ 或 ​​my.ini​​，添加以下内容：

[mysqld]
server-id=1
log_bin=mysql-bin
binlog_do_db=your_database_name
auto-increment-offset=1
auto-increment-increment=2
Server B (192.168.1.11)

编辑 MySQL 配置文件 ​​my.cnf​​ 或 ​​my.ini​​，添加以下内容：

[mysqld]
server-id=2
log_bin=mysql-bin
binlog_do_db=your_database_name
auto-increment-offset=2
auto-increment-increment=2

步骤 2: 创建复制用户

在两台服务器上分别创建一个用于复制的用户，并赋予相应的权限。

在 Server A 上执行：
CREATE USER 'repl'@'192.168.1.11' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.11';
FLUSH PRIVILEGES;
在 Server B 上执行：
CREATE USER 'repl'@'192.168.1.10' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.10';
FLUSH PRIVILEGES;

步骤 3: 获取二进制日志文件和位置

在两台服务器上分别获取当前的二进制日志文件名和位置。

在 Server A 上执行：
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

记下 ​​File​​ 和 ​​Position​​ 的值，例如 ​​mysql-bin.000001​​ 和 ​​12345​​。

在 Server B 上执行：
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

同样记下 ​​File​​ 和 ​​Position​​ 的值。

步骤 4: 设置从属关系

在 Server A 上执行：
CHANGE MASTER TO 
MASTER_HOST='192.168.1.11',
MASTER_USER='repl',
MASTER_PASSWORD='password',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=12345;
START SLAVE;
在 Server B 上执行：
CHANGE MASTER TO 
MASTER_HOST='192.168.1.10',
MASTER_USER='repl',
MASTER_PASSWORD='password',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=12345;
START SLAVE;

步骤 5: 检查复制状态

在两台服务器上检查复制状态，确保一切正常。

在 Server A 和 Server B 上执行：
SHOW SLAVE STATUS \G

确保 ​​Slave_IO_Running​​ 和 ​​Slave_SQL_Running​​ 均为 ​​Yes​​，并且没有错误信息。

  • 双向复制可能导致数据冲突，特别是当两个服务器同时更新同一行数据时。可以通过设计应用程序逻辑来避免这种情况。
  • 使用 ​​auto-increment-offset​​ 和 ​​auto-increment-increment​​ 来防止自增列冲突。
  • 定期监控复制状态和延迟，确保系统稳定运行。

以上步骤提供了一个基本的双向热备份配置示例。在生产环境中，还需要考虑更多的安全性和性能优化措施。MySQL的双机双向热备份通常指的是MySQL的主从复制（Master-Slave Replication）和主主复制（Master-Master Replication）。这种配置可以确保数据在两台服务器之间同步，提高系统的可用性和数据的安全性。下面我将详细介绍如何设置MySQL的主主复制，并提供相应的SQL命令。

环境准备

假设你有两台MySQL服务器，分别命名为​​Server A​​和​​Server B​​，它们的IP地址分别为​​192.168.1.10​​和​​192.168.1.11​​。

配置文件修改

首先，需要修改两台服务器上的MySQL配置文件​​my.cnf​​或​​my.ini​​，添加或修改以下内容：

Server A (​​/etc/mysql/my.cnf​​)
[mysqld]
server-id=1
log-bin=mysql-bin
binlog-do-db=your_database_name
relay-log=mysql-relay-bin
auto-increment-offset=1
auto-increment-increment=2
Server B (​​/etc/mysql/my.cnf​​)
[mysqld]
server-id=2
log-bin=mysql-bin
binlog-do-db=your_database_name
relay-log=mysql-relay-bin
auto-increment-offset=2
auto-increment-increment=2

重启MySQL服务

修改配置文件后，需要重启MySQL服务以使配置生效：

sudo systemctl restart mysql

创建复制用户

在两台服务器上创建一个用于复制的MySQL用户，并授予必要的权限。

在Server A上执行
CREATE USER 'replication'@'192.168.1.11' IDENTIFIED BY 'your_password';
GRANT REPLICATION SLAVE ON *.* TO 'replication'@'192.168.1.11';
FLUSH PRIVILEGES;
在Server B上执行
CREATE USER 'replication'@'192.168.1.10' IDENTIFIED BY 'your_password';
GRANT REPLICATION SLAVE ON *.* TO 'replication'@'192.168.1.10';
FLUSH PRIVILEGES;

获取二进制日志位置

在开始复制之前，需要获取当前的二进制日志文件名和位置。

在Server A上执行
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

记录下​​File​​和​​Position​​的值，例如：

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 12345    | your_database_name |              |
+------------------+----------+--------------+------------------+
在Server B上执行
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

同样记录下​​File​​和​​Position​​的值。

配置复制

使用​​CHANGE MASTER TO​​命令配置复制。

在Server A上执行
CHANGE MASTER TO
MASTER_HOST='192.168.1.11',
MASTER_USER='replication',
MASTER_PASSWORD='your_password',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=12345;

START SLAVE;
在Server B上执行
CHANGE MASTER TO
MASTER_HOST='192.168.1.10',
MASTER_USER='replication',
MASTER_PASSWORD='your_password',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=12345;

START SLAVE;

检查复制状态

最后，检查复制状态以确保一切正常。

在Server A上执行
SHOW SLAVE STATUS \G

确保​​Slave_IO_Running​​和​​Slave_SQL_Running​​都为​​Yes​​。

在Server B上执行
SHOW SLAVE STATUS \G

同样确保​​Slave_IO_Running​​和​​Slave_SQL_Running​​都为​​Yes​​。

测试复制

可以在任意一台服务器上创建一个测试表并插入一些数据，然后检查另一台服务器上是否也同步了这些数据。

在Server A上执行
USE your_database_name;
CREATE TABLE test (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50));
INSERT INTO test (name) VALUES ('Test1');
在Server B上执行
USE your_database_name;
SELECT * FROM test;

如果能看到​​Test1​​这条记录，说明复制配置成功。

通过以上步骤，你就可以成功配置MySQL的主主复制，实现双机双向热备份。

【声明】本内容来自华为云开发者社区博主，不代表华为云及华为云开发者社区的观点和立场。转载时必须标注文章的来源（华为云社区）、文章链接、文章作者等基本信息，否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱： cloudbbs@huaweicloud.com
MySQL
  • 点赞
  • 收藏
  • 关注作者

评论（0）

0/1000
抱歉，系统识别当前为高风险访问，暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称，即可参与社区互动！

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

确认 取消

加入云驻计划，成为创作者

  • 华为云周边好礼
  • 免费体验产品
  • 特殊身份标识
  • 线下官方门票
  • 内部专家零距离
  • 与10000+优质创作者共同成长
立即加入