{"id":926,"date":"2016-06-30T17:04:56","date_gmt":"2016-06-30T09:04:56","guid":{"rendered":"http:\/\/cn.hostease.com\/xueyuan\/?p=926"},"modified":"2024-11-15T11:30:11","modified_gmt":"2024-11-15T03:30:11","slug":"%e8%af%af%e5%88%a0mysql%e8%a1%a8%e7%89%a9%e7%90%86%e6%96%87%e4%bb%b6%e7%9a%84%e8%a7%a3%e5%86%b3%e6%96%b9%e6%b3%95","status":"publish","type":"post","link":"https:\/\/cn.hostease.com\/xueyuan\/jishu\/shujuku\/%e8%af%af%e5%88%a0mysql%e8%a1%a8%e7%89%a9%e7%90%86%e6%96%87%e4%bb%b6%e7%9a%84%e8%a7%a3%e5%86%b3%e6%96%b9%e6%b3%95\/","title":{"rendered":"\u8bef\u5220mysql\u8868\u7269\u7406\u6587\u4ef6\u7684\u89e3\u51b3\u65b9\u6cd5"},"content":{"rendered":"
\n
    \n
  1. \u8be5\u65b9\u6cd5\u53ea\u4ecb\u7ecd\u4e86\u5982\u4f55\u6551\u56de\u8fd9\u4e2a\u8868\u540d(\u6570\u636e\u4e0d\u6062\u590d) \u5982\u679c\u60f3\u8981\u6062\u590d\u539f\u6765\u6570\u636e \u76f4\u63a5\u7528extundelete\u628a\u6587\u4ef6\u6062\u590d\u540e\u653e\u56de\u53bb\u5373\u53ef<\/li>\n
  2. \u00a0\u5e76\u4e14\u662f\u9002\u7528\u4e8e\u5e73\u65f6\u6ca1\u6709\u5168\u5907\u7684\u60c5\u51b5\u4e0b \u00a0\u5982\u679c\u6709\u5168\u5907 \u76f4\u63a5\u90a3\u5168\u5907\u7684frm\u548cidb\u6587\u4ef6\u653e\u56de\u53bb \u5c31\u53ef\u4ee5\u4e86<\/li>\n
  3. \u00a0\u8be5\u65b9\u6cd5\u540c\u6837\u9002\u7528\u4e8e\u6570\u636e\u8868\u8fc1\u79fb(\u53ea\u8fc1\u79fb\u4e00\u4e2a\u8868) \u00a0\u56e0\u4e3adiscard\u518dimport\u7684\u901f\u5ea6 \u8fdc\u6bd4\u5148dump\u518d\u6062\u590d\u7684\u901f\u5ea6\u8981\u5feb\u5f97\u591a<\/li>\n<\/ol>\n

    \u5efa\u8bae: \u5e73\u65f6\u5907\u4efd\u4e00\u4e0b\u8868\u7ed3\u6784\u662f\u975e\u5e38\u91cd\u8981\u7684\u00a0<\/strong><\/p>\n

    — \u5982\u679c\u4f60\u76f4\u63a5\u5220\u9664\u4e86mysql\u7684\u8868\u6587\u4ef6 (.frm .idb) \u00a0\u5728mysql5.6 \u53ef\u80fd\u4f60\u5c31\u60b2\u5267\u4e86 \u00a0\u53ef\u80fd\u518d\u4e5f\u7528\u4e0d\u56de\u8fd9\u4e2a\u8868\u540d\u4e86<\/p>\n<\/div>\n

    \u4f8b\u5b50\u5982\u4e0b<\/b><\/h3>\n
    — \u5168\u5728datadir\u76ee\u5f55\u4e0b\u64cd\u4f5c<\/div>\n
    <\/div>\n
    — \u76f4\u63a5\u5220\u9664\u4e86\u8868 tracking20160501\u7684\u7269\u7406\u6587\u4ef6<\/div>\n
    <\/div>\n
    \n
    \n
    \n
    <\/div>\n
    \n\n\n\n
    \n
    \n
    1<\/div>\n
    2<\/div>\n<\/div>\n<\/td>\n
    		\n
    \n
    rm<\/span>\u00a0–<\/span>rf<\/span>\u00a0tracking20160501<\/span>.<\/span>*<\/span><\/div>\n
    —<\/span> \u5220\u9664\u4e86\u8868tracking20160501<\/span>\u7684frm<\/span>\u6587\u4ef6\u548cidb<\/span>\u6587\u4ef6<\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n
    — \u6b64\u65f6\u5728\u6570\u636e\u5e93\u5df2\u7ecf\u770b\u4e0d\u5230\u8be5\u8868<\/div>\n
    \n
    \n
    \n
    <\/div>\n
    \n\n\n\n
    \n
    \n
    1<\/div>\n
    2<\/div>\n<\/div>\n<\/td>\n
    		\n
    \n
    mysql<\/span>><\/span> show<\/span>\u00a0tables<\/span>;<\/span><\/div>\n
    —<\/span> \u67e5\u770b\u6570\u636e\u5e93\u8868<\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n
    — \u4f46\u82e5\u60f3\u518d\u521b\u5efa\u8be5\u8868\u6216\u5220\u9664\u8be5\u8868 \u00a0\u4e5f\u8bb8\u5c31\u60b2\u5267\u4e86<\/div>\n
    \n
    \n
    \n
    <\/div>\n
    \n\n\n\n
    \n
    \n
    1<\/div>\n
    2<\/div>\n
    3<\/div>\n
    4<\/div>\n
    5<\/div>\n
    6<\/div>\n<\/div>\n<\/td>\n
    		\n
    \n
    mysql<\/span>><\/span> create <\/span>table <\/span>tracking20160501<\/span>(<\/span>id <\/span>int<\/span>)<\/span>;<\/span><\/div>\n
    ERROR<\/span> 1050<\/span> (<\/span>42S01<\/span>)<\/span>:<\/span> Table<\/span> ‘tracking20160501’<\/span> already <\/span>exists<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span><\/div>\n
    —<\/span> \u660e\u660e\u5df2\u7ecf\u770b\u4e0d\u5230\u8be5\u8868\u4e86 \u5374\u663e\u793a\u8868\u5df2\u5b58\u5728<\/div>\n
    mysql<\/span>><\/span> drop <\/span>table <\/span>tracking20160501<\/span>;<\/span><\/div>\n
    ERROR<\/span> 1051<\/span> (<\/span>42S02<\/span>)<\/span>:<\/span> Unknown <\/span>table<\/span> ‘kdnet_analyze.tracking20160501’<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span><\/div>\n
    —<\/span> \u60b2\u5267\u4e86\u5427 \u521b\u5efa\u4e0d\u5230\u4e5f\u5220\u4e0d\u5230\u3002\u3002<\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n
    <\/div>\n
    — \u67e5\u770b\u4e00\u4e0b\u73b0\u5728\u7684\u7269\u7406\u6587\u4ef6\u60c5\u51b5<\/div>\n
    \n
    \n
    \n
    <\/div>\n
    \n\n\n\n
    \n
    \n
    1<\/div>\n
    2<\/div>\n
    3<\/div>\n<\/div>\n<\/td>\n
    		\n
    \n
    ls<\/span>\u00a0tracking20160501<\/span>.<\/span>*<\/span><\/div>\n
    tracking20160501<\/span>.<\/span>ibd<\/span><\/div>\n
    \u00a0—<\/span> \u4e4b\u524d\u5220\u9664\u4e86\u7684\u8868\u7a7a\u95f4\u6587\u4ef6 \u4ed6\u81ea\u5df1\u53c8\u521b\u5efa\u4e86\u4e2a\u51fa\u6765 \u00a0\u53ef\u80fd\u662f\u521a\u521a\u7684create <\/span>table<\/span>\u547d\u4ee4\u5bfc\u81f4\u7684 \u8fd9\u91cc\u4e0d\u7528\u7406<\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n
    \u539f\u56e0: \u7531\u4e8e\u76f4\u63a5\u5220\u9664\u4e86\u8868\u7684\u7269\u7406\u6587\u4ef6 \u4f46mysql\u7684\u4fe1\u606f\u5e93 information_schema \u6216 mysql \u5e93\u5bf9\u8be5\u8868\u7684\u4fe1\u606f\u8fd8\u5b58\u5728(\u5177\u4f53\u8bb0\u5728\u54ea\u91cc \u8fd8\u6ca1\u627e\u51fa\u6765) \u5bfc\u81f4mysql\u8fd8\u8ba4\u4e3a\u8be5\u8868\u5b58\u5728 \u6240\u4ee5\u521b\u5efa\u4e0d\u4e86 \u5220\u9664\u8868\u65f6\u7531\u4e8e\u53c8\u627e\u4e0d\u5230\u5bf9\u5e94\u7684\u7269\u7406\u6587\u4ef6 \u6240\u4ee5\u4e5f\u5220\u9664\u4e0d\u4e86 \u00a0\u8fd9\u6837\uff01\uff01 \u96be\u9053\u8fd9\u4e2a\u8868\u540d\u5c31\u65e0\u6cd5\u518d\u7528\u4e86\u5417\uff1f \u00a0\u6709\u89e3\u51b3\u65b9\u6cd5 \u5982\u4e0b<\/div>\n

    \u89e3\u51b3\u65b9\u6cd5:\u00a0<\/b><\/h3>\n
    — \u627e\u5176\u4ed6\u8868(\u6700\u597d\u662f\u8868\u7ed3\u6784\u4e00\u6837\u7684) \u8fd9\u91cc\u627e\u7684\u8868\u53ebip_taobao \u5148\u590d\u5236\u8fd9\u4e2a\u8868\u7684.frm(\u8868\u7ed3\u6784)\u6587\u4ef6 \u6539\u540d\u4e3a\u8bef\u5220\u7684\u8868\u540d<\/div>\n
    \n
    \n
    \n
    <\/div>\n
    \n\n\n\n
    \n
    \n
    1<\/div>\n
    2<\/div>\n<\/div>\n<\/td>\n
    		\n
    \n
    cp<\/span>\u00a0–<\/span>a<\/span>\u00a0ip_taobao<\/span>.<\/span>frm<\/span>\u00a0tracking20160501<\/span>.<\/span>frm<\/span><\/div>\n
    —<\/span> \u8fd9\u91cc\u4e3a\u4e86\u4fdd\u6301mysql<\/span>\u6587\u4ef6\u7684\u62e5\u6709\u4eba\u548c\u6240\u5c5e\u7ec4 \u6240\u4ee5\u4f7f\u7528–<\/span>a<\/span>\u53c2\u6570<\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n
    — \u5982\u679c\u4e0b\u9762\u7684\u64cd\u4f5c\u6709\u4ec0\u4e48\u5947\u8469\u95ee\u9898 \u53ef\u4ee5\u91cd\u542f\u4e00\u4e0b\u6570\u636e\u5e93<\/div>\n
    <\/div>\n
    — \u5728mysql\u91cc \u4f7f\u7528discard space\u547d\u4ee4 \u5e9f\u5f03\u8bef\u5220\u8868\u7684\u8868\u7a7a\u95f4\u6587\u4ef6<\/div>\n
    \n
    \n
    \n
    <\/div>\n
    \n\n\n\n
    \n
    \n
    1<\/div>\n<\/div>\n<\/td>\n
    		\n
    \n
    alter<\/span>\u00a0table<\/span>\u00a0tracking20160501<\/span>\u00a0discard<\/span>\u00a0tablespace<\/span>;<\/span><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n
    — \u518d\u590d\u5236ip_taobao\u8868\u7684\u8868\u7a7a\u95f4\u6587\u4ef6 \u6539\u540d\u4e3a\u8bef\u5220\u7684\u8868\u540d<\/div>\n
    \n
    \n
    \n
    <\/div>\n
    \n\n\n\n
    \n
    \n
    1<\/div>\n<\/div>\n<\/td>\n
    		\n
    \n
    cp<\/span>\u00a0–<\/span>a<\/span>\u00a0ip_taobao<\/span>.<\/span>ibd<\/span>\u00a0tracking20160501<\/span>.<\/span>ibd<\/span> \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0—<\/span> \u540c\u6837\u4f7f\u7528–<\/span>a<\/span> \u4fdd\u6301\u62e5\u6709\u4eba\u548c\u6240\u5c5e\u7ec4<\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

    — \u5728mysql\u91cc \u4f7f\u7528import space\u547d\u4ee4 \u5bfc\u5165\u65b0\u7684\u8868\u7a7a\u95f4\u6587\u4ef6<\/p>\n<\/div>\n<\/div>\n

    \n
    \n
    <\/div>\n
    \n\n\n\n
    \n
    \n
    1<\/div>\n
    2<\/div>\n<\/div>\n<\/td>\n
    		\n
    \n
    mysql<\/span>><\/span>\u00a0alter<\/span>\u00a0table<\/span>\u00a0tracking20160501<\/span>\u00a0import<\/span>\u00a0tablespace<\/span>;<\/span> \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 —<\/span> \u5bfc\u5165\u65f6\u95f4\u53ef\u80fd\u4f1a\u957f\u70b9<\/div>\n
    Query<\/span>\u00a0OK<\/span>,<\/span>\u00a00<\/span>\u00a0rows<\/span>\u00a0affected<\/span>,<\/span>\u00a05<\/span>\u00a0warnings<\/span>\u00a0(<\/span>7<\/span>\u00a0min<\/span>\u00a036.94<\/span>\u00a0sec<\/span>)<\/span><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n<\/div>\n
    — \u73b0\u5728\u53ef\u4ee5\u6b63\u5e38\u67e5\u8be2\u3001\u5220\u9664\u548c\u521b\u5efa\u56de\u8fd9\u4e2a\u8868\u4e86<\/div>\n
    \n
    \n
    <\/div>\n
    \n\n\n\n
    \n
    \n
    1<\/div>\n
    2<\/div>\n
    3<\/div>\n<\/div>\n<\/td>\n
    		\n
    \n
    mysql<\/span>><\/span> select *<\/span> from <\/span>tracking20160501 <\/span>limit<\/span> 1<\/span><\/div>\n
    mysql<\/span>><\/span> drop <\/span>table <\/span>tracking20160501<\/span>;<\/span><\/div>\n
    mysql<\/span>><\/span> create <\/span>table <\/span>tracking20160501<\/span>(<\/span>id <\/span>int<\/span>)<\/span>;<\/span><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

    \u8be5\u65b9\u6cd5\u53ea\u4ecb\u7ecd\u4e86\u5982\u4f55\u6551\u56de\u8fd9\u4e2a\u8868\u540d(\u6570\u636e\u4e0d\u6062\u590d) \u5982\u679c\u60f3\u8981\u6062\u590d\u539f\u6765\u6570\u636e \u76f4\u63a5\u7528extundelete\u628a\u6587\u4ef6\u6062\u590d\u540e\u653e … \u9605\u8bfb\u66f4\u591a<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[10],"tags":[45,1467],"class_list":["post-926","post","type-post","status-publish","format-standard","hentry","category-shujuku","tag-mysql"],"aioseo_notices":[],"jetpack_featured_media_url":"","jetpack-related-posts":[],"jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/cn.hostease.com\/xueyuan\/wp-json\/wp\/v2\/posts\/926","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/cn.hostease.com\/xueyuan\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/cn.hostease.com\/xueyuan\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/cn.hostease.com\/xueyuan\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/cn.hostease.com\/xueyuan\/wp-json\/wp\/v2\/comments?post=926"}],"version-history":[{"count":2,"href":"https:\/\/cn.hostease.com\/xueyuan\/wp-json\/wp\/v2\/posts\/926\/revisions"}],"predecessor-version":[{"id":931,"href":"https:\/\/cn.hostease.com\/xueyuan\/wp-json\/wp\/v2\/posts\/926\/revisions\/931"}],"wp:attachment":[{"href":"https:\/\/cn.hostease.com\/xueyuan\/wp-json\/wp\/v2\/media?parent=926"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/cn.hostease.com\/xueyuan\/wp-json\/wp\/v2\/categories?post=926"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/cn.hostease.com\/xueyuan\/wp-json\/wp\/v2\/tags?post=926"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}