{"id":891,"date":"2018-07-08T12:20:54","date_gmt":"2018-07-08T04:20:54","guid":{"rendered":"https:\/\/blog.freesilo.com\/?p=891"},"modified":"2018-07-08T12:20:56","modified_gmt":"2018-07-08T04:20:56","slug":"%e5%ad%a6%e4%bc%9a4%e7%a7%8d%e5%a4%87%e4%bb%bdmysql%e6%95%b0%e6%8d%ae%e5%ba%93%ef%bc%88%e5%9f%ba%e6%9c%ac%e5%a4%87%e4%bb%bd%e6%96%b9%e9%9d%a2%e6%b2%a1%e9%97%ae%e9%a2%98%e4%ba%86%ef%bc%89","status":"publish","type":"post","link":"https:\/\/freesilo.com\/?p=891","title":{"rendered":"\u5b66\u4f1a4\u79cd\u5907\u4efdMySQL\u6570\u636e\u5e93\uff08\u57fa\u672c\u5907\u4efd\u65b9\u9762\u6ca1\u95ee\u9898\u4e86\uff09"},"content":{"rendered":"<h2 id=\"\u524d\u8a00\">\u524d\u8a00<\/h2>\n<blockquote><p>\u6211\u4eec\u8bd5\u7740\u60f3\u4e00\u60f3, \u5728\u751f\u4ea7\u73af\u5883\u4e2d\u4ec0\u4e48\u6700\u91cd\u8981\uff1f\u5982\u679c\u6211\u4eec\u670d\u52a1\u5668\u7684\u786c\u4ef6\u574f\u4e86\u53ef\u4ee5\u7ef4\u4fee\u6216\u8005\u6362\u65b0, \u8f6f\u4ef6\u95ee\u9898\u53ef\u4ee5\u4fee\u590d\u6216\u91cd\u65b0\u5b89\u88c5, \u4f46\u662f\u5982\u679c\u6570\u636e\u6ca1\u4e86\u5462\uff1f\u8fd9\u53ef\u80fd\u662f\u6700\u6050\u6016\u7684\u4e8b\u60c5\u4e86\u5427, \u6211\u611f\u89c9\u5728\u751f\u4ea7\u73af\u5883\u4e2d\u5e94\u8be5\u6ca1\u6709\u4ec0\u4e48\u6bd4\u6570\u636e\u8ddf\u66f4\u4e3a\u91cd\u8981. \u90a3\u4e48\u6211\u4eec\u8be5\u5982\u4f55\u4fdd\u8bc1\u6570\u636e\u4e0d\u4e22\u5931\u3001\u6216\u8005\u4e22\u5931\u540e\u53ef\u4ee5\u5feb\u901f\u6062\u590d\u5462\uff1f\u53ea\u8981\u770b\u5b8c\u8fd9\u7bc7, \u5927\u5bb6\u5e94\u8be5\u5c31\u80fd\u5bf9<code>MySQL<\/code>\u4e2d\u5b9e\u73b0\u6570\u636e\u5907\u4efd\u548c\u6062\u590d\u80fd\u6709\u4e00\u5b9a\u7684\u4e86\u89e3\u3002<\/p><\/blockquote>\n<h2 id=\"\u4e3a\u4ec0\u4e48\u9700\u8981\u5907\u4efd\u6570\u636e\"><a name=\"\u4e3a\u4ec0\u4e48\u9700\u8981\u5907\u4efd\u6570\u636e\uff1f\"><\/a>\u4e3a\u4ec0\u4e48\u9700\u8981\u5907\u4efd\u6570\u636e\uff1f<\/h2>\n<blockquote><p>\u5176\u5b9e\u5728<code>\u524d\u8a00<\/code>\u4e2d\u4e5f\u5927\u6982\u8bf4\u660e\u4e86\u4e3a\u4ec0\u4e48\u8981\u5907\u4efd\u6570\u636e, \u4f46\u662f\u6211\u4eec\u8fd8\u662f\u5e94\u8be5\u5177\u4f53\u4e86\u89e3\u4e00\u4e0b\u4e3a\u4ec0\u4e48\u8981\u5907\u4efd\u6570\u636e<\/p>\n<p>\u5728\u751f\u4ea7\u73af\u5883\u4e2d\u6211\u4eec\u6570\u636e\u5e93\u53ef\u80fd\u4f1a\u906d\u9047\u5404\u79cd\u5404\u6837\u7684\u4e0d\u6d4b\u4ece\u800c\u5bfc\u81f4\u6570\u636e\u4e22\u5931, \u5927\u6982\u5206\u4e3a\u4ee5\u4e0b\u51e0\u79cd.<\/p>\n<ul class=\" list-paddingleft-2\">\n<li>\u786c\u4ef6\u6545\u969c<\/li>\n<li>\u8f6f\u4ef6\u6545\u969c<\/li>\n<li>\u81ea\u7136\u707e\u5bb3<\/li>\n<li>\u9ed1\u5ba2\u653b\u51fb<\/li>\n<li>\u8bef\u64cd\u4f5c (\u5360\u6bd4\u6700\u5927)<\/li>\n<\/ul>\n<p>\u6240\u4ee5, \u4e3a\u4e86\u5728\u6570\u636e\u4e22\u5931\u4e4b\u540e\u80fd\u591f\u6062\u590d\u6570\u636e, \u6211\u4eec\u5c31\u9700\u8981\u5b9a\u671f\u7684\u5907\u4efd\u6570\u636e, \u5907\u4efd\u6570\u636e\u7684\u7b56\u7565\u8981\u6839\u636e\u4e0d\u540c\u7684\u5e94\u7528\u573a\u666f\u8fdb\u884c\u5b9a\u5236, \u5927\u81f4\u6709\u51e0\u4e2a\u53c2\u8003\u6570\u503c, \u6211\u4eec\u53ef\u4ee5\u6839\u636e\u8fd9\u4e9b\u6570\u503c\u4ece\u800c\u5b9a\u5236\u7b26\u5408\u7279\u5b9a\u73af\u5883\u4e2d\u7684\u6570\u636e\u5907\u4efd\u7b56\u7565<\/p>\n<ul class=\" list-paddingleft-2\">\n<li>\u80fd\u591f\u5bb9\u5fcd\u4e22\u5931\u591a\u5c11\u6570\u636e<\/li>\n<li>\u6062\u590d\u6570\u636e\u9700\u8981\u591a\u957f\u65f6\u95f4<\/li>\n<li>\u9700\u8981\u6062\u590d\u54ea\u4e00\u4e9b\u6570\u636e<\/li>\n<\/ul>\n<\/blockquote>\n<h2 id=\"\u6570\u636e\u7684\u5907\u4efd\u7c7b\u578b\"><a name=\"\u6570\u636e\u7684\u5907\u4efd\u7c7b\u578b\"><\/a>\u6570\u636e\u7684\u5907\u4efd\u7c7b\u578b<\/h2>\n<blockquote><p>\u6570\u636e\u7684\u5907\u4efd\u7c7b\u578b\u6839\u636e\u5176\u81ea\u8eab\u7684\u7279\u6027\u4e3b\u8981\u5206\u4e3a\u4ee5\u4e0b\u51e0\u7ec4<\/p>\n<ul class=\" list-paddingleft-2\">\n<li>\u5b8c\u5168\u5907\u4efd<\/li>\n<li>\u90e8\u5206\u5907\u4efd\n<p>\u5b8c\u5168\u5907\u4efd\u6307\u7684\u662f<strong>\u5907\u4efd\u6574\u4e2a\u6570\u636e\u96c6( \u5373\u6574\u4e2a\u6570\u636e\u5e93 )<\/strong>\u3001\u90e8\u5206\u5907\u4efd\u6307\u7684\u662f<strong>\u5907\u4efd\u90e8\u5206\u6570\u636e\u96c6(\u4f8b\u5982: \u53ea\u5907\u4efd\u4e00\u4e2a\u8868)<\/strong><\/li>\n<\/ul>\n<p>\u800c\u90e8\u5206\u5907\u4efd\u53c8\u5206\u4e3a\u4ee5\u4e0b\u4e24\u79cd<\/p>\n<ul class=\" list-paddingleft-2\">\n<li>\u589e\u91cf\u5907\u4efd<\/li>\n<li>\u5dee\u5f02\u5907\u4efd\n<p>\u589e\u91cf\u5907\u4efd\u6307\u7684\u662f<strong>\u5907\u4efd\u81ea\u4e0a\u4e00\u6b21\u5907\u4efd\u4ee5\u6765(\u589e\u91cf\u6216\u5b8c\u5168)\u4ee5\u6765\u53d8\u5316\u7684\u6570\u636e<\/strong>; \u7279\u70b9: \u8282\u7ea6\u7a7a\u95f4\u3001\u8fd8\u539f\u9ebb\u70e6<br \/>\n\u5dee\u5f02\u5907\u4efd\u6307\u7684\u662f<strong>\u5907\u4efd\u81ea\u4e0a\u4e00\u6b21\u5b8c\u5168\u5907\u4efd\u4ee5\u6765\u53d8\u5316\u7684\u6570\u636e<\/strong>\u00a0\u7279\u70b9: \u6d6a\u8d39\u7a7a\u95f4\u3001\u8fd8\u539f\u6bd4\u589e\u91cf\u5907\u4efd\u7b80\u5355<\/li>\n<\/ul>\n<p>\u793a\u610f\u56fe<\/p>\n<p><img decoding=\"async\" title=\"1461716541918741.png\" src=\"https:\/\/blog.freesilo.com\/wp-content\/uploads\/2018\/07\/b4b6f6aa60d0f053822f7b2da82f7675.png\" alt=\"blob.png\" \/><\/p><\/blockquote>\n<h2 id=\"mysql\u5907\u4efd\u6570\u636e\u7684\u65b9\u5f0f\"><strong><a name=\"MySQL\u5907\u4efd\u6570\u636e\u7684\u65b9\u5f0f\"><\/a>MySQL\u5907\u4efd\u6570\u636e\u7684\u65b9\u5f0f<\/strong><\/h2>\n<blockquote><p>\u5728<code>MySQl<\/code>\u4e2d\u6211\u4eec\u5907\u4efd\u6570\u636e\u4e00\u822c\u6709\u51e0\u79cd\u65b9\u5f0f<\/p>\n<ul class=\" list-paddingleft-2\">\n<li>\u70ed\u5907\u4efd<\/li>\n<li>\u6e29\u5907\u4efd<\/li>\n<li>\u51b7\u5907\u4efd\n<p>\u70ed\u5907\u4efd\u6307\u7684\u662f\u5f53\u6570\u636e\u5e93\u8fdb\u884c\u5907\u4efd\u65f6,\u00a0<strong>\u6570\u636e\u5e93\u7684\u8bfb\u5199\u64cd\u4f5c\u5747\u4e0d\u662f\u53d7\u5f71\u54cd\u00a0<\/strong><br \/>\n\u6e29\u5907\u4efd\u6307\u7684\u662f\u5f53\u6570\u636e\u5e93\u8fdb\u884c\u5907\u4efd\u65f6,\u00a0<strong>\u6570\u636e\u5e93\u7684\u8bfb\u64cd\u4f5c\u53ef\u4ee5\u6267\u884c, \u4f46\u662f\u4e0d\u80fd\u6267\u884c\u5199\u64cd\u4f5c<\/strong><br \/>\n\u51b7\u5907\u4efd\u6307\u7684\u662f\u5f53\u6570\u636e\u5e93\u8fdb\u884c\u5907\u4efd\u65f6,\u00a0<strong>\u6570\u636e\u5e93\u4e0d\u80fd\u8fdb\u884c\u8bfb\u5199\u64cd\u4f5c, \u5373\u6570\u636e\u5e93\u8981\u4e0b\u7ebf<\/strong><\/li>\n<\/ul>\n<p><code>MySQL<\/code>\u4e2d\u8fdb\u884c\u4e0d\u540c\u65b9\u5f0f\u7684\u5907\u4efd\u8fd8\u8981\u8003\u8651\u5b58\u50a8\u5f15\u64ce\u662f\u5426\u652f\u6301<\/p>\n<ul class=\" list-paddingleft-2\">\n<li>MyISAM\n<p>\u70ed\u5907 \u00d7<\/p>\n<p>\u6e29\u5907 \u221a<\/p>\n<p>\u51b7\u5907 \u221a<\/li>\n<li>InnoDB\n<p>\u70ed\u5907 \u221a<\/p>\n<p>\u6e29\u5907 \u221a<\/p>\n<p>\u51b7\u5907 \u221a<\/p>\n<p>\u6211\u4eec\u5728\u8003\u8651\u5b8c\u6570\u636e\u5728\u5907\u4efd\u65f6, \u6570\u636e\u5e93\u7684\u8fd0\u884c\u72b6\u6001\u4e4b\u540e\u8fd8\u9700\u8981\u8003\u8651\u5bf9\u4e8e<code>MySQL<\/code>\u6570\u636e\u5e93\u4e2d\u6570\u636e\u7684\u5907\u4efd\u65b9\u5f0f<\/p>\n<p>\u7269\u7406\u5907\u4efd\u4e00\u822c\u5c31\u662f<strong>\u901a\u8fc7<\/strong><strong><code>tar<\/code>,<code>cp<\/code>\u7b49\u547d\u4ee4\u76f4\u63a5\u6253\u5305\u590d\u5236\u6570\u636e\u5e93\u7684\u6570\u636e\u6587\u4ef6<\/strong>\u8fbe\u5230\u5907\u4efd\u7684\u6548\u679c<br \/>\n\u903b\u8f91\u5907\u4efd\u4e00\u822c\u5c31\u662f<strong>\u901a\u8fc7\u7279\u5b9a\u5de5\u5177\u4ece\u6570\u636e\u5e93\u4e2d\u5bfc\u51fa\u6570\u636e\u5e76\u53e6\u5b58\u5907\u4efd<\/strong>(\u903b\u8f91\u5907\u4efd\u4f1a\u4e22\u5931\u6570\u636e\u7cbe\u5ea6)<\/p>\n<ul class=\" list-paddingleft-2\">\n<li>\u7269\u7406\u5907\u4efd<\/li>\n<li>\u903b\u8f91\u5907\u4efd<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/blockquote>\n<h2 id=\"\u5907\u4efd\u9700\u8981\u8003\u8651\u7684\u95ee\u9898\"><a name=\"\u5907\u4efd\u9700\u8981\u8003\u8651\u7684\u95ee\u9898\"><\/a>\u5907\u4efd\u9700\u8981\u8003\u8651\u7684\u95ee\u9898<\/h2>\n<blockquote><p>\u5b9a\u5236\u5907\u4efd\u7b56\u7565\u524d, \u6211\u4eec\u8fd8\u9700\u8981\u8003\u8651\u4e00\u4e9b\u95ee\u9898<\/p>\n<p><strong>\u6211\u4eec\u8981\u5907\u4efd\u4ec0\u4e48?<\/strong><\/p>\n<p>\u4e00\u822c\u60c5\u51b5\u4e0b, \u6211\u4eec\u9700\u8981\u5907\u4efd\u7684\u6570\u636e\u5206\u4e3a\u4ee5\u4e0b\u51e0\u79cd<\/p>\n<ul class=\" list-paddingleft-2\">\n<li>\u6570\u636e<\/li>\n<li>\u4e8c\u8fdb\u5236\u65e5\u5fd7, InnoDB\u4e8b\u52a1\u65e5\u5fd7<\/li>\n<li>\u4ee3\u7801(\u5b58\u50a8\u8fc7\u7a0b\u3001\u5b58\u50a8\u51fd\u6570\u3001\u89e6\u53d1\u5668\u3001\u4e8b\u4ef6\u8c03\u5ea6\u5668)<\/li>\n<li>\u670d\u52a1\u5668\u914d\u7f6e\u6587\u4ef6<\/li>\n<\/ul>\n<p><strong>\u5907\u4efd\u5de5\u5177<\/strong><\/p>\n<p>\u8fd9\u91cc\u6211\u4eec\u5217\u4e3e\u51fa\u5e38\u7528\u7684\u51e0\u79cd\u5907\u4efd\u5de5\u5177<br \/>\n<code>mysqldump<\/code>\u00a0: \u903b\u8f91\u5907\u4efd\u5de5\u5177, \u9002\u7528\u4e8e\u6240\u6709\u7684\u5b58\u50a8\u5f15\u64ce, \u652f\u6301\u6e29\u5907\u3001\u5b8c\u5168\u5907\u4efd\u3001\u90e8\u5206\u5907\u4efd\u3001\u5bf9\u4e8e<strong>InnoDB<\/strong>\u5b58\u50a8\u5f15\u64ce\u652f\u6301\u70ed\u5907<br \/>\n<code>cp, tar \u7b49\u5f52\u6863\u590d\u5236\u5de5\u5177<\/code>: \u7269\u7406\u5907\u4efd\u5de5\u5177, \u9002\u7528\u4e8e\u6240\u6709\u7684\u5b58\u50a8\u5f15\u64ce, \u51b7\u5907\u3001\u5b8c\u5168\u5907\u4efd\u3001\u90e8\u5206\u5907\u4efd<br \/>\n<code>lvm2 snapshot<\/code>: \u51e0\u4e4e\u70ed\u5907, \u501f\u52a9\u6587\u4ef6\u7cfb\u7edf\u7ba1\u7406\u5de5\u5177\u8fdb\u884c\u5907\u4efd<br \/>\n<code>mysqlhotcopy<\/code>: \u540d\u4e0d\u526f\u5b9e\u7684\u7684\u4e00\u4e2a\u5de5\u5177, \u51e0\u4e4e\u51b7\u5907, \u4ec5\u652f\u6301<strong>MyISAM<\/strong>\u5b58\u50a8\u5f15\u64ce<br \/>\n<code>xtrabackup<\/code>: \u4e00\u6b3e\u975e\u5e38\u5f3a\u5927\u7684<strong>InnoDB\/XtraDB<\/strong>\u70ed\u5907\u5de5\u5177, \u652f\u6301\u5b8c\u5168\u5907\u4efd\u3001\u589e\u91cf\u5907\u4efd, \u7531<code>percona<\/code>\u63d0\u4f9b<\/p><\/blockquote>\n<h2 id=\"\u8bbe\u8ba1\u5408\u9002\u7684\u5907\u4efd\u7b56\u7565\"><a name=\"\u8bbe\u8ba1\u5408\u9002\u7684\u5907\u4efd\u7b56\u7565\"><\/a>\u8bbe\u8ba1\u5408\u9002\u7684\u5907\u4efd\u7b56\u7565<\/h2>\n<blockquote><p>\u9488\u5bf9\u4e0d\u540c\u7684\u573a\u666f\u4e0b, \u6211\u4eec\u5e94\u8be5\u5236\u5b9a\u4e0d\u540c\u7684\u5907\u4efd\u7b56\u7565\u5bf9\u6570\u636e\u5e93\u8fdb\u884c\u5907\u4efd, \u4e00\u822c\u60c5\u51b5\u4e0b, \u5907\u4efd\u7b56\u7565\u4e00\u822c\u4e3a\u4ee5\u4e0b\u4e09\u79cd<\/p>\n<ul class=\" list-paddingleft-2\">\n<li><strong>\u76f4\u63a5cp,tar\u590d\u5236\u6570\u636e\u5e93\u6587\u4ef6<\/strong><\/li>\n<li><strong>mysqldump+\u590d\u5236BIN LOGS<\/strong><\/li>\n<li><strong>lvm2\u5feb\u7167+\u590d\u5236BIN LOGS<\/strong><\/li>\n<li><strong>xtrabackup<\/strong><\/li>\n<\/ul>\n<p>\u4ee5\u4e0a\u7684\u51e0\u79cd\u89e3\u51b3\u65b9\u6848\u5206\u522b\u9488\u5bf9\u4e8e\u4e0d\u540c\u7684\u573a\u666f<\/p>\n<ol class=\" list-paddingleft-2\">\n<li>\u5982\u679c\u6570\u636e\u91cf\u8f83\u5c0f, \u53ef\u4ee5\u4f7f\u7528\u7b2c\u4e00\u79cd\u65b9\u5f0f, \u76f4\u63a5\u590d\u5236\u6570\u636e\u5e93\u6587\u4ef6<\/li>\n<li>\u5982\u679c\u6570\u636e\u91cf\u8fd8\u884c, \u53ef\u4ee5\u4f7f\u7528\u7b2c\u4e8c\u79cd\u65b9\u5f0f, \u5148\u4f7f\u7528mysqldump\u5bf9\u6570\u636e\u5e93\u8fdb\u884c\u5b8c\u5168\u5907\u4efd, \u7136\u540e\u5b9a\u671f\u5907\u4efdBINARY LOG\u8fbe\u5230\u589e\u91cf\u5907\u4efd\u7684\u6548\u679c<\/li>\n<li>\u5982\u679c\u6570\u636e\u91cf\u4e00\u822c, \u800c\u53c8\u4e0d\u8fc7\u5206\u5f71\u54cd\u4e1a\u52a1\u8fd0\u884c, \u53ef\u4ee5\u4f7f\u7528\u7b2c\u4e09\u79cd\u65b9\u5f0f, \u4f7f\u7528<code>lvm2<\/code>\u7684\u5feb\u7167\u5bf9\u6570\u636e\u6587\u4ef6\u8fdb\u884c\u5907\u4efd, \u800c\u540e\u5b9a\u671f\u5907\u4efdBINARY LOG\u8fbe\u5230\u589e\u91cf\u5907\u4efd\u7684\u6548\u679c<\/li>\n<li>\u5982\u679c\u6570\u636e\u91cf\u5f88\u5927, \u800c\u53c8\u4e0d\u8fc7\u5206\u5f71\u54cd\u4e1a\u52a1\u8fd0\u884c, \u53ef\u4ee5\u4f7f\u7528\u7b2c\u56db\u79cd\u65b9\u5f0f, \u4f7f\u7528<code>xtrabackup<\/code>\u8fdb\u884c\u5b8c\u5168\u5907\u4efd\u540e, \u5b9a\u671f\u4f7f\u7528<code>xtrabackup<\/code>\u8fdb\u884c\u589e\u91cf\u5907\u4efd\u6216\u5dee\u5f02\u5907\u4efd<\/li>\n<\/ol>\n<\/blockquote>\n<h2 id=\"\u5b9e\u6218\u6f14\u7ec3\"><a name=\"\u5b9e\u6218\u6f14\u7ec3\"><\/a>\u5b9e\u6218\u6f14\u7ec3<\/h2>\n<h3 id=\"\u4f7f\u7528cp\u8fdb\u884c\u5907\u4efd\"><a name=\"\u4f7f\u7528cp\u8fdb\u884c\u5907\u4efd\"><\/a>\u4f7f\u7528cp\u8fdb\u884c\u5907\u4efd<\/h3>\n<blockquote><p>\u6211\u4eec\u8fd9\u91cc\u4f7f\u7528\u7684\u662f\u4f7f\u7528yum\u5b89\u88c5\u7684<code>mysql-5.1<\/code>\u7684\u7248\u672c, \u4f7f\u7528\u7684\u6570\u636e\u96c6\u4e3a\u4ece\u7f51\u7edc\u4e0a\u627e\u5230\u7684\u4e00\u4e2a\u5458\u5de5\u6570\u636e\u5e93<\/p><\/blockquote>\n<p><strong>\u67e5\u770b\u6570\u636e\u5e93\u7684\u4fe1\u606f<\/strong><\/p>\n<pre class=\"prettyprint hljs-light\"><code class=\"language-sql hljs\">mysql&gt; <span class=\"hljs-operator\"><span class=\"hljs-keyword\">SHOW DATABASES; \u00a0 \u00a0#\u67e5\u770b\u5f53\u524d\u7684\u6570\u636e\u5e93, \u6211\u4eec\u7684\u6570\u636e\u5e93\u4e3aemployees\r\n+<span class=\"hljs-comment\">--------------------+\r\n| Database \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 |\r\n+--------------------+\r\n| information_schema |\r\n| employees \u00a0 \u00a0 \u00a0 \u00a0 \u00a0|\r\n| mysql \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0|\r\n| test \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 |\r\n+--------------------+\r\n4 rows in set (<span class=\"hljs-number\">0.00 sec)\r\n\r\nmysql&gt; USE employees;\r\nDatabase changed\r\nmysql&gt; SHOW TABLES; \u00a0 \u00a0 \u00a0 \u00a0 #\u67e5\u770b\u5f53\u524d\u5e93\u4e2d\u7684\u8868\r\n+---------------------+\r\n| Tables_in_employees |\r\n+---------------------+\r\n| departments \u00a0 \u00a0 \u00a0 \u00a0 |\r\n| dept_emp \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0|\r\n| dept_manager \u00a0 \u00a0 \u00a0 \u00a0|\r\n| employees \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 |\r\n| salaries \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0|\r\n| titles \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0|\r\n+---------------------+\r\n6 rows in set (0.00 sec)\r\n\r\nmysql&gt; SELECT COUNT(*) FROM employees; \u00a0 #\u7531\u4e8e\u7bc7\u5e45\u539f\u56e0, \u6211\u4eec\u8fd9\u91cc\u53ea\u770b\u4e00\u4e0bemployees\u7684\u884c\u6570\u4e3a300024\r\n+----------+\r\n| COUNT(*) |\r\n+----------+\r\n| \u00a0 300024 |\r\n+----------+\r\n1 row in set (0.05 sec)\r\n\r\n<\/span><\/span><\/span><\/span><\/code><\/pre>\n<p><strong>\u5411\u6570\u636e\u5e93\u65bd\u52a0\u8bfb\u9501<\/strong><\/p>\n<pre class=\"prettyprint hljs-light\"><code class=\"language-sql hljs\">mysql&gt; <span class=\"hljs-operator\"><span class=\"hljs-keyword\">FLUSH TABLES WITH READ LOCK; \u00a0 \u00a0#\u5411\u6240\u6709\u8868\u65bd\u52a0\u8bfb\u9501\r\nQuery OK, 0 rows affected (0.00 sec)\r\n<\/span><\/span><\/code><\/pre>\n<p><strong>\u5907\u4efd\u6570\u636e\u6587\u4ef6<\/strong><\/p>\n<pre class=\"prettyprint hljs-light\"><code class=\"hljs coffeescript\">[root<span class=\"hljs-property\">@node1 ~]<span class=\"hljs-comment\"># mkdir \/backup \u00a0 #\u521b\u5efa\u6587\u4ef6\u5939\u5b58\u653e\u5907\u4efd\u6570\u636e\u5e93\u6587\u4ef6\r\n[root@node1 ~]# cp -a \/var\/lib\/mysql\/* \/backup \u00a0 \u00a0 #\u4fdd\u7559\u6743\u9650\u7684\u62f7\u8d1d\u6e90\u6570\u636e\u6587\u4ef6\r\n[root@node1 ~]# ls \/backup \u00a0 #\u67e5\u770b\u76ee\u5f55\u4e0b\u7684\u6587\u4ef6\r\nemployees \u00a0ibdata1 \u00a0ib_logfile0 \u00a0ib_logfile1 \u00a0mysql \u00a0mysql.sock \u00a0test\r\n<\/span><\/span><\/code><\/pre>\n<p><strong>\u6a21\u62df\u6570\u636e\u4e22\u5931\u5e76\u6062\u590d<\/strong><\/p>\n<pre class=\"prettyprint hljs-light\"><code class=\"language-sql hljs\">[root@node1 ~]# rm -rf \/var\/lib\/mysql<span class=\"hljs-comment\">\/* \u00a0 \u00a0#\u5220\u9664\u6570\u636e\u5e93\u7684\u6240\u6709\u6587\u4ef6\r\n[root@node1 ~]# service mysqld restart \u00a0 #\u91cd\u542fMySQL, \u5982\u679c\u662f\u7f16\u8bd1\u5b89\u88c5\u7684\u5e94\u8be5\u4e0d\u80fd\u542f\u52a8, \u5982\u679crpm\u5b89\u88c5\u5219\u4f1a\u91cd\u65b0\u521d\u59cb\u5316\u6570\u636e\u5e93\r\n\r\n\r\nmysql&gt; SHOW DATABASES; \u00a0 \u00a0#\u56e0\u4e3a\u6211\u4eec\u662frpm\u5b89\u88c5\u7684, \u8fde\u63a5\u5230MySQL\u8fdb\u884c\u67e5\u770b, \u53d1\u73b0\u6570\u636e\u4e22\u5931\u4e86\uff01\r\n+--------------------+\r\n| Database \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 |\r\n+--------------------+\r\n| information_schema |\r\n| mysql \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0|\r\n| test \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 |\r\n+--------------------+\r\n3 rows in set (0.00 sec)\r\n\r\n[root@node1 ~]# rm -rf \/var\/lib\/mysql\/* \u00a0 \u00a0#\u8fd9\u4e00\u6b65\u53ef\u4ee5\u4e0d\u505a\r\n[root@node1 ~]# cp -a \/backup\/* \/var\/lib\/mysql\/ \u00a0 \u00a0#\u5c06\u5907\u4efd\u7684\u6570\u636e\u6587\u4ef6\u62f7\u8d1d\u56de\u53bb\r\n[root@node1 ~]# service mysqld restart \u00a0#\u91cd\u542fMySQL\r\n\r\n\r\n#\u91cd\u65b0\u8fde\u63a5\u6570\u636e\u5e76\u67e5\u770b\r\n\r\nmysql&gt; SHOW DATABASES; \u00a0 \u00a0#\u6570\u636e\u5e93\u5df2\u6062\u590d\r\n+--------------------+\r\n| Database \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 |\r\n+--------------------+\r\n| information_schema |\r\n| employees \u00a0 \u00a0 \u00a0 \u00a0 \u00a0|\r\n| mysql \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0|\r\n| test \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 |\r\n+--------------------+\r\n4 rows in set (0.00 sec)\r\n\r\nmysql&gt; USE employees; \u00a0 \u00a0 \u00a0\r\n\r\nmysql&gt; SELECT COUNT(*) FROM employees; \u00a0 \u00a0#\u8868\u7684\u884c\u6570\u6ca1\u6709\u53d8\u5316\r\n+----------+\r\n| COUNT(*) |\r\n+----------+\r\n| \u00a0 300024 |\r\n+----------+\r\n1 row in set (0.06 sec)\r\n\r\n\r\n##\u5b8c\u6210\r\n<\/span><\/code><\/pre>\n<h3 id=\"\u4f7f\u7528mysqldump\u590d\u5236binary-log\u5907\u4efd\"><a name=\"\u4f7f\u7528mysqldump+\u590d\u5236BINARY LOG\u5907\u4efd\"><\/a>\u4f7f\u7528mysqldump+\u590d\u5236BINARY LOG\u5907\u4efd<\/h3>\n<blockquote><p>\u6211\u4eec\u8fd9\u91cc\u4f7f\u7528\u7684\u662f\u4f7f\u7528yum\u5b89\u88c5\u7684<code>mysql-5.1<\/code>\u7684\u7248\u672c, \u4f7f\u7528\u7684\u6570\u636e\u96c6\u4e3a\u4ece\u7f51\u7edc\u4e0a\u627e\u5230\u7684\u4e00\u4e2a\u5458\u5de5\u6570\u636e\u5e93<\/p>\n<p>\u6211\u4eec\u901a\u8fc7mysqldump\u8fdb\u884c\u4e00\u6b21\u5b8c\u5168\u5907\u4efd, \u518d\u4fee\u6539\u8868\u4e2d\u7684\u6570\u636e, \u7136\u540e\u518d\u901a\u8fc7binary log\u8fdb\u884c\u6062\u590d\u00a0<strong>\u4e8c\u8fdb\u5236\u65e5\u5fd7\u9700\u8981\u5728mysql\u914d\u7f6e\u6587\u4ef6\u4e2d\u6dfb\u52a0 log_bin=on \u5f00\u542f<\/strong><\/p><\/blockquote>\n<p><strong><code>mysqldump<\/code>\u547d\u4ee4\u4ecb\u7ecd<\/strong><\/p>\n<blockquote><p><code>mysqldump<\/code>\u662f\u4e00\u4e2a\u5ba2\u6237\u7aef\u7684\u903b\u8f91\u5907\u4efd\u5de5\u5177, \u53ef\u4ee5\u751f\u6210\u4e00\u4e2a\u91cd\u73b0\u521b\u5efa\u539f\u59cb\u6570\u636e\u5e93\u548c\u8868\u7684SQL\u8bed\u53e5, \u53ef\u4ee5\u652f\u6301\u6240\u6709\u7684\u5b58\u50a8\u5f15\u64ce, \u5bf9\u4e8eInnoDB\u652f\u6301\u70ed\u5907<\/p>\n<p><a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/mysqldump.html\" target=\"_blank\" rel=\"noopener\">\u5b98\u65b9\u6587\u6863\u4ecb\u7ecd<\/a><\/p><\/blockquote>\n<pre class=\"prettyprint hljs-light\"><code class=\"hljs livecodeserver\"><span class=\"hljs-comment\">#\u57fa\u672c\u8bed\u6cd5\u683c\u5f0f\r\n\r\n<span class=\"hljs-built_in\">shell&gt; mysqldump [options] db_name [tbl_name ...] \u00a0 \u00a0\u6062\u590d\u9700\u8981\u624b\u52a8CRATE DATABASES\r\nshell&gt; mysqldump [options] --databases db_name ... \u00a0 \u6062\u590d\u4e0d\u9700\u8981\u624b\u52a8\u521b\u5efa\u6570\u636e\u5e93\r\nshell&gt; mysqldump [options] --all-databases \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u6062\u590d\u4e0d\u9700\u8981\u624b\u52a8\u521b\u5efa\u6570\u636e\u5e93\r\n\r\n\r\n\u5176\u4ed6\u9009\u9879:\r\n \u00a0 \u00a0 -E, --events: \u5907\u4efd\u4e8b\u4ef6\u8c03\u5ea6\u5668\r\n \u00a0 \u00a0 -R, --routines: \u5907\u4efd\u5b58\u50a8\u8fc7\u7a0b\u548c\u5b58\u50a8\u51fd\u6570\r\n \u00a0 \u00a0 --triggers: \u5907\u4efd\u8868\u7684\u89e6\u53d1\u5668; --skip-triggers \r\n \u00a0 \u00a0 --master-date[=value] \u00a0\r\n \u00a0 \u00a0 \u00a0 \u00a0 <span class=\"hljs-number\">1: \u8bb0\u5f55\u4e3aCHANGE MASTER TO \u8bed\u53e5\u3001\u8bed\u53e5\u4e0d\u88ab\u6ce8\u91ca\r\n \u00a0 \u00a0 \u00a0 \u00a0 2: \u8bb0\u5f55\u4e3a\u6ce8\u91ca\u7684CHANGE MASTER TO\u8bed\u53e5\r\n \u00a0 \u00a0 \u00a0 \u00a0 \u57fa\u4e8e\u4e8c\u8fdb\u5236\u8fd8\u539f\u53ea\u80fd\u5168\u5e93\u8fd8\u539f\r\n\r\n \u00a0 \u00a0 --flush-logs: \u65e5\u5fd7\u6eda\u52a8\r\n \u00a0 \u00a0 \u00a0 \u00a0 \u9501\u5b9a\u8868\u5b8c\u6210\u540e\u6267\u884c\u65e5\u5fd7\u6eda\u52a8\r\n<\/span><\/span><\/span><\/code><\/pre>\n<p><strong>\u67e5\u770b\u6570\u636e\u5e93\u7684\u4fe1\u606f<\/strong><\/p>\n<pre class=\"prettyprint hljs-light\"><code class=\"language-sql hljs\">mysql&gt; <span class=\"hljs-operator\"><span class=\"hljs-keyword\">SHOW DATABASES; \u00a0 \u00a0#\u67e5\u770b\u5f53\u524d\u7684\u6570\u636e\u5e93, \u6211\u4eec\u7684\u6570\u636e\u5e93\u4e3aemployees\r\n+<span class=\"hljs-comment\">--------------------+\r\n| Database \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 |\r\n+--------------------+\r\n| information_schema |\r\n| employees \u00a0 \u00a0 \u00a0 \u00a0 \u00a0|\r\n| mysql \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0|\r\n| test \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 |\r\n+--------------------+\r\n4 rows in set (<span class=\"hljs-number\">0.00 sec)\r\n\r\nmysql&gt; USE employees;\r\nDatabase changed\r\nmysql&gt; SHOW TABLES; \u00a0 \u00a0 \u00a0 \u00a0 #\u67e5\u770b\u5f53\u524d\u5e93\u4e2d\u7684\u8868\r\n+---------------------+\r\n| Tables_in_employees |\r\n+---------------------+\r\n| departments \u00a0 \u00a0 \u00a0 \u00a0 |\r\n| dept_emp \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0|\r\n| dept_manager \u00a0 \u00a0 \u00a0 \u00a0|\r\n| employees \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 |\r\n| salaries \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0|\r\n| titles \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0|\r\n+---------------------+\r\n6 rows in set (0.00 sec)\r\n\r\nmysql&gt; SELECT COUNT(*) FROM employees; \u00a0 #\u7531\u4e8e\u7bc7\u5e45\u539f\u56e0, \u6211\u4eec\u8fd9\u91cc\u53ea\u770b\u4e00\u4e0bemployees\u7684\u884c\u6570\u4e3a300024\r\n+----------+\r\n| COUNT(*) |\r\n+----------+\r\n| \u00a0 300024 |\r\n+----------+\r\n1 row in set (0.05 sec)\r\n\r\n<\/span><\/span><\/span><\/span><\/code><\/pre>\n<p><strong>\u4f7f\u7528<code>mysqldump<\/code>\u5907\u4efd\u6570\u636e\u5e93<\/strong><\/p>\n<pre class=\"prettyprint hljs-light\"><code class=\"language-sql hljs\">[root@node1 ~]# mysql -uroot -p -e '<span class=\"hljs-operator\"><span class=\"hljs-keyword\">SHOW MASTER STATUS<span class=\"hljs-string\">' \u00a0 #\u67e5\u770b\u5f53\u524d\u4e8c\u8fdb\u5236\u6587\u4ef6\u7684\u72b6\u6001, \u5e76\u8bb0\u5f55\u4e0bposition\u7684\u6570\u5b57\r\n+------------------+----------+--------------+------------------+\r\n| File \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 | Position | Binlog_Do_DB | Binlog_Ignore_DB |\r\n+------------------+----------+--------------+------------------+\r\n| mysql-bin.000003 | \u00a0 \u00a0 \u00a0106 | \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0| \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0|\r\n+------------------+----------+--------------+------------------+\r\n\r\n[root@node1 ~]# mysqldump --all-databases --lock-all-tables \u00a0&gt; backup.sql \u00a0 #\u5907\u4efd\u6570\u636e\u5e93\u5230backup.sql\u6587\u4ef6\u4e2d\r\n\r\nmysql&gt; CREATE DATABASE TEST1; \u00a0 #\u521b\u5efa\u4e00\u4e2a\u6570\u636e\u5e93\r\nQuery OK, 1 row affected (0.00 sec)\r\n\r\nmysql&gt; SHOW MASTER STATUS; \u00a0 #\u8bb0\u4e0b\u73b0\u5728\u7684position\r\n+------------------+----------+--------------+------------------+\r\n| File \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 | Position | Binlog_Do_DB | Binlog_Ignore_DB |\r\n+------------------+----------+--------------+------------------+\r\n| mysql-bin.000003 | \u00a0 \u00a0 \u00a0191 | \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0| \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0|\r\n+------------------+----------+--------------+------------------+\r\n1 row in set (0.00 sec)\r\n\r\n[root@node1 ~]# cp \/var\/lib\/mysql\/mysql-bin.000003 \/root \u00a0#\u5907\u4efd\u4e8c\u8fdb\u5236\u6587\u4ef6\r\n[root@node1 ~]# service mysqld stop \u00a0 #\u505c\u6b62MySQL\r\n[root@node1 ~]# rm -rf \/var\/lib\/mysql\/* \u00a0 #\u5220\u9664\u6240\u6709\u7684\u6570\u636e\u6587\u4ef6\r\n[root@node1 ~]# service mysqld start \u00a0 \u00a0#\u542f\u52a8MySQL, \u5982\u679c\u662f\u7f16\u8bd1\u5b89\u88c5\u7684\u5e94\u8be5\u4e0d\u80fd\u542f\u52a8(\u9700\u91cd\u65b0\u521d\u59cb\u5316), \u5982\u679crpm\u5b89\u88c5\u5219\u4f1a\u91cd\u65b0\u521d\u59cb\u5316\u6570\u636e\u5e93\r\n\r\n\r\nmysql&gt; SHOW DATABASES; \u00a0 #\u67e5\u770b\u6570\u636e\u5e93, \u6570\u636e\u4e22\u5931!\r\n+--------------------+\r\n| Database \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 |\r\n+--------------------+\r\n| information_schema |\r\n| mysql \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0|\r\n| test \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 |\r\n+--------------------+\r\n3 rows in set (0.00 sec)\r\n\r\nmysql&gt; SET sql_log_bin=OFF; \u00a0 #\u6682\u65f6\u5148\u5c06\u4e8c\u8fdb\u5236\u65e5\u5fd7\u5173\u95ed \u00a0\r\nQuery OK, 0 rows affected (0.00 sec)\r\n\r\n\r\nmysql&gt; source backup.sql \u00a0#\u6062\u590d\u6570\u636e\uff0c\u6240\u9700\u65f6\u95f4\u6839\u636e\u6570\u636e\u5e93\u65f6\u95f4\u5927\u5c0f\u800c\u5b9a\r\n\r\nmysql&gt; SET sql_log_bin=ON; \u5f00\u542f\u4e8c\u8fdb\u5236\u65e5\u5fd7\r\n\r\nmysql&gt; SHOW DATABASES; \u00a0 #\u6570\u636e\u5e93\u6062\u590d, \u4f46\u662f\u7f3a\u5c11TEST1\r\n+--------------------+\r\n| Database \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 |\r\n+--------------------+\r\n| information_schema |\r\n| employees \u00a0 \u00a0 \u00a0 \u00a0 \u00a0|\r\n| mysql \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0|\r\n| test \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 |\r\n+--------------------+\r\n4 rows in set (0.00 sec)\r\n\r\n[root@node1 ~]# mysqlbinlog --start-position=106 --stop-position=191 mysql-bin.000003 | mysql employees #\u901a\u8fc7\u4e8c\u8fdb\u5236\u65e5\u5fd7\u589e\u91cf\u6062\u590d\u6570\u636e\r\n\r\nmysql&gt; SHOW DATABASES; \u00a0 \u00a0#\u73b0\u5728TEST1\u51fa\u73b0\u4e86\uff01\r\n+--------------------+\r\n| Database \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 |\r\n+--------------------+\r\n| information_schema |\r\n| TEST1 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0|\r\n| employees \u00a0 \u00a0 \u00a0 \u00a0 \u00a0|\r\n| mysql \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0|\r\n| test \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 |\r\n+--------------------+\r\n5 rows in set (0.00 sec)\r\n\r\n\r\n\r\n#\u5b8c\u6210\r\n<\/span><\/span><\/span><\/code><\/pre>\n<h3 id=\"\u4f7f\u7528lvm2\u5feb\u7167\u5907\u4efd\u6570\u636e\"><a name=\"\u4f7f\u7528lvm2\u5feb\u7167\u5907\u4efd\u6570\u636e\"><\/a>\u4f7f\u7528lvm2\u5feb\u7167\u5907\u4efd\u6570\u636e<\/h3>\n<blockquote><p>\u505a\u5b9e\u9a8c\u4e4b\u524d\u6211\u4eec\u5148\u56de\u987e\u4e00\u4e0b<code>lvm2-snapshot<\/code>\u7684\u77e5\u8bc6<\/p>\n<p><code>LVM<\/code>\u5feb\u7167\u7b80\u5355\u6765\u8bf4\u5c31\u662f\u5c06\u6240\u5feb\u7167\u6e90\u5206\u533a\u4e00\u4e2a\u65f6\u95f4\u70b9\u6240\u6709\u6587\u4ef6\u7684\u5143\u6570\u636e\u8fdb\u884c\u4fdd\u5b58\uff0c\u5982\u679c\u6e90\u6587\u4ef6\u6ca1\u6709\u6539\u53d8\uff0c\u90a3\u4e48\u8bbf\u95ee\u5feb\u7167\u5377\u7684\u76f8\u5e94\u6587\u4ef6\u5219\u76f4\u63a5\u6307\u5411\u6e90\u5206\u533a\u7684\u6e90\u6587\u4ef6\uff0c\u5982\u679c\u6e90\u6587\u4ef6\u53d1\u751f\u6539\u53d8\uff0c\u5219\u5feb\u7167\u5377\u4e2d\u4e0e\u4e4b\u5bf9\u5e94\u7684\u6587\u4ef6\u4e0d\u4f1a\u53d1\u751f\u6539\u53d8\u3002\u5feb\u7167\u5377\u4e3b\u8981\u7528\u4e8e\u8f85\u52a9\u5907\u4efd\u6587\u4ef6\u3002 \u8fd9\u91cc\u53ea\u7b80\u5355\u4ecb\u7ecd\uff0c<a href=\"http:\/\/www.360doc.com\/content\/13\/0522\/16\/11801283_287305129.shtml\" target=\"_blank\" rel=\"noopener\">\u70b9\u51fb\u67e5\u770b\u8be6\u7ec6\u4ecb\u7ecd<\/a><\/p><\/blockquote>\n<p><strong>\u90e8\u7f72lvm\u73af\u5883<\/strong><\/p>\n<pre class=\"prettyprint hljs-light\"><code class=\"hljs livescript\">\u6dfb\u52a0\u786c\u76d8; \u8fd9\u91cc\u6211\u4eec\u76f4\u63a5\u5b9e\u73b0SCSI\u786c\u76d8\u7684\u70ed\u63d2\u62d4, \u9996\u5148\u5728\u865a\u62df\u673a\u4e2d\u6dfb\u52a0\u4e00\u5757\u786c\u76d8, \u4e0d\u91cd\u542f\r\n\r\n[root<span class=\"hljs-property\">@node1 ~]<span class=\"hljs-comment\"># ls \/dev\/sd* \u00a0 #\u53ea\u6709\u4ee5\u4e0b\u51e0\u5757\u786c\u76d8, \u4f46\u662f\u6211\u4eec\u4e0d\u91cd\u542f\u53ef\u4ee5\u8ba9\u7cfb\u7edf\u8bc6\u522b\u65b0\u6dfb\u52a0\u7684\u786c\u76d8\r\n\/dev\/sda \u00a0\/dev\/sda1 \u00a0\/dev\/sda2\r\n\r\n[root@node1 ~]# echo '- - -' &gt; \/sys\/class\/scsi_host\/host0\/scan \r\n[root@node1 ~]# echo '- - -' &gt; \/sys\/class\/scsi_host\/host1\/scan \r\n[root@node1 ~]# echo '- - -' &gt; \/sys\/class\/scsi_host\/host2\/scan \r\n\r\n[root@node1 ~]# ls \/dev\/sd* \u00a0 \u00a0#\u770b\uff01sdb\u8bc6\u522b\u51fa\u6765\u4e86\r\n\/dev\/sda \u00a0\/dev\/sda1 \u00a0\/dev\/sda2 \u00a0\/dev\/sdb\r\n\r\n\r\n[root@node1 ~]# fdisk \/dev\/sdb \u00a0 #\u5206\u533a\r\nDevice contains neither a valid DOS partition table, nor Sun, SGI <span class=\"hljs-keyword\">or OSF disklabel\r\nBuilding a new DOS disklabel with disk identifier <span class=\"hljs-number\">0xd353d192.\r\nChanges will remain in memory only, until you decide to write them.\r\nAfter <span class=\"hljs-literal\">that, of course, the previous content won<span class=\"hljs-string\">'t be recoverable.\r\n\r\nWarning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)\r\n\r\nWARNING: DOS-compatible mode is deprecated. It's strongly recommended to\r\n \u00a0 \u00a0 \u00a0 \u00a0 switch off the mode (command 'c') and change display units to\r\n \u00a0 \u00a0 \u00a0 \u00a0 sectors (command 'u').\r\n\r\nCommand (m for help): n\r\nCommand action\r\n \u00a0 e \u00a0 extended\r\n \u00a0 p \u00a0 primary partition (1-4)\r\np\r\nPartition number (1-4): 1\r\nFirst cylinder (1-2610, default 1): \r\nUsing default value 1\r\nLast cylinder, +cylinders or +size{K,M,G} (1-2610, default 2610): +15G\r\n\r\nCommand (m for help): t\r\nSelected partition 1\r\nHex code (type L to list codes): 8e\r\nChanged system type of partition 1 to 8e (Linux LVM)\r\n\r\nCommand (m for help): w\r\nThe partition table has been altered!\r\n\r\nCalling ioctl() to re-read partition table.\r\nSyncing disks.\r\nYou have new mail in \/var\/spool\/mail\/root\r\n[root@node1 ~]# partx -a \/dev\/sdb\r\n<span class=\"hljs-attribute\">BLKPG: Device or resource busy\r\nerror adding partition 1\r\n\r\n##\u521b\u5efa\u903b\u8f91\u5377\r\n[root@node1 ~]# pvcreate \/dev\/sdb1\r\n \u00a0Physical volume \"\/dev\/sdb1\" successfully created\r\n[root@node1 ~]# vgcreate myvg \/dev\/sdb1 \r\n \u00a0Volume group \"myvg\" successfully created\r\n[root@node1 ~]# lvcreate -n mydata -L 5G myvg \r\n \u00a0Logical volume \"mydata\" created.\r\n\r\n[root@node1 ~]# mkfs.ext4 \/dev\/mapper\/myvg-mydata \u00a0 #\u683c\u5f0f\u5316\r\n[root@node1 ~]# mkdir \/lvm_data\r\n[root@node1 ~]# mount \/dev\/mapper\/myvg-mydata \/lvm_data \u00a0#\u6302\u8f7d\u5230\/lvm_data\r\n\r\n\r\n[root@node1 ~]# vim \/etc\/my.cnf \u00a0 \u00a0#\u4fee\u6539mysql\u914d\u7f6e\u6587\u4ef6\u7684datadir\u5982\u4e0b\r\n\r\ndatadir=\/lvm_data\r\n\r\n[root@node1 ~]# service mysqld restart \u00a0#\u91cd\u542fMySQL\r\n\r\n####\u91cd\u65b0\u5bfc\u5165employees\u6570\u636e\u5e93########\u7565\u8fc7####\r\n\r\n<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/code><\/pre>\n<p><strong>\u67e5\u770b\u6570\u636e\u5e93\u7684\u4fe1\u606f<\/strong><\/p>\n<pre class=\"prettyprint hljs-light\"><code class=\"language-sql hljs\">mysql&gt; <span class=\"hljs-operator\"><span class=\"hljs-keyword\">SHOW DATABASES; \u00a0 \u00a0#\u67e5\u770b\u5f53\u524d\u7684\u6570\u636e\u5e93, \u6211\u4eec\u7684\u6570\u636e\u5e93\u4e3aemployees\r\n+<span class=\"hljs-comment\">--------------------+\r\n| Database \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 |\r\n+--------------------+\r\n| information_schema |\r\n| employees \u00a0 \u00a0 \u00a0 \u00a0 \u00a0|\r\n| mysql \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0|\r\n| test \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 |\r\n+--------------------+\r\n4 rows in set (<span class=\"hljs-number\">0.00 sec)\r\n\r\nmysql&gt; USE employees;\r\nDatabase changed\r\nmysql&gt; SHOW TABLES; \u00a0 \u00a0 \u00a0 \u00a0 #\u67e5\u770b\u5f53\u524d\u5e93\u4e2d\u7684\u8868\r\n+---------------------+\r\n| Tables_in_employees |\r\n+---------------------+\r\n| departments \u00a0 \u00a0 \u00a0 \u00a0 |\r\n| dept_emp \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0|\r\n| dept_manager \u00a0 \u00a0 \u00a0 \u00a0|\r\n| employees \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 |\r\n| salaries \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0|\r\n| titles \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0|\r\n+---------------------+\r\n6 rows in set (0.00 sec)\r\n\r\nmysql&gt; SELECT COUNT(*) FROM employees; \u00a0 #\u7531\u4e8e\u7bc7\u5e45\u539f\u56e0, \u6211\u4eec\u8fd9\u91cc\u53ea\u770b\u4e00\u4e0bemployees\u7684\u884c\u6570\u4e3a300024\r\n+----------+\r\n| COUNT(*) |\r\n+----------+\r\n| \u00a0 300024 |\r\n+----------+\r\n1 row in set (0.05 sec)\r\n\r\n<\/span><\/span><\/span><\/span><\/code><\/pre>\n<p><strong>\u521b\u5efa\u5feb\u7167\u5377\u5e76\u5907\u4efd<\/strong><\/p>\n<pre class=\"prettyprint hljs-light\"><code class=\"hljs perl\">mysql&gt; FLUSH TABLES WITH READ LOCK; \u00a0 \u00a0 <span class=\"hljs-comment\">#\u9501\u5b9a\u6240\u6709\u8868\r\nQuery OK, <span class=\"hljs-number\">0 rows affected (0.00 sec)\r\n\r\n[root<span class=\"hljs-variable\">@node1 lvm_data]# lvcreate -L 1G -n mydata-snap -p r -s \/dev\/mapper\/myvg-mydata \u00a0 #\u521b\u5efa\u5feb\u7167\u5377\r\n \u00a0Logical volume <span class=\"hljs-string\">\"mydata-snap\" created.\r\n\r\nmysql&gt; UNLOCK TABLES; \u00a0#\u89e3\u9501\u6240\u6709\u8868\r\nQuery OK, 0 rows affected (0.00 sec)\r\n\r\n[root@node1 lvm_data]# mkdir \/lvm_snap \u00a0#\u521b\u5efa\u6587\u4ef6\u5939\r\n[root@node1 lvm_data]# mount \/dev\/myvg\/mydata-snap \/lvm_snap\/ \u00a0#\u6302\u8f7dsnap\r\nmount: block device \/dev\/mapper\/myvg-mydata--snap is <span class=\"hljs-keyword\">write-protected, mounting read-only\r\n\r\n[root@node1 lvm_data]# cd \/lvm_snap\/\r\n[root@node1 lvm_snap]# ls\r\nemployees \u00a0ibdata1 \u00a0ib_logfile0 \u00a0ib_logfile1 \u00a0mysql \u00a0mysql-bin.000001 \u00a0mysql-bin.000002 \u00a0mysql-bin.000003 \u00a0mysql-bin.index \u00a0test\r\n[root@node1 lvm_snap]# tar cf \/tmp\/mysqlback.tar * \u00a0#\u6253\u5305\u6587\u4ef6\u5230\/tmp\/mysqlback.tar\r\n\r\n[root@node1 ~]# umount \/lvm_snap\/ \u00a0#\u5378\u8f7dsnap\r\n[root@node1 ~]# lvremove myvg mydata-snap \u00a0#\u5220\u9664snap\r\n\r\n\r\n<\/span><\/span><\/span><\/span><\/span><\/code><\/pre>\n<p><strong>\u6062\u590d\u6570\u636e<\/strong><\/p>\n<pre class=\"prettyprint hljs-light\"><code class=\"language-sql hljs\">[root@node1 lvm_snap]# rm -rf \/lvm_data<span class=\"hljs-comment\">\/*\r\n[root@node1 ~]# service mysqld start \u00a0 \u00a0#\u542f\u52a8MySQL, \u5982\u679c\u662f\u7f16\u8bd1\u5b89\u88c5\u7684\u5e94\u8be5\u4e0d\u80fd\u542f\u52a8(\u9700\u91cd\u65b0\u521d\u59cb\u5316), \u5982\u679crpm\u5b89\u88c5\u5219\u4f1a\u91cd\u65b0\u521d\u59cb\u5316\u6570\u636e\u5e93\r\n\r\n\r\nmysql&gt; SHOW DATABASES; \u00a0 #\u67e5\u770b\u6570\u636e\u5e93, \u6570\u636e\u4e22\u5931!\r\n+--------------------+\r\n| Database \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 |\r\n+--------------------+\r\n| information_schema |\r\n| mysql \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0|\r\n| test \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 |\r\n+--------------------+\r\n3 rows in set (0.00 sec)\r\n\r\n[root@node1 ~]# cd \/lvm_data\/\r\n[root@node1 lvm_data]# rm -rf * #\u5220\u9664\u6240\u6709\u6587\u4ef6\r\n[root@node1 lvm_data]# tar xf \/tmp\/mysqlback.tar \u00a0 \u00a0 #\u89e3\u538b\u5907\u4efd\u6570\u636e\u5e93\u5230\u6b64\u6587\u4ef6\u5939 \r\n[root@node1 lvm_data]# ls \u00a0#\u67e5\u770b\u5f53\u524d\u7684\u6587\u4ef6\r\nemployees \u00a0ibdata1 \u00a0ib_logfile0 \u00a0ib_logfile1 \u00a0mysql \u00a0mysql-bin.000001 \u00a0mysql-bin.000002 \u00a0mysql-bin.000003 \u00a0mysql-bin.index \u00a0test\r\n\r\nmysql&gt; SHOW DATABASES; \u00a0#\u6570\u636e\u6062\u590d\u4e86\r\n+--------------------+\r\n| Database \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 |\r\n+--------------------+\r\n| information_schema |\r\n| employees \u00a0 \u00a0 \u00a0 \u00a0 \u00a0|\r\n| mysql \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0|\r\n| test \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 |\r\n+--------------------+\r\n4 rows in set (0.00 sec)\r\n\r\n\r\n##\u5b8c\u6210\r\n<\/span><\/code><\/pre>\n<h3 id=\"\u4f7f\u7528xtrabackup\u5907\u4efd\"><a name=\"\u4f7f\u7528Xtrabackup\u5907\u4efd\"><\/a>\u4f7f\u7528Xtrabackup\u5907\u4efd<\/h3>\n<blockquote><p>\u4e3a\u4e86\u66f4\u597d\u5730\u6f14\u793a, \u6211\u4eec\u8fd9\u6b21\u4f7f\u7528<code>mariadb-5.5<\/code>\u7684\u7248\u672c, \u4f7f\u7528<code>xtrabackup<\/code>\u4f7f\u7528InnoDB\u80fd\u591f\u53d1\u6325\u5176\u6700\u5927\u529f\u6548, \u5e76\u4e14InnoDB\u7684\u6bcf\u4e00\u5f20\u8868\u5fc5\u987b\u4f7f\u7528\u5355\u72ec\u7684\u8868\u7a7a\u95f4, \u6211\u4eec\u9700\u8981\u5728\u914d\u7f6e\u6587\u4ef6\u4e2d\u6dfb\u52a0\u00a0<code>innodb_file_per_table = ON<\/code>\u00a0\u6765\u5f00\u542f<\/p><\/blockquote>\n<p><strong>\u4e0b\u8f7d\u5b89\u88c5<code>xtrabackup<\/code><\/strong><\/p>\n<pre class=\"prettyprint hljs-light\"><code class=\"hljs cpp\">\u6211\u4eec\u8fd9\u91cc\u901a\u8fc7wget percona\u5b98\u65b9\u7684rpm\u5305\u8fdb\u884c\u5b89\u88c5\r\n[root@node1 ~]<span class=\"hljs-preprocessor\"># wget https:<span class=\"hljs-comment\">\/\/www.percona.com\/downloads\/XtraBackup\/Percona-XtraBackup-2.3.4\/binary\/redhat\/6\/x86_64\/percona-xtrabackup-2.3.4-1.el6.x86_64.rpm \u00a0 \r\n[root@node1 ~]# yum localinstall percona-xtrabackup-<span class=\"hljs-number\">2.3.4-1.el6.x86_64.rpm \u00a0 #\u9700\u8981EPEL\u6e90\r\n<\/span><\/span><\/span><\/code><\/pre>\n<p><strong><code>xtrabackup<\/code>\u4ecb\u7ecd<\/strong><\/p>\n<blockquote><p><code>Xtrabackup<\/code>\u662f\u7531<code>percona<\/code>\u63d0\u4f9b\u7684<code>mysql<\/code>\u6570\u636e\u5e93\u5907\u4efd\u5de5\u5177\uff0c\u636e\u5b98\u65b9\u4ecb\u7ecd\uff0c\u8fd9\u4e5f\u662f\u4e16\u754c\u4e0a\u60df\u4e00\u4e00\u6b3e\u5f00\u6e90\u7684\u80fd\u591f\u5bf9innodb\u548cxtradb\u6570\u636e\u5e93\u8fdb\u884c\u70ed\u5907\u7684\u5de5\u5177\u3002\u7279\u70b9\uff1a<\/p>\n<ol class=\" list-paddingleft-2\">\n<li>\u5907\u4efd\u8fc7\u7a0b\u5feb\u901f\u3001\u53ef\u9760\uff1b<\/li>\n<li>\u5907\u4efd\u8fc7\u7a0b\u4e0d\u4f1a\u6253\u65ad\u6b63\u5728\u6267\u884c\u7684\u4e8b\u52a1\uff1b<\/li>\n<li>\u80fd\u591f\u57fa\u4e8e\u538b\u7f29\u7b49\u529f\u80fd\u8282\u7ea6\u78c1\u76d8\u7a7a\u95f4\u548c\u6d41\u91cf\uff1b<\/li>\n<li>\u81ea\u52a8\u5b9e\u73b0\u5907\u4efd\u68c0\u9a8c\uff1b<\/li>\n<li>\u8fd8\u539f\u901f\u5ea6\u5feb\uff1b<\/li>\n<\/ol>\n<p><strong>\u6458\u81ea\u9a6c\u54e5\u7684\u6587\u6863<\/strong><\/p><\/blockquote>\n<p><strong><code>xtrabackup<\/code>\u5b9e\u73b0\u5b8c\u5168\u5907\u4efd<\/strong><\/p>\n<blockquote><p>\u6211\u4eec\u8fd9\u91cc\u4f7f\u7528<code>xtrabackup<\/code>\u7684\u524d\u7aef\u914d\u7f6e\u5de5\u5177<code>innobackupex<\/code>\u6765\u5b9e\u73b0\u5bf9\u6570\u636e\u5e93\u7684\u5b8c\u5168\u5907\u4efd<\/p>\n<p>\u4f7f\u7528<code>innobackupex<\/code>\u5907\u4efd\u65f6, \u4f1a\u8c03\u7528<code>xtrabackup<\/code>\u5907\u4efd\u6240\u6709\u7684<strong>InnoDB<\/strong>\u8868, \u590d\u5236\u6240\u6709\u5173\u4e8e\u8868\u7ed3\u6784\u5b9a\u4e49\u7684\u76f8\u5173\u6587\u4ef6(.frm)\u3001\u4ee5\u53ca<strong>MyISAM<\/strong>\u3001<strong>MERGE<\/strong>\u3001<strong>CSV<\/strong>\u548c<strong>ARCHIVE<\/strong>\u8868\u7684\u76f8\u5173\u6587\u4ef6, \u540c\u65f6\u8fd8\u4f1a\u5907\u4efd\u89e6\u53d1\u5668\u548c\u6570\u636e\u5e93\u914d\u7f6e\u6587\u4ef6\u4fe1\u606f\u76f8\u5173\u7684\u6587\u4ef6, \u8fd9\u4e9b\u6587\u4ef6\u4f1a\u88ab\u4fdd\u5b58\u81f3\u4e00\u4e2a\u4ee5\u65f6\u95f4\u547d\u540d\u7684\u76ee\u5f55.<\/p><\/blockquote>\n<p><strong>\u5907\u4efd\u8fc7\u7a0b<\/strong><\/p>\n<pre class=\"prettyprint hljs-light\"><code class=\"hljs coffeescript\">[root<span class=\"hljs-property\">@node1 ~]<span class=\"hljs-comment\"># mkdir \/extrabackup \u00a0#\u521b\u5efa\u5907\u4efd\u76ee\u5f55\r\n[root@node1 ~]# innobackupex --user=root \/extrabackup\/ #\u5907\u4efd\u6570\u636e\r\n###################\u63d0\u793acomplete\u8868\u793a\u6210\u529f*********************\r\n\r\n[root@node1 ~]# ls \/extrabackup\/ \u00a0#\u770b\u5230\u5907\u4efd\u76ee\u5f55\r\n<span class=\"hljs-number\">2016-04-27_07-30-48 \r\n\r\n<\/span><\/span><\/span><\/code><\/pre>\n<blockquote><p>\u4e00\u822c\u60c5\u51b5, \u5907\u4efd\u5b8c\u6210\u540e, \u6570\u636e\u4e0d\u80fd\u7528\u4e8e\u6062\u590d\u64cd\u4f5c, \u56e0\u4e3a\u5907\u4efd\u7684\u6570\u636e\u4e2d\u53ef\u80fd\u4f1a\u5305\u542b\u5c1a\u672a\u63d0\u4ea4\u7684\u4e8b\u52a1\u6216\u5df2\u7ecf\u63d0\u4ea4\u4f46\u5c1a\u672a\u540c\u6b65\u81f3\u6570\u636e\u6587\u4ef6\u4e2d\u7684\u4e8b\u52a1\u3002\u56e0\u6b64, \u6b64\u65f6\u7684\u6570\u636e\u6587\u4ef6\u4ecd\u4e0d\u4e00\u81f4, \u6240\u4ee5\u6211\u4eec\u9700\u8981\u201d\u51c6\u5907\u201d\u4e00\u4e2a\u5b8c\u5168\u5907\u4efd<\/p><\/blockquote>\n<pre class=\"prettyprint hljs-light\"><code class=\"hljs cpp\">[root@node1 ~]<span class=\"hljs-preprocessor\"># innobackupex --apply-log \/extrabackup\/<span class=\"hljs-number\">2016-04-27_07-30-48\/ \u00a0#\u6307\u5b9a\u5907\u4efd\u6587\u4ef6\u7684\u76ee\u5f55\r\n\r\n#\u4e00\u822c\u60c5\u51b5\u4e0b\u4e0b\u9762\u4e09\u884c\u7ed3\u5c3e\u4ee3\u8868\u6210\u529f*****************\r\nInnoDB: Starting shutdown...\r\nInnoDB: Shutdown completed; <span class=\"hljs-built_in\">log sequence number 369661462\r\n160427 07:40:11 completed OK!\r\n\r\n[root@node1 ~]# cd \/extrabackup\/2016-04-27_07-30-48\/\r\n[root@node1 2016-04-27_07-30-48]# ls -hl \u00a0#\u67e5\u770b\u5907\u4efd\u6587\u4ef6\r\ntotal 31M\r\n-rw-r----- 1 root root \u00a0386 Apr 27 07:30 backup-my.cnf\r\ndrwx------ 2 root root 4.0K Apr 27 07:30 employees\r\n-rw-r----- 1 root root \u00a018M Apr 27 07:40 ibdata1\r\n-rw-r--r-- 1 root root 5.0M Apr 27 07:40 ib_logfile0\r\n-rw-r--r-- 1 root root 5.0M Apr 27 07:40 ib_logfile1\r\ndrwx------ 2 root root 4.0K Apr 27 07:30 mysql\r\ndrwx------ 2 root root 4.0K Apr 27 07:30 performance_schema\r\ndrwx------ 2 root root 4.0K Apr 27 07:30 test\r\n-rw-r----- 1 root root \u00a0 27 Apr 27 07:30 xtrabackup_binlog_info\r\n-rw-r--r-- 1 root root \u00a0 29 Apr 27 07:40 xtrabackup_binlog_pos_innodb\r\n-rw-r----- 1 root root \u00a0117 Apr 27 07:40 xtrabackup_checkpoints\r\n-rw-r----- 1 root root \u00a0470 Apr 27 07:30 xtrabackup_info\r\n-rw-r----- 1 root root 2.0M Apr 27 07:40 xtrabackup_logfile\r\n\r\n<\/span><\/span><\/span><\/code><\/pre>\n<p><strong>\u6062\u590d\u6570\u636e<\/strong><\/p>\n<pre class=\"prettyprint hljs-light\"><code class=\"hljs gherkin\">[root<span class=\"hljs-comment\">@node1 ~]# rm -rf \/data\/* \u00a0 #\u5220\u9664\u6570\u636e\u6587\u4ef6\r\n\r\n<span class=\"hljs-keyword\">***\u4e0d\u7528\u542f\u52a8\u6570\u636e\u5e93\u4e5f\u53ef\u4ee5\u8fd8\u539f*************\r\n\r\n[root@node1 ~]# innobackupex --copy-back \/extrabackup\/2016-04-27_07-30-48\/ \u00a0 #\u6062\u590d\u6570\u636e, \u8bb0\u6e05\u4f7f\u7528\u65b9\u6cd5\r\n\r\n#########\u6211\u4eec\u8fd9\u91cc\u662f\u7f16\u8bd1\u5b89\u88c5\u7684mariadb\u6240\u4ee5\u9700\u8981\u505a\u4e00\u4e9b\u64cd\u4f5c##########\r\n[root@node1 data]# killall mysqld\r\n\r\n[root@node1 ~]# chown -R mysql:mysql .\/* \r\n[root@node1 ~]# ll \/data\/ \u00a0 \u00a0 \u00a0#\u6570\u636e\u6062\u590d\r\ntotal 28704\r\n-rw-rw---- 1 mysql mysql \u00a0 \u00a016384 Apr 27 07:43 aria_log.00000001\r\n-rw-rw---- 1 mysql mysql \u00a0 \u00a0 \u00a0 52 Apr 27 07:43 aria_log_control\r\n-rw-rw---- 1 mysql mysql 18874368 Apr 27 07:43 ibdata1\r\n-rw-rw---- 1 mysql mysql \u00a05242880 Apr 27 07:43 ib_logfile0\r\n-rw-rw---- 1 mysql mysql \u00a05242880 Apr 27 07:43 ib_logfile1\r\n-rw-rw---- 1 mysql mysql \u00a0 \u00a0 \u00a0264 Apr 27 07:43 mysql-bin.000001\r\n-rw-rw---- 1 mysql mysql \u00a0 \u00a0 \u00a0 19 Apr 27 07:43 mysql-bin.index\r\n-rw-r----- 1 mysql mysql \u00a0 \u00a0 2166 Apr 27 07:43 node1.anyisalin.com.err\r\n\r\n\r\n[root@node1 data]# service mysqld restart\r\nMySQL server PID file could not be found! \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0[FAILED]\r\nStarting MySQL.. \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 [ \u00a0OK \u00a0]\r\n\r\nMariaDB [(none)]&gt; SHOW DATABASES; \u00a0#\u67e5\u770b\u6570\u636e\u5e93, \u5df2\u7ecf\u6062\u590d\r\n+--------------------+\r\n|<span class=\"hljs-string\"> Database \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 |\r\n+--------------------+\r\n| information_schema |\r\n| employees \u00a0 \u00a0 \u00a0 \u00a0 \u00a0|\r\n| mysql \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0|\r\n| performance_schema |\r\n| test \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 |\r\n+--------------------+\r\n5 rows in set (0.00 sec\r\n\r\n<\/span><\/span><\/span><\/code><\/pre>\n<p><strong>\u589e\u91cf\u5907\u4efd<\/strong><\/p>\n<pre class=\"prettyprint hljs-light\"><code class=\"hljs applescript\"><span class=\"hljs-comment\">#########\u521b\u5efa\u8fde\u4e24\u4e2a\u6570\u636e\u5e93\u4ee5\u4f9b\u6d4b\u8bd5#####################\r\nMariaDB [(none)]&gt; CREATE DATABASE TEST1;\r\nQuery OK, <span class=\"hljs-number\">1 row affected (0.00 sec)\r\n\r\nMariaDB [(none)]&gt; CREATE DATABASE TEST2;\r\nQuery OK, 1 row affected (0.00 sec)\r\n\r\n[root@node1 ~]# innobackupex --incremental \/extrabackup\/ --incremental-basedir=\/extrabackup\/2016-04-27_07-30-48\/ \r\n\r\n[root@node1 ~]# ls \/extrabackup\/2016-04-27_07-57-22\/ #\u67e5\u770b\u5907\u4efd\u6587\u4ef6\r\ntotal 96\r\n-rw-r----- 1 root root \u00a0 386 Apr 27 07:57 backup-my.cnf\r\ndrwx------ 2 root root \u00a04096 Apr 27 07:57 employees\r\n-rw-r----- 1 root root 49152 Apr 27 07:57 ibdata1.delta\r\n-rw-r----- 1 root root \u00a0 \u00a044 Apr 27 07:57 ibdata1.meta\r\ndrwx------ 2 root root \u00a04096 Apr 27 07:57 mysql\r\ndrwx------ 2 root root \u00a04096 Apr 27 07:57 performance_schema\r\ndrwx------ 2 root root \u00a04096 Apr 27 07:57 test\r\ndrwx------ 2 root root \u00a04096 Apr 27 07:57 TEST1\r\ndrwx------ 2 root root \u00a04096 Apr 27 07:57 TEST2\r\n-rw-r----- 1 root root \u00a0 \u00a021 Apr 27 07:57 xtrabackup_binlog_info\r\n-rw-r----- 1 root root \u00a0 123 Apr 27 07:57 xtrabackup_checkpoints\r\n-rw-r----- 1 root root \u00a0 530 Apr 27 07:57 xtrabackup_info\r\n-rw-r----- 1 root root \u00a02560 Apr 27 07:57 xtrabackup_logfile\r\n\r\n<\/span><\/span><\/code><\/pre>\n<blockquote><p>BASEDIR\u6307\u7684\u662f\u5b8c\u5168\u5907\u4efd\u6240\u5728\u7684\u76ee\u5f55\uff0c\u6b64\u547d\u4ee4\u6267\u884c\u7ed3\u675f\u540e\uff0c<code>innobackupex<\/code>\u547d\u4ee4\u4f1a\u5728<code>\/extrabackup<\/code>\u76ee\u5f55\u4e2d\u521b\u5efa\u4e00\u4e2a\u65b0\u7684\u4ee5\u65f6\u95f4\u547d\u540d\u7684\u76ee\u5f55\u4ee5\u5b58\u653e\u6240\u6709\u7684\u589e\u91cf\u5907\u4efd\u6570\u636e\u3002\u53e6\u5916\uff0c\u5728\u6267\u884c\u8fc7\u589e\u91cf\u5907\u4efd\u4e4b\u540e\u518d\u4e00\u6b21\u8fdb\u884c\u589e\u91cf\u5907\u4efd\u65f6\uff0c\u5176<code>--incremental-basedir<\/code>\u5e94\u8be5\u6307\u5411\u4e0a\u4e00\u6b21\u7684\u589e\u91cf\u5907\u4efd\u6240\u5728\u7684\u76ee\u5f55\u3002<\/p>\n<p>\u9700\u8981\u6ce8\u610f\u7684\u662f\uff0c\u589e\u91cf\u5907\u4efd\u4ec5\u80fd\u5e94\u7528\u4e8eInnoDB\u6216XtraDB\u8868\uff0c\u5bf9\u4e8eMyISAM\u8868\u800c\u8a00\uff0c\u6267\u884c\u589e\u91cf\u5907\u4efd\u65f6\u5176\u5b9e\u8fdb\u884c\u7684\u662f\u5b8c\u5168\u5907\u4efd\u3002<\/p><\/blockquote>\n<p><strong>\u6574\u7406\u589e\u91cf\u5907\u4efd<\/strong><\/p>\n<pre class=\"prettyprint hljs-light\"><code class=\"hljs cpp\">[root@node1 ~]<span class=\"hljs-preprocessor\"># innobackupex --apply-log --redo-only \/extrabackup\/<span class=\"hljs-number\">2016-04-27_07-30-48\/\r\n[root@node1 ~]# innobackupex --apply-log --redo-only \/extrabackup\/2016-04-27_07-30-48\/ --incremental-dir=\/extrabackup\/2016-04-27_07-5\r\n7-22\/\r\n<\/span><\/span><\/code><\/pre>\n<p><strong>\u6062\u590d\u6570\u636e<\/strong><\/p>\n<pre class=\"prettyprint hljs-light\"><code class=\"hljs haskell\">[root@node1 ~]# rm -rf \/<span class=\"hljs-typedef\"><span class=\"hljs-keyword\">data\/* \u00a0 #\u5220\u9664\u6570\u636e\r\n\r\n[root@node1 ~]# innobackupex <span class=\"hljs-comment\">--copy-back \/extrabackup\/2016-04-27_07-30-48\/ \u00a0 \u00a0 #\u6574\u7406\u589e\u91cf\u5907\u4efd\u4e4b\u540e\u53ef\u4ee5\u76f4\u63a5\u901a\u8fc7\u5168\u91cf\u5907\u4efd\u8fd8\u539f\r\n\r\n[root@node1 ~]# chown -<span class=\"hljs-type\">R mysql.mysql \/data\/\r\n[root@node1 ~]# ls \/data\/ -l\r\n<span class=\"hljs-title\">total <span class=\"hljs-number\">28732\r\n-rw-rw---- 1 mysql mysql \u00a0 \u00a0 8192 Apr 27 08:05 aria_log.00000001\r\n-rw-rw---- 1 mysql mysql \u00a0 \u00a0 \u00a0 52 Apr 27 08:05 aria_log_control\r\ndrwx------ 2 mysql mysql \u00a0 \u00a0 4096 Apr 27 08:05 employees\r\n-rw-r----- 1 mysql mysql 18874368 Apr 27 08:05 ibdata1\r\n-rw-r----- 1 mysql mysql \u00a05242880 Apr 27 08:05 ib_logfile0\r\n-rw-r----- 1 mysql mysql \u00a05242880 Apr 27 08:05 ib_logfile1\r\ndrwx------ 2 mysql mysql \u00a0 \u00a0 4096 Apr 27 08:05 mysql\r\n-rw-rw---- 1 mysql mysql \u00a0 \u00a0 \u00a0245 Apr 27 08:05 mysql-bin.000001\r\n-rw-rw---- 1 mysql mysql \u00a0 \u00a0 \u00a0 19 Apr 27 08:05 mysql-bin.index\r\n-rw-r----- 1 mysql mysql \u00a0 \u00a0 1812 Apr 27 08:05 node1.anyisalin.com.err\r\n-rw-rw---- 1 mysql mysql \u00a0 \u00a0 \u00a0 \u00a05 Apr 27 08:05 node1.anyisalin.com.pid\r\ndrwx------ 2 mysql mysql \u00a0 \u00a0 4096 Apr 27 08:05 performance_schema\r\ndrwx------ 2 mysql mysql \u00a0 \u00a0 4096 Apr 27 08:05 test\r\ndrwx------ 2 mysql mysql \u00a0 \u00a0 4096 Apr 27 08:05 TEST1\r\ndrwx------ 2 mysql mysql \u00a0 \u00a0 4096 Apr 27 08:05 TEST2\r\n-rw-r----- 1 mysql mysql \u00a0 \u00a0 \u00a0 29 Apr 27 08:05 xtrabackup_binlog_pos_innodb\r\n-rw-r----- 1 mysql mysql \u00a0 \u00a0 \u00a0530 Apr 27 08:05 xtrabackup_info\r\n\r\nMariaDB [(none)]&gt; SHOW DATABASES; \u00a0#\u6570\u636e\u8fd8\u539f\r\n+--------------------+\r\n| Database \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 |\r\n+--------------------+\r\n| information_schema |\r\n| TEST1 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0|\r\n| TEST2 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0|\r\n| employees \u00a0 \u00a0 \u00a0 \u00a0 \u00a0|\r\n| mysql \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0|\r\n| performance_schema |\r\n| test \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 |\r\n+--------------------+\r\n7 rows in set (0.00 sec)\r\n\r\n<span class=\"hljs-preprocessor\">#\u5173\u4e8extrabackup\u8fd8\u6709\u5f88\u591a\u5f3a\u5927\u7684\u529f\u80fd\u6ca1\u6709\u53d9\u8ff0\u3001\u6709\u5174\u8da3\u53ef\u4ee5\u53bb\u770b\u5b98\u65b9\u6587\u6863\r\n<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/code><\/pre>\n<h2 id=\"\u603b\u7ed3\"><a name=\"\u603b\u7ed3\"><\/a>\u603b\u7ed3<\/h2>\n<table>\n<thead>\n<tr class=\"firstRow\">\n<th align=\"left\">\u5907\u4efd\u65b9\u6cd5<\/th>\n<th align=\"right\">\u5907\u4efd\u901f\u5ea6<\/th>\n<th align=\"center\">\u6062\u590d\u901f\u5ea6<\/th>\n<th>\u4fbf\u6377\u6027<\/th>\n<th>\u529f\u80fd<\/th>\n<th>\u4e00\u822c\u7528\u4e8e<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td align=\"left\">cp<\/td>\n<td align=\"right\">\u5feb<\/td>\n<td align=\"center\">\u5feb<\/td>\n<td>\u4e00\u822c\u3001\u7075\u6d3b\u6027\u4f4e<\/td>\n<td>\u5f88\u5f31<\/td>\n<td>\u5c11\u91cf\u6570\u636e\u5907\u4efd<\/td>\n<\/tr>\n<tr>\n<td align=\"left\">mysqldump<\/td>\n<td align=\"right\">\u6162<\/td>\n<td align=\"center\">\u6162<\/td>\n<td>\u4e00\u822c\u3001\u53ef\u65e0\u89c6\u5b58\u50a8\u5f15\u64ce\u7684\u5dee\u5f02<\/td>\n<td>\u4e00\u822c<\/td>\n<td>\u4e2d\u5c0f\u578b\u6570\u636e\u91cf\u7684\u5907\u4efd<\/td>\n<\/tr>\n<tr>\n<td align=\"left\">lvm2\u5feb\u7167<\/td>\n<td align=\"right\">\u5feb<\/td>\n<td align=\"center\">\u5feb<\/td>\n<td>\u4e00\u822c\u3001\u652f\u6301\u51e0\u4e4e\u70ed\u5907\u3001\u901f\u5ea6\u5feb<\/td>\n<td>\u4e00\u822c<\/td>\n<td>\u4e2d\u5c0f\u578b\u6570\u636e\u91cf\u7684\u5907\u4efd<\/td>\n<\/tr>\n<tr>\n<td align=\"left\">xtrabackup<\/td>\n<td align=\"right\">\u8f83\u5feb<\/td>\n<td align=\"center\">\u8f83\u5feb<\/td>\n<td>\u5b9e\u73b0innodb\u70ed\u5907\u3001\u5bf9\u5b58\u50a8\u5f15\u64ce\u6709\u8981\u6c42<\/td>\n<td>\u5f3a\u5927<\/td>\n<td>\u8f83\u5927\u89c4\u6a21\u7684\u5907\u4efd<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<blockquote><p>\u5176\u5b9e\u6211\u4eec\u8fd8\u53ef\u4ee5\u901a\u8fc7<code>Master-Slave Replication<\/code>\u00a0\u8fdb\u884c\u5907\u4efd\u3002<\/p>\n<p>\u8f6c\u81ea\uff1ahttp:\/\/www.cnblogs.com\/liangshaoye\/p\/5464794.html<\/p><\/blockquote>\n","protected":false},"excerpt":{"rendered":"<p>\u524d\u8a00 \u6211\u4eec\u8bd5\u7740\u60f3\u4e00\u60f3, \u5728\u751f\u4ea7\u73af\u5883\u4e2d\u4ec0\u4e48\u6700\u91cd\u8981\uff1f\u5982\u679c\u6211\u4eec\u670d\u52a1\u5668\u7684\u786c\u4ef6\u574f\u4e86\u53ef\u4ee5\u7ef4\u4fee&#8230;<br \/><a class=\"read-more-button\" href=\"https:\/\/freesilo.com\/?p=891\">Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[18],"tags":[],"class_list":["post-891","post","type-post","status-publish","format-standard","hentry","category-mysql"],"_links":{"self":[{"href":"https:\/\/freesilo.com\/index.php?rest_route=\/wp\/v2\/posts\/891","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/freesilo.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/freesilo.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/freesilo.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/freesilo.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=891"}],"version-history":[{"count":1,"href":"https:\/\/freesilo.com\/index.php?rest_route=\/wp\/v2\/posts\/891\/revisions"}],"predecessor-version":[{"id":893,"href":"https:\/\/freesilo.com\/index.php?rest_route=\/wp\/v2\/posts\/891\/revisions\/893"}],"wp:attachment":[{"href":"https:\/\/freesilo.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=891"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/freesilo.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=891"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/freesilo.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=891"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}