{"id":378,"date":"2013-08-01T17:52:50","date_gmt":"2013-08-01T09:52:50","guid":{"rendered":"http:\/\/www.royalwzy.com\/?p=378"},"modified":"2015-09-02T07:32:50","modified_gmt":"2015-09-01T23:32:50","slug":"mysql%e5%ad%a6%e4%b9%a05-mysql5-1%e7%9a%84%e7%89%a9%e7%90%86%e7%bb%93%e6%9e%84","status":"publish","type":"post","link":"http:\/\/www.royalwzy.com\/?p=378","title":{"rendered":"MySQL\u5b66\u4e605–MySQL5.1\u7684\u7269\u7406\u7ed3\u6784"},"content":{"rendered":"
Mysql\u7684\u7269\u7406\u7ed3\u6784<\/p>\n
    \n
  1. \u65e5\u5fd7\u6587\u4ef6\n
      \n
    1. error log:\n
        \n
      1. \u8bb0\u5f55mysql\u542f\u52a8,\u5173\u95ed\u548c\u8fd0\u884c\u65f6\u4ea7\u751f\u7684\u91cd\u5927\u7684\u9519\u8bef\u7684\u4fe1\u606f;<\/li>\n
      2. \u5982\u679cmysqld\u8b66\u544a\u4e00\u4e2a\u8868\u9700\u8981\u81ea\u52a8\u7684\u68c0\u67e5\u6216\u8005\u4fee\u590d,\u4e5f\u4f1a\u8bb0\u5f55\u4e00\u4e2a\u9519\u8bef\u65e5\u5fd7;<\/li>\n
      3. \u53ef\u4ee5\u4f7f\u7528–log-error=file_name\u9009\u9879\u6765\u6307\u5b9a\u9519\u8bef\u65e5\u5fd7\u6587\u4ef6,\u5982\u679c\u6ca1\u6709\u6307\u5b9a,\u5219\u7cfb\u7edf\u9ed8\u8ba4\u5728data\u76ee\u5f55\u4e0b\u751f\u4ea7\u4e00\u4e2ahostname.err\u7684\u6587\u4ef6;<\/li>\n
      4. \u4e5f\u53ef\u4ee5\u5728\u914d\u7f6e\u6587\u4ef6\u4e2d\u914d\u7f6elog_error\u53d8\u91cf;<\/li>\n
      5. \u67e5\u8be2log_error\u7684\u4f4d\u7f6e:>show variables like ‘log_error’;\u67e5\u770b\u4e3b\u673a\u540d:>system hostname;<\/li>\n<\/ol>\n<\/li>\n
      6. binary log:\n
          \n
        1. \u8bb0\u5f55\u6240\u6709\u5bf9\u6570\u636e\u5e93\u66f4\u65b0\u548c\u6f5c\u5728\u7684\u66f4\u65b0\u8bed\u53e5(\u4e00\u4e2adelete\u8bed\u53e5,\u4f46\u662f\u6ca1\u6709\u5f71\u54cd\u7684\u884c),\u8bed\u53e5\u4ee5\u4e8b\u4ef6(event)\u7684\u65b9\u5f0f\u5b58\u50a8,\u540c\u65f6\u4e5f\u5305\u542b\u4e86\u66f4\u65b0\u8bed\u53e5\u6267\u884c\u7684\u65f6\u95f4\u4fe1\u606f;<\/li>\n
        2. \u5b83\u4e0d\u8bb0\u5f55\u90a3\u4e9b\u4e0d\u4fee\u6539\u4efb\u4f55\u6570\u636e\u7684\u8bed\u53e5,\u5982\u679c\u60f3\u8981\u8bb0\u5f55\u6240\u6709\u7684\u8bed\u53e5,\u53ef\u4ee5\u4f7f\u7528query log;<\/li>\n
        3. \u5b83\u4e3b\u8981\u7684\u76ee\u7684\u662f\u5728\u505a\u8fd8\u539f\u64cd\u4f5c\u65f6\u5c3d\u53ef\u80fd\u5168\u7684\u66f4\u65b0\u6570\u636e\u5e93,\u56e0\u4e3a\u5b83\u5305\u542b\u5728\u4e00\u6b21\u5907\u4efd\u540e\u7684\u6240\u6709\u66f4\u65b0\u64cd\u4f5c.\u5b83\u540c\u6837\u88ab\u7528\u5728master replication server\u4e2d\u4f5c\u4e3a\u4e00\u4e2a\u8bb0\u5f55\u53d1\u9001\u7ed9slave servers;<\/li>\n
        4. \u6253\u5f00\u4e8c\u8fdb\u5236\u65e5\u5fd7\u4f1a\u635f\u59311%\u7684\u6027\u80fd,\u4f46\u662f\u5b83\u5e26\u6765\u7684\u597d\u5904\u8fdc\u8fdc\u8d85\u8fc7\u8fd9\u4e9b;<\/li>\n
        5. \u67e5\u770b\u4e8c\u8fdb\u5236\u65e5\u5fd7\u662f\u5426\u6253\u5f00,\u9700\u8981\u67e5\u770blog_bin\u53c2\u6570\u662f\u5426\u662fON:>show variables like ‘binlog_format’;<\/li>\n
        6. \u547d\u4ee4\u884c\u53c2\u6570\n
            \n
          1. –log-bin=filename:\u8bb0\u5f55\u4e8c\u8fdb\u5236\u65e5\u5fd7\u6587\u4ef6\u7684\u4f4d\u7f6e,\u5c3d\u91cf\u6307\u5b9a\u8def\u5f84\u540d,\u5982\u679c\u4e0d\u6307\u5b9a\u7684\u8bdd\u5219\u4fdd\u5b58\u5728\u6570\u636e\u76ee\u5f55;<\/li>\n
          2. –log-bin-index=file:\u8bb0\u5f55\u4e8c\u8fdb\u5236\u65e5\u5fd7\u6587\u4ef6\u7d22\u5f15\u7684\u4f4d\u7f6e,\u4fdd\u5b58\u4e86\u65e5\u5fd7\u6587\u4ef6\u540d;<\/li>\n
          3. –max_binlog_size:\u5355\u4e2a\u6587\u4ef6\u6700\u5927\u591a\u5c11;<\/li>\n
          4. –binlog-do-db=db_name:\u54ea\u4e2a\u6570\u636e\u5e93\u4f7f\u7528,\u53ea\u6709\u8fd9\u4e2a\u6570\u636e\u5e93\u4f7f\u7528;<\/li>\n
          5. –binlog-ignore-db=db_name:\u54ea\u4e2a\u6570\u636e\u5e93\u4e0d\u4f7f\u7528,\u53ea\u6709\u8fd9\u4e2a\u6570\u636e\u5e93\u4e0d\u4f7f\u7528;<\/li>\n<\/ol>\n<\/li>\n
          6. \u7cfb\u7edf\u53d8\u91cf\n
              \n
            1. log_bin:\u65e5\u5fd7\u7684\u4f4d\u7f6e;<\/li>\n
            2. binlog_cache_size:\u4e8c\u8fdb\u5236\u65e5\u5fd7\u7f13\u5b58\u5927\u5c0f,\u662f\u6bcf\u4e00\u4e2a\u8fde\u63a5\u8fdb\u6765\u7684\u7ebf\u7a0b\u5206\u914d\u7684\u5927\u5c0f,\u4e0d\u662f\u6574\u4e2a\u670d\u52a1\u5668\u7684\u5927\u5c0f;<\/li>\n
            3. max_binlog_cache_size:\u6700\u5927\u7f13\u5b58\u5927\u5c0f;<\/li>\n
            4. max_binlog_size:\u5355\u4e2a\u6587\u4ef6\u6700\u5927\u5927\u5c0f,\u8d85\u8fc7\u6b64\u5927\u5c0f\u5219\u518d\u5206\u914d\u4e00\u4e2a\u6587\u4ef6,\u4f46\u662f\u4e00\u4e2a\u4e8b\u52a1\u5fc5\u987b\u5728\u4e00\u4e2a\u6587\u4ef6\u4e2d,\u6240\u4ee5\u53ef\u80fd\u4f1a\u7a0d\u5927\u70b9;<\/li>\n
            5. binlog_cache_use:\u5f53\u524d\u8fde\u63a5\u4f7f\u7528\u7684binlog\u7f13\u5b58\u7684\u4e8b\u52a1\u7684\u6570\u91cf,\u4f7f\u7528show status like ‘binlog_cache_use’\u67e5\u770b(show status\u547d\u4ee4\u663e\u793a\u4e86\u6240\u6709\u8fde\u63a5\u5230mysql\u670d\u52a1\u5668\u7684\u72b6\u6001\u503c);<\/li>\n
            6. binlog_cache_disk_use:\u5982\u679cbinlog_cache_use\u4e0d\u591f\u7528,\u5219\u5728\u78c1\u76d8\u4e0a\u7f13\u5b58,\u5e94\u8be5\u5c3d\u91cf\u907f\u514d;<\/li>\n
            7. binlog_do_db:\u8bbe\u7f6emaster-slave\u65f6\u4f7f\u7528;<\/li>\n
            8. binlog-ignore-db:\u8bbe\u7f6e\u54ea\u4e2a\u6570\u636e\u5e93\u4e0d\u8bb0\u5f55\u65e5\u5fd7;<\/li>\n
            9. sync_binlog:\u7f13\u5b58\u4e0e\u786c\u76d8\u7684\u540c\u6b65\u9891\u7387(commit\u591a\u5c11\u4e0b\u540c\u6b65\u4e00\u6b21,0\u8868\u793a\u670d\u52a1\u5668\u81ea\u52a8\u63a7\u5236);<\/li>\n<\/ol>\n<\/li>\n
            10. \u67e5\u770b\u5f53\u524d\u4e8c\u8fdb\u5236\u6587\u4ef6\u7684\u540d\u79f0\u548c\u5927\u5c0f,show binary\/master logs;<\/li>\n
            11. \u5982\u679c\u4e0d\u6307\u5b9a\u4e8c\u8fdb\u5236\u65e5\u5fd7\u6587\u4ef6\u7684\u4f4d\u7f6e,\u9ed8\u8ba4\u5b58\u653e\u5728data\u6587\u4ef6\u5939\u4e0b,\u65e5\u5fd7\u6587\u4ef6\u662f:mysql-bin.xxxxxxx,\u7d22\u5f15\u6587\u4ef6\u662fmysql-bin.index;<\/li>\n
            12. \u5982\u679c\u8981\u5207\u6362\u65e5\u5fd7\u7684\u8bdd,\u6267\u884cflush logs\u547d\u4ee4;<\/li>\n
            13. \u521d\u59cb\u5316\u4e8c\u8fdb\u5236\u65e5\u5fd7\u7cfb\u7edf,\u4ece\u65b0\u751f\u6210:reset master\u547d\u4ee4;<\/li>\n
            14. \u5220\u9664\u67d0\u4e2a\u65e5\u5fd7\u6587\u4ef6:purge binary logs [before ‘datetime’ \/ to ‘log_name’] \u5220\u9664\u6307\u5b9a\u65e5\u671f\u4e4b\u524d\u7684\u548c\u5220\u9664\u6307\u5b9a\u6587\u4ef6\u4e4b\u524d\u7684\u65e5\u5fd7\u6587\u4ef6;<\/li>\n
            15. \u8bbe\u7f6e\u65e5\u5fd7\u6587\u4ef6\u7684\u5931\u6548\u671f:\u53c2\u6570\u4e3aexpire_logs_days,set global expire_log_days=n,N\u5929\u524d\u7684\u65e5\u5fd7\u81ea\u52a8\u5220\u9664;<\/li>\n
            16. \u4e8c\u8fdb\u5236\u65e5\u5fd7\u7684\u683c\u5f0f\n
                \n
              1. \u67e5\u770b\u683c\u5f0f:show [global] variables like ‘binlog_format’;<\/li>\n
              2. \u8bbe\u7f6e\u65e5\u5fd7\u683c\u5f0f:set [global] binlog_format = statement|row|mixed;<\/li>\n
              3. \u4f7f\u7528mysqlbinlog\u7a0b\u5e8f\u6253\u5f00;<\/li>\n<\/ol>\n<\/li>\n<\/ol>\n<\/li>\n
              4. general query log:\n
                  \n
                1. \u4e00\u822c\u67e5\u8be2\u65e5\u5fd7\u8bb0\u5f55\u670d\u52a1\u5668\u8fd0\u884c\u671f\u95f4\u6240\u6709\u64cd\u4f5c\u7684\u65e5\u5fd7.\u5f53\u5ba2\u6237\u7aef\u8fde\u63a5\u6216\u8005\u65ad\u5f00\u7684\u65f6\u5019\u670d\u52a1\u5668\u4f1a\u8bb0\u5f55\u4fe1\u606f\u5230\u65e5\u5fd7\u4e2d,\u5e76\u8bb0\u5f55\u6240\u6709\u4ece\u5ba2\u6237\u7aef\u63a5\u6536\u5230\u7684sql\u8bed\u53e5,\u5b83\u5bf9\u4e8e\u5728\u5ba2\u6237\u7aef\u4e2d\u6392\u9519\u548c\u67e5\u770b\u662f\u54ea\u4e2a\u5ba2\u6237\u7aef\u53d1\u9001\u7684\u547d\u4ee4\u5f88\u6709\u5e2e\u52a9;<\/li>\n
                2. mysqld\u662f\u6309\u7167\u63a5\u6536\u5230\u547d\u4ee4\u7684\u65b9\u5f0f\u8bb0\u5f55\u8bed\u53e5\u7684,\u8fd9\u53ef\u80fd\u8ddf\u5b83\u4eec\u6267\u884c\u7684\u987a\u5e8f\u4e0d\u540c(\u8fd9\u4e0e\u4e8c\u8fdb\u5236\u65e5\u5fd7\u662f\u6709\u533a\u522b\u7684,\u4e8c\u8fdb\u5236\u65e5\u5fd7\u662f\u6267\u884c\u540e\u8bb0\u5f55).<\/li>\n
                3. \u5728mysql5.1.6\u4e2d\u53ef\u4ee5\u4f7f\u7528–log\u9009\u9879\u542f\u52a8\u548c\u4f7f\u7528–log-output\u9009\u9879\u6307\u5b9a\u65e5\u5fd7\u8f93\u51fa\u7684\u4f4d\u7f6e,\u4e5f\u53ef\u4ee5\u8f93\u51fa\u5230”Server Log Tables”\u8868\u4e2d.\u57285.1.6\u4e4b\u524d,\u5f00\u542f\u6b64\u529f\u80fd\u662f\u7528–log=file_name or -l file_name\u9009\u9879(\u4e4b\u540e\u7248\u672c\u4f7f\u7528–general-log\u9009\u9879),\u5982\u679c\u6ca1\u6709\u6307\u5b9afile_name,\u9ed8\u8ba4\u662f\u5728data\u76ee\u5f55\u4e0b\u751f\u4ea7\u4e00\u4e2ahostname.log\u6587\u4ef6;<\/li>\n
                4. \u670d\u52a1\u5668\u91cd\u65b0\u542f\u52a8\u548clog flush\u4e0d\u4f1a\u4ea7\u751f\u4e00\u4e2a\u65b0\u7684\u6587\u4ef6;<\/li>\n
                5. \u9ed8\u8ba4\u6b64\u529f\u80fd\u5173\u95ed,\u901a\u8fc7show variables like ‘log’\/’general_log’,log\u4e0egeneral_log\u610f\u601d\u76f8\u540c;<\/li>\n
                6. \u8bbe\u7f6e\u6253\u5f00\u4e00\u822c\u67e5\u8be2\u65e5\u5fd7:set global log=1;(\u4e24\u4e2a\u53d8\u91cf\u4f1a\u540c\u65f6\u6253\u5f00\u5173\u95ed,\u6253\u5f00\u4e4b\u540e\u7acb\u5373\u751f\u6548);<\/li>\n
                7. \u53ef\u4ee5\u5728\u542f\u52a8\u7684\u65f6\u5019\u6307\u5b9a–general-file\u9009\u9879\u6216\u8005\u5728\u914d\u7f6e\u6587\u4ef6\u4e2d\u6307\u5b9ageneral_log=1,general_file_log=\/path;<\/li>\n
                8. \u4e00\u534a\u4e0d\u6253\u5f00\u6b64\u65e5\u5fd7\u529f\u80fd,\u6570\u636e\u91cf\u592a\u5927,\u5982\u679c\u6253\u5f00\u53ef\u4ee5\u653e\u5230\u5355\u72ec\u7684\u78c1\u76d8\u4e2d.<\/li>\n
                9. \u5907\u4efd\u5207\u6362\u4e00\u822c\u65e5\u5fd7\u6587\u4ef6:\n
                    \n
                  1. \u5148\u5907\u4efd:>mv hostname.log hostname.log.bak<\/li>\n
                  2. \u5207\u6362\u65e5\u5fd7\u7ec4:mysqladmin flush-logs;<\/li>\n<\/ol>\n<\/li>\n<\/ol>\n<\/li>\n
                  3. slow query log:\n
                      \n
                    1. \u8c03\u4f18\u65f6\u4f7f\u7528,\u8bb0\u5f55\u8d85\u51fa\u6307\u5b9a\u65f6\u95f4\u7684sql\u8bed\u53e5;<\/li>\n
                    2. \u6162\u67e5\u8be2\u65e5\u5fd7\u4e3b\u8981\u8bb0\u5f55\u6267\u884c\u65f6\u95f4\u8d85\u8fc7long_query_time\u53d8\u91cf\u6307\u5b9a\u65f6\u95f4\u7684sql\u8bed\u53e5,\u8fd9\u4e2a\u65f6\u95f4\u4e0d\u5305\u62ec\u83b7\u5f97\u9501\u7684\u65f6\u95f4,\u53ea\u5305\u542b\u6267\u884c\u65f6\u95f4,\u7cfb\u7edf\u9ed8\u8ba4\u65f6\u95f4\u662f10s(long_query_time=10.0);<\/li>\n
                    3. \u5728mysql5.1.6\u7248\u672c\u53ef\u4ee5\u6307\u5b9amysqld\u7684–long-slow-queries=file_name\u9009\u9879\u542f\u52a8,\u5728\u4e4b\u540e\u7684\u7248\u672c\u4f7f\u7528–slow-query-log=0|1\u9009\u9879\u548c–slow-query-file-log=file_name\u9009\u9879\u6307\u5b9a;<\/li>\n
                    4. \u547d\u4ee4\u884c\u53c2\u6570:\n
                        \n
                      1. –log-slow-queries=file_name;\u6307\u5b9a\u6162\u67e5\u8be2\u65e5\u5fd7\u6587\u4ef6<\/li>\n<\/ol>\n<\/li>\n
                      2. \u7cfb\u7edf\u53d8\u91cf:\n
                          \n
                        1. low-query-log:\u5f00\u542f\u6162\u67e5\u8be2\u529f\u80fd,set global\u00a0low-query-log =0|1;<\/li>\n
                        2. slow-query-file-log:\u6307\u5b9a\u6162\u67e5\u8be2\u65e5\u5fd7\u6587\u4ef6,\u7cfb\u7edf\u9ed8\u8ba4\u662f\u5728data\u76ee\u5f55\u4e0b\u7684hostname-slow.log\u6587\u4ef6;<\/li>\n
                        3. long_query_time:\u6307\u5b9a\u67e5\u8be2\u7684\u6700\u5927\u65f6\u95f4,set global long_query_time=n;<\/li>\n
                        4. long_queries_not_using_indexes:\u4e0d\u7528\u7d22\u5f15\u7684\u6162\u67e5\u8be2,\u9ed8\u8ba4\u662f\u529f\u80fd\u5173\u95ed\u7684;\n
                            \n
                          1. \u67e5\u770b\u67d0\u4e2a\u8868\u662f\u5426\u6709\u7d22\u5f15:> show index from t;<\/li>\n
                          2. \u6253\u5f00\u6b64\u529f\u80fd:>set global\u00a0long_queries_not_using_indexes =1;<\/li>\n
                          3. \u67e5\u770b\u53d8\u91cf:>show variables like ‘long_queries_not_using_indexes’;<\/li>\n<\/ol>\n<\/li>\n<\/ol>\n<\/li>\n
                          4. \u5206\u6790\u6162\u65e5\u5fd7\u6587\u4ef6\u7684\u5de5\u5177:mysqldumpslow,\u67e5\u770b\u5177\u4f53\u7684\u5e2e\u52a9\u662f\u7528mysqldumpslow –help;<\/li>\n<\/ol>\n<\/li>\n
                          5. innodb redo log;\n
                              \n
                            1. \u4e0einnodb\u6570\u636e\u5f15\u64ce\u76f8\u5173;<\/li>\n
                            2. \u7528\u6765\u5b9e\u73b0\u707e\u96be\u6062\u590d(crash recovery),\u7a81\u7136\u65ad\u7535\u4f1a\u5bfc\u81f4innodb\u8868\u7a7a\u95f4\u4e2d\u7684\u6570\u636e\u6ca1\u6709\u5199\u5230\u78c1\u76d8\u4e0a,\u901a\u8fc7\u6267\u884credo log\u80fd\u591f\u91cd\u65b0\u6267\u884c\u8fd9\u4e9b\u64cd\u4f5c\u6765\u6062\u590d\u6570\u636e;<\/li>\n
                            3. \u63d0\u5347innodb\u7684i\/o\u6027\u80fd,innodb\u5f15\u64ce\u628a\u6570\u636e\u548c\u7d22\u5f15\u90fd\u8f7d\u5165\u5230\u5185\u5b58\u4e2d\u7684\u7f13\u51b2\u6c60\u4e2d,\u5982\u679c\u6bcf\u6b21\u4f11\u606f\u6570\u636e\u548c\u7d22\u5f15\u90fd\u9700\u8981\u66f4\u65b0\u5230\u78c1\u76d8,\u5fc5\u5b9a\u4f1a\u589e\u52a0i\/o\u8bf7\u6c42,\u800c\u4e14\u56e0\u4e3a\u6bcf\u6b21\u66f4\u65b0\u7684\u4f4d\u7f6e\u90fd\u662f\u968f\u673a\u7684,\u78c1\u5934\u9700\u8981\u9891\u7e41\u7684\u5b9a\u4f4d\u5bfc\u81f4\u6548\u7387\u5f88\u4f4e,\u6240\u4ee5innodb\u6bcf\u5904\u7406\u5b8c\u4e00\u4e2a\u4e8b\u52a1\u540e\u53ea\u6dfb\u52a0\u4e00\u6761\u65e5\u5fd7log,\u53e6\u5916\u6709\u4e00\u4e2a\u7ebf\u7a0b\u8d1f\u8d23\u667a\u80fd\u7684\u8bfb\u53d6\u65e5\u5fd7\u6587\u4ef6\u5e76\u6279\u91cf\u66f4\u65b0\u5230\u78c1\u76d8\u4e0a,\u5b9e\u73b0\u6700\u9ad8\u6548\u7684\u78c1\u76d8\u5199\u5165;<\/li>\n
                            4. \u7cfb\u7edf\u53d8\u91cf:\n
                                \n
                              1. innodb_log_buffer_size:\u65e5\u5fd7\u7f13\u51b2\u533a\u7684\u5927\u5c0f;<\/li>\n
                              2. innodb_log_file_size:\u65e5\u5fd7\u6587\u4ef6\u7684\u5927\u5c0f;<\/li>\n
                              3. innodb_log_files_in_group:\u4e00\u7ec4\u65e5\u5fd7\u4e2d\u6709\u51e0\u4e2a\u6587\u4ef6:\n
                                  \n
                                1. \u6587\u4ef6\u540d\u4e3aib_logfileX(X\u4ece0\u5f00\u59cb\u4e00\u6b21\u589e\u52a0);<\/li>\n
                                2. \u5148\u5173\u95ed\u6570\u636e\u5e93\u670d\u52a1:>mysqladmin shutdown(mysql.server stop);<\/li>\n
                                3. \u628adata\u76ee\u5f55\u4e0b\u7684ib_logfile*\u6587\u4ef6\u79fb\u52a8\u8d70:>mv ib_logfile* \/tmp;<\/li>\n
                                4. \u5728\u914d\u7f6e\u6587\u4ef6\u4e2d\u6dfb\u52a0innodb_log_files_in_group=n\u7684\u53c2\u6570;<\/li>\n
                                5. \u542f\u52a8\u6570\u636e\u5e93\u670d\u52a1:>mysqld –defaults-file=.\/my.cnf –user=mysql(mysql.server start);<\/li>\n
                                6. \u53ef\u4ee5\u67e5\u770berror log\u6587\u4ef6\u89c2\u5bdf\u542f\u52a8\u8fc7\u7a0b;<\/li>\n<\/ol>\n<\/li>\n
                                7. innodb_log_group_home_dir:\u65e5\u5fd7\u5b58\u653e\u7684\u6027\u5bf9\u8def\u5f84(\u76f8\u5bf9\u4e8e$MYSQL_HOME\/mysql\/data\u76ee\u5f55,\u5373datadir\u76ee\u5f55);\n
                                    \n
                                  1. \u5173\u95ed\u670d\u52a1\u5668;<\/li>\n
                                  2. \u5728\u914d\u7f6e\u6587\u4ef6\u4e2d\u6dfb\u52a0\u6b64\u53c2\u6570,\u5e76\u6307\u5b9a\u8def\u5f84;<\/li>\n
                                  3. \u542f\u52a8\u670d\u52a1\u5668;<\/li>\n<\/ol>\n<\/li>\n
                                  4. innodb_flush_log_at_trx_commit:\u6839\u636e\u4e0d\u540c\u7684\u6570\u636e\u5b89\u5168\u7ea7\u522b\u53bb\u8bbe\u5b9a.\n
                                      \n
                                    1. 0:\u65e5\u5fd7\u7f13\u51b2\u6bcf\u79d2\u4e00\u6b21\u7684\u88ab\u5199\u5165\u5230\u65e5\u5fd7\u6587\u4ef6,\u5e76\u4e14\u5bf9\u65e5\u5fd7\u6587\u4ef6\u505a\u5230\u78c1\u76d8\u64cd\u4f5c\u7684\u5237\u65b0,\u4f46\u662f\u5728\u4e00\u4e2a\u4e8b\u52a1\u63d0\u4ea4\u4e0d\u505a\u4efb\u4f55\u4fee\u6539;<\/li>\n
                                    2. 1:\u6bcf\u4e2a\u4e8b\u52a1\u63d0\u4ea4\u65f6,\u65e5\u5fd7\u7f13\u51b2\u88ab\u5199\u5230\u65e5\u5fd7\u6587\u4ef6,\u5bf9\u65e5\u5fd7\u6587\u4ef6\u505a\u5230\u78c1\u76d8\u64cd\u4f5c\u7684\u5237\u65b0;<\/li>\n
                                    3. 2:\u6bcf\u4e2a\u4e8b\u52a1\u63d0\u4ea4\u540e,\u65e5\u5fd7\u7f13\u51b2\u88ab\u5199\u5230\u65e5\u5fd7\u6587\u4ef6,\u4f46\u4e0d\u5bf9\u65e5\u5fd7\u6587\u4ef6\u505a\u5230\u78c1\u76d8\u64cd\u4f5c\u5237\u65b0,\u5bf9\u65e5\u5fd7\u6587\u4ef6\u6bcf\u79d2\u5237\u65b0\u4e00\u6b21;<\/li>\n
                                    4. \u67e5\u770b\u6b64\u53d8\u91cf:>show variables like ‘innodb_flush_log_at_trx_commit’;<\/li>\n<\/ol>\n<\/li>\n
                                    5. innodb_os_log_written:\u5199\u5165\u5230\u6587\u4ef6\u65e5\u5fd7\u7684\u6570\u636e\u91cf,\u4f7f\u7528show status\u67e5\u8be2;<\/li>\n
                                    6. innodb_os_log_fsyncs:\u5199\u5165\u5230\u78c1\u76d8\u7684\u6b21\u6570,\u4f7f\u7528show status\u67e5\u8be2;<\/li>\n<\/ol>\n<\/li>\n<\/ol>\n<\/li>\n<\/ol>\n<\/li>\n
                                    7. \u6570\u636e\u6587\u4ef6:MySql\u670d\u52a1\u5668\u4f7f\u7528\u78c1\u76d8\u7a7a\u95f4\u6709\u51e0\u79cd\u65b9\u5f0f,\u4e3b\u8981\u662f\u5728\u670d\u52a1\u5668\u6570\u636e\u76ee\u5f55(datadir)\u4e0b\u7684\u6570\u636e\u5e93\u76ee\u5f55\u548c\u6587\u4ef6,\u4e3b\u8981\u6709\u4ee5\u4e0b\u51e0\u70b9:\n
                                        \n
                                      1. \u6570\u636e\u5e93\u76ee\u5f55:\u6bcf\u4e00\u4e2a\u6570\u636e\u5e93\u5bf9\u5e94\u4e00\u4e2a\u6570\u636e\u76ee\u5f55(datadir)\u4e0b\u7684\u76ee\u5f55,\u4e0d\u7ba1\u4f60\u521b\u5efa\u54ea\u79cd\u7c7b\u578b\u7684\u8868.\u6bd4\u5982,\u4e00\u4e2a\u6570\u636e\u5e93\u76ee\u5f55\u53ef\u4ee5\u5305\u542bMYISAM\u5f15\u64ce\u7684\u8868,INNODB\u5f15\u64ce\u7684\u8868\u6216\u8005\u6df7\u5408\u7684\u8868;<\/li>\n
                                      2. .frm\u6587\u4ef6(Table Format Files):\u5305\u542b\u4e86\u8868\u7ed3\u6784\u7684\u63cf\u8ff0\u4fe1\u606f,\u6bcf\u4e00\u4e2a\u8868\u90fd\u6709\u4e00\u4e2a.frm\u6587\u4ef6\u5728\u5bf9\u5e94\u7684\u6570\u636e\u5e93\u76ee\u5f55\u4e0b.\u5b83\u4e0e\u8868\u4f7f\u7528\u7684\u54ea\u79cd\u5f15\u64ce\u6ca1\u6709\u5173\u7cfb;<\/li>\n
                                      3. .MYD\/.MYI:\u7531MYISAM\u5b58\u50a8\u5f15\u64ce\u5728\u9002\u5f53\u7684\u6570\u636e\u5e93\u76ee\u5f55\u4e0b\u521b\u5efa\u7684\u6570\u636e\u5e93\u6570\u636e\u6587\u4ef6\u548c\u7d22\u5f15\u6587\u4ef6.<\/li>\n
                                      4. INNODB\u5b58\u50a8\u5f15\u64ce\u6709\u5b83\u81ea\u5df1\u7684\u8868\u7a7a\u95f4\u548c\u65e5\u5fd7\u6587\u4ef6,\u8868\u7a7a\u95f4\u5305\u542b\u6240\u6709\u4f7f\u7528InnoDB\u5f15\u64ce\u8868\u7684\u6570\u636e\u548c\u7d22\u5f15\u4fe1\u606f,\u540c\u6837\u4e5f\u5305\u62ec\u4e86\u4e8b\u52a1\u56de\u6eda\u6240\u5fc5\u987b\u7684undo logs.\u65e5\u5fd7\u6587\u4ef6\u8bb0\u5f55\u4e86\u63d0\u4ea4\u8fc7\u7684\u4e8b\u52a1\u7684\u4fe1\u606f,\u7528\u6765\u9632\u6b62\u6570\u636e\u4e22\u5931.\u9ed8\u8ba4\u60c5\u51b5\u4e0b,\u8868\u7a7a\u95f4\u548c\u65e5\u5fd7\u6587\u4ef6\u4fdd\u5b58\u5728\u6570\u636e\u76ee\u5f55,\u9ed8\u8ba4\u7684\u8868\u7a7a\u95f4\u6587\u4ef6\u540d\u53ebibdata1,\u9ed8\u8ba4\u7684\u65e5\u5fd7\u6587\u4ef6\u540d\u53ebib_logfile0\u548cib_logfile1.(\u53ef\u4ee5\u4e3a\u6bcf\u4e00\u4e2a\u4f7f\u7528InnoDB\u5f15\u64ce\u7684\u8868\u914d\u7f6e\u4e00\u4e2a\u8868\u7a7a\u95f4,\u8fd9\u79cd\u60c5\u51b5\u4e0b,InnoDB\u7ed9\u6307\u5b9a\u7684\u8868\u521b\u5efa\u8868\u5728\u8868\u6570\u636e\u5e93\u76ee\u5f55\u521b\u5efa\u8868\u7a7a\u95f4)\n
                                          \n
                                        1. \u67e5\u8be2\u8868\u7a7a\u95f4\u6a21\u5f0f:>show variables like ‘innodb_file_per_table’ ON:\u5355\u72ec\u8868\u7a7a\u95f4,OFF:\u5171\u4eab\u8868\u7a7a\u95f4;<\/li>\n
                                        2. \u5982\u679c\u8981\u4fee\u6539\u8fd9\u4e2a\u503c\u7684\u8bdd\u8981\u5173\u95ed\u670d\u52a1\u5668,\u4fee\u6539\u914d\u7f6e\u6587\u4ef6,\u542f\u52a8\u670d\u52a1\u8fd9\u51e0\u6b65;<\/li>\n
                                        3. \u5982\u679c\u4f7f\u7528\u5355\u72ec\u8868\u7a7a\u95f4\u6a21\u5f0f\u7684\u8bdd\u4f1a\u5728\u76f8\u5e94\u7684\u6570\u636e\u5e93\u76ee\u5f55\u521b\u5efatablename.frm(\u8868\u7ed3\u6784\u6587\u4ef6)\u548ctablename.ibd\u6587\u4ef6(\u6570\u636e\u548c\u7d22\u5f15\u6587\u4ef6);<\/li>\n<\/ol>\n<\/li>\n
                                        4. \u670d\u52a1\u5668\u65e5\u5fd7\u6587\u4ef6\u548c\u72b6\u6001\u6587\u4ef6:\u8fd9\u4e9b\u6587\u4ef6\u5305\u542b\u670d\u52a1\u5668\u4e0a\u6267\u884c\u8fc7\u7684\u8bed\u53e5\u4fe1\u606f,\u65e5\u5fd7\u88ab\u7528\u4e8e\u590d\u5236\u548c\u6570\u636e\u6062\u590d,\u83b7\u5f97\u4f18\u5316\u67e5\u8be2\u6027\u80fd\u7684\u4fe1\u606f\u548c\u8bef\u64cd\u4f5c\u4fe1\u606f;<\/li>\n<\/ol>\n<\/li>\n
                                        5. Relication\u76f8\u5173\u6587\u4ef6<\/li>\n
                                        6. \u5176\u4ed6\u5c0f\u6587\u4ef6<\/li>\n<\/ol>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

                                          Mysql\u7684\u7269\u7406\u7ed3\u6784 \u65e5\u5fd7\u6587\u4ef6 error log: \u8bb0\u5f55mysql\u542f\u52a8,\u5173\u95ed\u548c\u8fd0\u884c\u65f6\u4ea7\u751f\u7684\u91cd\u5927\u7684\u9519\u8bef\u7684\u4fe1\u606f; […]<\/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],"class_list":["post-378","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-mysql"],"_links":{"self":[{"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=\/wp\/v2\/posts\/378","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=378"}],"version-history":[{"count":0,"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=\/wp\/v2\/posts\/378\/revisions"}],"wp:attachment":[{"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=378"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=378"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=378"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}