mysql从备份部分恢复

发布于 2024-10-14 12:34:55 字数 368 浏览 5 评论 0原文

由于服务器崩溃，我需要将在特定时间段内创建的一些行从备份（位于我的本地计算机上）恢复到服务器上的实时数据库上。

要选择有问题的行，我计划从备份数据库执行类似的操作：

SELECT *
FROM access AS t1
WHERE AccessId IN (
SELECT AccessId FROM access_completed AS t1
WHERE (TIMEDIFF(CCompleteDateTime, "2011-01-24 02:00:00") < 23 AND TIMEDIFF(CCompleteDateTime, "2011-01-24 02:00:00") > 0)
)

如何将结果行插入实时数据库？

原文

Due to a server crash I need to restore some rows, created within a certain period of time, from the backup (located on my local machine) onto the live db on server.

To select the rows in question I plan to do something like this from the backupdatabase:

SELECT *
FROM access AS t1
WHERE AccessId IN (
SELECT AccessId FROM access_completed AS t1
WHERE (TIMEDIFF(CCompleteDateTime, "2011-01-24 02:00:00") < 23 AND TIMEDIFF(CCompleteDateTime, "2011-01-24 02:00:00") > 0)
)

How do I insert the resulting rows into the live db?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

生活了然无味 2024-10-21 12:34:55

您可以使用 SELECT ... INTO OUTFILE< /a> 备份并 LOAD DATA INFILE加载数据。

INTO OUTFILE 将选定的数据转储到本地文件，其格式是 MySQL 可以使用 LOAD DATA INFILE 解析的格式。因此，您只需像这样转储和加载：

SELECT * FROM [rest of your query] INTO OUTFILE '/tmp/outfile'

将 outfile 复制到其他服务器

在其他服务器上：

LOAD DATA INFILE /tmp/outfile' INTO TABLE access;

它也可以与 FEDERATED 表。这将允许从主服务器查询备份数据库；所以你可以执行INSERT INTO access ... SELECT ... FROM federated_access ...。

You can use SELECT ... INTO OUTFILE on the backup and LOAD DATA INFILE to load data.

INTO OUTFILE dumps the selected data to a local file, in a format that MySQL can parse back using LOAD DATA INFILE. So you just have to dump and load like this:

SELECT * FROM [rest of your query] INTO OUTFILE '/tmp/outfile'

Copy outfile to the other server

And on the other server:

LOAD DATA INFILE /tmp/outfile' INTO TABLE access;

It would also work with FEDERATED tables. This would allow to query the backup database from the main server; so you could do INSERT INTO access ... SELECT ... FROM federated_access ....

回复收藏 0 原文

~没有更多了~