Comments on: On restoring a single table from mysqldump https://shlomi-noach.github.io/blog/mysql/on-restoring-a-single-table-from-mysqldump Blog by Shlomi Noach Tue, 20 Dec 2011 11:08:20 +0000 hourly 1 https://wordpress.org/?v=5.3.3 By: 切割mysqldump文件工具 https://shlomi-noach.github.io/blog/mysql/on-restoring-a-single-table-from-mysqldump/comment-page-1#comment-63668 Tue, 20 Dec 2011 11:08:20 +0000 https://shlomi-noach.github.io/blog/?p=1630#comment-63668 […] On restoring a single table from mysqldump：这篇文章则对比了使用grep sed 和“权限控制”三种方法的速度。 […]

]]> By: 使用tbdba-restore-mysqldump.pl切割mysqldump文件 | 岭南六少 - 一朵在LAMP架构下挣扎的云 https://shlomi-noach.github.io/blog/mysql/on-restoring-a-single-table-from-mysqldump/comment-page-1#comment-63543 Mon, 19 Dec 2011 11:48:32 +0000 https://shlomi-noach.github.io/blog/?p=1630#comment-63543 […] On restoring a single table from mysqldump：这篇文章则对比了使用grep sed 和“权限控制”三种方法的速度。 […]

]]> By: 使用tbdba-restore-mysqldump.pl切割mysqldump文件 - 一个故事@MySQL DBA https://shlomi-noach.github.io/blog/mysql/on-restoring-a-single-table-from-mysqldump/comment-page-1#comment-62941 Wed, 14 Dec 2011 14:41:06 +0000 https://shlomi-noach.github.io/blog/?p=1630#comment-62941 […] On restoring a single table from mysqldump：这篇文章则对比了使用grep sed 和“权限控制”三种方法的速度。 […]

]]> By: Useful sed / awk liners for MySQL | code.openark.org https://shlomi-noach.github.io/blog/mysql/on-restoring-a-single-table-from-mysqldump/comment-page-1#comment-45513 Wed, 06 Jul 2011 06:41:04 +0000 https://shlomi-noach.github.io/blog/?p=1630#comment-45513 […] On restoring a single table from mysqldump […]

]]> By: Extract satu tabel dari file backup mysqldump « Setsuga's Weblog https://shlomi-noach.github.io/blog/mysql/on-restoring-a-single-table-from-mysqldump/comment-page-1#comment-41590 Mon, 30 May 2011 10:29:15 +0000 https://shlomi-noach.github.io/blog/?p=1630#comment-41590 […] tidak lupa saya cantumkan link asli GA_googleAddAttr("AdOpt", "1"); GA_googleAddAttr("Origin", "other"); […]

]]> By: Vitaly https://shlomi-noach.github.io/blog/mysql/on-restoring-a-single-table-from-mysqldump/comment-page-1#comment-17217 Thu, 02 Sep 2010 11:00:21 +0000 https://shlomi-noach.github.io/blog/?p=1630#comment-17217 Even it’s not directly related to this topic, I noticed that if we cutting specific table dump from the whole DB dump there is a problem with restoring of TIMESTAMP fields – they become UTC instead of local time.

adding

/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE=’+00:00′ */;

(as we have in a dump produced by mysqldump) before table data fixes this problem.

Regards,
Vitaly Karasik

]]> By: shlomi https://shlomi-noach.github.io/blog/mysql/on-restoring-a-single-table-from-mysqldump/comment-page-1#comment-8111 Tue, 15 Dec 2009 13:31:30 +0000 https://shlomi-noach.github.io/blog/?p=1630#comment-8111 @TVNshack

sed can come in handy here, too.

]]> By: TVNshack https://shlomi-noach.github.io/blog/mysql/on-restoring-a-single-table-from-mysqldump/comment-page-1#comment-8106 Tue, 15 Dec 2009 11:15:39 +0000 https://shlomi-noach.github.io/blog/?p=1630#comment-8106 Watchout the problem of the automatically generated AUTO_INCREMENT table option that mysqldump generates and that MATCH only with the INSERT statements that may come right after in the dump.

Even if you specify a –no-data, that AUTO_INCREMENT=nnn table option will still appear. If you intend to create a table from that extracted definition and for which you may not want to insert the exact same data, LAST_INSERT_ID will not be adapted for the need of your empty table. Your table first row inserted relying on AUTO_INCREMENT column definition option will start with an offset AUTO_INCREMENT value.

I would recommend to edit the mysqldump output to reset each AUTO_INCREMENT table option to AUTO_INCREMENT=1 and let the eventual INSERTs manage LAST_INSERT_ID

(see bug #44425)

]]> By: Log Buffer #171: a Carnival of the Vanities for DBAs | The Pythian Blog https://shlomi-noach.github.io/blog/mysql/on-restoring-a-single-table-from-mysqldump/comment-page-1#comment-7693 Fri, 04 Dec 2009 18:05:02 +0000 https://shlomi-noach.github.io/blog/?p=1630#comment-7693 […] Noach shares his thoughts on restoring a single table from mysqldump. “Given a dump file (generated by mysqldump), how do you restore a single table, without […]

]]> By: Gavin Towey https://shlomi-noach.github.io/blog/mysql/on-restoring-a-single-table-from-mysqldump/comment-page-1#comment-7584 Thu, 03 Dec 2009 00:32:08 +0000 https://shlomi-noach.github.io/blog/?p=1630#comment-7584 Nice! The sed only method is a good improvement.

]]>