{"id":3364,"date":"2011-12-14T22:40:42","date_gmt":"2011-12-14T14:40:42","guid":{"rendered":"http:\/\/www.orczhou.com\/?p=3364"},"modified":"2011-12-19T19:10:48","modified_gmt":"2011-12-19T11:10:48","slug":"how-to-split-mysqldump-file","status":"publish","type":"post","link":"https:\/\/www.orczhou.com\/index.php\/2011\/12\/how-to-split-mysqldump-file\/","title":{"rendered":"\u4f7f\u7528tbdba-restore-mysqldump.pl\u5207\u5272mysqldump\u6587\u4ef6"},"content":{"rendered":"

\u8fd9\u91cc\u4ecb\u7ecd\u4e00\u4e2a\u6700\u8fd1\u7528\u5f97\u5f88\u591a\u7684\u4e00\u4e2a\u5c0f\u5de5\u5177\uff1atbdba-restore-mysqldump.pl<\/a>\u3002<\/p>\n

\u4e3b\u8981\u6709\u4e24\u4e2a\u529f\u80fd\uff1a<\/p>\n

(1) \u5c3d\u53ef\u80fd\u5feb\u7684\u4ece\u4e00\u4e2a\u975e\u5e38\u5927\u7684mysqldump\u6587\u4ef6\u7684\u5206\u79bb\u51fa\u67d0\u4e2a\u5355\u8868\u7684\u5907\u4efd\u6587\u4ef6<\/p>\n

(2) \u53ef\u4ee5\u5e2e\u4f60\u628a\u4e00\u4e2a\u5927\u7684mysqldump\u6587\u4ef6\uff0c\u5207\u5272\u6210\u975e\u5e38\u5c0f\u7684\u5355\u8868\u5907\u4efd\u6587\u4ef6\uff08\u53ef\u7ee7\u7eed\u505a\u5e76\u884c\u6062\u590d\uff09<\/p>\n

1. \u4ec0\u4e48\u65f6\u5019\u9700\u8981\u8fd9\u4e48\u505a<\/div>\n

(1) \u5982\u679c\u628aMySQL\u4e2d\u67d0\u4e00\u4e2a\u8868<\/strong>\u6570\u636e\u5f04\u4e22\u4e86\uff0c\u9700\u8981\u4ece\u5f88\u5927\u7684mysqldump\u5907\u4efd\u6587\u4ef6\u4e2d\u6062\u590d\u8fd9\u4e2a\u8868<\/p>\n

(2) \u5982\u679c\u4f60\u60f3\u5e76\u884c\u6062\u590d\u6574\u4e2amysqldump\u5907\u4efd\u6587\u4ef6\u65f6\uff0c\u8fd9\u4e2a\u811a\u672c\u53ef\u4ee5\u5e2e\u4f60\u628a\u5927\u6587\u4ef6\u5207\u5272\u6210\u591a\u4e2a\u5c0f\u7684\u5355\u8868\u5907\u4efd\u6587\u4ef6\uff0c\u7136\u540e\u5c31\u53ef\u4ee5\u65b9\u4fbf\u5e76\u884c\u6062\u590d\u591a\u4e2a\u6587\u4ef6\u4e86<\/p>\n

2. \u5982\u4f55\u4f7f\u7528\u8fd9\u4e2a\u811a\u672c<\/div>\n

\u8fd9\u91cc\u4ee5\u5b9e\u4f8b\u7684\u65b9\u5f0f\u4ecb\u7ecd\u5982\u4f55\u4f7f\u7528\u8be5\u811a\u672c\uff1a<\/p>\n

(1) \u4ecebackup.sql\u6587\u4ef6\u4e2d\u83b7\u53d6\u8868process\u7684\u5907\u4efd\uff1a<\/p>\n

\n
tbdba-restore-mysqldump.pl -t process -f backup.sql<\/div>\n<\/blockquote>\n

(2) \u4ecebackup.sql\u6587\u4ef6\u4e2d\u83b7\u53d6\u6570\u636e\u5e93monitor\u4e2d\u7684\u8868process\u7684\u5907\u4efd\uff1a<\/p>\n

\n
tbdba-restore-mysqldump.pl -t process -s monitor -f backup.sql<\/div>\n<\/blockquote>\n

<\/p>\n

(3) \u4ecebackup.sql\u6587\u4ef6\u4e2d\u83b7\u53d6\u591a\u4e2a\u8868\u7684\u5907\u4efd\u6587\u4ef6\uff08\u4f8b\u5982\u8868process\u3001users\uff09\uff1a<\/p>\n

\n
tbdba-restore-mysqldump.pl -t process,user -s monitor -f backup.sql<\/div>\n<\/blockquote>\n

(4) \u76f4\u63a5\u63a5\u6536\u6765\u81ea\u7ba1\u9053\u7684\u8f93\u51fa\uff08\u5982\u679c\u4f60\u7684mysqldump\u5907\u4efd\u662f\u538b\u7f29\u540e\uff0c\u5219\u53ef\u4ee5\u4f7f\u7528\uff09\uff1a<\/p>\n

\n
gunzip -c backup.sql.gz|tbdba-restore-mysqldump.pl -t process,user -s monitor<\/div>\n<\/blockquote>\n

(5) \u4ecebackup.sql\u6587\u4ef6\u4e2d\u83b7\u53d6\u6570\u636e\u5e93monitor\u4e0b\u6240\u6709\u8868<\/strong>\u7684\u5907\u4efd\u6587\u4ef6\uff1a<\/p>\n

\n
gunzip -c backup.sql.gz|tbdba-restore-mysqldump.pl -s monitor <\/div>\n<\/blockquote>\n

(6) \u4ecebackup.sql\u6587\u4ef6\u4e2d\u83b7\u53d6\u6240\u6709\u6570\u636e\u5e93\u4e0b\u6240\u6709\u8868<\/strong>\u7684\u5907\u4efd\u6587\u4ef6\uff1a<\/p>\n

\n
gunzip -c backup.sql.gz|tbdba-restore-mysqldump.pl –all-tables<\/div>\n<\/blockquote>\n

(7) \u4f7f\u7528-d\u53c2\u6570\uff0c\u5219\u53ef\u4ee5\u770b\u5230\u5207\u5272\u7684\u8fc7\u7a0b\u4e2d\u7684\u66f4\u591a\u4fe1\u606f\uff1a<\/p>\n

\n
date && gunzip -c \/backdir\/backup.sql.gz|tbdba-restore-mysqldump.pl -d -a && date<\/div>\n<\/blockquote>\n
3. tbdba-restore-mysqldump.pl\u6709\u4ec0\u4e48\u4f18\u52bf<\/div>\n

(1) \u5982\u679c\u6307\u5b9a\u4e86-s\uff08\u83b7\u53d6\u67d0\u4e2a<\/strong>\u6570\u636e\u5e93\u4e2d\u7684\u5907\u4efd\uff09\u53c2\u6570\uff0c\u5219\u811a\u672c\u5728\u6210\u529f\u622a\u53d6\u9700\u8981\u6062\u590d\u7684\u8868<\/strong>\u540e\u5c31\u4f1a\u7acb\u523b\u9000\u51fa\uff0c\u6240\u4ee5\u5982\u679c\u4f60\u8981\u6062\u590d\u7684\u8868\u6070\u597d\u5728\u5907\u4efd\u6587\u4ef6\u7684\u6bd4\u8f83\u9760\u524d\u7684\u4f4d\u7f6e\u65f6\uff0c\u8be5\u811a\u672c\u7684\u901f\u5ea6\u4f1a\u975e\u5e38\u5feb\u3002<\/p>\n

\u4e00\u4e2a\u5b9e\u9645\u5de5\u4f5c\u4f8b\u5b50\uff1a<\/p>\n

$ls -lh backup.sql.gz
\n -rw-r–r– 1 mysql dba 14G Nov 21 04:49 backup.sql.gz
\n$date && gunzip -c backup.sql.gz|.\/tbdba-restore-mysqldump.pl -s monitor_general -t monitor_host_info && date
\nFri Nov 25 14:35:06 CST 2011
\nFri Nov 25 14:46:49 CST 2011
\n(the unzip of backup.sql.gz is 88G)<\/p><\/blockquote>\n

\u5982\u679c\u8981\u5168\u91cf\u6062\u590d\u7684\u8bdd\uff0c\u6839\u636e\u7ecf\u9a8c\u503c\uff1a88GB\u7684sql\u6587\u4ef6\u5b8c\u5168\u6062\u590d\u7ea6\u9700\u8981400\u5206\u949f\uff08\u7ecf\u9a8c\u503c<\/a>\uff09\u3002<\/p>\n

(2) \u4e3a\u4e86\u8ba9\u6bcf\u4e2a\u72ec\u7acb\u7684\u5355\u8868\u5907\u4efd\u6587\u4ef6\u80fd\u591f\u51c6\u786e\u6062\u590d\uff0c\u811a\u672c\u505a\u4e86\u4e24\u4e2a\u989d\u5916\u7684\u5904\u7406\u5de5\u4f5c\uff1a\u5728\u6bcf\u4e2a\u5355\u8868\u5907\u4efd\u524d\u52a0\u4e0a’use db’\uff0c\u8ba9\u8be5\u8868\u80fd\u591f\u6062\u590d\u5230\u6b63\u786e\u7684\u6570\u636e\u5e93\uff1b\u4e3a\u4e86\u8ba9\u5355\u8868\u6062\u590d\u65f6\u5b57\u7b26\u96c6\u4e0d\u51fa\u9519\u8bef\uff0c\u811a\u672c\u5728\u67d0\u4e2a\u5355\u8868\u5907\u4efd\u524d\u52a0\u4e0a\u4e86\u5bf9\u5e94\u7684SET NAMES utf8\u3001SET TIME_ZONE\u7b49\u547d\u4ee4\u3002<\/p>\n

4. \u5176\u4ed6\u4eba\u90fd\u600e\u4e48\u505a<\/div>\n

Extract a Single Table from a mysqldump File<\/a>\uff1a\u8fd9\u7bc7\u6587\u7ae0\u63d0\u5230\u4e86\u4e09\u4e2a\u529e\u6cd5\uff0c\u5206\u522b\u662f\uff1aperl\u811a\u672c\uff08\u6211\u8fd9\u91cc\u7684\u505a\u6cd5\u57fa\u672c\u201c\u96f7\u540c\u201d\uff09\uff0cawk\u89e3\u6790\u540e\u5207\u5272\uff0c\u5148\u6062\u590d\u5230\u4e34\u65f6\u5e93(\u5bf9\u5927\u6587\u4ef6\u8fd9\u4e2a\u4e0d\u73b0\u5b9e…)\u3002\u5bf9\u6bd4\u4e86\u6211\u4eec\u7684Perl\u811a\u672c\uff0c\u8fd9\u91cc\u505a\u4e86\u51e0\u4e2a\u6539\u8fdb\uff1a\u53ef\u4ee5\u540c\u65f6\u89e3\u6790\u51fa\u591a\u4e2a\u8868\uff1b\u5b8c\u6210\u76ee\u6807\u8868\u7684\u5207\u5272\u540e\uff0c\u5219\u7acb\u523b\u9000\u51fa\uff0c\u4e0d\u518d\u626b\u63cf\u5269\u4f59\u90e8\u5206\uff1b\u4f1a\u628amysqldump\u5934\u90e8\u8f93\u51fa\u653e\u5230\u6bcf\u4e00\u4e2a\u5207\u5272\u6587\u4ef6\u4e2d\uff0c\u65b9\u4fbf\u5404\u79cd\u5b57\u7b26\u96c6\u7684\u6062\u590d\uff1b<\/p>\n

Restore a Single Table From mysqldump<\/a>\uff1a\u8fd9\u7bc7\u6587\u7ae0\u4ecb\u7ecd\u5982\u4f55\u7528Sed\u6765\u5b8c\u6210\u8fd9\u4e2a\u5de5\u4f5c\u3002<\/p>\n

MySQL – Restoring a single table from nightly backup <\/a>\uff1a\u8fd9\u4f4d\u670b\u53cb\u5219\uff0c\u60f3\u51fa\u4e00\u4e2a\u201c\u66f4\u635f\u201d\u7684\u62db\uff1a\u53ea\u7ed9\u6062\u590d\u7528\u6237\u8d4b\u4e88\u9700\u8981\u6062\u590d\u7684\u8868\u7684\u6743\u9650\uff0c\u7136\u540e\u7528–force\u53c2\u6570\u6062\u590d\u6574\u4e2amysqldump\u6587\u4ef6\u3002<\/p>\n

On restoring a single table from mysqldump<\/a>\uff1a\u8fd9\u7bc7\u6587\u7ae0\u5219\u5bf9\u6bd4\u4e86\u4f7f\u7528grep sed \u548c\u201c\u6743\u9650\u63a7\u5236\u201d\u4e09\u79cd\u65b9\u6cd5\u7684\u901f\u5ea6\u3002<\/p>\n

\u6700\u540e\uff0c\u5982\u679c\u4e0d\u559c\u6b22mysqldump\u8fd9\u79cd\u4e00\u80a1\u8111\u7684\u5907\u4efd\u65b9\u5f0f\uff0c\u53ef\u4ee5\u8003\u8651\u8bd5\u7528mydumper\u3002<\/p>\n

OK\uff0cThat’s all.<\/p>\n

<\/embed><\/p>\n

\u5e7f\u544a\uff1a\u6211\u4eec\u5bfb\u627e\u9760\u8c31\u7684\u4eba<\/a> | \u611f\u8c22\u4f5c\u8005<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"

\u8fd9\u91cc\u4ecb\u7ecd\u4e00\u4e2a\u6700\u8fd1\u7528\u5f97\u5f88\u591a\u7684\u4e00\u4e2a\u5c0f\u5de5\u5177\uff1atbdba-restore-mysqldump.pl\u3002 \u4e3b\u8981\u6709\u4e24\u4e2a\u529f\u80fd\uff1a (1) \u5c3d\u53ef\u80fd\u5feb\u7684\u4ece\u4e00\u4e2a\u975e\u5e38\u5927\u7684mysqldump\u6587\u4ef6\u7684\u5206\u79bb\u51fa\u67d0\u4e2a\u5355\u8868\u7684\u5907\u4efd\u6587\u4ef6 (2) \u53ef\u4ee5\u5e2e\u4f60\u628a\u4e00\u4e2a\u5927\u7684mysqldump\u6587\u4ef6\uff0c\u5207\u5272\u6210\u975e\u5e38\u5c0f\u7684\u5355\u8868\u5907\u4efd\u6587\u4ef6\uff08\u53ef\u7ee7\u7eed\u505a\u5e76\u884c\u6062\u590d\uff09 1. \u4ec0\u4e48\u65f6\u5019\u9700\u8981\u8fd9\u4e48\u505a (1) \u5982\u679c\u628aMySQL\u4e2d\u67d0\u4e00\u4e2a\u8868\u6570\u636e\u5f04\u4e22\u4e86\uff0c\u9700\u8981\u4ece\u5f88\u5927\u7684mysqldump\u5907\u4efd\u6587\u4ef6\u4e2d\u6062\u590d\u8fd9\u4e2a\u8868 (2) \u5982\u679c\u4f60\u60f3\u5e76\u884c\u6062\u590d\u6574\u4e2amysqldump\u5907\u4efd\u6587\u4ef6\u65f6\uff0c\u8fd9\u4e2a\u811a\u672c\u53ef\u4ee5\u5e2e\u4f60\u628a\u5927\u6587\u4ef6\u5207\u5272\u6210\u591a\u4e2a\u5c0f\u7684\u5355\u8868\u5907\u4efd\u6587\u4ef6\uff0c\u7136\u540e\u5c31\u53ef\u4ee5\u65b9\u4fbf\u5e76\u884c\u6062\u590d\u591a\u4e2a\u6587\u4ef6\u4e86 2. \u5982\u4f55\u4f7f\u7528\u8fd9\u4e2a\u811a\u672c \u8fd9\u91cc\u4ee5\u5b9e\u4f8b\u7684\u65b9\u5f0f\u4ecb\u7ecd\u5982\u4f55\u4f7f\u7528\u8be5\u811a\u672c\uff1a (1) \u4ecebackup.sql\u6587\u4ef6\u4e2d\u83b7\u53d6\u8868process\u7684\u5907\u4efd\uff1a tbdba-restore-mysqldump.pl -t process -f backup.sql (2) \u4ecebackup.sql\u6587\u4ef6\u4e2d\u83b7\u53d6\u6570\u636e\u5e93monitor\u4e2d\u7684\u8868process\u7684\u5907\u4efd\uff1a tbdba-restore-mysqldump.pl -t process -s monitor -f backup.sql<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_eb_attr":"","inline_featured_image":false,"_tocer_settings":[],"footnotes":""},"categories":[6],"tags":[8,10],"class_list":["post-3364","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-mysqldump","tag-10"],"_links":{"self":[{"href":"https:\/\/www.orczhou.com\/index.php\/wp-json\/wp\/v2\/posts\/3364","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.orczhou.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.orczhou.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.orczhou.com\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.orczhou.com\/index.php\/wp-json\/wp\/v2\/comments?post=3364"}],"version-history":[{"count":19,"href":"https:\/\/www.orczhou.com\/index.php\/wp-json\/wp\/v2\/posts\/3364\/revisions"}],"predecessor-version":[{"id":3387,"href":"https:\/\/www.orczhou.com\/index.php\/wp-json\/wp\/v2\/posts\/3364\/revisions\/3387"}],"wp:attachment":[{"href":"https:\/\/www.orczhou.com\/index.php\/wp-json\/wp\/v2\/media?parent=3364"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.orczhou.com\/index.php\/wp-json\/wp\/v2\/categories?post=3364"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.orczhou.com\/index.php\/wp-json\/wp\/v2\/tags?post=3364"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}