迹忆客 专注技术分享

本文旨在演示如何实现数据库同步以用于备份和记录保存目的。

MySQL数据库同步

在处理大量数据时，考虑数据可能受到损害的情况可能至关重要。

它被破坏的原因可能取决于在设计或规划解决此特定问题的不同策略时必须考虑的许多因素。

选择实施搜索措施极为重要； 根据数据的价值，如果数据因某些技术故障而丢失而没有任何备份储备，则可能会给公司或机构带来巨大的损失，因为数据会永久丢失。

解决方案

这个问题的解决方案可以根据不同的条件和限制而有所不同。 总而言之，下面是数据库最常用的方法之一。

1. 客户端/服务器模型——不使用数据库，而是使用直接与服务器通信的应用程序来访问/存储数据。
2. 主/从模型 - 一个服务器被分配用于写入数据（主），而另一个（可以是多个）专门用于读取数据（从）。
3. 离线模型 - 在固定的时间后，在本地执行所需的操作并将其发送到服务器。

在本文中，我们将讨论实现主/从模型。

先决条件

1. 两台机器充当运行 Ubuntu (20.0+) 的服务器。 每台机器都应该有一个非管理员用户和正确配置的 sudo 权限。 还需要使用 UFW 配置防火墙。
2. 两台机器上都安装了 MySQL。 最好是最新版本（8.0+）。

配置源计算机防火墙

使用 UFW 设置防火墙后，防火墙将阻止来自副本服务器的任何传入连接。 要解决此问题，请在源计算机上执行以下命令。

$ sudo ufw allow REPLICA_SERVER_IP_ADDR to any port MySQL_PORT

如果执行成功，将显示输出：

Rule Added

请记住，在以下命令中：

1. REPLICA_SERVER_IP_ADDR - 副本服务器的 IP 地址。
2. MySQL_PORT - MySQL 服务器的端口（默认为 3306）。

在上面的命令中，我们向 UFW 添加了一条规则，指示它允许来自 IP REPLICA_SERVER_IP_ADDR 的任何传入连接到端口 MySQL_PORT。

配置源数据库

需要调整其某些设置，以便源数据库继续进行数据复制。

为此，我们需要访问名为 mysqld.cnf 的 MySQL 服务器配置文件。 在 Ubuntu 20+ 上，它可以在 /etc/mysql/mysql.conf.d/ 目录中找到。

使用您喜欢的文本编辑器打开此文件。 或者，使用以下命令：

sudo gedit /etc/mysql/mysql.conf.d/mysqld.cnf

在 mysql.cnf 文件中，搜索以下行：

. . .
bind-address            = 127.0.0.1
. . .

bind-address 属性指示数据库将在哪里接受来自 127.0.0.1 代表本地主机的连接； 这意味着服务器将只接受来自当前安装服务器的机器的连接请求。

要接受来自副本服务器的连接，我们需要更改给 bind-address 属性的地址。 将其更改为源服务器的 IP 地址即可。

更改 IP 地址后，您的文件应如下所示：

. . .
bind-address            = SRC_SERVER_IP_ADDR
. . .

SRC_SERVER_IP_ADDR 是源机器的 IP 地址。

接下来，我们需要为 master 分配一个唯一的 id。 每台服务器都将成为复制环境的一部分，因此必须为每台服务器分配一个唯一的 ID，以确保服务器之间不会发生冲突。

为此，请在 mysql.cnf 文件中搜索 server-id。 结果如下所示：

. . .
# server-id             = 1
. . .

# 号表示该行当前被注释掉； 删除符号并选择任何整数值分配给服务器。 进行更改后，该行将如下所示：

. . .
server-id             = 10
. . .

之后，搜索 log_bin 指令并取消注释。 log_bin 是副本服务器读取以了解它应该如何复制数据库所必需的。

进行更改后，该行应如下所示：

. . .
log_bin                       = /var/log/mysql/mysql-bin.log
. . .

最后，搜索 binlog_do_db 指令； 该行也将被注释，取消注释并将其值设置为您要复制的数据库的名称。 修改后，它看起来像这样：

. . .
binlog_do_db          = DB_NAME

# For multiple DBs
binlog_do_db          = DB_NAME_1
binlog_do_db          = DB_NAME_2
. . .

或者，您可以排除某个数据库并使用 binlog_ignore_db 复制其余数据库。

. . .
binlog_ignore_db          = DB_NAME_TO_IGNORE
. . .

在 mysqld.cnf 中进行这些更改后，保存文件并重新启动 mysql 服务。

您可以使用以下命令执行此操作。

$ sudo systemctl restart mysql

创建复制用户

副本服务器必须提供用户名和密码才能连接到源服务器。 副本服务器可以使用源计算机上可用的任何用户配置文件进行连接，但在本教程中，我们将为复制创建一个专用用户。

按照步骤创建用于复制目的的用户。

在您的源计算机上，执行以下命令：

sudo mysql -u USER -p PASS

这里:

• USER - 用户的用户名
• PASS - 用户密码

在 MySQL 提示符下，您可以使用以下命令创建一个新用户：

mysql> CREATE USER 'REPLICA_USER'@'REPLICA_IP' IDENTIFIED WITH mysql_native_password BY 'PASS';

这里:

• REPLICA_USER - 副本服务器用户的用户名
• REPLICA_IP - 副本机器的 IP 地址
• PASS - 用户密码

成功创建用户后，我们需要授予它适当的权限。 用户必须至少拥有 REPLICATION SLAVE。

使用以下命令授予用户应用程序：

mysql> GRANT REPLICATION SLAVE ON *.* TO 'REPLICA_USER'@'REPLICA_IP';

之后，我们必须刷新权限以使用 GRANT 和 CREATE USER 命令清除所有使用的内存和缓存。

mysql> FLUSH PRIVILEGES;

最后，我们需要检查数据库的状态，并使用读锁来防止在日志状态捕获过程中发生任何写操作。

为此，请执行以下命令：

mysql> FLUSH TABLES WITH READ LOCK;

然后使用以下命令检查服务器的状态：

mysql> SHOW MASTER STATUS;

它将以以下格式返回输出：

+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      899 | db           |                  |                   |
| mysql-bin.000002 |      900 | db2          |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
2 rows in set (0.01 sec)

在此之后，您可以使用 mysqldump 复制整个数据库：

$ sudo mysqldump -u root db > db.sql

将生成一个名为 new_db.sql 的文件，您可以将其传输到从机以完成备份过程。

备份完成后，您可以解锁表。

mysql> UNLOCK TABLES

配置从服务器

在从服务器中，您需要创建一个新的数据库，该数据库将被复制。 你可以这样做：

mysql> CREATE DATABASE DATABASE_NAME

现在从源计算机导入导出的 sqldump。

sudo mysql db < /PATH/db.sql

将数据库导入从机后，在 mysqld.cnf 中配置指令如下：

server-id = 11
log_bin = /data/mysql/mysql-bin.log
binlog_do_db = DATABASE_NAME

进行必要的更改后，重新启动 MySQL 服务。

$ sudo systemctl restart mysql

最后，要开始从服务器复制数据，请执行以下命令：

在登录后的MySQL提示中，进行如下操作：

mysql> CHANGE REPLICATION SOURCE TO
mysql> SOURCE_HOST='SRC_SERVER_IP_ADDR',
mysql> SOURCE_USER='REP_USER',
mysql> SOURCE_PASSWORD='PASS',
mysql> SOURCE_LOG_FILE='mysql-bin.000001',
mysql> SOURCE_LOG_POS=899;

最后，启动从属服务器。

mysql > START REPLICA

现在服务器将开始从源服务器复制更改。

要查看副本服务器的状态，请使用以下命令：

SHOW REPLICA STATUS\G

\G 重新格式化输出以提高可读性。

上述命令的输出格式如下：