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

原创

大盘鸡拌面

发布于 2025-07-21 20:53:44

14500

代码可运行

运行总次数：0

代码可运行

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的主主复制，实现双机双向热备份。

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

腾讯技术创作特训营S14#红城堡

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

腾讯技术创作特训营S14#红城堡

登录后参与评论

0 条评论

热度