MySQL 主从结构中，将备库作为历史库来存储历史数据

在 MySQL 主从结构中，您可以将备库作为历史库来存储历史数据。以下是一个具体方案：

在备库上创建用于存储历史数据的新表。这些新表应具有与主数据库中的原始表相同的结构。可以使用 SHOW CREATE TABLE 语句获取原始表的创建语句，然后在备库中执行该语句以创建新表。
在主库上，定期将当前数据复制到备库的新表中。您可以使用 INSERT INTO ... SELECT 语句将数据从原表复制到备库的新表中。

例如，将数据从 table1 插入到备库的 table1_backup 中：

mysql> INSERT INTO backup_db.table1_backup SELECT * FROM current_db.table1;

这里，backup_db 是备库中包含新表的数据库名称，current_db 是主库中包含原表的数据库名称。

定期删除主库中的旧数据。在主库上，您可以根据需要使用 DELETE 语句或其他方法来删除旧数据。
在备库上，可以根据需要查询历史数据。由于备库仍然与主库同步，如果主库出现问题，您仍可以将备库用作故障切换目标。

请注意，这个方案有一定的局限性，因为备库仍然需要处理来自主库的所有写操作。在高负载的环境中，这可能会对备库的性能产生影响。您还需要确保备库具有足够的磁盘空间来存储历史数据。

此外，这种方案可能会导致主从不同步，因为主库中的某些数据在备库中是不可用的。在应用此方案时，请确保您了解可能的影响，并定期检查主从数据库之间的数据一致性。

使用 bash 编写的脚本，用于将主数据库中的数据复制到备库的历史表中。脚本的大致流程如下：

在备库上创建用于存储历史数据的新表，如果该表已经存在，则不执行任何操作。
获取备库历史表中的最大主键值，以便确定从主库中复制哪些数据。
将主库上尚未存在于备库历史表中的数据复制到备库的新表中，其中使用主键进行过滤。

以下是脚本的具体实现细节：

#!/bin/bash

# MySQL 配置
MYSQL_USER="your_user"
MYSQL_PASS="your_password"
MYSQL_HOST_MASTER="master_host"
MYSQL_HOST_SLAVE="slave_host"
MYSQL_DB_MASTER="master_db"
MYSQL_DB_SLAVE="slave_db"
MYSQL_TABLE_MASTER="table_master"
MYSQL_TABLE_SLAVE="table_slave"
MYSQL_PRIMARY_KEY="id"

# 在备库上创建用于存储历史数据的新表
echo "Creating table on the slave database..."
mysql -u$MYSQL_USER -p$MYSQL_PASS -h$MYSQL_HOST_SLAVE $MYSQL_DB_SLAVE -e "CREATE TABLE IF NOT EXISTS $MYSQL_TABLE_SLAVE LIKE $MYSQL_DB_MASTER.$MYSQL_TABLE_MASTER;"

# 获取备库历史表中的最大主键值
MAX_ID=$(mysql -u$MYSQL_USER -p$MYSQL_PASS -h$MYSQL_HOST_SLAVE $MYSQL_DB_SLAVE -sN -e "SELECT MAX($MYSQL_PRIMARY_KEY) FROM $MYSQL_TABLE_SLAVE;")

# 如果没有数据，设置 MAX_ID 为 0
if [ -z "$MAX_ID" ]; then
  MAX_ID=0
fi

# 将主库上尚未存在于备库历史表中的数据复制到备库的新表中
echo "Copying data from master to slave database..."
mysql -u$MYSQL_USER -p$MYSQL_PASS -h$MYSQL_HOST_MASTER $MYSQL_DB_MASTER -e "INSERT INTO $MYSQL_DB_SLAVE.$MYSQL_TABLE_SLAVE SELECT * FROM $MYSQL_DB_MASTER.$MYSQL_TABLE_MASTER WHERE $MYSQL_PRIMARY_KEY > $MAX_ID;"

echo "Data transfer completed."

可以将脚本保存为一个文件，例如 transfer_data.sh，并使用 chmod +x transfer_data.sh 命令将其设置为可执行文件。然后，您可以使用 ./transfer_data.sh 命令来运行脚本，将主数据库中的数据复制到备库的历史表中。

文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/181652.html