![更新 MySQL 数据库](https://linux22.com/image/483833/%E6%9B%B4%E6%96%B0%20MySQL%20%E6%95%B0%E6%8D%AE%E5%BA%93.png)
我们有一个使用 MySQL 数据库的在线系统。我们需要将较旧的备份数据库(完整表,而不是转储文件)与当前数据库合并。在不经历任何服务器停机的情况下,您将如何做到这一点?请注意,系统全天候在线。可能出现哪些问题?
谢谢。
答案1
当弄乱 MySQL 的文件系统时,您必须停止 MySQL 服务器。为了避免您的实时计算机停机,请使用具有相同版本 MySQL 服务器的备份/虚拟机。虽然备份MySQL 服务器停止后,将表(我假设是 .FRM、.MYI 等?)复制到 /var/lib/mysql/BACKUP_DB(BACKUP_DB 的对应目录)的文件系统中。
启动备份MySQL 服务器,并使用脚本或 CLI 确保数据已正确加载。验证后,mysqldump BACKUP_DB 数据库,以便可以将其加载到实时服务器中:
mysqldump --extended-insert BACKUP_DB > /root/sql/BACKUP_DB.sql
现在,您已将原始备份数据转换为 SQL 语句,这些语句无需停机即可加载到 MySQL(与原始数据不同)。移至BACKUP_DB.sql
实时机器。
导入BACKUP_DB.sql
到您的居住MySQL 实例作为不同的数据库:
mysql BACKUP_DB < /root/sql/BACKUP_DB.sql
现在您应该已将备份数据库作为 BACKUP_DB 加载到 MySQL 中。
现在,依赖于 INSERT IGNORE 或 REPLACE INTO 语句(您是否覆盖旧数据或“填补索引中的空白”?):
mysqldump --no-create-db
--no-create-info --extended-insert --insert-ignore MERGE_SOURCE | mysql BACKUP_DB
或者,对于 REPLACE INTO 操作:
mysqldump --no-create-db --no-create-info --extended-insert MERGE_SOURCE | sed 's/INSERT INTO/REPLACE INTO/g' | mysql BACKUP_DB
或者,不要将输出通过管道传输回 MySQL,而是发送到文件并检查 SQL 语句。
mysqldump --no-create-db --no-create-info --extended-insert --insert-ignore MERGE_SOURCE > /root/sql/merge.sql
mysqldump --no-create-db --no-create-info --extended-insert MERGE_SOURCE | sed 's/INSERT INTO/REPLACE INTO/g' > /root/sql/merge.sql
最后,为了避免停机,将第一个数据库转储到第二个数据库上:
mysqldump BACKUP_DB | mysql CURRENT_DB
您可以先锁定数据库,以防止数据被写入(例如)具有 a 表中外键的 z 表(该表已被覆盖):
FLUSH TABLES WITH READ LOCK;
(按照上一步执行转储)
UNLOCK TABLES;
将 FLUSH 命令添加到转储 .sql 文件的开头,并将 UNLOCK 添加到结尾。
在这种情况下,请务必仔细检查您的数据库名称!询问任何您不确定的后续问题,因为这是高风险的数据混合内容。如果可能,请在开发服务器上执行(并详细记录)所需的确切步骤,或者虚拟化您的测试,或者创建小规模测试。只需测试。当然,还要进行足够的备份以涵盖所有数据丢失的可能性。
答案2
对于此示例,我们假设您的数据库名为“zig”,备份数据库名为“zig_backup”。我还假设您的备份数据库和实时数据库具有相同的模式。
我还假设您的实时数据库正在不断更新,并且您无法“离线”执行合并,然后拨动开关使合并的副本“实时”。
需要注意的问题是:
- 由于某种原因(通常是出现错误或意外丢失记录),新旧索引或主键可能会发生冲突
- 自备份以来发生的删除不应“合并”
更新过程需要规划先合并哪些表以及最后合并哪些表。我通常倾向于先处理繁重的工作(大型表),然后再处理较小的表。根据您的数据大小,这可能并不重要。
合并的过程如下:
- 使用修改后的数据库名称(例如“zig_backup”)将备份数据库恢复到实时数据库旁边
- 对于实时数据库中的每个表,从备份数据库合并
- 对于备份数据库中的每个表,检查该表是否存在于实时数据库中,如果不存在,则添加该表。
最安全的方法是进行全表连接并插入不匹配的行:
for each table in the "live" database:
INSERT INTO zig.$table
SELECT BACKUP.* FROM zig_backup.$table AS BACKUP
LEFT OUTER JOIN zig.$table AS LIVE ON LIVE.ID=BACKUP.ID
WHERE LIVE.ID IS NULL;
当然,这需要了解每个表的主键。直接“REPLACE”可能不起作用,因为实时数据库中的行可能已更改,并且更改的数据将被覆盖
要合并丢失的表,请执行以下操作:
\u zig_backup
SHOW TABLES
要获取所有表的列表,并确定该表是否存在于实时数据库中,您可以执行以下操作:
\u zig
SHOW TABLES LIKE 'tablename';
或者,可以将备份数据库中“实时”数据库中不存在的表以二进制形式复制到实时数据库目录中。
最后,处理“实时”数据库中的删除操作非常困难,特别是因为在合并过程中由于某种原因丢失了一些数据。
答案3
设置复制,并在准备就绪并经过充分测试后将 DB 服务器切换为从属服务器 (!!!)。
除非您在执行该操作时遇到一些技术限制,否则这只是有效的变体(我的意思是您应该能够在将更改放入 24/7 环境之前对其进行测试)。
答案4
您可以使用 mysqldump 的 -T 选项将表输出为制表符分隔值。然后,创建实时数据库的副本作为凯尔建议。然后使用 mysqlimport 将数据重新导入 live-copy 并在那里进行测试。当您准备从 live 切换到 live-copy 时,您需要停机一段时间。
我自己还没有尝试过,买者自负。