— \u5907\u4efd\u57fa\u7840\u77e5\u8bc6;
\n1.\u6700\u91cd\u8981\u7684\u5907\u4efd\u539f\u56e0:
\n 1.\u5b8c\u6574\u7cfb\u7edf\u6062\u590d:\u5982\u679c\u7cfb\u7edf\u53d1\u751f\u6545\u969c,\u5219\u62e5\u6709\u7cfb\u7edf\u7684\u5907\u4efd\u81f3\u5173\u91cd\u8981,\u56e0\u4e3a\u53ef\u4ee5\u6062\u590d\u7cfb\u7edf;\u5b9e\u65bd\u600e\u6837\u7684\u5907\u4efd\u548c\u6062\u590d\u7b56\u7565\u53d6\u51b3\u4e8e\u88ab\u6062\u590d\u6570\u636e\u8981\u8fbe\u5230\u7684\u5b8c\u6574\u6027\u548c\u65f6\u6548\u6027;
\n 2.\u5ba1\u8ba1\u529f\u80fd:\u5bf9\u4e8e\u67d0\u4e9b\u7cfb\u7edf\u53ca\u5173\u8054\u7684\u6d41\u7a0b,\u53ef\u80fd\u9700\u8981\u5ba1\u8ba1\u6216\u5206\u6790\u72ec\u7acb\u4e8e\u4e3b\u751f\u4ea7\u73af\u5883\u7684\u73af\u5883\u4e2d\u7684\u6570\u636e;\u53ef\u4ee5\u4f7f\u7528\u5907\u4efd\u521b\u5efa\u8fd9\u6837\u4e00\u4e2a\u72ec\u7acb\u7684\u73af\u5883;
\n 3.\u5e38\u89c1DBA\u4efb\u52a1:\u5728\u9700\u8981\u6267\u884c\u5e38\u89c1DBA\u4efb\u52a1(\u4f8b\u5982\u5c06\u6570\u636e\u4ece\u4e00\u4e2a\u7cfb\u7edf\u4f20\u8f93\u5230\u53e6\u4e00\u4e2a\u7cfb\u7edf,\u6839\u636e\u7279\u5b9a\u7684\u751f\u4ea7\u670d\u52a1\u5668\u72b6\u6001\u521b\u5efa\u5f00\u53d1\u670d\u52a1\u5668,\u6216\u8005\u5c06\u7cfb\u7edf\u7684\u7279\u5b9a\u90e8\u5206\u6062\u590d\u5230\u7528\u6237\u51fa\u9519\u524d\u7684\u67d0\u4e2a\u72b6\u6001)\u65f6\u4f7f\u7528\u5907\u4efd;
\n2.\u5907\u4efd\u7c7b\u578b:
\n 1.\u70ed\u5907\u4efd:\u8fd9\u4e9b\u52a8\u6001\u5907\u4efd\u5728\u8bfb\u53d6\u6216\u4fee\u6539\u6570\u636e\u7684\u8fc7\u7a0b\u4e2d\u8fdb\u884c,\u5f88\u5c11\u4e2d\u65ad\u6216\u8005\u4e0d\u4e2d\u65ad\u4f20\u8f93\u6216\u5904\u7406\u6570\u636e\u7684\u529f\u80fd;\u4f7f\u7528\u70ed\u5907\u4efd\u65f6,\u7cfb\u7edf\u4ecd\u53ef\u4f9b\u8bfb\u53d6\u548c\u4fee\u6539\u6570\u636e\u7684\u64cd\u4f5c\u8bbf\u95ee;
\n 2.\u51b7\u5907\u4efd:\u8fd9\u4e9b\u5907\u4efd\u5728\u7528\u6237\u4e0d\u80fd\u8bbf\u95ee\u6570\u636e\u65f6\u8fdb\u884c,\u56e0\u6b64\u65e0\u6cd5\u8bfb\u53d6\u6216\u4fee\u6539\u6570\u636e;\u8fd9\u4e9b\u8131\u673a\u5907\u4efd\u4f1a\u963b\u6b62\u6267\u884c\u4efb\u4f55\u4f7f\u7528\u6570\u636e\u7684\u6d3b\u52a8,\u8fd9\u4e9b\u7c7b\u578b\u7684\u5907\u4efd\u4e0d\u4f1a\u5e72\u6270\u6b63\u5e38\u8fd0\u884c\u7684\u7cfb\u7edf\u7684\u6027\u80fd;\u4f46\u662f,\u5bf9\u4e8e\u67d0\u4e9b\u5e94\u7528\u7a0b\u5e8f,\u4f1a\u65e0\u6cd5\u63a5\u53d7\u5fc5\u987b\u5728\u4e00\u6bb5\u8f83\u957f\u7684\u65f6\u95f4\u91cc\u9501\u5b9a\u6216\u5b8c\u5168\u963b\u6b62\u7528\u6237\u8bbf\u95ee\u6570\u636e;
\n 3.\u6e29\u5907\u4efd:\u8fd9\u4e9b\u5907\u4efd\u5728\u8bfb\u53d6\u6570\u636e\u65f6\u8fdb\u884c,\u4f46\u5728\u591a\u6570\u60c5\u51b5\u4e0b,\u5728\u8fdb\u884c\u5907\u4efd\u65f6\u4e0d\u80fd\u4fee\u6539\u6570\u636e\u672c\u8eab;\u8fd9\u79cd\u4e2d\u9014\u5907\u4efd\u7c7b\u578b\u7684\u4f18\u70b9\u662f\u4e0d\u5fc5\u5b8c\u5168\u9501\u5b9a\u6700\u7ec8\u7528\u6237,\u4f46\u662f,\u5176\u4e0d\u8db3\u4e4b\u5904\u5728\u4e8e\u65e0\u6cd5\u5728\u8fdb\u884c\u5907\u4efd\u65f6\u4fee\u6539\u6570\u636e\u96c6,\u8fd9\u53ef\u80fd\u4f7f\u8fd9\u79cd\u7c7b\u578b\u7684\u5907\u4efd\u4e0d\u9002\u7528\u4e8e\u67d0\u4e9b\u5e94\u7528\u7a0b\u5e8f;\u5728\u5907\u4efd\u8fc7\u7a0b\u4e2d\u65e0\u6cd5\u4fee\u6539\u6570\u636e\u53ef\u80fd\u4ea7\u751f\u6027\u80fd\u95ee\u9898;
\n3.\u78c1\u76d8:
\n 1.\u53ef\u4ee5\u4f7f\u7528\u590d\u5236\u6216RAID\u955c\u50cf\u4e4b\u7c7b\u6d41\u7a0b,\u6216\u8005\u4f7f\u7528DRBD\u4e4b\u7c7b\u7684\u5916\u90e8\u5e94\u7528\u7a0b\u5e8f,\u5c06\u6570\u636e\u76f4\u63a5\u5907\u4efd\u5230\u5176\u4ed6\u78c1\u76d8;
\n 2.\u8fd9\u4e9b\u6280\u672f\u63d0\u4f9b\u5b9e\u65f6(\u6216\u51e0\u4e4e\u5b9e\u65f6)\u5907\u4efd,\u4ee5\u53ca\u5feb\u901f\u6062\u590d\u6570\u636e\u7684\u65b9\u6cd5;
\n4.\u4e8c\u8fdb\u5236\u65e5\u5fd7:
\n 1.\u4e8c\u8fdb\u5236\u65e5\u5fd7\u8bb0\u5f55\u5bf9\u6570\u636e\u7684\u4fee\u6539;\u56e0\u6b64,\u4e8c\u8fdb\u5236\u65e5\u5fd7\u5bf9\u6062\u590d\u81ea\u4e0a\u6b21\u5b8c\u6574\u5907\u4efd\u4ee5\u6765\u53d1\u751f\u7684\u4e8b\u4ef6\u5f88\u6709\u7528;
\n 2.\u5907\u4efd\u4e8c\u8fdb\u5236\u65e5\u5fd7\u7684\u4f18\u70b9\u662f\u5176\u4e2d\u5305\u542b\u4e86\u5404\u4e2a\u65f6\u95f4\u5bf9\u6570\u636e\u6240\u505a\u7684\u6240\u6709\u66f4\u6539\u7684\u8bb0\u5f55,\u800c\u4e0d\u662f\u6570\u636e\u7684\u5feb\u7167;
\n 3.\u53ef\u4ee5\u6309\u987a\u5e8f\u521b\u5efa\u591a\u4e2a\u4e8c\u8fdb\u5236\u65e5\u5fd7\u5907\u4efd,\u6839\u636e\u4fee\u6539\u7684\u6570\u636e\u91cf\u4ee5\u53ca\u5b8c\u6210\u5b8c\u6574\u5907\u4efd\u7684\u9891\u7387,\u51b3\u5b9a\u8981\u5728\u5907\u4efd\u4e4b\u95f4\u521b\u5efa\u7684\u4e8c\u8fdb\u5236\u65e5\u5fd7\u5907\u4efd\u7684\u6570\u91cf;
\n 4.\u4e8c\u8fdb\u5236\u65e5\u5fd7\u7684\u4e0d\u8db3\u4e4b\u5904\u662f\u5fc5\u987b\u6062\u590d\u81ea\u5e8f\u5217\u4e2d\u6700\u540e\u4e00\u6b21\u5b8c\u6574\u5907\u4efd\u4ee5\u6765\u521b\u5efa\u7684\u6240\u6709\u6309\u987a\u5e8f\u7684\u4e8c\u8fdb\u5236\u65e5\u5fd7;\u6b64\u5916,\u4ece\u7cfb\u7edf\u6545\u969c\u4e2d\u6062\u590d\u7684\u901f\u5ea6\u53ef\u80fd\u4f1a\u5f88\u6162,\u5177\u4f53\u53d6\u51b3\u4e8e\u5fc5\u987b\u6062\u590d\u7684\u4e8c\u8fdb\u5236\u65e5\u5fd7\u7684\u6570\u91cf;
\n5.\u903b\u8f91\/\u6587\u672c\u5907\u4efd:
\n 1.\u53ef\u4ee5\u4f7f\u7528mysqldump\u8fdb\u884c\u5b8c\u6574\u6570\u636e\u8f6c\u50a8,\u8fd9\u4e9b\u6570\u636e\u8f6c\u50a8\u57fa\u4e8e\u7279\u5b9a\u7684\u65f6\u95f4\u70b9,\u4f46\u662f\u662f\u6240\u6709\u5907\u4efd\u526f\u672c\u4e2d\u901f\u5ea6\u6700\u6162\u7684;
\n 2.\u4f7f\u7528mysqldump\u7684\u4f18\u70b9\u662f\u6240\u521b\u5efa\u7684\u6587\u4ef6\u662f\u7b80\u5355\u7684SQL\u811a\u672c,\u5176\u4e2d\u5305\u542b\u53ef\u5728MySQL\u670d\u52a1\u5668\u4e0a\u8fd0\u884c\u7684\u8bed\u53e5;
\n 3.\u4e0d\u8db3\u4e4b\u5904\u5728\u4e8emysqldump\u4f1a\u5728\u8f6c\u50a8\u8fc7\u7a0b\u4e2d\u9501\u5b9a\u8868,\u8fd9\u4f1a\u963b\u6b62\u7528\u6237\u5728\u5907\u4efd\u8fc7\u7a0b\u4e2d\u8bfb\u5199\u6587\u4ef6;<\/p>\n
— \u4f7f\u7528MySQL\u8fdb\u884c\u5907\u4efd;
\nMySQL\u5907\u4efd\u53ef\u4ee5\u662f\u4e0b\u5217\u5907\u4efd\u4e4b\u4e00:
\n1.\u903b\u8f91\u5907\u4efd:\u903b\u8f91\u5907\u4efd\u4f1a\u4ea7\u751f\u4e00\u4e2a\u6587\u672c\u6587\u4ef6,\u5176\u4e2d\u5305\u542b\u91cd\u6784\u6570\u636e\u5e93\u7684SQL\u8bed\u53e5;
\n2.\u7269\u7406\u5907\u4efd:\u8fd9\u662fMySQL\u6570\u636e\u5e93\u6587\u4ef6\u7684\u4e8c\u8fdb\u5236\u526f\u672c;
\n3.\u57fa\u4e8e\u5feb\u7167\u7684\u5907\u4efd;
\n4.\u57fa\u4e8e\u590d\u5236\u7684\u5907\u4efd;
\n5.\u589e\u91cf\u5907\u4efd:\u901a\u8fc7\u5237\u65b0MySQL\u4e8c\u8fdb\u5236\u65e5\u5fd7\u521b\u5efa\u7684\u5907\u4efd;<\/p>\n
— \u6d4b\u9a8c;
\nb<\/p>\n
— \u903b\u8f91(\u6587\u672c)\u5907\u4efd;
\n1.\u903b\u8f91\u5907\u4efd:
\n 1.\u5c06\u6570\u636e\u5e93\u548c\u8868\u7684\u5185\u5bb9\u8f6c\u6362\u4e3aSQL\u8bed\u53e5;
\n 2.\u53ef\u79fb\u690d:\u8fd9\u4e9bSQL\u8bed\u53e5\u5305\u542b\u91cd\u5efaMySQL\u6570\u636e\u5e93\u548c\u8868\u6240\u9700\u7684\u5168\u90e8\u4fe1\u606f,\u53ef\u4ee5\u4f7f\u7528\u8be5\u6587\u672c\u6587\u4ef6\u5728\u8fd0\u884c\u4e0d\u540c\u4f53\u7cfb\u7ed3\u6784\u7684\u5176\u4ed6\u4e3b\u673a\u4e0a\u91cd\u65b0\u88c5\u5165\u6570\u636e\u5e93;
\n 3.\u8981\u6c42MySQL\u670d\u52a1\u5668\u5728\u5907\u4efd\u671f\u95f4\u8fd0\u884c:\u56e0\u4e3a\u670d\u52a1\u5668\u5728\u521b\u5efa\u6587\u4ef6\u65f6\u8981\u8bfb\u53d6\u5907\u4efd\u7684\u8868\u7684\u7ed3\u6784\u548c\u5185\u5bb9,\u7136\u540e\u5c06\u7ed3\u6784\u548c\u6570\u636e\u8f6c\u6362\u4e3aSQL\u8bed\u53e5;
\n 4.\u53ef\u4ee5\u5907\u4efd\u672c\u5730\u548c\u8fdc\u7a0bMySQL\u670d\u52a1\u5668:\u5176\u4ed6\u7c7b\u578b\u7684\u5907\u4efd(\u539f\u59cb\u5907\u4efd)\u53ea\u80fd\u5728\u672c\u5730MySQL\u670d\u52a1\u5668\u4e0a\u6267\u884c;
\n 5.\u901a\u5e38\u6bd4\u539f\u59cb(\u4e8c\u8fdb\u5236)\u5907\u4efd\u7684\u901f\u5ea6\u6162:
\n 1.\u56e0\u4e3aMySQL\u670d\u52a1\u5668\u5fc5\u987b\u8bfb\u53d6\u8868\u5e76\u89e3\u91ca\u5176\u5185\u5bb9,\u7136\u540e,\u5c06\u8868\u5185\u5bb9\u8f6c\u6362\u6210\u78c1\u76d8\u6587\u4ef6,\u6216\u8005\u5c06\u8bed\u53e5\u53d1\u9001\u7ed9\u5ba2\u6237\u673a\u7a0b\u5e8f,\u7531\u5ba2\u6237\u673a\u7a0b\u5e8f\u5c06\u8bed\u53e5\u5199\u51fa;
\n 2.\u5728\u6062\u590d\u8fc7\u7a0b\u4e2d,\u903b\u8f91\u5907\u4efd\u901f\u5ea6\u6bd4\u539f\u59cb\u5907\u4efd\u6162;\u8fd9\u662f\u56e0\u4e3a\u6062\u590d\u7684\u65b9\u6cd5\u6267\u884c\u5355\u4e2aCREATE\u548cINSERT\u8bed\u53e5\u6765\u91cd\u65b0\u521b\u5efa\u6bcf\u4e2a\u5907\u4efd\u8868\u548c\u884c;
\n2.\u903b\u8f91\u5907\u4efd\u6587\u4ef6\u7684\u5927\u5c0f\u53ef\u80fd\u4f1a\u8d85\u8fc7\u5907\u4efd\u7684\u6570\u636e\u5e93\u5927\u5c0f;<\/p>\n
— \u7269\u7406(\u539f\u59cb\u6216\u4e8c\u8fdb\u5236)\u5907\u4efd;
\n1.\u7269\u7406\u5907\u4efd:
\n 1.\u751f\u6210\u6570\u636e\u5e93\u6587\u4ef6\u7684\u5b8c\u6574\u4e8c\u8fdb\u5236\u526f\u672c,\u8fd9\u4e9b\u526f\u672c\u4ee5\u5b8c\u5168\u76f8\u540c\u7684\u683c\u5f0f\u4fdd\u7559\u6570\u636e\u5e93;\u53ef\u4ee5\u4f7f\u7528\u6807\u51c6\u547d\u4ee4,\u5982tar\/cp\/cpio\/rsync\/xcopy;
\n 2.\u5fc5\u987b\u6062\u590d\u5230\u540c\u4e00\u4e2a\u6570\u636e\u5e93\u5f15\u64ce:\u56e0\u4e3a\u539f\u59cb\u5907\u4efd\u662f\u6570\u636e\u5e93\u6587\u4ef6\u4f4d\u7684\u5b8c\u6574\u8868\u73b0\u5f62\u5f0f;
\n 3.\u53ef\u4ee5\u5728\u4e0d\u540c\u7684\u8ba1\u7b97\u673a\u4f53\u7cfb\u7ed3\u6784\u95f4\u6062\u590d;
\n 4.\u6bd4\u903b\u8f91\u5907\u4efd\u548c\u6062\u590d\u7684\u901f\u5ea6\u5feb:\u56e0\u4e3a\u8be5\u8fc7\u7a0b\u662f\u7b80\u5355\u7684\u6587\u4ef6\u590d\u5236,\u4e0d\u9700\u8981\u4e86\u89e3\u6587\u4ef6\u7684\u5185\u90e8\u7ed3\u6784;
\n2.\u6570\u636e\u5e93\u6587\u4ef6\u5728\u5907\u4efd\u671f\u95f4\u4e0d\u80fd\u6709\u66f4\u6539:
\n 1.\u5b9e\u73b0\u8fd9\u4e00\u70b9\u7684\u65b9\u6cd5\u53d6\u51b3\u4e8e\u5b58\u50a8\u5f15\u64ce;
\n 2.\u5bf9\u4e8eInnoDB:\u9700\u8981\u5173\u95edMySQL\u670d\u52a1\u5668;
\n 3.\u5bf9\u4e8eMyISAM:\u9501\u5b9a\u8868\u4ee5\u5141\u8bb8\u8bfb\u53d6,\u4f46\u4e0d\u5141\u8bb8\u66f4\u6539;
\n 4.\u53ef\u4ee5\u4f7f\u7528\u5feb\u7167,\u590d\u5236\u6216\u4e13\u6709\u65b9\u6cd5:\u6700\u5927\u9650\u5ea6\u5730\u51cf\u5c0f\u5bf9MySQL\u548c\u5e94\u7528\u7a0b\u5e8f\u7684\u5f71\u54cd;<\/p>\n
— \u57fa\u4e8e\u5feb\u7167\u7684\u5907\u4efd;
\n1.\u521b\u5efa\u6570\u636e\u7684\u65f6\u95f4\u70b9\u201c\u526f\u672c\u201d;
\n2.\u63d0\u4f9b\u4e00\u4e2a\u903b\u8f91\u4e0a\u51bb\u7ed3\u7684\u6587\u4ef6\u7cfb\u7edf\u7248\u672c,\u53ef\u4ece\u4e2d\u8fdb\u884cMySQL\u5907\u4efd;
\n3.\u663e\u8457\u51cf\u5c11\u6570\u636e\u5e93\u548c\u5e94\u7528\u7a0b\u5e8f\u4e0d\u53ef\u7528\u7684\u65f6\u95f4\u539f\u59cb\u5907\u4efd\u901a\u5e38\u9488\u5bf9\u5feb\u7167\u526f\u672c\u8fdb\u884c;
\n4.\u5916\u90e8\u5feb\u7167\u529f\u80fd:
\n 1.\u57fa\u4e8e\u5feb\u7167\u7684\u5907\u4efd\u4f7f\u7528MySQL\u5916\u90e8\u7684\u5feb\u7167\u529f\u80fd;
\n 2.\u4f8b\u5982,\u5982\u679cMySQL\u6570\u636e\u5e93\u548c\u4e8c\u8fdb\u5236\u65e5\u5fd7\u5728\u5177\u6709\u76f8\u5e94\u6587\u4ef6\u7cfb\u7edf\u7684LVM2\u903b\u8f91\u5377\u4e0a,\u5219\u53ef\u521b\u5efa\u5feb\u7167\u526f\u672c;
\n 3.\u57fa\u4e8e\u5feb\u7167\u7684\u5907\u4efd\u6700\u9002\u5408InnoDB\u4e4b\u7c7b\u7684\u4e8b\u52a1\u5f15\u64ce,\u53ef\u4ee5\u4e3aInnoDB\u8868\u6267\u884c\u70ed\u5907\u4efd;\u5bf9\u4e8e\u5176\u4ed6\u5f15\u64ce,\u53ef\u4ee5\u6267\u884c\u6e29\u5907\u4efd;
\n5.\u53ef\u4f38\u7f29:
\n 1.\u5feb\u7167\u5907\u4efd\u662f\u53ef\u4f38\u7f29\u7684,\u56e0\u4e3a\u6267\u884c\u5feb\u7167\u6240\u9700\u7684\u65f6\u95f4\u4e0d\u4f1a\u968f\u6570\u636e\u5e93\u5927\u5c0f\u7684\u589e\u957f\u800c\u589e\u52a0;
\n 2.\u4e8b\u5b9e\u4e0a,\u4ece\u5e94\u7528\u7a0b\u5e8f\u7684\u89d2\u5ea6\u6765\u770b,\u5907\u4efd\u671f\u9650\u51e0\u4e4e\u662f\u96f6,\u4f46\u662f,\u521b\u5efa\u5feb\u7167\u540e,\u57fa\u4e8e\u5feb\u7167\u7684\u5907\u4efd\u51e0\u4e4e\u603b\u662f\u5305\u62ec\u4e00\u4e2a\u539f\u59cb\u5907\u4efd;<\/p>\n
— \u57fa\u4e8e\u590d\u5236\u7684\u5907\u4efd;
\n1.MySQL\u590d\u5236\u53ef\u7528\u4e8e\u5907\u4efd:
\n 1.\u4e3b\u670d\u52a1\u5668\u7528\u4e8e\u751f\u4ea7\u5e94\u7528\u7a0b\u5e8f;
\n 2.\u4ece\u5c5e\u670d\u52a1\u5668\u7528\u4e8e\u5907\u4efd\u76ee\u7684;
\n2.\u8fd9\u6837\u53ef\u907f\u514d\u5f71\u54cd\u751f\u4ea7\u5e94\u7528\u7a0b\u5e8f;
\n3.\u4ece\u5c5e\u670d\u52a1\u5668\u7684\u5907\u4efd\u4e3a\u903b\u8f91\u5907\u4efd\u6216\u539f\u59cb\u5907\u4efd;
\n4.\u8f83\u9ad8\u7684\u6210\u672c:\u5fc5\u987b\u6709\u53e6\u4e00\u53f0\u670d\u52a1\u5668\u548c\u5b58\u50a8\u8bbe\u5907\u7528\u4e8e\u5b58\u50a8\u6570\u636e\u5e93\u7684\u526f\u672c;
\n5.\u57fa\u4e8e\u5f02\u6b65\u590d\u5236:
\n 1.\u4ece\u5c5e\u670d\u52a1\u5668\u76f8\u5bf9\u4e8e\u4e3b\u670d\u52a1\u5668\u53ef\u80fd\u4f1a\u6709\u5ef6\u8fdf;
\n 2.\u5982\u679c\u5728\u4ece\u5c5e\u670d\u52a1\u5668\u8bfb\u53d6\u4e8c\u8fdb\u5236\u65e5\u5fd7\u4e4b\u524d\u672a\u6e05\u9664\u4e8c\u8fdb\u5236\u65e5\u5fd7,\u8fd9\u662f\u53ef\u63a5\u53d7\u7684;<\/p>\n
— \u6d4b\u9a8c;
\na<\/p>\n
— \u4e8c\u8fdb\u5236\u65e5\u5fd7\u8bb0\u5f55\u548c\u589e\u91cf\u5907\u4efd;
\n1.\u5728\u4f1a\u8bdd\u7ea7\u522b\u63a7\u5236\u4e8c\u8fdb\u5236\u65e5\u5fd7\u8bb0\u5f55,\u5fc5\u987b\u62e5\u6709SUPER\u7279\u6743:SET SQL_LOG_BIN = {0|1|ON|OFF};
\n2.\u6267\u884c\u903b\u8f91\u5907\u4efd\u6216\u539f\u59cb\u5907\u4efd\u65f6\u5237\u65b0\u4e8c\u8fdb\u5236\u65e5\u5fd7:\u5c06\u4e8c\u8fdb\u5236\u65e5\u5fd7\u540c\u6b65\u5230\u5907\u4efd;
\n3.\u903b\u8f91\u5907\u4efd\u548c\u539f\u59cb\u5907\u4efd\u662f\u5b8c\u6574\u5907\u4efd:\u5c06\u5907\u4efd\u6240\u6709\u8868\u7684\u6240\u6709\u884c;
\n4.\u8981\u6267\u884c\u589e\u91cf\u5907\u4efd,\u9700\u590d\u5236\u4e8c\u8fdb\u5236\u65e5\u5fd7;
\n5.\u4e8c\u8fdb\u5236\u65e5\u5fd7\u53ef\u7528\u4e8e\u7ec6\u7c92\u5ea6\u7ea7\u6062\u590d:\u53ef\u4ee5\u6807\u8bc6\u5bfc\u81f4\u635f\u574f\u7684\u4e8b\u52a1\u5e76\u5728\u6062\u590d\u8fc7\u7a0b\u4e2d\u8df3\u8fc7\u8fd9\u4e9b\u4e8b\u52a1;<\/p>\n
— \u5907\u4efd\u5de5\u5177:\u6982\u8ff0;
\n1.\u903b\u8f91\u5907\u4efd\u7684SQL\u8bed\u53e5;
\n2.\u6267\u884cSQL\u8bed\u53e5(\u7528\u4e8e\u9501\u5b9a)\u4e0e\u64cd\u4f5c\u7cfb\u7edf\u547d\u4ee4(\u7528\u4e8e\u751f\u6210\u4e8c\u8fdb\u5236\u526f\u672c)\u7ec4\u5408\u7684\u539f\u59cb\u5907\u4efd;
\n3.MySQL\u7684\u5176\u4ed6\u539f\u59cb\u5907\u4efd\u5de5\u5177:
\n 1.MySQL Enterprise Backup:\u6267\u884cMySQL\u6570\u636e\u5e93\u70ed\u5907\u4efd\u64cd\u4f5c;\u8be5\u4ea7\u54c1\u7684\u8bbe\u8ba1\u76ee\u7684\u5c31\u662f\u4e3a\u4e86\u9ad8\u6548\u4e14\u53ef\u9760\u5730\u5907\u4efd\u7531InnoDB\u5b58\u50a8\u5f15\u64ce\u521b\u5efa\u7684\u8868,\u4e3a\u5b8c\u6574\u8d77\u89c1,\u8be5\u4ea7\u54c1\u8fd8\u80fd\u5907\u4efd\u5176\u4ed6\u5b58\u50a8\u5f15\u64ce\u4e2d\u7684\u8868;
\n 2.mysqldump:\u8be5\u5b9e\u7528\u7a0b\u5e8f\u6267\u884c\u903b\u8f91\u5907\u4efd,\u53ef\u4e0e\u4efb\u4f55\u6570\u636e\u5e93\u5f15\u64ce\u4e00\u8d77\u4f7f\u7528;\u53ef\u4ee5\u4f7f\u7528crontab(\u5728Linux\u548cUNIX\u4e2d)\u548cWindows\u4efb\u52a1\u8c03\u5ea6\u7a0b\u5e8f(\u5728Windows\u4e2d)\u81ea\u52a8\u8fd0\u884c\u8be5\u5b9e\u7528\u7a0b\u5e8f;mysqldump\u6ca1\u6709\u4efb\u4f55\u8ddf\u8e2a\u6216\u62a5\u544a\u5de5\u5177;
\n 3.mysqlhotcopy:\u8be5\u5b9e\u7528\u7a0b\u5e8f\u6267\u884c\u539f\u59cb\u5907\u4efd,\u4ec5\u7528\u4e8e\u4f7f\u7528MyISAM\u6216ARCHIVE\u6570\u636e\u5e93\u5f15\u64ce\u7684\u6570\u636e\u5e93;\u540d\u79f0\u6697\u6307mysqlhotcopy\u6267\u884c\u201c\u70ed\u201d\u5907\u4efd,\u5373\u4e0d\u4e2d\u65ad\u6570\u636e\u5e93\u53ef\u7528\u6027,\u4f46\u662f,\u7531\u4e8e\u5df2\u5bf9\u6570\u636e\u5e93\u8fdb\u884c\u4e86\u8bfb\u53d6\u9501\u5b9a,\u65e0\u6cd5\u5728\u5907\u4efd\u8fc7\u7a0b\u4e2d\u66f4\u6539,\u56e0\u6b64\u6700\u597d\u5c06\u5176\u63cf\u8ff0\u4e3a\u201c\u6e29\u201d\u5907\u4efd;
\n4.\u7b2c\u4e09\u65b9\u5de5\u5177;
\n 1.\u672c\u8bfe\u7a0b\u4e3b\u8981\u662f\u8bb2Oracle\u5546\u4e1a\u548c\u793e\u533a\u5de5\u5177;
\n 2.\u8865\u5145Percona\u7684Xtrabackup;<\/p>\n
— MySQL Enterprise Backup;
\n1.\u70ed\u5907\u4efd:
\n 1.\u70ed\u5907\u4efd\u662f\u5728\u6570\u636e\u5e93\u8fd0\u884c\u671f\u95f4\u6267\u884c\u7684,\u8fd9\u79cd\u7c7b\u578b\u7684\u5907\u4efd\u4e0d\u963b\u6b62\u6b63\u5e38\u7684\u6570\u636e\u5e93\u64cd\u4f5c,\u751a\u81f3\u80fd\u6355\u83b7\u5907\u4efd\u8fdb\u884c\u671f\u95f4\u53d1\u751f\u7684\u66f4\u6539;
\n 2.mysqlbackup\u662fMySQL Enterprise Backup\u4ea7\u54c1\u7684\u57fa\u672c\u547d\u4ee4\u884c\u5de5\u5177,\u5bf9\u4e8eInnoDB\u8868,\u6b64\u5de5\u5177\u53ef\u6267\u884c\u70ed\u5907\u4efd\u64cd\u4f5c;
\n2.\u6e29\u5907\u4efd:
\n 1.\u5bf9\u4e8e\u975eInnoDB\u5b58\u50a8\u5f15\u64ce,MySQL Enterprise Backup\u6267\u884c\u6e29\u5907\u4efd;\u8fd0\u884c\u975eInnoDB\u5907\u4efd\u65f6,\u53ef\u4ee5\u8bfb\u53d6\u6570\u636e\u5e93\u8868,\u4f46\u4e0d\u80fd\u4fee\u6539\u6570\u636e\u5e93;
\n3.\u589e\u91cf\u5907\u4efd:
\n 1.\u5907\u4efd\u81ea\u4e0a\u4e00\u6b21\u5907\u4efd\u4ee5\u6765\u6709\u53d8\u5316\u7684\u6570\u636e;
\n 2.\u4e3b\u8981\u7528\u4e8eInnoDB\u8868\u6216\u8005\u53ea\u8bfb\u6216\u5f88\u5c11\u66f4\u65b0\u7684\u975eInnoDB\u8868;
\n4.\u5355\u6587\u4ef6\u5907\u4efd:\u56e0\u4e3a\u53ef\u4ee5\u5c06\u5355\u6587\u4ef6\u5907\u4efd\u4f20\u8f93\u7ed9\u5176\u4ed6\u8fdb\u7a0b(\u5982\u78c1\u5e26\u5907\u4efd\u6216scp\u4e4b\u7c7b\u7684\u547d\u4ee4),\u56e0\u6b64\u53ef\u4f7f\u7528\u6b64\u6280\u672f\u5c06\u5907\u4efd\u653e\u5728\u5176\u4ed6\u5b58\u50a8\u8bbe\u5907\u6216\u670d\u52a1\u5668\u4e0a,\u4e0d\u4f1a\u5728\u539f\u59cb\u6570\u636e\u5e93\u670d\u52a1\u5668\u4e0a\u4ea7\u751f\u663e\u8457\u7684\u5b58\u50a8\u5f00\u9500;<\/p>\n
— mysqlbackup; — \u4f7f\u7528mysqlbackup\u6062\u590d\u5907\u4efd; \u8865\u5145: — mysqlbackup\u5355\u6587\u4ef6\u5907\u4efd; — \u6062\u590dmysqlbackup\u5355\u4e2a\u6587\u4ef6\u5907\u4efd; — \u6d4b\u9a8c; — mysqlhotcopy; — \u539f\u59cbInnoDB\u5907\u4efd; — \u539f\u59cbMyISAM\u548cARCHIVE\u5907\u4efd; — LVM\u5feb\u7167; \u8865\u5145:LVM\u5feb\u7167\u539f\u7406; 1.\u4f8b\u5982,\u5047\u5b9a\u6709\u4e00\u4e2a\u5377\u7ec4VG_MYSQL\u548c\u4e00\u4e2a\u903b\u8f91\u5377lv_datadir:lvcreate -s -n lv_datadirbackup -L 2G \/dev\/VG_MYSQL\/lv_datadir;\u524d\u4e00\u6761\u8bed\u53e5\u521b\u5efa\u5feb\u7167lv_datadirbackup,\u5176\u4fdd\u7559\u5927\u5c0f\u4e3a2GB; — \u539f\u59cb\u4e8c\u8fdb\u5236\u53ef\u79fb\u690d\u6027; — mysqldump; — \u4e0emysqldump\u4fdd\u6301\u4e00\u81f4; — mysqldump\u8f93\u51fa\u683c\u5f0f\u9009\u9879; — \u6062\u590dmysqldump\u5907\u4efd; \u8865\u5145: — \u5982\u4f55\u4f7f\u7528mysqldump\u5907\u4efd — \u6d4b\u9a8c; — \u5907\u4efd\u65e5\u5fd7\u548c\u72b6\u6001\u6587\u4ef6; — \u5c06\u590d\u5236\u7528\u4f5c\u5907\u4efd\u7684\u8f85\u52a9\u5de5\u5177; — \u5907\u4efd\u65b9\u6cd5\u6bd4\u8f83; — \u5907\u4efd\u7b56\u7565; — \u5904\u7406\u4e8c\u8fdb\u5236\u65e5\u5fd7\u5185\u5bb9; \u8865\u5145: — \u6d4b\u9a8c; — \u8bfe\u540e\u7ec3\u4e60;<\/p>\n \u8865\u5145: <\/orig><\/password><\/database><\/login><\/password><\/user><\/original><\/size><\/snapshot><\/backup><\/db_name><\/password><\/user><\/file><\/image_file><\/image><\/backup><\/image><\/password><\/user><\/image><\/backup><\/image><\/backup><\/image><\/password><\/user><\/cnf><\/backup><\/password><\/user><\/p>\n","protected":false},"excerpt":{"rendered":" — \u5907\u4efd\u57fa\u7840\u77e5\u8bc6; 1.\u6700\u91cd\u8981\u7684\u5907\u4efd\u539f\u56e0: 1.\u5b8c\u6574\u7cfb\u7edf\u6062\u590d:\u5982\u679c\u7cfb\u7edf\u53d1\u751f\u6545\u969c,\u5219\u62e5\u6709\u7cfb\u7edf\u7684\u5907\u4efd\u81f3 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[21],"tags":[27,31],"class_list":["post-1319","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-mysql","tag-mysql_ocp"],"_links":{"self":[{"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=\/wp\/v2\/posts\/1319","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1319"}],"version-history":[{"count":2,"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=\/wp\/v2\/posts\/1319\/revisions"}],"predecessor-version":[{"id":1331,"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=\/wp\/v2\/posts\/1319\/revisions\/1331"}],"wp:attachment":[{"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1319"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1319"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1319"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
\n\u4f7f\u7528mysqlbackup\u5907\u4efd\u7684\u539f\u59cb\u6587\u4ef6
\n1.InnoDB\u6570\u636e:
\n 1.ibdata*\u6587\u4ef6:\u5171\u4eab\u8868\u7a7a\u95f4\u6587\u4ef6;
\n 2..ibd\u6587\u4ef6:\u57fa\u4e8e\u6bcf\u4e2a\u8868\u7684\u6570\u636e\u6587\u4ef6;
\n 3.ib_logfile*\u6587\u4ef6:\u65e5\u5fd7\u6587\u4ef6,\u4eceib_logfile*\u6587\u4ef6\u63d0\u53d6\u7684\u6570\u636e(\u4ee3\u8868\u5728\u5907\u4efd\u671f\u95f4\u53d1\u751f\u7684\u66f4\u6539\u7684\u91cd\u505a\u65e5\u5fd7\u4fe1\u606f),\u5b58\u50a8\u5728\u65b0\u5907\u4efd\u6587\u4ef6ibbackup_logfile\u4e2d;
\n2.\u8981\u5305\u62ec\u7684\u6570\u636e\u76ee\u5f55\u4e2d\u7684\u6240\u6709\u6587\u4ef6:
\n 1..opt\u6587\u4ef6:\u6570\u636e\u5e93\u914d\u7f6e\u4fe1\u606f;
\n 2..TRG\u6587\u4ef6:\u89e6\u53d1\u5668\u53c2\u6570;
\n 3..MYD\u6587\u4ef6:MyISAM\u6570\u636e\u6587\u4ef6;
\n 4..MYI\u6587\u4ef6:MyISAM\u7d22\u5f15\u6587\u4ef6;
\n 5..FRM\u6587\u4ef6:\u8868\u6570\u636e\u5b57\u5178\u6587\u4ef6;
\n 6.TIPS:\u9ed8\u8ba4\u60c5\u51b5\u4e0b,mysqlbackup\u5907\u4efd\u6570\u636e\u76ee\u5f55\u4e2d\u7684\u6240\u6709\u6587\u4ef6,\u5982\u679c\u6307\u5b9a–only-known-file-types\u9009\u9879,\u5219\u5907\u4efd\u4ec5\u5305\u62ec\u5177\u6709MySQL\u516c\u8ba4\u6269\u5c55\u540d\u7684\u5176\u4ed6\u6587\u4ef6;
\n3.mysqlbackup\u662f\u4e00\u79cd\u6613\u4e8e\u4f7f\u7528\u7684\u5de5\u5177,\u9002\u7528\u4e8e\u6240\u6709\u5907\u4efd\u548c\u6062\u590d\u64cd\u4f5c:
\n 1.\u53ef\u4ee5\u4f7f\u7528mysqlbackup\u8054\u673a\u5907\u4efdInnoDB\u8868\u4ee5\u53ca\u751f\u6210\u5bf9\u5e94\u4e8e\u4e0eInnoDB\u5907\u4efd\u76f8\u540c\u7684binlog\u4f4d\u7f6e\u7684MyISAM\u8868\u7684\u5feb\u7167;
\n 2.\u9664\u4e86\u521b\u5efa\u5907\u4efd\u4ee5\u5916,mysqlbackup\u8fd8\u53ef\u4ee5\u5c06\u5907\u4efd\u6570\u636e\u6253\u5305\u548c\u89e3\u5305,\u5c06\u5728\u5907\u4efd\u64cd\u4f5c\u8fc7\u7a0b\u4e2d\u5bf9InnoDB\u8868\u6240\u505a\u7684\u4efb\u4f55\u66f4\u6539\u5e94\u7528\u4e8e\u5907\u4efd\u6570\u636e,\u4ee5\u53ca\u5c06\u6570\u636e\/\u7d22\u5f15\u548c\u65e5\u5fd7\u6587\u4ef6\u590d\u5236\u56de\u5176\u539f\u59cb\u4f4d\u7f6e;
\n4.\u5907\u4efd\u8fc7\u7a0b:
\n 1.mysqlbackup\u6253\u5f00\u5230\u8981\u6267\u884c\u5907\u4efd\u7684MySQL\u670d\u52a1\u5668\u7684\u8fde\u63a5;
\n 2.\u7136\u540e,mysqlbackup\u5bf9InnoDB\u8868\u6267\u884c\u8054\u673a\u5907\u4efd;
\n 3.\u5f53mysqlbackup\u8fd0\u884c\u51e0\u4e4e\u5b8c\u6210\u65f6,\u6267\u884cSQL\u547d\u4ee4FLUSH TABLES WITH READ LOCK,\u7136\u540e\u5c06\u975eInnoDB\u6587\u4ef6(\u5982MyISAM\u8868\u548c.frm\u6587\u4ef6)\u590d\u5236\u5230\u5907\u4efd\u76ee\u5f55;
\n 1.\u5982\u679c\u6b64\u65f6\u672a\u5728\u6570\u636e\u5e93\u4e2d\u957f\u65f6\u95f4\u8fd0\u884cSELECT\u6216\u5176\u4ed6\u67e5\u8be2,\u5219MyISAM\u8868\u5f88\u5c0f,\u9501\u5b9a\u9636\u6bb5\u4ec5\u6301\u7eed\u51e0\u79d2\u949f;
\n 2.\u5426\u5219,\u5305\u62ecInnoDB\u7c7b\u578b\u8868\u5728\u5185\u7684\u6574\u4e2a\u6570\u636e\u5e93\u90fd\u4f1a\u9501\u5b9a,\u76f4\u5230\u5728\u5907\u4efd\u4e4b\u524d\u5f00\u59cb\u7684\u6240\u6709\u957f\u65f6\u95f4\u67e5\u8be2\u5b8c\u6210;
\n 4.mysqlbackup\u8fd0\u884c\u5b8c\u6210,\u5e76\u5bf9\u8868\u6267\u884cUNLOCK\u89e3\u9501;
\n5.\u57fa\u672c\u7528\u6cd5:mysqlbackup -u
\n 1.backup:\u6267\u884c\u5907\u4efd\u521d\u59cb\u9636\u6bb5;
\n 2.backup-and-apply-log:\u5305\u62ec\u5907\u4efd\u7684\u521d\u59cb\u9636\u6bb5\u4ee5\u53ca\u7b2c\u4e8c\u4e2a\u9636\u6bb5,\u5373\u5c06InnoDB\u8868\u653e\u5230\u6700\u65b0\u7684\u5907\u4efd\u4e2d,\u5176\u4e2d\u5305\u62ec\u5728\u5907\u4efd\u8fd0\u884c\u671f\u95f4\u5bf9\u6570\u636e\u6240\u505a\u7684\u4efb\u4f55\u66f4\u6539;<\/p>\n
\n\u57fa\u672c\u7528\u6cd5:mysqlbackup –backup-dir=<\/backup>
\n1.<\/backup>
\n2.copy-back:\u6307\u793amysqlbackup\u6267\u884c\u6062\u590d\u64cd\u4f5c;
\n 1.\u6062\u590d\u64cd\u4f5c\u5c06<\/backup>
\n 2.\u4f7f\u7528copy-back\u9009\u9879\u5fc5\u987b\u5148\u5173\u95ed\u6570\u636e\u5e93\u670d\u52a1\u5668,\u7136\u540e\u624d\u80fd\u4f7f\u7528mysqlbackup\u4e0ecopy-back\u9009\u9879;\u4f7f\u7528\u6b64
\n 3.\u9009\u9879\u65f6,\u53ef\u5c06\u6570\u636e\u6587\u4ef6,\u65e5\u5fd7\u53ca\u5176\u4ed6\u5907\u4efd\u6587\u4ef6\u4ece\u5907\u4efd\u76ee\u5f55\u590d\u5236\u56de\u5230\u5176\u539f\u59cb\u4f4d\u7f6e,\u5e76\u5bf9\u5176\u6267\u884c\u4efb\u4f55\u5fc5\u9700\u7684\u540e\u671f\u5904\u7406;
\n 4.\u5728copy-back\u8fc7\u7a0b\u4e2d,mysqlbackup\u65e0\u6cd5\u4ece\u670d\u52a1\u5668\u67e5\u8be2\u5176\u8bbe\u7f6e,\u56e0\u6b64\u4ece\u6807\u51c6\u914d\u7f6e\u6587\u4ef6\u4e2d\u8bfb\u53d6datadir\u4e4b\u7c7b\u9009\u9879;
\n 5.\u5982\u679c\u8981\u6062\u590d\u5230\u4e0d\u540c\u7684\u670d\u52a1\u5668,\u5219\u53ef\u4f7f\u7528–defaults-file\u9009\u9879\u63d0\u4f9b\u975e\u6807\u51c6\u9ed8\u8ba4\u8bbe\u7f6e\u6587\u4ef6;<\/p>\n
\n— \u4f7f\u7528mysqlbackup\u5907\u4efd\u6062\u590d:
\n1.\u5b8c\u5168\u5907\u4efd\u8fd8\u539f:
\n 1.\u5907\u4efd\u6570\u636e\u5e93:
\n 1.\u521b\u5efa\u76ee\u5f55:mkdir -p \/mysqlbackup; chown -Rf mysql:mysql \/mysqlbackup;
\n 2.\u521b\u5efa\u5907\u4efd:mysqlbackup –user=root –password= –with-timestamp –backup-dir=\/mysqlbackup\/ backup-and-apply-log;
\n 2.\u8fd8\u539f\u6570\u636e\u5e93:
\n 1.\u8981\u628amysql\u670d\u52a1\u5173\u6389;
\n 2.\u4f7f\u7528\u7684\u5907\u4efd\u5fc5\u987b\u662f\u6570\u636e\u4e00\u81f4\u6027\u7684,\u5373\u6307\u5b9a\u4e86–bakcup-and-apply-log\u53c2\u6570\u7684;
\n 3.\u4f7f\u7528copy-back\u9009\u9879,\u8fd9\u4e2a\u64cd\u4f5c\u4f1a\u62f7\u8d1d\u8868\/\u7d22\u5f15\/\u5143\u6570\u636e\u548c\u5176\u5b83\u6062\u590d\u9700\u8981\u7684\u6587\u4ef6\u6062\u590d\u5230\u539f\u6765\u7684\u4f4d\u7f6e(\u5b9a\u4e49\u5728\u53c2\u6570\u6587\u4ef6\u4e2d);
\n 4.\u8fd8\u539f\u6570\u636e\u5e93:mysqlbackup –defaults-file=\/etc\/my.cnf –backup-dir=\/mysqlbackup\/2015-08-25_17-20-34\/ copy-back;
\n 5.\u8fd8\u539f\u4e4b\u540e\u68c0\u67e5\u6587\u4ef6\u7684\u6743\u9650(chown -Rf mysql:mysql data\/ logs\/),\u5e76\u5f00\u542fmysql\u670d\u52a1\u5373\u53ef;
\n 6.\u5982\u679c\u662f\u9700\u8981\u8fd8\u539f\u5230\u4e0d\u540c\u7684\u76ee\u5f55\u7684\u8bdd,\u53ea\u9700\u8981\u4fee\u6539–defaults-file\u53c2\u6570\u6307\u5b9a\u7684my.cnf\u6587\u4ef6\u5373\u53ef;
\n2.\u589e\u91cf\u5907\u4efd\u8fd8\u539f:
\n 1.\u589e\u91cf\u5907\u4efd\u5206\u4e24\u79cd\u60c5\u51b5:
\n 1.–incremental-base:\u4f7f\u7528\u8fd9\u79cd\u65b9\u5f0f,\u4e0d\u9700\u8981\u77e5\u9053\u4e24\u6b21\u5907\u4efd\u7684LSN(Log Sequence Number),\u53ea\u9700\u8981\u6307\u5b9a\u4e0a\u4e00\u6b21\u5907\u4efd(\u5b8c\u5168\u5907\u4efd\u6216\u8005\u5dee\u5f02\u5907\u4efd)\u7684\u76ee\u5f55\u5373\u53ef,mysqlbackup\u5de5\u5177\u4f1a\u4ecemetadata\u6587\u4ef6\u4e2d\u627e\u5230\u5907\u4efd\u5f00\u59cb\u7684\u4f4d\u7f6e.\u7f3a\u70b9\u662f\u5fc5\u987b\u6307\u5b9a\u4e00\u7cfb\u5217\u76ee\u5f55\u7684\u540d\u79f0,\u53ef\u4ee5\u4f7f\u7528\u5e94\u7f16\u7801\u6216\u8005\u7528shell\u5b9e\u73b0,\u7528–with-timestamp\u65f6\u9700\u8981\u5236\u5b9a\u89c4\u5219;
\n 2.–start-lsn:\u4f7f\u7528\u8fd9\u79cd\u65b9\u5f0f,\u5c31\u5fc5\u987b\u8bb0\u5f55\u4e0a\u6b21\u5907\u4efd\u7684LSN\u53f7,\u4e0d\u9700\u8981\u5173\u5fc3\u4e0a\u6b21\u5907\u4efd\u7684\u76ee\u5f55.\u7f3a\u70b9\u662f\u9700\u8981\u77e5\u9053\u4e0a\u6b21\u5907\u4efd\u7684\u540e\u7684LSN,\u53ef\u4ee5\u901a\u8fc7shell\u53bb\u83b7\u53d6,\u5907\u4efd\u7684\u76ee\u5f55\u53ef\u4ee5\u4f7f\u7528–with-timestamp\u9009\u9879;
\n 2.–incremental-base\u65b9\u5f0f\u589e\u91cf\u5907\u4efd:mysqlbackup –defaults-file=\/usr\/local\/mysql\/my.cnf –incremental –incremental-base=dir:\/mysqlbackup\/2012-07-15_17-31-55\/ –incremental-backup-dir=\/mysqlbackup\/incremental\/sunday –with-timestamp backup;
\n 3.–start-lsn\u65b9\u5f0f\u589e\u91cf\u5907\u4efd:
\n 1.\u67e5\u770b\u4e0a\u6b21\u5907\u4efd\u7ed3\u675f\u7684LSN\u53f7\u7801:\/path\/meta\/backup_variables.txt\u6587\u4ef6\u4e2dend_lsn\u8868\u793a;
\n 2.\u547d\u4ee4:mysqlbackup –defaults-file=\/usr\/local\/mysql\/my.cnf –incremental –start-lsn=xxxxx –with-timestamp –incremental-backup-dir=\/mysqlbackup\/incremental\/sunday\/ backup;
\n3.\u538b\u7f29\u5907\u4efd\u529f\u80fd:
\n 1.\u6570\u636e\u538b\u7f29\u7684\u529f\u80fd\u53ea\u9002\u7528\u4e8eInnoDb\u5f15\u64ce\u7684\u8868;
\n 2.\u538b\u7f29\u9009\u9879–compress\u53ea\u80fd\u7528\u4e8e\u5b8c\u5168\u5907\u4efd,\u4e0d\u80fd\u7528\u4e8e\u589e\u91cf\u5907\u4efd;
\n 3.\u6267\u884c\u5b8c\u5168\u5907\u4efd\u7684\u547d\u4ee4:>mysqlbackup –compress –backup-dir=\/mysqlbackup –with-timestamp backup;
\n \u7136\u540e\u6267\u884capply-log\u9009\u9879;
\n4.\u90e8\u5206\u5907\u4efd(\u652f\u6301\u4e09\u79cd\u90e8\u5206\u5907\u4efd):
\n 1.Leaving out files that are present in the MySQL data directory but not actually part of the MySQL instance. This operation involves the –only-known-file-types option;
\n 2.Including certain InnoDB tables but not others. This operation involves the –include, –only-innodb, and –only-innodb-with-frm options;
\n 3.Including certain database directories but not others. This operation involves the –databases and –databases-list-file options;
\n5.\u5355\u6587\u4ef6\u5907\u4efd:
\n 1.\u53ef\u4ee5\u901a\u8fc7\u6307\u5b9abackup-to-image\u53c2\u6570\u6765\u521b\u5efa\u5355\u6587\u4ef6\u5907\u4efd,\u6240\u6709\u7684\u6e90\u6570\u636e\u6587\u4ef6\u90fd\u5fc5\u987b\u5728\u540c\u4e00\u4e2a\u76ee\u5f55\u4e0b,\u6240\u4ee5\u5c3d\u91cf\u914d\u7f6edatadir, innodb_log_group_home_dir, and innodb_data_home_dir\u53c2\u6570\u76f8\u540c.
\n 2.\u5907\u4efd\u5355\u4e00\u6587\u4ef6\u5230\u7edd\u5bf9\u76ee\u5f55:mysqlbackup –backup-image=\/backups\/sales.mbi –backup-dir=\/backup-tmp backup-to-image;
\n6.\u5907\u4efd\u5185\u5b58\u4e2d\u6570\u636e:
\n 1.mysqlbackup\u547d\u4ee4\u7684–exec-when-locked\u9009\u9879\u53ef\u4ee5\u5728\u5907\u4efd\u7ed3\u675f\u4e4b\u524d\u6307\u5b9a\u547d\u4ee4\u6216\u8005\u53c2\u6570\u6267\u884c,\u800c\u6b64\u65f6\u6570\u636e\u5e93\u662f\u9501\u4f4f\u7684.\u8fd9\u4e2a\u547d\u4ee4\u53ef\u4ee5\u521b\u5efa\u6216\u8005\u62f7\u8d1d\u4e00\u4e2a\u9644\u52a0\u7684\u6587\u4ef6\u5230\u5907\u4efd\u76ee\u5f55;
\n 2.\u53ef\u4ee5\u7528\u6b64\u9009\u9879\u6765\u8c03\u7528mysqldump\u547d\u4ee4\u5907\u4efdMEMORY\u7c7b\u578b\u7684\u8868;<\/p>\n
\n1.\u57fa\u672c\u7528\u6cd5:
\n mysqlbackup -u
\n2.\u5176\u4ed6\u60c5\u5f62
\n 1.\u6807\u51c6\u8f93\u51fa:
\n … –backup-dir=<\/backup>
\n 2.\u5c06\u73b0\u6709\u7684\u5907\u4efd\u76ee\u5f55\u8f6c\u6362\u4e3a\u5355\u4e2a\u6587\u4ef6:
\n … –backup-dir=
\n1.\u63d0\u53d6\u9009\u62e9\u7684\u6587\u4ef6:
\n mysqlbackup -u
\n2.\u5176\u4ed6\u60c5\u5f62:
\n 1.\u5217\u51fa\u5185\u5bb9:
\n … –backup-image=
\n 2.\u5c06\u73b0\u6709\u7684\u5907\u4efd\u76ee\u5f55\u8f6c\u6362\u4e3a\u5355\u4e2a\u6587\u4ef6:
\n … –backup-image=
\n 1.–src-entry:\u786e\u5b9a\u8981\u4ece\u5355\u6587\u4ef6\u5907\u4efd\u4e2d\u63d0\u53d6\u7684\u6587\u4ef6\u6216\u76ee\u5f55;
\n 2.–dst-entry:\u4e0e\u5355\u6587\u4ef6\u5907\u4efd\u914d\u5408\u4f7f\u7528,\u5c06\u5355\u4e2a\u6587\u4ef6\u6216\u76ee\u5f55\u63d0\u53d6\u5230\u7528\u6237\u6307\u5b9a\u7684\u8def\u5f84;<\/p>\n
\nd<\/p>\n
\n1.Perl\u811a\u672c:
\n 1.\u5907\u4efdMyISAM\u548cARCHIVE\u8868;
\n 2.\u4f7f\u7528FLUSH TABLES,LOCK TABLES\u4ee5\u53cacp\u6216scp\u53ef\u4ee5\u8fdb\u884c\u6570\u636e\u5e93\u5907\u4efd;
\n 3.\u5728\u6570\u636e\u5e93\u76ee\u5f55\u6240\u5728\u7684\u540c\u4e00\u53f0\u8ba1\u7b97\u673a\u4e0a\u8fd0\u884c:\u4ee5\u4fbf\u5728\u8868\u9501\u5b9a\u671f\u95f4\u590d\u5236\u8868\u6587\u4ef6;
\n 4.\u4ec5\u9650Unix;
\n 5.MySQL\u670d\u52a1\u5668\u5fc5\u987b\u5904\u4e8e\u8fd0\u884c\u72b6\u6001:\u4ee5\u4fbf\u8fde\u63a5\u5230\u670d\u52a1\u5668;
\n 6.mysqlhotcopy\u7684\u64cd\u4f5c\u901f\u5ea6\u5f88\u5feb,\u56e0\u4e3a\u5b83\u76f4\u63a5\u590d\u5236\u8868\u6587\u4ef6,\u800c\u4e0d\u662f\u901a\u8fc7\u7f51\u7edc\u5907\u4efd\u8868\u6587\u4ef6;
\n2.\u57fa\u672c\u7528\u6cd5:mysqlhotcopy -u
\n3.\u9009\u9879:
\n 1.–flush-log:\u5728\u6240\u6709\u8868\u90fd\u9501\u5b9a\u540e\u5237\u65b0\u65e5\u5fd7;
\n 2.–record_log_pos=db_name.tbl_name:\u5728\u6307\u5b9a\u7684\u6570\u636e\u5e93db_name\u548c\u8868tbl_name\u4e2d\u8bb0\u5f55\u4e3b\u4ece\u670d\u52a1\u5668\u72b6\u6001;<\/p>\n
\n1.\u5907\u4efd\u8fc7\u7a0b:
\n 1.\u5728\u590d\u5236\u64cd\u4f5c\u671f\u95f4\u505c\u6b62\u670d\u52a1\u5668;
\n 2.\u9a8c\u8bc1\u670d\u52a1\u5668\u662f\u5426\u6b63\u5e38\u5173\u95ed,\u6ca1\u6709\u51fa\u9519;
\n 3.\u751f\u6210\u6bcf\u4e2a\u7ec4\u4ef6\u7684\u526f\u672c:
\n 1.\u6bcf\u4e2aInnoDB\u8868\u4e00\u4e2a.frm\u6587\u4ef6;
\n 2.\u8868\u7a7a\u95f4\u6587\u4ef6;
\n 1.\u7cfb\u7edf\u8868\u7a7a\u95f4;
\n 2.\u57fa\u4e8e\u6bcf\u4e2a\u8868\u7684\u8868\u7a7a\u95f4;
\n 3.InnoDB\u65e5\u5fd7\u6587\u4ef6;
\n 4.my.cnf\u6587\u4ef6;
\n 4.\u91cd\u65b0\u542f\u52a8\u670d\u52a1\u5668;
\n TIPS:\u6240\u6709\u6570\u636e\u5e93\u4e2d\u7684\u6240\u6709InnoDB\u8868\u5fc5\u987b\u4e00\u8d77\u5907\u4efd,\u56e0\u4e3aInnoDB\u4f1a\u5728\u7cfb\u7edf\u8868\u7a7a\u95f4\u4e2d\u96c6\u4e2d\u7ef4\u62a4\u67d0\u4e9b\u4fe1\u606f;
\n2.\u6062\u590d:
\n 1.\u8981\u4f7f\u7528\u539f\u59cb\u5907\u4efd\u6062\u590dInnoDB\u8868,\u8bf7\u505c\u6b62\u670d\u52a1\u5668;
\n 2.\u66ff\u6362\u5176\u526f\u672c\u5728\u5907\u4efd\u8fc7\u7a0b\u4e2d\u751f\u6210\u7684\u6240\u6709\u7ec4\u4ef6;
\n 3.\u7136\u540e\u91cd\u65b0\u542f\u52a8\u670d\u52a1\u5668;
\n TIPS:\u9700\u8981\u66ff\u6362\u670d\u52a1\u5668\u4e0a\u7684\u6240\u6709\u73b0\u5b58\u7684\u8868\u7a7a\u95f4\u6587\u4ef6,\u4e0d\u80fd\u4f7f\u7528\u539f\u59cb\u5907\u4efd\u5c06\u4e00\u4e2a\u8868\u7a7a\u95f4\u6dfb\u52a0\u5230\u53e6\u4e00\u4e2a\u8868\u7a7a\u95f4;<\/p>\n
\n1.\u8981\u751f\u6210MyISAM\u6216ARCHIVE\u8868,\u9700\u590d\u5236MySQL\u7528\u4e8e\u4ee3\u8868\u8be5\u8868\u7684\u6587\u4ef6:
\n 1.\u5bf9\u4e8eMyISAM,\u8fd9\u4e9b\u6587\u4ef6\u662f.frm,.MYD\u548c.MYI\u6587\u4ef6;
\n 2.\u5bf9\u4e8eARCHIVE\u8868,\u8fd9\u4e9b\u6587\u4ef6\u662f.frm\u548c.ARZ\u6587\u4ef6;
\n 3.\u5728\u6b64\u590d\u5236\u64cd\u4f5c\u8fc7\u7a0b\u4e2d,\u5176\u4ed6\u7a0b\u5e8f(\u5305\u62ec\u670d\u52a1\u5668)\u4e0d\u80fd\u4f7f\u7528\u8be5\u8868;
\n 4.\u4e3a\u4e86\u907f\u514d\u670d\u52a1\u5668\u4ea4\u4e92\u95ee\u9898,\u8981\u5728\u590d\u5236\u64cd\u4f5c\u8fc7\u7a0b\u4e2d\u505c\u6b62\u670d\u52a1\u5668;
\n 5.\u6ce8:\u9501\u5b9a\u8868\u800c\u4e0d\u5173\u95ed\u670d\u52a1\u5668\u7684\u505a\u6cd5\u5728Linux\u7cfb\u7edf\u4e0a\u6709\u6548,\u5728Windows\u4e0a,\u6587\u4ef6\u9501\u5b9a\u884c\u4e3a\u4f1a\u5bfc\u81f4\u53ef\u80fd\u65e0\u6cd5\u590d\u5236\u88ab\u670d\u52a1\u5668\u9501\u5b9a\u7684\u8868\u7684\u8868\u6587\u4ef6,\u5728\u8fd9\u79cd\u60c5\u51b5\u4e0b,\u9700\u8981\u505c\u6b62\u670d\u52a1\u5668\u540e\u518d\u590d\u5236\u8868\u6587\u4ef6;
\n2.\u5728\u670d\u52a1\u5668\u8fd0\u884c\u671f\u95f4,\u9501\u5b9a\u8981\u590d\u5236\u7684\u8868:
\n mysql> USE mysql
\n mysql> FLUSH TABLES users WITH READ LOCK;
\n3.\u6267\u884c\u6587\u4ef6\u7cfb\u7edf\u590d\u5236;
\n4.\u542f\u52a8\u65b0\u7684\u4e8c\u8fdb\u5236\u65e5\u5fd7\u6587\u4ef6:FLUSH LOGS;
\n 1.\u65b0\u4e8c\u8fdb\u5236\u65e5\u5fd7\u6587\u4ef6\u5305\u542b\u5728\u5907\u4efd\u4e4b\u540e\u66f4\u6539\u4e86\u6570\u636e\u7684\u6240\u6709\u8bed\u53e5(\u4ee5\u53ca\u6240\u6709\u540e\u7eed\u7684\u4e8c\u8fdb\u5236\u65e5\u5fd7\u6587\u4ef6);
\n5.\u5728\u6587\u4ef6\u7cfb\u7edf\u590d\u5236\u540e\u89e3\u9664\u9501\u5b9a:UNLOCK TABLES;
\n6.\u6062\u590d:\u8981\u4ece\u539f\u59cb\u5907\u4efd\u4e2d\u6062\u590dMyISAM\u6216ARCHIVE\u8868,\u5e94\u505c\u6b62\u670d\u52a1\u5668,\u5c06\u5907\u4efd\u8868\u6587\u4ef6\u590d\u5236\u5230\u76f8\u5e94\u7684\u6570\u636e\u5e93\u76ee\u5f55\u4e2d,\u7136\u540e\u91cd\u65b0\u542f\u52a8\u670d\u52a1\u5668;<\/p>\n
\n1.\u5728\u4ee5\u4e0b\u60c5\u51b5\u4e0b,\u4f7f\u7528LVM\u5feb\u7167\u6267\u884c\u539f\u59cb\u5907\u4efd:
\n 1.\u4e3b\u673a\u652f\u6301LVM:\u4f8b\u5982,Linux\u652f\u6301LVM2;
\n 2.\u5305\u542bMySQL\u6570\u636e\u76ee\u5f55\u7684\u6587\u4ef6\u7cfb\u7edf\u5728\u903b\u8f91\u5377\u4e0a;
\n2.\u5907\u4efd\u8fc7\u7a0b:
\n 1.\u751f\u6210\u5305\u542bMySQL\u6570\u636e\u76ee\u5f55\u7684\u903b\u8f91\u5377\u7684\u5feb\u7167:\u5728\u5907\u4efd\u975eInnoDB\u8868\u65f6,\u4f7f\u7528FLUSH TABLES WITH READ LOCK;
\n 2.\u4ece\u5feb\u7167\u6267\u884c\u539f\u59cb\u5907\u4efd;
\n 3.\u5220\u9664\u5feb\u7167;<\/p>\n
\n1.\u5728\u652f\u6301LVM\u7684\u7cfb\u7edf(\u5982Linux)\u4e0a,\u53ef\u4ee5\u521b\u5efa\u8981\u5305\u542bMySQL\u6570\u636e\u76ee\u5f55\u7684\u903b\u8f91\u5377;
\n2.\u53ef\u4ee5\u521b\u5efa\u8be5\u5377\u7684\u5feb\u7167,\u8be5\u5feb\u7167\u7684\u884c\u4e3a\u5c31\u50cf\u662f\u903b\u8f91\u5377\u7684\u5373\u65f6\u526f\u672c,LVM\u4f7f\u7528\u79f0\u4e3a\u201c\u5199\u5165\u65f6\u590d\u5236\u201d(copy-on-write)\u7684\u673a\u5236\u521b\u5efa\u6700\u521d\u4e0d\u542b\u6570\u636e\u7684\u5feb\u7167;
\n3.\u5728\u4ece\u65b0\u521b\u5efa\u7684\u5feb\u7167\u8bfb\u53d6\u6587\u4ef6\u65f6,LVM\u4f1a\u4ece\u539f\u59cb\u5377\u8bfb\u53d6\u8fd9\u4e9b\u6587\u4ef6,\u5f53\u539f\u59cb\u5377\u53d1\u751f\u53d8\u5316\u65f6,LVM\u4f1a\u5728\u539f\u59cb\u5377\u4e0a\u7684\u6570\u636e\u53d1\u751f\u53d8\u5316\u4e4b\u524d,\u7acb\u5373\u5c06\u5176\u590d\u5236\u5230\u5feb\u7167,\u56e0\u6b64,\u5728\u751f\u6210\u5feb\u7167\u4ee5\u6765\u53d1\u751f\u53d8\u5316\u7684\u4efb\u4f55\u6570\u636e\u90fd\u4ee5\u5176\u539f\u59cb\u5f62\u5f0f\u5b58\u50a8\u5728\u5feb\u7167\u4e2d;
\n\u8fd9\u6837\u505a\u7684\u7ed3\u679c\u662f,\u5f53\u4ece\u5feb\u7167\u8bfb\u53d6\u6587\u4ef6\u65f6,\u5c06\u83b7\u5f97\u5728\u521b\u5efa\u5feb\u7167\u65f6\u5b58\u5728\u7684\u6570\u636e\u7248\u672c;
\n4.\u56e0\u4e3a\u5feb\u7167\u51e0\u4e4e\u662f\u5373\u65f6\u7684,\u56e0\u6b64\u53ef\u4ee5\u5047\u5b9a\u5728\u751f\u6210\u5feb\u7167\u8fc7\u7a0b\u4e2d\u5e95\u5c42\u6570\u636e\u6ca1\u6709\u53d1\u751f\u4efb\u4f55\u53d8\u5316,\u8fd9\u4f7f\u5f97\u5feb\u7167\u5bf9\u4e8e\u5728\u4e0d\u5173\u95ed\u670d\u52a1\u5668\u7684\u60c5\u51b5\u4e0b\u5907\u4efdInnoDB\u6570\u636e\u5e93\u975e\u5e38\u6709\u7528;
\n5.\u8981\u521b\u5efa\u5feb\u7167,\u53ef\u4f7f\u7528\u4ee5\u4e0b\u8bed\u6cd5:lvcreate -s -n
\n2.\u5982\u679c\u53ea\u662f\u77ed\u65f6\u95f4\u9700\u8981\u8be5\u5feb\u7167,\u5219\u4fdd\u7559\u5927\u5c0f\u53ef\u4ee5\u6bd4\u539f\u59cb\u5377\u7684\u5927\u5c0f\u5c11\u5f88\u591a,\u56e0\u4e3a\u5feb\u7167\u7684\u5b58\u50a8\u4ec5\u5305\u542b\u5728\u539f\u59cb\u5377\u4e2d\u53d1\u751f \u66f4\u6539\u7684\u6570\u636e\u5757;
\n3.\u4f8b\u5982,\u5982\u679c\u8981\u4f7f\u7528\u5feb\u7167\u6267\u884c\u5907\u4efd,\u5219\u4fdd\u7559\u5927\u5c0f\u4ec5\u5b58\u50a8\u5728\u6267\u884c\u5907\u4efd\u4ee5\u53ca\u5220\u9664\u5feb\u7167\u7684\u65f6\u95f4\u5185\u6240\u505a\u7684\u66f4\u6539,\u53ef\u4ee5\u50cf\u6302\u8f7d\u6807\u51c6\u5377\u4e00\u6837\u6302\u8f7d\u5feb\u7167;\u6302\u8f7d\u4e86\u5feb\u7167\u540e,\u5c31\u50cf\u5904\u7406\u5176\u4ed6\u4efb\u4f55\u539f\u59cb\u5907\u4efd\u4e00\u6837,\u4ece\u8be5\u5377\u6267\u884c\u539f\u59cb\u5907\u4efd(\u4f8b\u5982,\u901a\u8fc7\u4f7f\u7528tar\u6216cp);
\n4.\u4ece\u5feb\u7167\u5907\u4efd\u65f6,\u6570\u636e\u5e93\u5728\u5907\u4efd\u8fc7\u7a0b\u4e2d\u4e0d\u80fd\u6709\u66f4\u6539,\u60a8\u80af\u5b9a\u4f1a\u83b7\u5f97\u4e0e\u5907\u4efd\u65f6\u4e00\u6837\u7684\u4e00\u81f4\u6570\u636e\u6587\u4ef6\u7248\u672c,\u65e0\u9700\u505c\u6b62\u670d\u52a1\u5668;
\n5.\u968f\u7740\u65f6\u95f4\u7684\u63a8\u79fb,\u5feb\u7167\u7684\u7a7a\u95f4\u8981\u6c42\u901a\u5e38\u4f1a\u589e\u957f\u5230\u539f\u59cb\u5377\u7684\u5927\u5c0f,\u6b64\u5916,\u5bf9\u539f\u59cb\u5377\u4e0a\u6570\u636e\u5757\u7684\u6bcf\u9879\u521d\u59cb\u6570\u636e\u66f4\u6539\u5c06\u5bfc\u81f4\u4e24\u6b21\u5411\u5377\u7ec4\u5199\u5165\u6570\u636e:\u8bf7\u6c42\u7684\u66f4\u6539\u548c\u5bf9\u5feb\u7167\u7684\u5199\u5165\u65f6\u590d\u5236;\u8fd9\u53ef\u80fd\u4f1a\u5f71\u54cd\u5feb\u7167\u4fdd\u7559\u671f\u95f4\u7684\u6027\u80fd;
\n6.\u7531\u4e8e\u4ee5\u4e0a\u539f\u56e0,\u5e94\u5728\u6267\u884c\u4e86\u5907\u4efd\u4e4b\u540e\u5c3d\u5feb\u5220\u9664\u5feb\u7167,\u8981\u5220\u9664\u7531\u524d\u4e00\u6761\u8bed\u53e5\u521b\u5efa\u7684\u5feb\u7167,\u53ef\u4f7f\u7528\u4ee5\u4e0b\u8bed\u53e5:lvremove VG_MYSQL\/lv_datadirbackup;<\/p>\n
\n1.\u53ef\u5728MySQL\u670d\u52a1\u5668\u4e4b\u95f4\u590d\u5236\u4e8c\u8fdb\u5236\u6570\u636e\u5e93:\u5f53\u5c06\u4e00\u53f0\u8ba1\u7b97\u673a\u4e0a\u751f\u6210\u7684\u4e8c\u8fdb\u5236\u5907\u4efd\u62ff\u5230\u5177\u6709\u4e0d\u540c\u4f53\u7cfb\u7ed3\u6784\u7684\u53e6\u4e00\u53f0\u8ba1\u7b97\u673a\u4e0a\u65f6,\u4e8c\u8fdb\u5236\u53ef\u79fb\u690d\u6027\u4f1a\u5f88\u6709\u7528;
\n2.InnoDB:\u6570\u636e\u5e93\u7684\u6240\u6709\u8868\u7a7a\u95f4\u548c\u65e5\u5fd7\u6587\u4ef6\u90fd\u53ef\u76f4\u63a5\u590d\u5236,\u6e90\u7cfb\u7edf\u4e0e\u76ee\u6807\u7cfb\u7edf\u4e0a\u7684\u6570\u636e\u5e93\u76ee\u5f55\u540d\u79f0\u5fc5\u987b\u76f8\u540c;
\n3.MyISAM\/ARCHIVE:\u5355\u4e2a\u8868\u7684\u6240\u6709\u6587\u4ef6\u90fd\u53ef\u76f4\u63a5\u590d\u5236;
\n4.Windows\u517c\u5bb9\u6027:
\n 1.\u5728Windows\u7cfb\u7edf\u4e0a,MySQL\u670d\u52a1\u5668\u5728\u5185\u90e8\u5b58\u50a8\u5c0f\u5199\u7684\u6570\u636e\u5e93\u548c\u8868\u540d\u79f0;
\n 2.\u5bf9\u4e8e\u533a\u5206\u5927\u5c0f\u5199\u7684\u6587\u4ef6\u7cfb\u7edf,\u53ef\u4f7f\u7528\u9009\u9879\u6587\u4ef6\u8bed\u53e5:lower_case_table_names=1;<\/p>\n
\n1.\u5c06\u8868\u5185\u5bb9\u8f6c\u50a8\u5230\u6587\u4ef6:
\n \u2013 \u6240\u6709\u6570\u636e\u5e93,\u7279\u5b9a\u6570\u636e\u5e93\u6216\u7279\u5b9a\u8868;
\n \u2013 \u5141\u8bb8\u5907\u4efd\u672c\u5730\u670d\u52a1\u5668\u6216\u8fdc\u7a0b\u670d\u52a1\u5668;
\n \u2013 \u4e0e\u5b58\u50a8\u5f15\u64ce\u65e0\u5173;
\n \u2013 \u4ee5\u6587\u672c\u683c\u5f0f\u5199\u5165:\u5305\u542b\u7528\u4e8e\u91cd\u65b0\u521b\u5efa\u8868\u7684CREATE TABLE\u548cINSERT\u8bed\u53e5\u7684SQL\u683c\u5f0f\u8f6c\u50a8\u6587\u4ef6;
\n \u2013 \u53ef\u79fb\u690d;
\n \u2013 \u5353\u8d8a\u7684\u590d\u5236\/\u79fb\u52a8\u7b56\u7565;
\n \u2013 \u9002\u7528\u4e8e\u5c0f\u89c4\u6a21\u5bfc\u51fa,\u4f46\u4e0d\u9002\u7528\u4e8e\u5b8c\u6574\u5907\u4efd\u89e3\u51b3\u65b9\u6848;
\n2.\u57fa\u672c\u7528\u6cd5:mysqldump –user=
\n1.\u4ec5\u9650–master-data\u9009\u9879:
\n 1.\u5728\u5907\u4efd\u8fc7\u7a0b\u4e2d\u9501\u5b9a\u8868;
\n 2.\u5728\u5907\u4efd\u6587\u4ef6\u4e2d\u8bb0\u5f55binlog\u4f4d\u7f6e;
\n2.–master-data\u548c–single-transaction\u9009\u9879\u4e00\u8d77\u4f7f\u7528:\u4e0d\u9501\u5b9a\u8868,\u4ec5\u4fdd\u8bc1InnoDB\u8868\u4e00\u81f4\u6027;
\n3.–lock-all-tables:\u901a\u8fc7\u9501\u5b9a\u8868\u5b9e\u73b0\u4e00\u81f4\u6027;
\n4.–flush-logs:\u542f\u52a8\u65b0\u7684\u4e8c\u8fdb\u5236\u65e5\u5fd7;<\/p>\n
\n1.\u5220\u9664\u9009\u9879:
\n 1.–add-drop-database:\u5c06\u4e00\u6761DROP DATABASE\u8bed\u53e5\u6dfb\u52a0\u5230\u6bcf\u6761CREATE DATABASE\u4e4b\u524d;
\n 2.–add-drop-table:\u5c06\u4e00\u6761DROP TABLE\u8bed\u53e5\u6dfb\u52a0\u5230\u6bcf\u6761CREATE TABLE\u8bed\u53e5\u4e4b\u524d;
\n2.\u521b\u5efa\u9009\u9879:
\n 1.–no-create-db:\u4e0d\u751f\u6210CREATE DATABASE\u8bed\u53e5;
\n 2.–no-create-info:\u4e0d\u751f\u6210CREATE TABLE\u8bed\u53e5;
\n 3.–no-data:\u521b\u5efa\u6570\u636e\u5e93\u548c\u8868\u7ed3\u6784,\u4f46\u4e0d\u8f6c\u50a8\u6570\u636e;
\n 4.–no-tablespaces:\u6307\u793aMySQL\u670d\u52a1\u5668\u4e0d\u5199\u5165\u4efb\u4f55CREATE LOGFILE GROUP\u6216CREATE TABLESPACE\u8bed\u53e5\u5230\u8f93\u51fa;
\n3.MySQL\u7f16\u7a0b\u7ec4\u4ef6:
\n 1.–routines:\u4ece\u5df2\u8f6c\u50a8\u7684\u6570\u636e\u5e93\u4e2d\u8f6c\u50a8\u5b58\u50a8\u4f8b\u7a0b(\u8fc7\u7a0b\u548c\u51fd\u6570);
\n 2.–triggers:\u8f6c\u50a8\u6bcf\u4e2a\u5df2\u8f6c\u50a8\u8868\u7684\u89e6\u53d1\u5668;
\n4.\u4e00\u4e2a\u9009\u9879\u4e2d\u7684\u6700\u9ad8\u9009\u9879(–opt):\u8fd9\u662f\u7528\u4e8e\u521b\u5efa\u9ad8\u6548\u5b8c\u6574\u7684\u5907\u4efd\u6587\u4ef6\u7684\u6700\u5e38\u7528\u9009\u9879\u7684\u5feb\u6377\u65b9\u5f0f;<\/p>\n
\n1.\u4f7f\u7528mysql\u91cd\u65b0\u88c5\u5165mysqldump\u5907\u4efd:mysql –login-path= | mysql –login-path=
\n3.mysqlimport:
\n 1.\u5982\u679c\u8c03\u7528mysqldump\u65f6\u4f7f\u7528–tab(\u4e0e–fields-terminated-by,–fields-enclosed-by\u4e00\u8d77\u4f7f\u7528)\u9009\u9879,\u5219\u5c06\u751f\u6210\u5236\u8868\u7b26\u5206\u9694\u7684\u6570\u636e\u6587\u4ef6:
\n 1.\u5305\u542bCREATE TABLE\u8bed\u53e5\u7684SQL\u6587\u4ef6;
\n 2.\u5305\u542b\u8868\u6570\u636e\u7684\u6587\u672c\u6587\u4ef6;
\n 2.\u8981\u91cd\u65b0\u88c5\u5165\u8868,\u53ef\u5c06\u4f4d\u7f6e\u66f4\u6539\u4e3a\u5907\u4efd\u76ee\u5f55,\u901a\u8fc7\u4f7f\u7528mysql\u5904\u7406.sql\u6587\u4ef6,\u7136\u540e\u4f7f\u7528mysqlimport\u88c5\u5165.tsv\u6587\u4ef6:
\n shell> cd
\n shell> mysql –login-path=
\n— mysqldump\u7684\u53c2\u6570
\n1.-A,–all-databases:\u5bfc\u51fa\u6240\u6709\u7684\u6570\u636e\u5e93,\u8ddf\u4f7f\u7528–databases\u540e\u9762\u8ddf\u4e0a\u6240\u6709\u7684\u6570\u636e\u5e93\u662f\u4e00\u6837\u7684;
\n2.–add-drop-database:\u5728\u521b\u5efa\u6570\u636e\u5e93\u524d\u6dfb\u52a0drop database\u7684\u8bed\u53e5;
\n3.–add-drop-table:\u5728\u521b\u5efa\u8868\u4e4b\u524d\u6dfb\u52a0drop table\u8bed\u53e5;
\n4.–add-locks:\u5728\u63d2\u5165\u8bed\u53e5\u524d\u52a0\u9501;
\n5.–allow-keywords:\u521b\u5efa\u7684\u5217\u5141\u8bb8\u4f7f\u7528\u5173\u952e\u5b57;
\n6.-i,–comments:\u5199\u5165\u9644\u52a0\u4fe1\u606f,\u5373\u6dfb\u52a0\u6ce8\u91ca;
\n7.-c,–complete-insert:\u4f7f\u7528\u5b8c\u5168\u63d2\u5165\u8bed\u53e5,\u4e2a\u4eba\u89c9\u5f97\u8fd8\u662f-e\u53c2\u6570\u597d,\u6570\u636e\u91cf\u5c0f\u7528-e,\u6570\u636e\u91cf\u5927\u7528-c;
\n8.-B,–databases:\u5907\u4efd\u591a\u4e2a\u6570\u636e\u5e93,\u628a\u8981\u5907\u4efd\u7684\u6570\u636e\u5e93\u8ddf\u5728\u53c2\u6570\u540e\u9762\u5373\u53ef,\u5f53\u524d\u6570\u636e\u5e93\u4e5f\u4f1a\u88ab\u5305\u6db5\u8fdb\u6765;
\n9.–delete-master-logs:\u5907\u4efd\u5b8c\u6210\u540e\u5220\u9664\u4e3b\u673a\u65e5\u5fd7,\u81ea\u52a8\u6253\u5f00–master-data\u9009\u9879;
\n10.-e,–extended-insert:\u4f7f\u7528multiple-row INSERT\u8bed\u53e5,\u5373\u4e00\u4e2ainsert\u8bed\u53e5\u540e\u9762\u6709\u591a\u4e2a\u503c\u7684\u5217\u8868,\u8fd9\u662f\u4e00\u79cd\u66f4\u9ad8\u6548\u7684\u63d2\u5165\u65b9\u5f0f;
\n11.-F,–flush-logs:\u5f00\u59cb\u5907\u4efd\u524d\u5207\u6362\u4e00\u4e0b\u65e5\u5fd7,\u5982\u679c\u4f60\u4e00\u6b21\u5907\u4efd\u591a\u4e2a\u6570\u636e\u5e93(\u4f7f\u7528–databases\u6216\u8005–all-databases\u9009\u9879\u65f6),\u5219\u5728\u5907\u4efd\u6bcf\u4e2a\u6570\u636e\u5e93\u524d\u90fd\u4f1a\u5207\u6362\u65e5\u5fd7.\u5f53\u4f7f\u7528–lock-all-tables or –master-data\u65f6,\u65e5\u5fd7\u53ea\u4f1a\u88ab\u5207\u6362\u4e00\u6b21,\u56e0\u4e3a\u6b64\u65f6\u6240\u6709\u7684\u8868\u90fd\u88ab\u9501\u4f4f,\u6570\u636e\u5e93\u4fdd\u6301\u4e00\u81f4.\u6240\u4ee5\u5f53\u4f60\u60f3\u8981\u5907\u4efd\u548c\u65e5\u5fd7\u7ec4\u5207\u6362\u540c\u65f6\u53d1\u751f\u65f6,\u8981\u7528–lock-all-tables or –master-data\u548c–flush-logs\u4e00\u8d77\u4f7f\u7528;
\n12.-h,–host=name:\u8fde\u63a5\u5230\u4e3b\u673a;-u,–user-name:\u7528\u6237\u540d;-p,–password:\u7528\u6237\u5bc6\u7801;
\n13.–ignore-table=name:\u4e0d\u5907\u4efd\u6307\u5b9a\u7684\u8868,\u5982\u679c\u8981\u6307\u5b9a\u591a\u4e2a\u8868,\u5219\u8981\u6570\u636e\u5e93\u548c\u8868\u660e\u4e00\u8d77\u6307\u5b9a,\u5982:–ignore-table=database.table;
\n14.-x,–lock-all-tables:\u4f1a\u9501\u4f4f\u6240\u6709\u6570\u636e\u5e93\u7684\u8868,\u4f1a\u5728\u5907\u4efd\u671f\u95f4\u52a0\u5168\u5c40\u53ea\u8bfb\u9501,\u81ea\u52a8\u5173\u95ed–single-transaction\u548c–lock-tables\u9009\u9879;
\n15.–master-data[=#]:\u4f7f\u5f97\u4e8c\u8fdb\u5236\u65e5\u5fd7\u7684\u4f4d\u7f6e\u548c\u548c\u540d\u79f0\u88ab\u6dfb\u52a0\u5230\u8f93\u51fa\u6587\u4ef6\u4e2d,\u5982\u679c\u7b49\u4e8e1,\u4f1a\u50cfCHANGE MASTER\u547d\u4ee4\u4e00\u6837\u6253\u5370\u5b83,\u5982\u679c\u7b49\u4e8e2,\u547d\u4ee4\u4f1a\u4ee5\u6ce8\u91ca\u7684\u5f62\u5f0f\u51fa\u73b0.\u8fd9\u4e2a\u9009\u9879\u4f1a\u6253\u5f00–lock-all-table\u9009\u9879,\u9664\u975e–single-transaction\u9009\u9879\u4e5f\u88ab\u6307\u5b9a(\u6b64\u65f6\u5168\u5c40\u53ea\u8bfb\u9501\u77e5\u4f1a\u5728\u5f00\u59cb\u5907\u4efd\u65f6\u6709\u6548),\u53ef\u4ee5\u901a\u8fc7show master status\u547d\u4ee4\u67e5\u770b\u5f53\u524d\u65e5\u5fd7\u4fe1\u606f,\u5728\u6062\u590d\u548c\u590d\u5236\u529f\u80fd\u65f6\u6709\u7528.
\n16.-n,–no-create-db:\u4e0d\u5305\u62ec\u521b\u5efa\u6570\u636e\u5e93\u7684\u8bed\u53e5;
\n17.-t,–no-create-info:\u4e0d\u5305\u62ec\u521b\u5efa\u8868\u7ed3\u6784\u8bed\u53e5;
\n18.-d,–no-data:\u53ea\u5305\u542b\u8868\u5b9a\u4e49,\u4e0d\u5305\u542b\u8868\u6570\u636e;
\n19.–order-by-primary:\u4f7f\u6bcf\u4e2a\u8868\u4e2d\u7684\u8bb0\u5f55\u6309\u7167\u4e3b\u952e\u6392\u5e8f,\u5982\u679c\u6ca1\u6709\u4e3b\u952e,\u5219\u4f7f\u7528\u7b2c\u4e00\u4e2a\u552f\u4e00\u7d22\u5f15.\u5f53\u5bfc\u51fa\u4e00\u4e2aMyISAM\u8868\u5230\u4e00\u4e2aInnoDB\u8868\u65f6\u6709\u7528,\u4f46\u662f\u4f1a\u5ef6\u957f\u5bfc\u51fa\u65f6\u95f4;
\n20.–quick:\u4e0d\u7f13\u5b58query,\u76f4\u63a5\u5bfc\u51fa\u5230\u6807\u51c6\u8f93\u51fa;
\n21.-R,–routines:\u5bfc\u51fastored routines(\u5b58\u50a8\u8fc7\u7a0b\u548c\u51fd\u6570);
\n22.–single-transaction:\u5728\u4e00\u4e2a\u4e8b\u52a1\u4e2d\u521b\u5efa\u4e00\u4e2a\u4e00\u81f4\u6027\u7684\u5feb\u7167,\u53ea\u5728\u652f\u6301\u591a\u7248\u672c\u63a7\u5236\u7684\u5f15\u64ce\u4e2d\u8d77\u4f5c\u7528,\u76ee\u524d\u53ea\u6709innodb\u5f15\u64ce.\u5f53–single-transaction\u8fdb\u7a0b\u5de5\u4f5c\u65f6,\u4e3a\u4e86\u4fdd\u6301\u6570\u636e\u4e00\u81f4\u6027,\u5219\u4e0d\u80fd\u4f7f\u7528ALTER TABLE,DROP TABLE,RENAME TABLE,TRUNCATE TABLEY\u8bed\u53e5,\u6b64\u9009\u9879\u81ea\u52a8\u5173\u95ed–lock-tables\u9009\u9879;
\n23.–opt:\u4e0e\u540c\u65f6\u6307\u5b9a–add-drop-table, –add-locks, –create-options, –quick, –extended-insert, –lock-tables, –set-charset, and –disable-keys\u76f8\u540c.\u9ed8\u8ba4\u5f00\u542f,\u8981\u5173\u95ed\u4f7f\u7528–skip-opt;
\n24.-w,–where=name:\u53ea\u5bfc\u51fa\u9009\u62e9\u7684\u8bb0\u5f55;<\/p>\n
\n1.\u975e\u4e8b\u52a1\u8868\u7684\u4e00\u81f4\u5907\u4efd:mysqldump –opt –lock-all-tables –master-data=2 -B db_name1 db_name2 > \/tmp\/backup.sql,\u5907\u4efd\u65f6\u8868\u662f\u53ea\u8bfb\u7684;
\n2.\u4e8b\u52a1\u8868\u7684\u4e00\u81f4\u5907\u4efd:mysqldump –opt –single-transaction –master-data=2 -B db_name1 db_name2 > \/tmp\/backup.sql,\u5907\u4efd\u65f6\u8868\u662f\u53ef\u8bfb\u5199\u7684;
\n3.\u53ea\u5907\u4efdroutines(\u5b58\u50a8\u8fc7\u7a0b\u548c\u51fd\u6570,-R\u53c2\u6570,\u5728information_schema.routines\u8868\u4e2d)\u548cevents(\u4f5c\u4e1a,-E\u53c2\u6570,\u5728information_schema.events\u8868\u4e2d)\u4fe1\u606f:mysqldump -n -t -d -R -E > \/tmp\/routines.sql;
\n4.\u5907\u4efd\u8868\u7ed3\u6784,\u89c6\u56fe,\u51fd\u6570,\u5b58\u50a8\u8fc7\u7a0b\u548c\u4f5c\u4e1a\u7684\u4fe1\u606f:mysqldump -d -R -E -S \/mysql\/logs\/mysqld.sock –databases db_name > \/tmp\/objects.sql;
\n5.\u5907\u4efd\u6570\u636e\u5e93test,\u5305\u62ec\u89c6\u56fe\u4fe1\u606f:mysqldump –opt –lock-all-tables -R -E –master-data=2 -B test > \/tmp\/test_backup.sql;(\u67e5\u770b\u5f53\u524d\u4e8c\u8fdb\u5236\u65e5\u5fd7\u7684\u540d\u79f0\u548c\u4f4d\u7f6e:show master logs;); <\/p>\n
\na<\/p>\n
\n1.\u4e8c\u8fdb\u5236\u65e5\u5fd7\u5b58\u50a8\u5728\u5907\u4efd\u5b8c\u6210\u540e\u6240\u505a\u7684\u66f4\u65b0;
\n2.\u670d\u52a1\u5668\u4f7f\u7528\u7684\u9009\u9879\u6587\u4ef6(my.cnf\u548cmy.ini\u6587\u4ef6):\u8fd9\u4e9b\u6587\u4ef6\u5305\u542b\u5728\u7cfb\u7edf\u5d29\u6e83\u540e\u5fc5\u987b\u6062\u590d\u7684\u914d\u7f6e\u4fe1\u606f;
\n3.\u590d\u5236\u6587\u4ef6:
\n 1.\u590d\u5236\u4ece\u5c5e\u670d\u52a1\u5668\u521b\u5efa\u4e00\u4e2a\u5305\u542b\u8fde\u63a5\u5230\u4e3b\u670d\u52a1\u5668\u6240\u9700\u4fe1\u606f\u7684master.info\u6587\u4ef6;
\n 2.\u4ee5\u53ca\u4e00\u4e2a\u6307\u793a\u5f53\u524d\u7684\u4e2d\u7ee7\u65e5\u5fd7\u5904\u7406\u8fdb\u5ea6\u7684relay-log.info\u6587\u4ef6;
\n4.\u590d\u5236\u4ece\u5c5e\u670d\u52a1\u5668\u6570\u636e\u6587\u4ef6:
\n 1.\u590d\u5236\u4ece\u5c5e\u670d\u52a1\u5668\u521b\u5efa\u7528\u4e8e\u5904\u7406LOAD DATA INFILE\u8bed\u53e5\u7684\u6570\u636e\u6587\u4ef6;
\n 2.\u8fd9\u4e9b\u6587\u4ef6\u4f4d\u4e8eslave_load_tmpdir\u7cfb\u7edf\u53d8\u91cf\u6307\u5b9a\u7684\u76ee\u5f55\u4e2d,\u5728\u670d\u52a1\u5668\u542f\u52a8\u65f6\u4f7f\u7528–slave-load-tmpdir\u9009\u9879\u53ef\u8bbe\u7f6e\u8be5\u53d8\u91cf;
\n 3.\u5982\u679c\u4e0d\u8bbe\u7f6eslave_load_tmpdir,\u5219\u5e94\u7528tmpdir\u7cfb\u7edf\u53d8\u91cf\u7684\u503c;
\n 4.\u8981\u4fdd\u62a4\u590d\u5236\u4ece\u5c5e\u670d\u52a1\u5668\u6570\u636e,\u9700\u8981\u5907\u4efd\u4ee5SQL_LOAD-\u5f00\u5934\u7684\u6587\u4ef6;
\n5.MySQL\u4e8c\u8fdb\u5236\u6587\u4ef6\u548c\u5e93;
\n6.\u7b56\u7565:
\n 1.\u9759\u6001\u6587\u4ef6:\u4f7f\u7528\u5e38\u89c4\u7cfb\u7edf\u5de5\u5177\u5728\u670d\u52a1\u5668\u8fd0\u884c\u7684\u60c5\u51b5\u4e0b\u5907\u4efd;
\n 2.\u52a8\u6001\u6587\u4ef6:\u4f7f\u7528\u5e38\u89c4\u7cfb\u7edf\u5de5\u5177\u5728\u670d\u52a1\u5668\u505c\u6b62\u7684\u60c5\u51b5\u4e0b\u5907\u4efd;<\/p>\n
\n1.\u4e3b\u670d\u52a1\u5668\u53ef\u4ee5\u7ee7\u7eed\u8fd0\u884c:
\n 1.\u4f7f\u7528\u4ece\u5c5e\u670d\u52a1\u5668\u751f\u6210\u5907\u4efd,\u800c\u4e0d\u5907\u4efd\u4e3b\u670d\u52a1\u5668;
\n 2.\u4e3b\u670d\u52a1\u5668\u4e0d\u4f1a\u88ab\u4e2d\u65ad,\u5907\u4efd\u8fc7\u7a0b\u4e0d\u4f1a\u5bf9\u4e3b\u670d\u52a1\u5668\u589e\u52a0\u5904\u7406\u8d1f\u8377,\u4e5f\u4e0d\u8981\u6c42\u589e\u52a0\u786c\u76d8\u7a7a\u95f4\u6216\u8fdb\u884c\u989d\u5916\u5904\u7406;
\n2.\u53ef\u4ee5\u505c\u6b62\u4ece\u5c5e\u670d\u52a1\u5668\u4ee5\u751f\u6210\u5907\u4efd:
\n 1.\u505c\u6b62\u670d\u52a1\u5668:\u5173\u95edmysqld\u8fdb\u7a0b;
\n 2.\u6216\u8005\u53d1\u51faSTOP SLAVE SQL_THREAD\u8bed\u53e5\u4ee5\u505c\u6b62\u670d\u52a1\u5668\u5904\u7406\u5176\u4ece\u4e3b\u670d\u52a1\u5668\u6536\u5230\u7684\u66f4\u65b0,\u5fc5\u987b\u5237\u65b0\u8868\u4ee5\u5f3a\u5236\u6302\u8d77\u5bf9\u78c1\u76d8\u7684\u66f4\u6539;
\n3.\u5907\u4efd\u4ece\u5c5e\u670d\u52a1\u5668\u7684\u6570\u636e\u5e93:
\n 1.\u505c\u6b62\u7684\u670d\u52a1\u5668\u53ef\u4ee5\u4f7f\u7528\u7cfb\u7edf\u5de5\u5177;
\n 2.\u4ece\u5c5e\u670d\u52a1\u5668\u7ebf\u7a0b\u505c\u6b62,\u4f46\u4ecd\u5728\u8fd0\u884c\u7684\u670d\u52a1\u5668\u53ef\u4ee5\u4f7f\u7528\u4efb\u4f55MySQL\u5de5\u5177;
\n4.\u542f\u52a8\u670d\u52a1\u5668:
\n 1.\u542f\u52a8\u505c\u6b62\u7684\u670d\u52a1\u5668;
\n 2.START SLAVE SQL_THREAD;<\/p>\n
\n1.\u53c2\u770bPPT\u7684\u5bf9\u6bd4;
\n2.\u5feb\u7167:\u5e76\u975e\u6240\u6709\u5f15\u64ce\u90fd\u4ee5\u76f8\u540c\u65b9\u5f0f\u5904\u7406\u5feb\u7167,\u4f8b\u5982,InnoDB\u8868\u4e0d\u9700\u8981FLUSH TABLES WITH READ LOCK\u5c31\u80fd\u542f\u52a8\u5feb\u7167,\u4f46MyISAM\u8868\u5374\u9700\u8981;<\/p>\n
\n\u9700\u8981\u5173\u5fc3\u7684\u95ee\u9898:
\n1.\u6211\u4eec\u7684\u7cfb\u7edf\u80fd\u5426\u627f\u53d7\u957f\u65f6\u95f4\u505c\u673a(\u505c\u673a\u65f6\u95f4)?
\n2.\u6709\u591a\u5c11\u6570\u636e\u8981\u5907\u4efd?
\n3.\u4f7f\u7528\u54ea\u4e9b\u5b58\u50a8\u5f15\u64ce\u6765\u5b58\u50a8\u6570\u636e(InnoDB,MyISAM\u6216\u4e24\u8005)?<\/p>\n
\n1.\u786e\u5b9a\u5728\u5907\u4efd\u751f\u6210\u540e\u5199\u5165\u54ea\u4e9b\u65e5\u5fd7:
\n 1.\u5728\u6062\u590d\u4e86\u4e8c\u8fdb\u5236\u5907\u4efd\u6587\u4ef6\u6216\u91cd\u65b0\u88c5\u5165\u4e86\u6587\u672c\u5907\u4efd\u6587\u4ef6\u540e,\u901a\u8fc7\u91cd\u65b0\u5904\u7406\u5728\u670d\u52a1\u5668\u7684\u4e8c\u8fdb\u5236\u65e5\u5fd7\u4e2d\u8bb0\u5f55\u7684\u6570\u636e\u66f4\u6539,\u5b8c\u6210\u6062\u590d\u64cd\u4f5c;
\n 2.\u4e3a\u6b64,\u5fc5\u987b\u786e\u5b9a\u5728\u751f\u6210\u5907\u4efd\u540e\u5199\u5165\u54ea\u4e9b\u65e5\u5fd7,\u7136\u540e,\u9700\u8981\u4f7f\u7528mysqlbinlog\u7a0b\u5e8f\u5c06\u8fd9\u4e9b\u4e8c\u8fdb\u5236\u65e5\u5fd7\u7684\u5185\u5bb9\u8f6c\u6362\u6210\u6587\u672cSQL\u8bed\u53e5,\u4ee5\u4fbf\u4f7f\u7528mysql\u5904\u7406\u7ed3\u679c\u8bed\u53e5;
\n2.\u4f7f\u7528mysqlbinlog\u8f6c\u6362\u5185\u5bb9:
\n 1.mysqlbinlog bin.000050 bin.000051 bin.000052 | mysql
\n 2.\u4f7f\u7528\u4e00\u4e2a\u547d\u4ee4\u5904\u7406\u6240\u6709binlog;
\n3.\u6062\u590d\u90e8\u5206binlog:
\n 1.–start-datetime\u9009\u9879:\u6307\u5b9a\u5f00\u59cb\u63d0\u53d6\u7684\u65e5\u671f\u548c\u65f6\u95f4,\u5176\u4e2d\u9009\u9879\u53c2\u6570\u91c7\u7528DATETIME\u683c\u5f0f;\u8bf7\u6ce8\u610f,–start-datetime\u7684\u7c92\u5ea6\u4ec5\u6709\u4e00\u79d2,\u56e0\u6b64\u53ef\u80fd\u4e0d\u591f\u7cbe\u786e,\u4e0d\u80fd\u6307\u5b9a\u5f00\u59cb\u7684\u786e\u5207\u4f4d\u7f6e;
\n 2.–start-position\u9009\u9879:\u53ef\u7528\u4e8e\u6307\u5b9a\u5728\u7ed9\u5b9a\u7684\u65e5\u5fd7\u4f4d\u7f6e\u5f00\u59cb\u63d0\u53d6;
\n 3.\u5bf9\u5e94\u7684–stop-datetime\u548c–stop-position\u9009\u9879,\u7528\u4e8e\u6307\u5b9a\u505c\u6b62\u63d0\u53d6\u65e5\u5fd7\u5185\u5bb9\u7684\u4f4d\u7f6e;
\n 4.mysqlbinlog –start-position=23456 binlog.000004 | mysql
\n 5.\u5982\u679c\u4e0d\u786e\u5b9a\u65e5\u5fd7\u6587\u4ef6\u4e2d\u5bf9\u5e94\u4e8e\u5904\u7406\u5f00\u59cb\u70b9\u7684\u65f6\u95f4\u6233\u6216\u4f4d\u7f6e,\u53ef\u4f7f\u7528mysqlbinlog(\u4e0d\u5e26mysql)\u663e\u793a\u65e5\u5fd7\u5185\u5bb9\u8fdb\u884c\u68c0\u67e5:
\n shell> mysqlbinlog file_name | more<\/p>\n
\n— mysqlbinlog\u7684\u53c2\u6570
\n1.-d,–database=name:\u5217\u51fa\u67d0\u4e00\u4e2a\u6570\u636e\u5e93\u7684\u65e5\u5fd7,\u53ea\u7528\u4e8e\u672c\u5730\u65e5\u5fd7;
\n2.-f,–force-read:\u5982\u679cmysqlbinlog\u8bfb\u5230\u5b83\u4e0d\u80fd\u8bc6\u522b\u7684\u4e8c\u8fdb\u5236\u65e5\u5fd7,\u4f1a\u6253\u5370\u8b66\u544a\u800c\u5ffd\u7565\u8be5\u4e8b\u4ef6\u5e76\u7ee7\u7eed,\u5982\u679c\u6ca1\u6709\u8be5\u4e8b\u4ef6\u5219\u505c\u6b62;
\n3.-o,–offset=#\uff1a\u5ffd\u7565\u524dN\u4e2a\u5b9e\u4f53;
\n4.-R,–read-from-remote-server:\u4ece\u8fdc\u7a0b\u670d\u52a1\u5668\u8bfb\u53d6\u4e8c\u8fdb\u5236\u65e5\u5fd7,\u5982\u679c\u6ca1\u6709\u6307\u5b9a\u6b64\u9009\u9879,\u5219–host, –user, –password, –port, –protocal, –socket\u9009\u9879\u90fd\u88ab\u5ffd\u7565;
\n5.-r,–result-file=name:\u76f4\u63a5\u8f93\u51fa\u5230\u7ed9\u5b9a\u7684\u6587\u4ef6;
\n6.–start-datetime=time:\u8bfb\u53d6\u4e8c\u8fdb\u5236\u65e5\u5fd7\u7684\u751f\u6210\u5f00\u59cb\u65f6\u95f4,\u53ef\u4ee5\u4f7f\u7528\u4efb\u4f55mysql\u670d\u52a1\u5668\u7684\u65f6\u95f4\u683c\u5f0f,datetime\u548ctimestamp\u7c7b\u578b,\u5982:’YYYY-MM-DD HH24:MI:SS’;
\n7.–stop-datetime=time:\u8bfb\u53d6\u4e8c\u8fdb\u5236\u65e5\u5fd7\u7684\u751f\u6210\u7ed3\u675f\u65f6\u95f4;
\n8.-j,–start-position=#:\u8bfb\u53d6\u4e8c\u8fdb\u5236\u65e5\u5fd7\u7684\u751f\u6210\u5f00\u59cb\u4f4d\u7f6e,\u662f\u4e00\u4e2a\u6574\u578b\u53c2\u6570;
\n9.–stop-position=#:\u8bfb\u53d6\u4e8c\u8fdb\u5236\u65e5\u5fd7\u7684\u751f\u6210\u7ed3\u675f\u4f4d\u7f6e,\u4e00\u4e2a\u6574\u578b\u53c2\u6570;
\n10.-t,–to-last-log:\u5728mysql\u670d\u52a1\u5668\u4e2d\u8bf7\u6c42\u7684\u4e8c\u8fdb\u5236\u65e5\u5fd7\u7ed3\u5c3e\u5904\u4e0d\u505c\u6b62,\u800c\u662f\u7ee7\u7eed\u6253\u5370\u76f4\u5230\u6700\u540e\u4e00\u4e2a\u4e8c\u8fdb\u5236\u65e5\u5fd7\u7684\u7ed3\u5c3e,\u5982\u679c\u5c06\u8f93\u51fa\u53d1\u9001\u7ed9\u540c\u4e00\u53f0mysql\u670d\u52a1\u5668,\u4f1a\u5bfc\u5bfc\u81f4\u65e0\u9650\u5faa\u73af,\u8981\u4e0e–read-from-remote-server\u8fde\u7528;
\n11.-D,–disable-log-bin:\u7981\u7528\u4e8c\u8fdb\u5236\u65e5\u5fd7,\u5982\u679c\u4f7f\u7528–to-last-logs\u9009\u9879\u5c06\u8f93\u51fa\u53d1\u9001\u7ed9\u540c\u4e00\u53f0mysql\u670d\u52a1\u5668,\u53ef\u4ee5\u907f\u514d\u65e0\u9650\u5faa\u73af,\u8be5\u9009\u9879\u5728\u5d29\u6e83\u6062\u590d\u4e5f\u5f88\u6709\u7528,\u53ef\u4ee5\u907f\u514d\u590d\u5236\u5df2\u7ecf\u8bb0\u5f55\u7684\u8bed\u53e5;<\/p>\n
\nc<\/p>\n
\n— \u8868\u7ed3\u6784\u7684\u590d\u5236
\n1.\u7b2c\u4e00\u79cd\u505a\u6cd5\u662f:CREATE TABLE table_name AS SELECT * FROM tb_name;
\n 1.\u53ef\u4ee5\u590d\u5236\u8868\u63a5\u53e3\u548c\u8868\u4e2d\u6570\u636e,\u5982\u679c\u53ea\u60f3\u8981\u8868\u63a5\u53e3\u53ef\u4ee5\u52a0\u4e00\u4e2afalse\u7684\u8fc7\u6ee4;
\n 2.\u4f46\u662f\u4f1a\u4e22\u5931\u8868\u4e2d\u5217\u4e0a\u9762\u7684\u5c5e\u6027(\u5982\u81ea\u589e\u5c5e\u6027)\u548c\u7d22\u5f15(\u4e3b\u5916\u952e);
\n2.\u7b2c\u4e8c\u79cd\u505a\u6cd5\u662f:CREATE TABLE table_name(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY) SELECT * FROM table_name;
\n 1.\u53ef\u4ee5\u5728\u521b\u5efa\u8868\u65f6\u6307\u5b9a\u6240\u6709\u7684\u5c5e\u6027,\u5e76\u540c\u6b65\u6570\u636e;
\n 2.\u4f46\u662f\u8bed\u6cd5\u592a\u9ebb\u70e6;
\n4.\u7b2c\u4e09\u79cd\u505a\u6cd5\u662f:CREATE TABLE table_name LIKE table_name;
\n 1.\u53ea\u590d\u5236\u8868\u7ed3\u6784,\u800c\u4e14\u4fdd\u7559\u5217\u7684\u5c5e\u6027\u548c\u7d22\u5f15;
\n 2.\u5982\u679c\u60f3\u8981\u6570\u636e\u7684\u8bdd\u53ef\u4ee5INSERT table_name SELECT * FROM table_name;<\/database><\/password><\/user><\/database><\/login><\/backup_dir><\/copy><\/login><\/table>\n