{"id":894,"date":"2018-07-08T12:24:53","date_gmt":"2018-07-08T04:24:53","guid":{"rendered":"https:\/\/blog.freesilo.com\/?p=894"},"modified":"2018-07-08T12:24:53","modified_gmt":"2018-07-08T04:24:53","slug":"mysql%e4%bd%93%e7%b3%bb%e7%bb%93%e6%9e%84%e4%b9%8b%e7%89%a9%e7%90%86%e6%96%87%e4%bb%b6","status":"publish","type":"post","link":"https:\/\/freesilo.com\/?p=894","title":{"rendered":"MySQL\u4f53\u7cfb\u7ed3\u6784\u4e4b\u7269\u7406\u6587\u4ef6"},"content":{"rendered":"

\u4e00\u3001MySQL\u65e5\u5fd7\u6587\u4ef6<\/h3>\n

mysql\u65e5\u5fd7\u6587\u4ef6\u53ca\u529f\u80fd\uff1a<\/p>\n\n\n\n\n\n\n\n
\u65e5\u5fd7\u6587\u4ef6<\/strong><\/td>\n\u529f\u80fd<\/strong><\/td>\n<\/tr>\n
\u9519\u8bef\u65e5\u5fd7<\/td>\n\u8bb0\u5f55\u542f\u52a8\u3001\u505c\u6b62\u3001\u8fd0\u884c\u8fc7\u7a0b\u4e2dmysqld\u65f6\u51fa\u73b0\u7684\u95ee\u9898<\/td>\n<\/tr>\n
\u901a\u7528\u65e5\u5fd7<\/td>\n\u8bb0\u5f55\u5efa\u7acb\u5ba2\u6237\u7aef\u8fde\u63a5\u548c\u6267\u884c\u7684\u8bed\u53e5<\/td>\n<\/tr>\n
\u4e8c\u8fdb\u5236\u65e5\u5fd7<\/td>\n\u8bb0\u5f55\u66f4\u6539\u6570\u636e\u7684\u6240\u6709\u8bed\u53e5\uff0c\u8fd8\u7528\u4e8e\u590d\u5236<\/td>\n<\/tr>\n
\u6162\u67e5\u8be2\u65e5\u5fd7<\/td>\n\u8bb0\u5f55\u6267\u884c\u65f6\u95f4\u8d85\u8fc7long_query_time\u79d2\u7684\u6240\u6709\u67e5\u8be2<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

\u9ed8\u8ba4\u60c5\u51b5\u4e0b\uff0cmysql\u6240\u6709\u65e5\u5fd7\u5747\u5b58\u50a8\u4e8emysql\u6570\u636e\u76ee\u5f55\u4e0b\u3002\u901a\u8fc7\u5237\u65b0\u65e5\u5fd7\uff0c\u53ef\u4ee5\u65f6mysqld\u5f3a\u5236\u5173\u95ed\u548c\u6253\u5f00\u65e5\u5fd7\u6587\u4ef6\uff08\u6216\u8005\u5728\u67d0\u4e9b\u60c5\u51b5\u4e0b\u5207\u6362\u5230\u4e0b\u4e00\u4e2a\u65e5\u5fd7\uff09\u3002<\/p>\n

\u5237\u65b0\u65e5\u5fd7\u65b9\u6cd5\uff1a<\/p>\n

1\uff09flush logs<\/p>\n

2\uff09mysqladmin flush-logs<\/p>\n

3\uff09mysqladmin refresh<\/p>\n

1\u3001\u9519\u8bef\u65e5\u5fd7<\/strong><\/h4>\n

1\uff09\u4f5c\u7528\uff1a\u8bb0\u5f55mysqld\u542f\u52a8\u3001\u505c\u6b62\u4ee5\u53camysql\u6570\u636e\u5e93\u5728\u8fd0\u884c\u8fc7\u7a0b\u4e2d\u53d1\u751f\u7684\u5404\u79cd\u4e25\u91cd\u9519\u8bef\u4fe1\u606f\u3002\u5f53\u6570\u636e\u5e93\u53d1\u751f\u4efb\u4f55\u6545\u969c\u5bfc\u81f4\u65e0\u6cd5\u91cd\u542f\u65f6\uff0c\u53ef\u4ee5\u53c2\u8003\u9519\u8bef\u65e5\u5fd7\u8fdb\u884c\u6545\u969c\u8bca\u65ad\u3002<\/p>\n

2\uff09\u4f4d\u7f6e\uff1a\u53ef\u4ee5\u4f7f\u7528–log-error[=file_name]\u53c2\u6570\u9009\u9879\u6765\u6307\u5b9amysqld\u4fdd\u5b58\u9519\u8bef\u65e5\u5fd7\u6587\u4ef6\u7684\u4f4d\u7f6e\u3002\u5982\u679c\u6ca1\u6709\u7ed9\u5b9afile_name\u6587\u4ef6\u4f4d\u7f6e\uff0cmysqld\u4f7f\u7528\u7684\u9519\u8bef\u65e5\u5fd7\u540d\u4e3ahost_name.err\u5e76\u9ed8\u8ba4\u4fdd\u5b58\u5728datadir\u6307\u5b9a\u7684\u76ee\u5f55\u4e0b\u3002<\/p>\n

2\u3001\u901a\u7528\u65e5\u5fd7<\/strong><\/h4>\n

1\uff09\u4f5c\u7528\uff1a\u67e5\u8be2\u65e5\u5fd7\u8bb0\u5f55\u5ba2\u6237\u7aef\u7684\u6240\u6709\u8bed\u53e5\uff08\u6240\u6709\u8fde\u63a5\u548c\u8bed\u53e5\u90fd\u8bb0\u5f55\u5230\u901a\u7528\u65e5\u5fd7\uff09\uff0c\u800cbinlog\u4e0d\u8bb0\u5f55select\u8bed\u53e5\u3002<\/p>\n

2\uff09\u4f4d\u7f6e\uff1a\u67e5\u8be2\u65e5\u5fd7\u548c\u6162\u67e5\u8be2\u65e5\u5fd7\u90fd\u53ef\u4ee5\u4fdd\u5b58\u5728\u6587\u4ef6\u6216\u8868\u4e2d\uff0c\u5e76\u4f7f\u7528\u53c2\u6570–log-output[=value]\u6765\u8fdb\u884c\u63a7\u5236\uff0cvalue\u7684\u503c\u53ef\u4ee5\u662ftable\uff0cfile\uff0cnone\u7684\u4e00\u4e2a\u6216\u591a\u4e2a\u7684\u7ec4\u5408\uff0c\u4e2d\u95f4\u7528\u9017\u53f7\u8fdb\u884c\u5206\u5272\uff0c\u5206\u522b\u8868\u793a\u65e5\u5fd7\u4fdd\u5b58\u5728\u8868\uff0c\u6587\u4ef6\uff0c\u4e0d\u4fdd\u5b58\u5728\u8868\u6216\u6587\u4ef6\u4e2d\uff0c\u8fd9\u91cc\u7684\u8868\u6307\u7684\u662fmysql\u5e93\u603b\u7684general_log\uff08\u6162\u67e5\u8be2\u65e5\u5fd7\u662fslow_log\uff09\u8868\u3002\u5176\u4e2dnone\u7684\u4f18\u5148\u7ea7\u6700\u9ad8\uff0c\u6bd4\u5982–log-output=table,file\u8868\u793a\u65e5\u5fd7\u540c\u65f6\u8f93\u51fa\u5230\u8868\u548c\u6587\u4ef6\u4e2d\uff0c–log-output=table,none\u8868\u793a\u65e5\u5fd7\u4e0d\u4fdd\u5b58\u5728\u8868\u4e2d\u3002\u5982\u679c\u4e0d\u663e\u793a\u4f7f\u7528\u6b64\u53c2\u6570\uff0c\u5219\u8868\u793a\u65e5\u5fd7\u8f93\u51fa\u5230\u6587\u4ef6\u4e2d\u3002\u4e00\u822c\u65e5\u5fd7\u8f93\u51fa\u5230\u8868\u4e2d\u8981\u5360\u7528\u66f4\u591a\u7684\u7cfb\u7edf\u8d44\u6e90\u3002<\/p>\n

\u5982\u679c\u8981\u542f\u7528\u67e5\u8be2\u65e5\u5fd7\uff0c\u53ef\u4ee5\u901a\u8fc7\u53c2\u6570–general_log[={0|1}]\u548c–general_log_file=file_name\u6765\u8fdb\u884c\u63a7\u5236\u3002\u524d\u8005\u63a7\u5236\u662f\u5426\u542f\u7528\u67e5\u8be2\u65e5\u5fd7\uff08–general_log\u8bbe\u7f6e\u4e3a1\u6216\u8005\u4e0d\u5e26\u503c\u90fd\u53ef\u4ee5\u542f\u7528\u67e5\u8be2\u65e5\u5fd7\uff0c\u8bbe\u7f6e\u4e3a0\u8868\u793a\u5173\u95ed\u67e5\u8be2\u65e5\u5fd7\uff0c\u4e0d\u6307\u5b9a\u6b64\u53c2\u6570\u8868\u793a\u4e0d\u542f\u52a8\u67e5\u8be2\u65e5\u5fd7\uff09\uff0c\u540e\u8005\u63a7\u5236\u65e5\u5fd7\u6587\u4ef6\u7684\u8def\u5f84\u3002\u4e5f\u53ef\u4ee5\u4f7f\u7528–log[=file_name]\u6216-l [file_name]\u9009\u9879\u542f\u52a8\u5b83\uff0c\u5982\u679c\u6ca1\u6709\u7ed9\u51fafile_name\uff0c\u90a3\u4e48\u9ed8\u8ba4\u503c\u5c31\u662fhost_name.log\u3002\u8fd0\u884c\u65f6\u53ef\u4ee5\u901a\u8fc7set global general_log on\u6253\u5f00\u901a\u7528\u65e5\u5fd7\u3002<\/p>\n

\u5982\u679c\u6ca1\u6709\u6307\u5b9a–general_log_file=file_name\u503c\uff0c\u4e14\u6ca1\u6709\u663e\u793a\u8bbe\u7f6e–log_output\u53c2\u6570\uff0c\u90a3\u4e48\u65e5\u5fd7\u9ed8\u8ba4\u5199\u5165datadir\u76ee\u5f55\u4e0b\u7684\uff0c\u9ed8\u8ba4\u6587\u4ef6\u540d\u4e3ahost_name.log\u3002\u8fd9\u4e24\u4e2a\u53c2\u6570\u90fd\u662fglobal\u7c7b\u578b\uff0c\u53ef\u4ee5\u5728\u542f\u52a8\u65f6\u6216\u7cfb\u7edf\u8fd0\u884c\u65f6\u52a8\u6001\u4fee\u6539\u3002\u5982\u679c\u60f3\u5728session\u7ea7\u522b\u63a7\u5236\u65e5\u5fd7\u662f\u5426\u88ab\u8bb0\u5f55\uff0c\u5219\u901a\u8fc7\u5728session\u4e2d\u8bbe\u7f6e\u53c2\u6570sql_log_off\u4e3aon\u6216off\u6765\u8fdb\u884c\u63a7\u5236\u3002<\/p>\n

\u67e5\u8be2\u901a\u7528\u65e5\u5fd7\u4f4d\u7f6e\uff1a<\/p>\n

mysql> show variables like ‘gene%’;<\/p>\n

+——————+————————————–+<\/p>\n

| Variable_name\u00a0\u00a0\u00a0 | Value\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/p>\n

+——————+————————————–+<\/p>\n

| general_log\u00a0\u00a0\u00a0\u00a0\u00a0 | OFF\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/p>\n

| general_log_file | \/usr\/local\/mysql\/data\/chavinking.log |<\/p>\n

+——————+————————————–+<\/p>\n

2 rows in set (0.00 sec)<\/p>\n

3\uff09\u65e5\u5fd7\u5185\u5bb9\u7684\u8bfb\u53d6\uff1a\u67e5\u8be2\u65e5\u5fd7\u4e3a\u6587\u672c\u6587\u6863\u683c\u5f0f\uff0c\u4e00\u822c\u76f4\u63a5\u8bfb\u53d6\u5c31\u53ef\u3002<\/p>\n

4\uff09\u4e00\u822c\u60c5\u51b5\u4e0b\u4e0d\u5efa\u8bae\u5f00\u542f\u67e5\u8be2\u65e5\u5fd7\u529f\u80fd\uff0c\u5426\u5219\u53ef\u80fd\u9020\u6210\u77ed\u65f6\u95f4\u5185\u78c1\u76d8\u5360\u7528\u7387\u731b\u589e\u3002<\/p>\n

3\u3001\u6162\u67e5\u8be2\u65e5\u5fd7<\/strong><\/h4>\n

1\uff09\u6982\u8ff0\uff1a\u5f53\u53c2\u6570slow_query_log=1\u65f6\uff0c\u6162\u67e5\u8be2\u65e5\u5fd7\u8bb0\u5f55\u4e86\u6240\u6709\u6267\u884c\u65f6\u95f4\u8d85\u8fc7\u53c2\u6570long_query_time\u8bbe\u7f6e\u503c\u5e76\u4e14\u626b\u63cf\u8bb0\u5f55\u6570\u4e0d\u5c0f\u4e8emin_examined_row_limit\u7684\u6240\u6709sql\u8bed\u53e5\u7684\u65e5\u5fd7\uff08\u83b7\u5f97\u8868\u9501\u5b9a\u7684\u65f6\u95f4\u4e0d\u80fd\u7b97\u4f5c\u6267\u884c\u65f6\u95f4\uff09\u3002\u5f53\u6ca1\u6709\u7ed9\u51fa\u6162\u67e5\u8be2\u65e5\u5fd7\u540d\u65f6\uff0c\u9ed8\u8ba4\u4e3a\u4e3b\u673a\u540d\uff0c\u540e\u7f00\u4e3a-slow.log\uff0c\u5f53\u7ed9\u51fa\u4e86\u6587\u4ef6\u540d\uff0c\u4f46\u4e0d\u662f\u7edd\u5bf9\u8def\u5f84\uff0c\u6162\u67e5\u8be2\u65e5\u5fd7\u4f1a\u8bb0\u5f55\u5230mysql\u7684\u6570\u636e\u76ee\u5f55\u4e0b\u3002\u6267\u884c\u5b8c\u8bed\u53e5\u5e76\u4e14\u91ca\u653e\u9501\u540e\uff0c\u5373\u53ef\u8bb0\u5165\u6162\u67e5\u8be2\u65e5\u5fd7\uff0c\u8bb0\u5f55\u987a\u5e8f\u53ef\u4ee5\u4e0e\u6267\u884c\u987a\u5e8f\u4e0d\u540c\u3002\u6162\u67e5\u8be2\u65e5\u5fd7\u53ef\u4ee5\u67e5\u627e\u6267\u884c\u65f6\u95f4\u957f\u7684\u8bed\u53e5\uff0c\u7528\u4e8e\u4f18\u5316\uff0c\u5b83\u662fMySQL\u6570\u636e\u4e2d\u5e38\u7528\u7684\u6027\u80fd\u4f18\u5316\u5de5\u5177\u3002Long_query_time\u9ed8\u8ba4\u4e3a10\u79d2\uff0c\u6700\u5c0f\u4e3a0\uff0c\u7cbe\u5ea6\u53ef\u4ee5\u5230\u5fae\u79d2\u3002<\/p>\n

\u4e00\u822c\u4e24\u7c7b\u8bed\u53e5\u4e0d\u8bb0\u5f55\u5230\u6162\u67e5\u8be2\u65e5\u5fd7\u4e2d\uff1a\u7ba1\u7406\u8bed\u53e5\u548c\u4e0d\u4f7f\u7528\u7d22\u5f15\u8fdb\u884c\u67e5\u8be2\u7684\u8bed\u53e5\u3002\u5982\u679c\u8981\u76d1\u63a7\u8fd9\u4e24\u7c7bSQL\u8bed\u53e5\uff0c\u53ef\u4ee5\u5206\u522b\u901a\u8fc7\u53c2\u6570–log-slow-admin-statements\u548clog_queries_not_using_indexes\u8fdb\u884c\u63a7\u5236\u3002<\/p>\n

2\uff09\u6587\u4ef6\u4f4d\u7f6e\u548c\u683c\u5f0f\uff1a\u6162\u67e5\u8be2\u65e5\u5fd7\u9ed8\u8ba4\u662f\u5173\u95ed\u7684\uff0c\u5728mysql5.1.29\u524d\uff0c\u5f53\u7528–log-slow-queries[=file_name]\u9009\u9879\u542f\u52a8mysqld\u65f6\uff0c\u6162\u67e5\u8be2\u65e5\u5fd7\u5f00\u59cb\u88ab\u8bb0\u5f55\u3002\u548c\u524d\u51e0\u79cd\u65e5\u5fd7\u4e00\u6837\uff0c\u5982\u679c\u6ca1\u6709\u6307\u5b9afile_name\u7684\u503c\uff0c\u65e5\u5fd7\u5c06\u5199\u5165datadir\u8def\u5f84\uff0c\u9ed8\u8ba4\u6587\u4ef6\u540d\u4e3ahost_name-slow.log\u3002\u5728mysql5.1.29\u540e\uff0c–log-slow-queries\u53c2\u6570\u5e9f\u5f03\uff0c\u91c7\u7528\u4e24\u4e2a\u65b0\u7684\u53c2\u6570\u8fdb\u884c\u66ff\u6362\uff1a–slow_query_log[={0|1}]\u663e\u793a\u6307\u5b9a\u6162\u67e5\u8be2\u65e5\u5fd7\u72b6\u6001\uff0c\u5982\u679c\u4e0d\u6307\u5b9a\u503c\u6216\u6307\u5b9a\u503c\u4e3a1\u90fd\u4f1a\u6253\u5f00\u6162\u67e5\u8be2\uff1b\u4f7f\u7528slow_query_log_file[=file_name]\u6765\u6307\u5b9a\u6162\u67e5\u8be2\u65e5\u5fd7\u8def\u5f84\uff0c\u53e6\u5916\u8fd8\u53ef\u4ee5\u6307\u5b9a–log-output\u53c2\u6570\u6307\u5b9a\u65e5\u5fd7\u7684\u8f93\u51fa\u683c\u5f0f\uff0c\u9ed8\u8ba4\u8f93\u51fa\u5230\u6587\u4ef6\u3002\u9700\u8981\u6ce8\u610f\u7684\u662f\u5982\u679c\u9009\u62e9\u8f93\u51fa\u5230\u8868\uff0c\u5219\u8868\u4e2d\u7684\u8bb0\u5f55\u7684\u6162\u67e5\u8be2\u65f6\u95f4\u53ea\u80fd\u7cbe\u786e\u5230\u79d2\uff0c\u800c\u65e5\u5fd7\u6587\u4ef6\u4e2d\u53ef\u4ee5\u7cbe\u786e\u5230\u5fae\u79d2\u3002<\/p>\n

3\uff09\u65e5\u5fd7\u6587\u4ef6\u7684\u8bfb\u53d6\uff1a\u6162\u67e5\u8be2\u65e5\u5fd7\u6587\u4ef6\u662f\u5b58\u6587\u672c\u65b9\u5f0f\u5b58\u50a8\uff0c\u53ef\u4ee5\u76f4\u63a5\u8bfb\u53d6\u3002\u4e5f\u53ef\u4ee5\u901a\u8fc7mysqldumpslow\u5de5\u5177\uff08..\/bin\/mysqldumpslow faspdev-slow.log\uff09\u5bf9\u6162\u67e5\u8be2\u65e5\u5fd7\u8fdb\u884c\u6c47\u603b\u5904\u7406\u3002\u6162\u67e5\u8be2\u65e5\u5fd7\u901a\u5e38\u7528\u6765\u5b9a\u4f4dmysql\u670d\u52a1\u4e2dsql\u95ee\u9898\uff0c\u9ed8\u8ba4\u5efa\u8bae\u6253\u5f00\u6162\u67e5\u8be2\u65e5\u5fd7\uff0c\u5e76\u5b9a\u671f\u67e5\u770b\u5206\u6790\u3002<\/p>\n

4\uff09\u6162\u67e5\u8be2\u65e5\u5fd7\u5728mysql\u6027\u80fd\u4f18\u5316\u4e2d\u7684\u4f5c\u7528\u8be6\u89e3\uff1a<\/p>\n

4.1\uff09\u6162\u67e5\u8be2\u65e5\u5fd7\u683c\u5f0f\uff1a<\/p>\n

mysql\u6162\u67e5\u8be2\u6807\u51c6\uff1a<\/p>\n

query_time\uff1a\u67e5\u8be2\u8017\u65f6<\/p>\n

rows_examined\uff1a\u68c0\u67e5\u591a\u5c11\u6761\u8bb0\u5f55<\/p>\n

rows_sent\uff1a\u8fd4\u56de\u591a\u5c11\u884c\u8bb0\u5f55<\/p>\n

mysql\u4f7f\u7528\u4ee5\u4e0a\u51e0\u70b9\u5927\u81f4\u8861\u91cfsql\u6210\u672c\u3002<\/p>\n

\u5176\u4ed6\u4fe1\u606f\u5305\u62ec\u5982\u4e0b\uff1a<\/p>\n

time\uff1a\u6267\u884csql\u5f00\u59cb\u65f6\u95f4<\/p>\n

lock time\uff1a\u7b49\u5f85table lock\u7684\u65f6\u95f4\uff0c\u6ce8\u610finnodb\u884c\u9501\u7b49\u5f85\u4e0d\u4f1a\u53cd\u5e94\u5728\u8fd9\u91cc\u3002<\/p>\n

user@host\uff1a\u6267\u884c\u67e5\u8be2\u7684\u7528\u6237\u548c\u5ba2\u6237\u7aefip<\/p>\n

\u4ee5\u4e0b\u662f\u4e00\u4e2a\u6162\u67e5\u8be2\u4f8b\u5b50\uff1a<\/p>\n\n\n\n
# Time: 170122 13:53:17<\/p>\n

# User@Host: root[root] @ localhost []\u00a0 Id:\u00a0\u00a0\u00a0\u00a0 1<\/p>\n

# Query_time: 9.839503\u00a0 Lock_time: 0.000159 Rows_sent: 0\u00a0 Rows_examined: 524288<\/p>\n

SET timestamp=1485064397;<\/p>\n

insert into column_charset\u00a0 select * from column_charset;<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

\u4e00\u822c\u6267\u884c\u65f6\u95f4\u6700\u957f\u7684sql\u662f\u9700\u8981\u4f18\u5316\u7684\uff0c\u5982\u679c\u68c0\u67e5\u4e86\u5927\u91cf\u6570\u636e\u800c\u53ea\u8fd4\u56de\u5c11\u91cf\u6570\u636e\uff0c\u5219\u610f\u5473\u7740\u8d28\u91cf\u4e0d\u4f73\u3002\u9700\u8981\u6ce8\u610f\u7684\u662f\u6162\u67e5\u8be2\u65e5\u5fd7\u91cc\u7684\u6162\u67e5\u8be2\u4e0d\u4e00\u5b9a\u5c31\u662f\u4e0d\u826fsql\uff0c\u8fd8\u53ef\u80fd\u53d7\u5176\u4ed6\u67e5\u8be2\u5f71\u54cd\uff0c\u6216\u8005\u53d7\u5230\u7cfb\u7edf\u8d44\u6e90\u9650\u5236\u5bfc\u81f4\u7684\u3002<\/p>\n

4.2\uff09\u5982\u4f55\u8bc6\u522b\u9700\u8981\u5173\u6ce8\u7684sql\uff1a<\/p>\n

\u786e\u8ba4\u5df2\u7ecf\u5f00\u542f\u4e86\u6162\u67e5\u8be2\u65e5\u5fd7\uff0c\u5e76\u4e14\u8bbe\u7f6e\u4e86\u5408\u7406\u7684\u9608\u503c\u3002\u4ee5\u4e0b\u547d\u4ee4\u53ef\u4ee5\u67e5\u770b\u662f\u5426\u542f\u7528\u6162\u67e5\u8be2\u65e5\u5fd7\u4ee5\u53ca\u6162\u67e5\u8be2\u65e5\u5fd7\u8def\u5f84\uff1a<\/p>\n

mysql> show variables like ‘%slow_query_log%’;<\/p>\n

+———————+——————————————- ————————+<\/p>\n

| Variable_name\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Value\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/p>\n

+———————+——————————————————————–+<\/p>\n

| slow_query_log\u00a0\u00a0\u00a0\u00a0\u00a0 | OFF\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/p>\n

| slow_query_log_file | \/usr\/local\/mysql5631\/data\/faspdev-slow.log |<\/p>\n

+———————+——————————————————————-+<\/p>\n

\u4ee5\u4e0b\u547d\u4ee4\u53ef\u4ee5\u67e5\u770b\u5168\u5c40\u53d8\u91cflong_query_time\u53c2\u6570\u7684\u503c\uff0c\u6240\u6709\u8d85\u8fc7\u8be5\u503c\u5f97sql\u90fd\u5c06\u8bb0\u5f55\u5230\u6162\u67e5\u8be2\u65e5\u5fd7\u4e2d\uff1a<\/p>\n

mysql> show variables like ‘long_query_time’;<\/p>\n

+—————–+———–+<\/p>\n

| Variable_name\u00a0\u00a0 | Value\u00a0\u00a0\u00a0\u00a0 |<\/p>\n

+—————–+———–+<\/p>\n

| long_query_time | 1.000000 |<\/p>\n

+—————–+———–+<\/p>\n

1 row in set (0.00 sec)<\/p>\n

\u5728mysql\u53c2\u6570\u6587\u4ef6\u4e2d\u5f00\u542f\u6162\u67e5\u8be2\u65e5\u5fd7\u8bbe\u7f6e\u5982\u4e0b\uff1a<\/p>\n

[mysqld]<\/p>\n

slow_query_log=1<\/p>\n

slow_query_log_file=\/usr\/local\/mysql\/data\/log\/slowquery.log<\/p>\n

long_query_time=0.5<\/p>\n

mysql 5.0\u6162\u67e5\u8be2\u53c2\u6570\u662f\u4e0d\u4e00\u6837\u7684\uff0c\u4e14\u9700\u8981\u91cd\u542f\u540e\u624d\u53ef\u4ee5\u751f\u6548\uff0c\u76f8\u5173\u53c2\u6570\u4e3alog_slow_queries\u548cslow_launch_time\u3002<\/p>\n

\u53c2\u6570log-queries-not-using-indexes\u7528\u4e8e\u6307\u5b9a\u5982\u679c\u6ca1\u6709\u4f7f\u7528\u5230\u7d22\u5f15\u6216\u867d\u7136\u4f7f\u7528\u4e86\u7d22\u5f15\u4f46\u4ecd\u7136\u904d\u5386\u4e86\u6240\u6709\u8bb0\u5f55\uff0c\u5c31\u5c06\u5176\u8bb0\u5f55\u4e0b\u6765\uff0c\u9ed8\u8ba4\u6b64\u53c2\u6570\u662f\u5173\u95ed\u7684\u3002<\/p>\n

4.3\uff09\u4f7f\u7528\u5de5\u5177\u5206\u6790\u6162\u67e5\u8be2\u65e5\u5fd7<\/p>\n

\u5982\u679c\u6162\u67e5\u8be2\u65e5\u5fd7\u5185\u5bb9\u6bd4\u8f83\u591a\uff0c\u5bf9\u4e8e\u5206\u6790\u662f\u6bd4\u8f83\u9ebb\u70e6\u7684\uff0c\u4e00\u822c\u6709\u4ee5\u4e0b\u4e24\u79cd\u529e\u6cd5\u53ef\u4ee5\u53c2\u8003\uff1a<\/p>\n

u \u8c03\u6574\u9608\u503c<\/p>\n

u \u4f7f\u7528\u547d\u4ee4\u3001\u811a\u672c\u3001\u5de5\u5177\u8fdb\u884c\u5206\u6790\uff0c\u5982mysqldumpslow\u3001pt-query-digest\u7b49\u3002<\/p>\n

A. \u4f7f\u7528\u64cd\u4f5c\u7cfb\u7edf\u547d\u4ee4\u8fdb\u884c\u5206\u6790\uff1a<\/p>\n

\u53ef\u4ee5\u4f7f\u7528shell\u547d\u4ee4\u8fdb\u884c\u7edf\u8ba1\u5206\u6790\uff0c\u5982grep\u3001awk\u3001wc\uff0c\u4f46\u662f\u8fd9\u4e9b\u5de5\u5177\u4e0d\u5bb9\u6613\u5b9e\u73b0\u9ad8\u7ea7\u529f\u80fd\u3002<\/p>\n

awk ‘\/^#Time:\/{print $3,$4,c;c=0}\/^#User\/{c++}’ mysql-slow.log > \/mnt\/slowquery.log<\/p>\n

B. \u4f7f\u7528mysqldumpslow\u5de5\u5177\u8fdb\u884c\u5206\u6790\uff1a<\/p>\n

mysqldumpslow\u5de5\u5177\u662f\u5b98\u65b9\u81ea\u5e26\u7684\uff0c\u6b64\u5de5\u5177\u53ef\u4ee5\u83b7\u5f97\u65e5\u5fd7\u4e2d\u7684\u67e5\u8be2\u6458\u8981\u3002\u4ee5\u4e0b\u662fmysqldumpslow\u5de5\u5177\u4f7f\u7528\u5e2e\u52a9\uff1a<\/p>\n\n\n\n
$mysqldumpslow –help<\/p>\n

Usage: mysqldumpslow [ OPTS… ] [ LOGS… ]<\/p>\n

Parse and summarize the MySQL slow query log. Options are<\/p>\n

–verbose\u00a0\u00a0\u00a0 verbose<\/p>\n

–debug\u00a0\u00a0\u00a0\u00a0\u00a0 debug<\/p>\n

–help\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 write this text to standard output<\/p>\n

-v\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 verbose<\/p>\n

-d\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 debug<\/p>\n

-s ORDER\u00a0\u00a0\u00a0\u00a0 what to sort by (t, at, l, al, r, ar etc), ‘at’ is default<\/p>\n

-r\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 reverse the sort order (largest last instead of first)<\/p>\n

-t NUM\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 just show the top n queries<\/p>\n

-a\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 don’t abstract all numbers to N and strings to ‘S’<\/p>\n

-n NUM\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 abstract numbers with at least n digits within names<\/p>\n

-g PATTERN\u00a0\u00a0 grep: only consider stmts that include this string<\/p>\n

-h HOSTNAME\u00a0 hostname of db server for *-slow.log filename (can be wildcard),<\/p>\n

default is ‘*’, i.e. match all<\/p>\n

-i NAME\u00a0\u00a0\u00a0\u00a0\u00a0 name of server instance (if using mysql.server startup script)<\/p>\n

-l\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 don’t subtract lock time from total time<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

\u67e5\u8be2\u65f6\u95f4\u6700\u957f\u768410\u4e2asql\uff1a<\/p>\n

mysqldumpslow -t 10 mysql-slow.log<\/p>\n

\u67e5\u8be2\u8bbf\u95ee\u6b21\u6570\u6700\u591a\u768410\u4e2asql\u547d\u4ee4\uff1a<\/p>\n

mysqldumpslow -s c -t 10 mysql-slow.log<\/p>\n

\u67e5\u8be2\u8bbf\u95ee\u8bb0\u5f55\u6700\u591a\u768410\u6761sql\u547d\u4ee4\uff1a<\/p>\n

mysqldumpslow -s r -t 10 mysql-slow.log<\/p>\n

4.4\uff09\u4ee5\u4e0b\u662fmysql\u8fd0\u884c\u72b6\u6001\u4e0b\u8bbe\u7f6e\u6162\u67e5\u8be2\u65e5\u5fd7\u7684\u4f8b\u5b50<\/p>\n\n\n\n\n
mysql> show variables like ‘long_query_time’;<\/p>\n

+—————–+———–+<\/p>\n

| Variable_name\u00a0\u00a0 | Value\u00a0\u00a0\u00a0\u00a0 |<\/p>\n

+—————–+———–+<\/p>\n

| long_query_time | 10.000000 |<\/p>\n

+—————–+———–+<\/p>\n

1 row in set (0.00 sec)<\/p>\n

mysql> set long_query_time=2;<\/p>\n

Query OK, 0 rows affected (0.00 sec)<\/td>\n<\/tr>\n

mysql> show variables like ‘%slow_query_log%’;<\/p>\n

+———————+——————————————–+<\/p>\n

| Variable_name\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Value\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/p>\n

+———————+——————————————–+<\/p>\n

| slow_query_log\u00a0\u00a0\u00a0\u00a0\u00a0 | OFF\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/p>\n

| slow_query_log_file | \/usr\/local\/mysql5631\/data\/faspdev-slow.log |<\/p>\n

+———————+——————————————–+<\/p>\n

2 rows in set (0.00 sec)<\/p>\n

mysql> set global slow_query_log=1;<\/p>\n

Query OK, 0 rows affected (0.00 sec)<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

4\u3001binlog\u65e5\u5fd7\u6587\u4ef6<\/strong><\/h4>\n

1\uff09\u4f5c\u7528\uff1aBinlog\u8bb0\u5f55\u4e86\u6570\u636e\u5e93\u6240\u6709\u7684ddl\u8bed\u53e5\u548cdml\u8bed\u53e5\uff0c\u4f46\u4e0d\u5305\u62ecselect\u8bed\u53e5\u5185\u5bb9\uff0c\u8bed\u53e5\u4ee5\u4e8b\u4ef6\u7684\u5f62\u5f0f\u4fdd\u5b58\uff0c\u63cf\u8ff0\u4e86\u6570\u636e\u7684\u53d8\u66f4\u987a\u5e8f\uff0cbinlog\u8fd8\u5305\u62ec\u4e86\u6bcf\u4e2a\u66f4\u65b0\u8bed\u53e5\u7684\u6267\u884c\u65f6\u95f4\u4fe1\u606f\uff0cbinlog\u4e3b\u8981\u4f5c\u7528\u662f\u7528\u4e8e\u6062\u590d\u6570\u636e\uff0c\u56e0\u6b64binlog\u5bf9\u4e8e\u707e\u96be\u6062\u590d\u548c\u5907\u4efd\u6062\u590d\u6765\u8bf4\u81f3\u5173\u91cd\u8981\u3002\u76f8\u5f53\u4e8eoracle\u6570\u636e\u5e93\u4e2d\u7684archivelog\u6587\u4ef6\u3002<\/p>\n

binlog\u8fd8\u7528\u4e8e\u5b9e\u73b0mysql\u4e3b\u4ece\u590d\u5236\u3002<\/p>\n

\u4e8c\u8fdb\u5236\u65e5\u5fd7\u6587\u4ef6\u8bb0\u5f55\u4e86\u6570\u636e\u5e93\u7684\u53d8\u66f4\u8fc7\u7a0b\uff0c\u5bf9\u4e8e\u6570\u636e\u5e93\u7684\u5b89\u5168\u6027\u548c\u5b8c\u6574\u6027\u8d77\u7740\u81f3\u5173\u91cd\u8981\u7684\u4f5c\u7528\u3002<\/p>\n

2\uff09\u4f4d\u7f6e\u548c\u683c\u5f0f\uff1a\u5f53\u7528–log-bin[=file_name]\u9009\u9879\u542f\u52a8\u65f6\uff0cmysqld\u5f00\u59cb\u5c06\u6570\u636e\u5e93\u53d8\u66f4\u60c5\u51b5\u5199\u5165\u65e5\u5fd7\u6587\u4ef6\u3002\u5982\u679c\u6ca1\u6709\u7ed9\u51fafile_name\u503c\uff0c\u9ed8\u8ba4\u540d\u4e3a\u4e3b\u673a\u540d-bin\u3002\u5982\u679c\u7ed9\u51fa\u4e86\u6587\u4ef6\u540d\uff0c\u4f46\u4e0d\u5305\u542b\u8def\u5f84\uff0c\u5219\u6587\u4ef6\u9ed8\u8ba4\u88ab\u5199\u5165datadir\u53c2\u6570\u6307\u5b9a\u8def\u5f84\u3002<\/p>\n

mysqld\u5c06\u5728\u6bcf\u4e2abinlog\u65e5\u5fd7\u540d\u540e\u6dfb\u52a0\u4e00\u4e2a\u6570\u5b57\u6269\u5c55\u540d\uff0c\u6bcf\u6b21\u8981\u542f\u52a8\u670d\u52a1\u5668\u6216\u5237\u65b0\u65e5\u5fd7\u65f6\uff0c\u8be5\u6570\u5b57\u5c06\u4f1a\u589e\u52a0\uff0c\u5982\u679c\u5f53\u524d\u65e5\u5fd7\u5927\u5c0f\u8fbe\u5230\u4e86max-binlog-size\u53c2\u6570\u8bbe\u7f6e\u7684\u503c\uff0c\u90a3\u4e48mysqld\u4f1a\u81ea\u52a8\u521b\u5efa\u65b0\u7684\u4e8c\u8fdb\u5236\u65e5\u5fd7\u3002<\/p>\n

mysqld\u8fd8\u5c06\u521b\u5efa\u4e00\u4e2abinlog\u7d22\u5f15\u6587\u4ef6\uff0c\u5176\u4e2d\u5305\u542b\u4e86\u6240\u6709binlog\u6587\u4ef6\u7684\u6587\u4ef6\u540d\uff0c\u9ed8\u8ba4\u8be5\u7d22\u5f15\u6587\u4ef6\u4e0ebinlog\u6587\u4ef6\u540d\u76f8\u540c\uff0c\u6269\u5c55\u540d\u4e3a.index\u3002\u5f53mysqld\u8fd0\u884c\u65f6\uff0c\u4e0d\u53ef\u4ee5\u624b\u5de5\u7f16\u8f91\u8be5\u7d22\u5f15\u6587\u4ef6\uff0c\u8fd9\u6837\u53ef\u80fd\u5bfc\u81f4mysqld\u5f02\u5e38\u3002\u5f53rm\u5220\u9664binlog\u540e\uff0c\u4f60\u4e5f\u8bb8\u4e0d\u5f97\u4e0d\u624b\u5de5\u7f16\u8f91\u7d22\u5f15\u6587\u4ef6\u3002<\/p>\n

3\uff09binlog\u65e5\u5fd7\u683c\u5f0f<\/p>\n

mysql\u6709\u4e24\u79cd\u8bb0\u5f55\u547d\u4ee4\u65b9\u5f0f\uff0c\u4e00\u79cd\u662f\u8bed\u53e5\u7ea7\uff08binlog_format=statement\uff09\uff0c\u4e00\u79cd\u662f\u884c\u7ea7\uff08binlog_format=row\uff09\uff0c\u5efa\u8bae\u547d\u4ee4\u683c\u5f0f\u8bbe\u7f6e\u4e3a\u6df7\u5408\u6a21\u5f0f\uff08binlog_format=mixed\uff09\uff0c\u8fd9\u5728\u5927\u90e8\u5206\u6a21\u5f0f\u4e0b\u8bd5\u7528\uff0c\u5b83\u5728\u4e00\u822c\u60c5\u51b5\u4e0b\u8bd5\u7528\u8bed\u53e5\u7ea7\u8bb0\u5f55\uff0c\u4f46\u662f\u5728\u4e00\u4e9b\u7279\u6b8a\u60c5\u51b5\u4e0b\u8bd5\u7528\u884c\u7ea7\u8bb0\u5f55\uff0c\u8fd9\u589e\u52a0\u4e86\u590d\u5236\u7684\u5065\u58ee\u6027\u3002<\/p>\n

Mysq5.5\u4e2d\uff0c\u4e8c\u8fdb\u5236\u65e5\u5fd7\u683c\u5f0f\u5206\u4e3a3\u79cd\uff1astatement\uff0crow\u548cmixed\uff0c\u53ef\u4ee5\u5728\u542f\u52a8\u6570\u636e\u5e93\u65f6\u901a\u8fc7\u53c2\u6570–binlog_format\u8fdb\u884c\u8bbe\u7f6e\uff0c\u8fd93\u79cd\u683c\u5f0f\u533a\u522b\u5982\u4e0b\uff1a<\/p>\n

3.1\uff09statement\uff1a<\/p>\n

Mysql5.1\u4e4b\u524d\u7684\u7248\u672c\u90fd\u91c7\u7528\u8fd9\u79cd\u65b9\u5f0f\uff0cbinlog\u65e5\u5fd7\u4e2d\u8bb0\u5f55\u7684\u90fd\u662f\u8bed\u53e5\uff08\u57fa\u4e8e\u8bed\u53e5\u7ea7\u7684\u65e5\u5fd7\u91cc\u5305\u542b\u4e86\u539f\u59cb\u6267\u884c\u7684sql\u8bed\u53e5\uff0c\u8fd8\u6709\u5176\u4ed6\u4fe1\u606f\uff0c\u5982\u6267\u884c\u8bed\u53e5\u7684\u7ebf\u7a0bid\uff0c\u8bed\u53e5\u6267\u884c\u7684\u65f6\u95f4\u6233\uff0c\u6267\u884c\u6240\u6d88\u8017\u65f6\u957f\uff09\uff0c\u6bcf\u4e00\u6761\u5bf9\u6570\u636e\u5e93\u9020\u6210\u4fee\u6539\u7684\u8bed\u53e5\u90fd\u4f1a\u8bb0\u5f55\u5728\u65e5\u5fd7\u4e2d\uff0c\u901a\u8fc7mysqlbinlog\u5de5\u5177\uff0c\u53ef\u4ee5\u6e05\u6670\u7684\u770b\u5230\u6bcf\u6761\u8bed\u53e5\u7684\u6587\u672c\u3002\u4e3b\u4ece\u590d\u5236\u65f6\uff0c\u4ece\u5e93\u7684\u65e5\u5fd7\u89e3\u6790\u4e3a\u539f\u6587\u672c\u5728\u4ece\u5e93\u4e2d\u6267\u884c\u3002\u4f18\u70b9\u662f\u65e5\u5fd7\u91cf\u5c0f\uff0c\u6e05\u6670\u6613\u61c2\uff0c\u5bf9io\u538b\u529b\u5c0f\uff0c\u7f3a\u70b9\u662f\u67d0\u4e9b\u60c5\u51b5\u4e0bslave\u7684\u65e5\u5fd7\u590d\u5236\u4f1a\u51fa\u5dee\u3002<\/p>\n

3.2\uff09row\uff1a<\/p>\n

Mysql5.1.11\u4e4b\u540e\u51fa\u73b0\u7684\u3002\u5b83\u5c06\u6bcf\u4e00\u884c\u53d8\u66f4\u8bb0\u5f55\u5230\u65e5\u5fd7\u4e2d\uff0c\u800c\u4e0d\u662f\u8bb0\u5f55sql\u8bed\u53e5\uff08\u8bb0\u5f55\u884c\u7684\u66f4\u6539\u4fe1\u606f\u800c\u4e0d\u662f\u8bed\u53e5\uff09\u3002\u4f18\u70b9\u662f\u8bb0\u5f55\u6bcf\u4e00\u884c\u6570\u636e\u53d8\u5316\u7684\u7ec6\u8282\uff0c\u4e0d\u4f1a\u51fa\u73b0slave\u6a21\u5f0f\u4e0b\u67d0\u4e9b\u60c5\u51b5\u65e0\u6cd5\u590d\u5236\u7684\u60c5\u51b5\u3002\u7f3a\u70b9\u662f\u65e5\u5fd7\u91cf\u5927\uff0c\u5bf9io\u5f71\u54cd\u8f83\u5927\u3002\u901a\u8fc7mysqlbinlog\u9ed8\u8ba4\u770b\u5230\u7684\u90fd\u662f\u7ecf\u8fc7base-64\u7f16\u7801\u7684\u4fe1\u606f\uff0cmysqlbinlog\u52a0\u53c2\u6570-verbose\uff08\u6216-v\uff09\u5c06\u4f1a\u751f\u6210\u5e26\u6ce8\u91ca\u7684\u8bed\u53e5\uff0c\u5982\u679c\u8fde\u7eed\u4e24\u6b21\u4f7f\u7528\u8fd9\u4e2a\u53c2\u6570\uff08\u5982-v -v\uff09\uff0c\u5219\u751f\u6210\u5b57\u6bb5\u7c7b\u578b\u3001\u957f\u5ea6\u3001\u662f\u5426\u4e3anull\u7b49\u5c5e\u6027\u4fe1\u606f\u3002<\/p>\n

\u4e00\u822c\u800c\u8a00\uff0crow\u6a21\u5f0f\u66f4\u4e3a\u5065\u58ee\uff0c\u800cstatement\u7ea7\u522b\u5982\u679c\u5e94\u7528\u4e86mysql\u7684\u4e00\u4e9b\u989d\u5916\u7279\u6027\uff0c\u6bd4\u5982\u5b58\u50a8\u8fc7\u7a0b\u3001\u89e6\u53d1\u5668\uff0c\u5219\u53ef\u80fd\u5bfc\u81f4\u590d\u5236\u5f02\u5e38\uff0c\u6240\u4ee5\uff0c\u5982\u679c\u4f7f\u7528\u8bed\u53e5\u7ea7\u8bb0\u5f55\uff0c\u90a3\u4e48\u9700\u8981\u4fdd\u6301mysql\u6570\u636e\u5e93\u5e94\u7528\u7684\u7b80\u5355\u6027\uff0c\u5373\u53ea\u7528\u6838\u5fc3\u529f\u80fd\u5373\u53ef\u3002<\/p>\n

3.3\uff09mixed\uff1a<\/p>\n

\u8fd9\u662f\u76ee\u524dmysql\u7684\u9ed8\u8ba4binlog\u65e5\u5fd7\u683c\u5f0f\uff0c\u5373\u6df7\u5408\u4e86statement\u548crow\u4e24\u79cd\u65e5\u5fd7\u3002\u9ed8\u8ba4\u60c5\u51b5\u4e0b\u91c7\u7528statement\uff0c\u4f46\u662f\u5728\u4e00\u4e9b\u7279\u6b8a\u60c5\u51b5\u4e0b\u91c7\u7528row\u65b9\u5f0f\u8bb0\u5f55\u65e5\u5fd7\u3002\u6b64\u79cd\u6a21\u5f0f\u5229\u7528statement\u548crow\u7684\u4f18\u70b9\uff0c\u800c\u5c3d\u91cf\u907f\u5f00\u4ed6\u4eec\u7684\u7f3a\u70b9\u3002<\/p>\n

\u6ce8\u610f\uff1a\u53ef\u4ee5\u5728global\u548csession\u7ea7\u522b\u5bf9binlog\u65e5\u5fd7\u7684\u683c\u5f0f\u8fdb\u884c\u4fee\u6539\u3002<\/p>\n

4\uff09\u65e5\u5fd7\u6587\u4ef6\u7684\u8bfb\u53d6<\/p>\n

\u7531\u4e8ebinlog\u4ee5\u4e8c\u8fdb\u5236\u65b9\u5f0f\u5b58\u50a8\uff0c\u4e0d\u80fd\u76f4\u63a5\u8bfb\u53d6\uff0c\u56e0\u6b64\u9700\u8981\u4f7f\u7528mysqlbinlog\u5de5\u5177\u8fdb\u884c\u65e5\u5fd7\u5206\u6790\uff0c\u8bed\u6cd5\uff1amysqlbinlog log-file\u3002<\/p>\n

\u4f8b\uff1a<\/p>\n

4.1\uff09\u4ee5binlog\u65b9\u5f0f\u542f\u52a8\u6570\u636e\u5e93\uff1a<\/p>\n

[root@faspdev bin]# .\/mysqld_safe –user=mysql –log-bin=binlog-test<\/p>\n

4.2\uff09\u6267\u884cdml\u8bed\u53e5\uff1a<\/p>\n

mysql> delete from t1;<\/p>\n

Query OK, 2 rows affected (0.02 sec)<\/p>\n

4.3\uff09mysqlbinlog\u67e5\u770bbinlog\u5185\u5bb9\uff1a<\/p>\n\n\n\n\n
[root@faspdev data]# ..\/bin\/mysqlbinlog binlog-test.000001<\/p>\n

\/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*\/;<\/p>\n

\/*!40019 SET @@session.max_insert_delayed_threads=0*\/;<\/p>\n

\/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*\/;<\/p>\n

DELIMITER \/*!*\/;<\/p>\n

# at 4<\/p>\n

#161111 17:45:52 server id 1\u00a0 end_log_pos 120 CRC32 0x5bfb8e4f Start: binlog v 4, server v 5.6.31-log created 161111 17:45:52 at startup<\/p>\n

# Warning: this binlog is either in use or was not closed properly.<\/p>\n

ROLLBACK\/*!*\/;<\/p>\n

BINLOG ‘<\/p>\n

UJMlWA8BAAAAdAAAAHgAAAABAAQANS42LjMxLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA<\/p>\n

AAAAAAAAAAAAAAAAAABQkyVYEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAU+O<\/p>\n

+1s=<\/p>\n

‘\/*!*\/;<\/p>\n

# at 120<\/p>\n

#161111 17:47:29 server id 1\u00a0 end_log_pos 199 CRC32 0x3f0abe26 Query thread_id=1 exec_time=0 error_code=0<\/p>\n

SET TIMESTAMP=1478857649\/*!*\/;<\/p>\n

SET @@session.pseudo_thread_id=1\/*!*\/;<\/p>\n

SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1\/*!*\/;<\/p>\n

SET @@session.sql_mode=1075838976\/*!*\/;<\/p>\n

SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1\/*!*\/;<\/p>\n

\/*!\\C utf8 *\/\/*!*\/;<\/p>\n

SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33\/*!*\/;<\/p>\n

SET @@session.lc_time_names=0\/*!*\/;<\/p>\n

SET @@session.collation_database=DEFAULT\/*!*\/;<\/p>\n

BEGIN<\/p>\n

\/*!*\/;<\/p>\n

# at 199<\/p>\n

#161111 17:47:29 server id 1\u00a0 end_log_pos 287 CRC32 0x78e57a7e Query thread_id=1 exec_time=0 error_code=0<\/p>\n

use `test`\/*!*\/;<\/p>\n

SET TIMESTAMP=1478857649\/*!*\/;<\/p>\n

delete from t1<\/p>\n

\/*!*\/;<\/p>\n

# at 287<\/p>\n

#161111 17:47:29 server id 1\u00a0 end_log_pos 318 CRC32 0x0b2d7e98 Xid = 9<\/p>\n

COMMIT\/*!*\/;<\/p>\n

DELIMITER ;<\/p>\n

# End of log file<\/p>\n

ROLLBACK \/* added by mysqlbinlog *\/;<\/p>\n

\/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*\/;<\/p>\n

\/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*\/;<\/td>\n<\/tr>\n

\u5982\u679c\u65e5\u5fd7\u683c\u5f0f\u662frow\uff0cmysqlbinlog\u89e3\u6790\u7684\u6587\u4ef6\u662f\u65e0\u6cd5\u8bfb\u61c2\u7684\u5b57\u7b26\uff0c\u53ef\u4ee5\u52a0-v\u6216-v -v\u8fdb\u884c\u67e5\u770b\u3002<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

5\uff09\u9ed8\u8ba4\u60c5\u51b5\u4e0b\uff0c\u5e76\u4e0d\u662f\u6bcf\u6b21\u5199\u5165\u65f6\u90fd\u5c06binlog\u4e0e\u78c1\u76d8\u540c\u6b65\uff0c\u56e0\u6b64\u64cd\u4f5c\u7cfb\u7edf\u6216\u673a\u5668\u53d1\u751f\u6545\u969c\uff0c\u90a3\u4e48binlog\u4e2d\u6700\u540e\u7684\u8bed\u53e5\u6709\u53ef\u80fd\u4f1a\u4e22\u5931\u3002\u8981\u9632\u6b62\u8fd9\u79cd\u60c5\u51b5\u53d1\u751f\uff0c\u53ef\u4ee5\u8bbe\u7f6esync_binlog\u5168\u5c40\u53d8\u91cf\u4e3an\uff081\u4e3a\u6700\u5b89\u5168\u7684\u503c\uff0c\u540c\u65f6\u4e5f\u662f\u6700\u6162\u7684\uff09\uff0c\u4f7fbinlog\u6bcfn\u6b64\u5199\u5165\u5c31\u4e0e\u78c1\u76d8\u505a\u540c\u6b65\u3002<\/p>\n

6\uff09\u65e5\u5fd7\u6587\u4ef6\u7684\u5220\u9664<\/p>\n

\u67e5\u770bbinlog\u65b9\u6cd5\uff1a<\/p>\n

mysql> show binary logs;<\/p>\n

6.1\uff09\u65b9\u6cd51\uff1areset master\u5220\u9664\u6240\u6709\u7684binlog\u65e5\u5fd7\u6587\u4ef6\uff0c\u5220\u9664\u540e\u7684\u65e5\u5fd7\u6587\u4ef6\u4ece1\u5f00\u59cb\u7f16\u53f7\u3002<\/p>\n

mysql> reset master;<\/p>\n

Query OK, 0 rows affected (0.00 sec)<\/p>\n

6.2\uff09\u65b9\u6cd52\uff1a\u4f7f\u7528purge binary logs\u547d\u4ee4\u5220\u9664\u90e8\u5206binlog\u6587\u4ef6\uff1a<\/p>\n

mysql> purge binary logs to ‘mysql-bin.000001’;<\/p>\n

Query OK, 0 rows affected (0.03 sec)<\/p>\n

6.3\uff09\u65b9\u6cd53\uff1apurge master logs to \u2018mysql-bin.******\u2019\u547d\u4ee4\uff0c\u8be5\u547d\u4ee4\u5220\u9664\u2018******\u2019\u7f16\u53f7\u4e4b\u524d\u7684\u6240\u6709\u65e5\u5fd7\u6587\u4ef6\u3002<\/p>\n

6.4\uff09\u65b9\u6cd54\uff1apurge master logs before \u2018yyyy-mm-dd hh24:mi:ss\u2019\u547d\u4ee4\uff0c\u8be5\u547d\u4ee4\u5220\u9664\u2018yyyy-mm-dd hh24:mi:ss\u2019\u65e5\u671f\u4e4b\u524d\u7684\u6240\u6709\u65e5\u5fd7\u3002<\/p>\n

6.5\uff09\u8bbe\u7f6e\u53c2\u6570–expire_logs_days=#,\u6b64\u53c2\u6570\u542b\u4e49\u662f\u8bbe\u7f6e\u65e5\u5fd7\u8fc7\u671f\u5929\u6570\uff0c \u8fc7\u4e86\u6307\u5b9a\u5929\u6570\u540e\u7684\u65e5\u5fd7\u5c06\u4f1a\u88ab\u81ea\u52a8\u5220\u9664\uff0c\u8fd9\u6837\u6709\u5229\u4e8e\u51cf\u5c11DBA\u7ba1\u7406\u65e5\u5fd7\u7684\u5de5\u4f5c\u91cf\u3002<\/p>\n

7\uff09mysqlbinlog\u89e3\u6790\uff1a<\/p>\n

u # at 199<\/p>\n

u #161111 17:47:29 server id 1\u00a0 end_log_pos 287<\/p>\n

u # Query thread_id=1 exec_time=0 error_code=0<\/p>\n

8\uff09binlog\u7ba1\u7406\u76f8\u5173\u53c2\u6570<\/p>\n

8.1\uff09–binlog-do-db=db_name:\u8be5\u53c2\u6570\u544a\u8bc9\u4e3b\u670d\u52a1\u5668\uff0c\u5982\u679c\u5f53\u524d\u7684\u6570\u636e\u5e93\u662fdb_name\uff0c\u5e94\u8be5\u5c06\u5176\u8bb0\u5f55\u5230binlog\u4e2d\uff0c\u5176\u4ed6\u6ca1\u6709\u88ab\u6307\u5b9a\u7684\u6570\u636e\u5e93\u5c06\u88ab\u5ffd\u7565\uff0c\u53ef\u591a\u6b21\u4f7f\u7528\uff0c\u5bf9\u591a\u4e2a\u6570\u636e\u5e93\u8fdb\u884c\u5b9a\u4e49\u3002<\/p>\n

8.2\uff09–binlog-ignore-db=db_name:\u6539\u53c2\u6570\u544a\u8bc9\u4e3b\u670d\u52a1\u5668\uff0c\u5982\u679c\u5f53\u524d\u6570\u636e\u5e93\u4e3adb_name\uff0c\u5219\u4e0d\u8bb0\u5f55binlog\uff0c\u5176\u4ed6\u6ca1\u6709\u6307\u5b9a\u7684\u6570\u636e\u5e93\u53d8\u5316\u5c06\u8bb0\u5f55binlog\u4e2d\uff0c\u53ef\u591a\u6b21\u4f7f\u7528\uff0c\u5bf9\u591a\u4e2a\u6570\u636e\u5e93\u8fdb\u884c\u5b9a\u4e49\u3002<\/p>\n

8.3\uff09–innodb-sage-binlog:\u8be5\u53c2\u6570\u7ecf\u5e38\u548c–sync-binlog=N\uff08\u6ca1\u5199N\u6b21\u65e5\u5fd7\u540c\u6b65\u78c1\u76d8\uff09\u4e00\u8d77\u914d\u5408\u4f7f\u7528\uff0c\u4f7f\u5f97\u4e8b\u52a1\u5728\u65e5\u5fd7\u4e2d\u7684\u8bb0\u5f55\u66f4\u52a0\u5b89\u5168\u3002<\/p>\n

8.4\uff09Set sql_log_bin=0\uff1a\u5177\u6709super\u6743\u9650\u7684\u5ba2\u6237\u7aef\u53ef\u4ee5\u901a\u8fc7\u6b64\u8bed\u53e5\u7981\u6b62\u5c06\u81ea\u5df1\u7684\u8bed\u53e5\u8bb0\u5f55\u4e8c\u8fdb\u5236\u6587\u4ef6\uff0c\u8fd9\u4e2a\u9009\u9879\u5728\u67d0\u4e9b\u73af\u5883\u4e0b\u662f\u6709\u7528\u7684\uff0c\u6bd4\u5982\u4e3b\u4e3b\u5207\u6362\uff0c\u6216\u8005mysql\u7248\u672c\u5347\u7ea7\uff0c\u4f46\u662f\u4f7f\u7528\u4e00\u5b9a\u500d\u52a0\u5c0f\u5fc3\uff0c\u514d\u5f97\u9020\u6210slave\u73af\u5883\u4e0b\u7684\u6570\u636e\u4e0d\u4e00\u81f4\u3002<\/p>\n

5\u3001\u65e5\u5fd7\u6587\u4ef6\u7ef4\u62a4<\/strong><\/h4>\n

\u5f88\u591a\u751f\u4ea7\u73af\u5883\uff0c\u65e5\u5fd7\u6587\u4ef6\u53ef\u4ee5\u5360\u7528\u5927\u91cf\u7684\u78c1\u76d8\u7a7a\u95f4\uff0c\u56e0\u6b64\uff0c\u9700\u8981\u5bf9\u65e5\u5fd7\u6587\u4ef6\u8fdb\u884c\u5b9a\u671f\u6e05\u7406\u64cd\u4f5c\uff0cmysql\u670d\u52a1\u5668\u5f53\u7136\u4e5f\u4e0d\u4f8b\u5916\u3002<\/p>\n

l \u5bf9\u4e8e\u9519\u8bef\u65e5\u5fd7\u6587\u4ef6\uff0c\u4e00\u822c\u4e0d\u4f1a\u592a\u5927\uff0c\u56e0\u6b64\u751f\u4ea7\u4e0a\u6ce8\u610f\u4e00\u4e0b\u5c31\u53ef\u4ee5\u4e86\u3002<\/p>\n

l \u901a\u7528\u65e5\u5fd7\u4e00\u822c\u751f\u4ea7\u4e0d\u5f00\u542f\uff0c\u56e0\u6b64\u4e5f\u4e0d\u5b58\u5728\u6e05\u7406\u4e00\u8bf4\u3002<\/p>\n

l \u6162\u67e5\u8be2\u65e5\u5fd7\u5728\u6162\u67e5\u8be2\u5f88\u591a\u7684\u60c5\u51b5\u4e0b\u53ef\u80fd\u53d8\u5f97\u5f88\u5927\uff0c\u53ef\u4ee5\u624b\u5de5\u6e05\u7406\u6216\u7f16\u5199\u811a\u672c\u7ba1\u7406\u3002<\/p>\n

l binlog\u65e5\u5fd7\u6587\u4ef6\u53ef\u4ee5\u8bbe\u7f6e\u5408\u9002\u7684\u8fc7\u671f\u7b56\u7565\uff0c\u5982expire-logs-days=10\uff0c\u8868\u793a\u8bbe\u7f6ebinlog\u8fc7\u671f\u65f6\u95f4\u4e3a10\u5929\u3002expires-logs-days\u8bbe\u7f6e\u4f1a\u5728\u8fd0\u884cflush logs\u547d\u4ee4\u540e\u89e6\u53d1\u5220\u9664\u8fc7\u671f\u65e5\u5fd7\uff0c\u6ce8\u610f\uff0c\u4e0d\u80fd\u4f7f\u7528rm\u5220\u9664binlog\u65e5\u5fd7\u6587\u4ef6\uff0c\u8fd9\u53ef\u80fd\u5bfc\u81f4\u4f60\u6267\u884c\u65e5\u5fd7\u6e05\u7406\u547d\u4ee4\u5931\u8d25\u3002mysql 5.6\u53ef\u4ee5\u8bbe\u7f6e\u4fdd\u7559binlog\u65e5\u5fd7\u6587\u4ef6\u5927\u5c0f\uff0c\u907f\u514d\u78c1\u76d8\u5bf9\u586b\u6ee1\u3002<\/p>\n

\u4e8c\u3001innodb\u6570\u636e\u6587\u4ef6\u548c\u65e5\u5fd7\u6587\u4ef6<\/strong><\/h3>\n

1\u3001\u4ee5\u4e0b\u7b80\u5355\u4ecb\u7ecdmysql\u6570\u636e\u76ee\u5f55\u4e0b\u7684\u6587\u4ef6\uff1a<\/p>\n

1\uff09db.opt<\/p>\n

\u6570\u636e\u5e93\u7ed3\u6784\u5b9a\u4e49\u548c\u8bbe\u7f6e<\/p>\n

2\uff09*.frm<\/p>\n

\u6570\u636e\u8868\u7684\u7ed3\u6784\u5b9a\u4e49<\/p>\n

3\uff09*.MYD<\/p>\n

Myisam\u5f15\u64ce\u8868\u6570\u636e<\/p>\n

4\uff09*.MYI<\/p>\n

Myisam\u5f15\u64ce\u7d22\u5f15\u6570\u636e<\/p>\n

5\uff09ibdata*<\/p>\n

Innodb\u8868\u7a7a\u95f4\u6570\u636e\u6587\u4ef6<\/p>\n

\u5982\u679c\u5c06innodb_file_per_table\u8bbe\u7f6e\u4e3a1\uff0c\u90a3\u4e48innodb\u6570\u636e\u8868\u53ef\u4ee5\u5404\u81ea\u5b58\u50a8\u4e3a\u4e00\u4e2a\u6587\u4ef6\uff0c\u8fd9\u79cd\u6a21\u5f0f\u6210\u4e3a\u72ec\u7acb\u8868\u7a7a\u95f4\u3002\u5982\u679c\u5c06innodb_file_per_table\u8bbe\u7f6e\u4e3a0\uff0c\u90a3\u4e48innodb\u6570\u636e\u5219\u53ef\u4ee5\u7edf\u4e00\u5b58\u50a8\u5728\u4e00\u4e2a\u5171\u4eab\u8868\u7a7a\u95f4\u91cc\u3002<\/p>\n

6\uff09ib_logfile*<\/p>\n

Innodb\u91cd\u505a\u65e5\u5fd7\u6587\u4ef6<\/p>\n

7\uff09*.idb<\/p>\n

Innodb\u6570\u636e\u548c\u7d22\u5f15\u3002<\/p>\n

8\uff09*.trg<\/p>\n

\u89e6\u53d1\u5668<\/p>\n

2\u3001innodb\u6570\u636e\u6587\u4ef6\u548c\u65e5\u5fd7\u6587\u4ef6<\/p>\n

\u5982\u679c\u6ca1\u6709\u518d\u53c2\u6570\u6587\u4ef6\u4e2d\u6307\u5b9ainnodb\u76f8\u5173\u53c2\u6570\uff0c\u90a3\u4e48mysql\u5c06\u4f1a\u5728data\u76ee\u5f55\u4e0b\u521b\u5efa\u4e00\u4e2a\u5927\u5c0f\u4e3a10m\u7684ibdata1\u6587\u4ef6\u548c\u5927\u5c0f5m\u7684\u4e24\u4e2aib_logfile*\u6587\u4ef6\u3002\u8fd9\u79cd\u8bbe\u7f6e\u5bf9\u4e8e\u751f\u4ea7\u800c\u8a00\u592a\u5c0f\uff0c\u4e00\u822c\u5efa\u8bae\u8bbe\u7f6elogfile\u4e3a256m\uff0c\u6570\u636e\u6587\u4ef6\u521d\u59cb\u5927\u5c0f1g~5g\uff0c\u5e76\u4e14\u8bbe\u7f6e\u81ea\u52a8\u589e\u957f\u3002<\/p>\n

\u53c2\u6570\u914d\u7f6e\u6837\u4f8b\uff1a<\/p>\n

Innodb_data_file_path=ibdata1:1000m:autiextend<\/p>\n

Innodb_log_file_size=256m<\/p>\n

Innodb_data_file_path\u7684\u503c\u53ef\u4ee5\u8bbe\u7f6e\u4e3a1\u4e2a\u6216\u591a\u4e2a\u5217\u8868\u3002\u5982\u679c\u8981\u547d\u540d\u4e00\u4e2a\u4ee5\u4e0a\u7684\u6570\u636e\u6587\u4ef6\uff0c\u8bf7\u7528\u201c;\u201d\u5206\u9694\u5b83\u4eec\u3002\u5176\u8bed\u6cd5\u683c\u5f0f\u4e3a\uff1aInnodb_data_file_path=datafile01:size[;datafile02:size;…]\u3002<\/p>\n

\u4f8b\u5982\uff1aInnodb_data_file_path=datafile01:2000m;datafile02:2000m;datafile03:2000m:autoextend<\/p>\n

\u5176\u4e2dautoextend\u53ca\u5176\u540e\u5c5e\u76f8\u53ea\u80fd\u7528\u4e8eInnodb_data_file_path\u5217\u8868\u4e2d\u6700\u540e\u4e00\u4e2a\u6570\u636e\u6587\u4ef6\uff0c\u9488\u5bf9\u4e8e\u6700\u540e\u4e00\u4e2a\u6570\u636e\u6587\u4ef6\uff0c\u5982\u679cinnodb\u5171\u4eab\u8868\u7a7a\u95f4\u8017\u5c3d\u540e\uff0c\u5c31\u4f1a\u6269\u5c55\u6700\u540e\u4e00\u4e2a\u6570\u636e\u6587\u4ef6\uff0c\u9ed8\u8ba4\u6269\u5c55\u5927\u5c0f\u4e3a8m\u3002<\/p>\n

1\uff09innodb\u72ec\u7acb\u8868\u7a7a\u95f4\u548c\u5171\u4eab\u8868\u7a7a\u95f4<\/p>\n

\u5171\u4eab\u8868\u7a7a\u95f4\u4f7f\u7528\u7b80\u5355\uff0c\u65b9\u4fbf\u7ef4\u62a4\uff0c\u4f46\u662f\u4e5f\u5b58\u5728\u7f3a\u70b9\uff0c\u4f7f\u7528\u8d21\u732e\u8868\u7a7a\u95f4\u660e\u663e\u7684\u7f3a\u70b9\u662f\u4e0d\u80fd\u5feb\u901f\u56de\u6536\u5220\u9664\u5927\u8868\u7684\u7a7a\u95f4\uff0cio\u64cd\u4f5c\u53ef\u80fd\u4f1a\u6d88\u8017\u66f4\u591a\u7684\u8d44\u6e90\u7b49\u5f85\u3002\u800c\u72ec\u7acb\u8868\u7a7a\u95f4\u662f\u5927\u90e8\u5206DBA\u63a8\u8350\u4f7f\u7528\u7684\u65b9\u5f0f\uff0c\u5b83\u6070\u597d\u5728\u8fd9\u4e00\u70b9\u5f25\u8865\u4e86\u5171\u4eab\u8868\u7a7a\u95f4\u4e0d\u8db3\u3002Innodb\u4e0b\u4f7f\u7528\u72ec\u7acb\u8868\u7a7a\u95f4\uff0c\u6bcf\u4e2a\u8868\u90fd\u6709\u5b83\u81ea\u5df1\u7684\u8868\u7a7a\u95f4\u3002<\/p>\n

\u5f00\u542f\u72ec\u7acb\u8868\u7a7a\u95f4\u65b9\u5f0f\uff1a<\/p>\n

\u5728my.cnf\u6587\u4ef6\u4e2d\u6dfb\u52a0\u5982\u4e0b\u53c2\u6570\uff0c\u91cd\u542fmysql\u5b9e\u4f8b\u5373\u53ef\u751f\u6548\uff1a<\/p>\n

[mysqld]<\/p>\n

innodb_file_per_table<\/p>\n

\u91cd\u542f\u5b9e\u4f8b\u540e\uff0cinnodb\u5c06\u4f1a\u628a\u65b0\u521b\u5efa\u7684\u8868\u5b58\u50a8\u5230\u6570\u636e\u76ee\u5f55\u4e0b\u7684\u6587\u4ef6tb1_name.ibd\u4e2d\uff0c\u8fd9\u7c7b\u4f3c\u57dfmyisam\u5b58\u50a8\u5f15\u64ce\u6240\u505a\u7684\u4e8b\uff0c\u4f46\u662fmyisam\u628a\u8868\u5206\u6210\u6570\u636e\u6587\u4ef6tb1_name.MYD\u548c\u7d22\u5f15\u6587\u4ef6tb1_name.MYI\u3002\u5bf9\u4e8einnodb\uff0c\u6570\u636e\u548c\u7d22\u5f15\u4f1a\u653e\u5728\u4e00\u8d77\uff0c\u90fd\u5b58\u653e\u5230.ibd\u6587\u4ef6\u4e2d\uff0c\u4e0d\u8fc7tb1_name.frm\u6587\u4ef6\u7167\u65e7\u4f1a\u88ab\u521b\u5efa\u3002<\/p>\n

\u5728my.cnf\u6587\u4ef6\u4e2d\u5220\u9664\u4e86innodb_file_per_table\u884c\uff0c\u91cd\u542f\u5b9e\u4f8b\u540e\uff0cinnodb\u65b0\u521b\u5efa\u7684\u8868\u4f1a\u5b58\u50a8\u5728\u5171\u4eab\u8868\u7a7a\u95f4\u91cc\uff0c\u5c31\u662f\u8bf4innodb_file_per_table\u8fd9\u4e2a\u53c2\u6570\u4ec5\u5f71\u54cd\u8868\u7684\u521b\u5efa\u4f4d\u7f6e\u3002innodb\u4f7f\u7528\u72ec\u7acb\u8868\u7a7a\u95f4\uff0c\u4e5f\u4ecd\u7136\u6709\u4e00\u90e8\u5206\u6570\u636e\u5728\u5171\u4eab\u8868\u7a7a\u95f4\u91cc\u3002<\/p>\n

\u76f8\u5bf9\u4e8emyisam\u5f15\u64ce\u7ba1\u7406\u7684\u8868\uff0c\u6211\u4eec\u4e0d\u80fd\u968f\u610f\u79fb\u52a8.ibd\u6587\u4ef6\uff0c\u8fd9\u662f\u56e0\u4e3a\u8868\u5b9a\u4e49\u662f\u88ab\u5b58\u50a8\u5728innodb\u5171\u4eab\u8868\u7a7a\u95f4\u91cc\u7684\uff0c\u800cinnodb\u5fc5\u987b\u4fdd\u6301\u4e8b\u52a1id\u548c\u4e8b\u52a1\u65e5\u5fd7\u987a\u5e8f\u53f7\u7684\u4e00\u81f4\u6027\u3002<\/p>\n

2\uff09innodb\u589e\u52a0\u6570\u636e\u6587\u4ef6<\/p>\n

\u624b\u52a8\u589e\u52a0\u4e00\u4e2ainnodb\u6570\u636e\u6587\u4ef6\u65f6\u9700\u8981\u91cd\u542f\u5b9e\u4f8b\u3002\u4e3ainnodb\u5b58\u50a8\u5f15\u64ce\u7ba1\u7406\u8868\u6dfb\u52a0\u6570\u636e\u6587\u4ef6\u9700\u8981\u4fee\u6539my.cnf\u53c2\u6570\u6587\u4ef6\uff0c\u628ainnodb_data_file_path\u53c2\u6570\u6307\u5b9a\u6dfb\u52a0\u7684\u6570\u636e\u6587\u4ef6\u53ca\u5176\u521d\u59cb\u5927\u5c0f\u548c\u76f8\u5173\u53c2\u6570\u3002\u6ce8\u610f\u5f53\u4f60\u6dfb\u52a0\u4e00\u4e2a\u6570\u636e\u6587\u4ef6\u5230innodb_data_file_path\u65f6\uff0c\u9700\u8981\u786e\u5b9a\u5b83\u4e0d\u5b58\u5728\uff0c\u5f53\u4f60\u91cd\u542f\u5b9e\u4f8b\u65f6\uff0cinnodb\u4f1a\u81ea\u52a8\u521b\u5efa\u8fd9\u4e2a\u6570\u636e\u6587\u4ef6\u3002\u6dfb\u52a0innodb\u6570\u636e\u6587\u4ef6\u9700\u8981\u9996\u5148\u8ba1\u7b97\u5f53\u524dinnodb_data_file_path\u4e2d\u6700\u540e\u4e00\u4e2a\u6570\u636e\u6587\u4ef6\u5927\u5c0f\uff0c\u7136\u540e\u4f30\u8ba1\u4e00\u4e2a\u8fd1\u4f3c\u503c\u7ed9\u5b83\uff0c\u518d\u6dfb\u52a0\u65b0\u7684\u6570\u636e\u6587\u4ef6\u3002Innodb\u6570\u636e\u6587\u4ef6\u53ea\u6709\u6700\u540e\u4e00\u4e2a\u53ef\u4ee5\u8bbe\u7f6e\u4e3a\u81ea\u52a8\u6269\u5c55\u3002<\/p>\n

3\uff09\u6539\u53d8innodb\u91cd\u505a\u65e5\u5fd7\u5927\u5c0f<\/p>\n

\u4e0d\u8981\u8bd5\u56fe\u901a\u8fc7\u76f4\u63a5\u66f4\u6539\u914d\u7f6e\u6587\u4ef6\u6765\u8bbe\u7f6einnodb\u4e8b\u52a1\u65e5\u5fd7\u5927\u5c0f\uff0c\u8fd9\u4f1a\u5bfc\u81f4\u4e0d\u80fd\u542f\u52a8\u6570\u636e\u5e93\u3002\u5982\u679c\u60f3\u8981\u6539\u53d8innodb\u4e8b\u52a1\u65e5\u5fd7\u7684\u6570\u91cf\u548c\u5927\u5c0f\uff0c\u5fc5\u987b\u6b63\u5e38\u5173\u95edmysql\u5b9e\u4f8b\uff0c\u7136\u540e\u590d\u5236\u65e7\u65e5\u5fd7\u6587\u4ef6\u5230\u4e00\u4e2a\u5b89\u5168\u7684\u5730\u65b9\u505a\u5907\u4efd\uff0c\u7136\u540e\u4ece\u65e5\u5fd7\u6587\u4ef6\u76ee\u5f55\u5220\u9664\u6240\u6709\u7684\u65e7\u65e5\u5fd7\u6587\u4ef6\uff0c\u4e4b\u540e\u66f4\u6539my.cnf\u53c2\u6570\u6587\u4ef6\u6539\u53d8\u65e5\u5fd7\u6587\u4ef6\u914d\u7f6e\uff0c\u5e76\u518d\u6b21\u542f\u52a8mysql\u5b9e\u4f8b\u3002Mysqld\u5728\u542f\u52a8\u65f6\u4f1a\u53d1\u73b0\u6ca1\u6709\u91cd\u505a\u65e5\u5fd7\u6587\u4ef6\uff0c\u7136\u540e\u544a\u8bc9\u4f60\u5b83\u6b63\u5728\u521b\u5efa\u4e00\u4e2a\u65b0\u7684\u65e5\u5fd7\u6587\u4ef6\u3002<\/p>\n

\u66f4\u6539innodb\u4e8b\u52a1\u65e5\u5fd7\u6b65\u9aa4\uff1a<\/p>\n

l \u5e72\u51c0\u5173\u95edmysql\u5b9e\u4f8b<\/p>\n

l \u4f7f\u7528mv\u547d\u4ee4\u8f6c\u79fbinnodb\u91cd\u505a\u65e5\u5fd7\u6587\u4ef6\u3002<\/p>\n

l \u4fee\u6539my.cnf\u914d\u7f6e\u6587\u4ef6\uff0c\u66f4\u6539innodb_log_file_size<\/p>\n

l \u542f\u52a8mysql\u5b9e\u4f8b<\/p>\n

\u6ce8\u610f\uff0c\u65e7\u7248\u672cmysql\u670d\u52a1\u5668\u91cd\u505a\u65e5\u5fd7\u603b\u5927\u5c0f\u4e0d\u80fd\u8d85\u8fc74g\uff0c\u5728mysql5.6\u4ee5\u540e\uff0c\u9650\u5236\u6269\u5c55\u5230521g\u3002<\/p>\n

4\uff09innodb\u7684undo\u533a\u57df<\/p>\n

Undo\u533a\u57df\u4e5f\u79f0\u4e3aundo\u7a7a\u95f4\u6216undo\u8868\u7a7a\u95f4\uff0c\u662finnodb\u8bbe\u8ba1\u7684\u4e00\u5757\u7279\u6b8a\u5b58\u50a8\u533a\u57df\uff0c\u5b83\u4fdd\u5b58\u4e86\u88ab\u6d3b\u52a8\u4e8b\u52a1\u66f4\u6539\u7684\u6570\u636e\u7684\u526f\u672c\uff08\u955c\u50cf\uff09\uff0c\u5982\u679c\u4e00\u4e2a\u4e8b\u52a1\u9700\u8981\u67e5\u770b\u539f\u6765\u7684\u6570\u636e\uff08\u6ee1\u8db3\u4e00\u81f4\u6027\u8bfb\uff09\uff0c\u90a3\u4e48\u53ef\u4ee5\u4eceundo\u533a\u57df\u4e2d\u83b7\u5f97\u672a\u88ab\u66f4\u6539\u7684\u6570\u636e\u3002\u9ed8\u8ba4\u60c5\u51b5\u4e0b\uff0cundo\u533a\u57df\u4e5f\u662f\u5728innodb\u5171\u4eab\u8868\u7a7a\u95f4\u5185\u3002Mysql5.6\u4ee5\u540e\u63d0\u4f9b\u4e86\u9009\u9879\uff0c\u53ef\u4ee5\u628aundo\u8868\u7a7a\u95f4\u72ec\u7acb\u5230\u8868\u7a7a\u95f4\uff0c\u8fd9\u6837\u5c31\u53ef\u4ee5\u8fdb\u884c\u70ed\u70b9\u5757\u7684io\u4f18\u5316\uff0c\u63d0\u5347\u6027\u80fd\u3002<\/p>\n

\u5982\u679cundo\u8868\u7a7a\u95f4\u66b4\u6da8\uff0c\u51fa\u73b0\u8fd9\u79cd\u60c5\u51b5\u53ef\u80fd\u662f\u56e0\u4e3a\u8d1f\u8f7d\u6bd4\u8f83\u5927\uff0c\u6216\u8005\u5b58\u5728\u957f\u65f6\u95f4\u672a\u63d0\u4ea4\u7684\u4e8b\u52a1\uff08\u957f\u4e8b\u52a1\uff09\u3002<\/p>\n

\u5bf9\u4e8e\u5199\u64cd\u4f5c\u6bd4\u8f83\u9891\u7e41\u7684\u5e94\u7528\uff0cinnodb\u6e05\u7406\u7ebf\u7a0b\u7684\u901f\u5ea6\u53ef\u80fd\u4f1a\u8ddf\u4e0d\u4e0a\uff0c\u4ece\u800c\u5bfc\u81f4undo\u8868\u7a7a\u95f4\u8d8a\u6765\u8d8a\u5927\uff0c\u53ef\u4ee5\u901a\u8fc7\u8bbe\u7f6einnodb_max_purge_lag\u53c2\u6570\u6765\u907f\u514dundo\u7a7a\u95f4\u8fc7\u5ea6\u589e\u5927\u3002Innodb\u4e8b\u52a1\u7cfb\u7edf\u7ef4\u6301\u4e00\u4e2a\u4e8b\u52a1\u5217\u8868\uff0c\u8be5\u5217\u8868\u8bb0\u5f55\u88abupdate\u6216delete\u64cd\u4f5c\u8868\u793a\u4e3a\u5220\u9664\u7684\u7d22\u5f15\u8bb0\u5f55\u3002\u8fd9\u4e2a\u5217\u8868\u7684\u957f\u5ea6\u4e3apurge_lag\u3002\u5f53purge_lag\u8d85\u8fc7\u4e86nnodb_max_purge_lag\u65f6\uff0c\u6bcf\u4e2adml\u8bed\u53e5\u90fd\u88ab\u5ef6\u8fdf\u4e00\u5b9a\u65f6\u95f4\u3002<\/p>\n

Undo\u7a7a\u95f4\u91cc\u4fdd\u5b58\u4e86\u6570\u636e\u7684\u524d\u955c\u50cf\uff0c\u8fd9\u662f\u6ee1\u8db3\u4e00\u81f4\u6027\u8bfb\u7684\u6839\u672c\u539f\u56e0\uff0c\u540c\u65f6\u4e5f\u662f\u707e\u96be\u6062\u590d\u7684\u91cd\u8981\u89d2\u8272\uff08\u5373\u56de\u6eda\uff09\u3002<\/p>\n

3\u3001\u4e34\u65f6\u6587\u4ef6<\/p>\n

Mysql\u4e34\u65f6\u6587\u4ef6\u4f5c\u7528\u7c7b\u4f3c\u4f46\u4e0d\u7b49\u540c\u4e8eoracle\u4e34\u65f6\u8868\u7a7a\u95f4\u3002Mysql\u4f7f\u7528\u73af\u5883\u53d8\u91cfTMPDIR\u7684\u503c\u4f5c\u4e3a\u4fdd\u5b58\u4e34\u65f6\u6587\u4ef6\u7684\u76ee\u5f55\u3002\u5982\u679c\u672a\u8bbe\u7f6e\uff0c\u5219\u9ed8\u8ba4\u4f7f\u7528\u7cfb\u7edf\u7684\u9ed8\u8ba4\u503c\uff0c\u4e00\u822c\u4e3a\/tmp\uff0c\/var\/tmp\uff0c\/usr\/tmp\u3002\u53ef\u4ee5\u4f7f\u7528–tmpdir\u53c2\u6570\u5728\u542f\u52a8\u65f6\u6307\u5b9amysql\u4e34\u65f6\u76ee\u5f55\uff0c\u6216\u8005\u5728my.cnf\u6587\u4ef6\u4e2d\u6307\u5b9atmpdir\u53c2\u6570\u8fdb\u884c\u5206\u914d\u3002<\/p>\n

\u5982\u679cmysql\u670d\u52a1\u5668\u6b63\u5728\u4f5c\u4e3a\u590d\u5236\u670d\u52a1\u5668\u4f7f\u7528\uff0c\u90a3\u4e48\u4e0d\u80fd\u5c06mysql\u4e34\u65f6\u8def\u5f84\u6307\u5411\u57fa\u4e8e\u5185\u5b58\u7684\u6587\u4ef6\u7cfb\u7edf\u76ee\u5f55\u6216\u8005\u4e3b\u673a\u91cd\u542f\u4f1a\u6e05\u7a7a\u7684\u76ee\u5f55\uff0c\u5426\u5219\u53ef\u80fd\u9020\u6210\u590d\u5236\u5931\u8d25\u3002Mysql\u9690\u542b\u521b\u5efa\u6240\u6709\u7684\u4e34\u65f6\u6587\u4ef6\u3002\u8fdb\u884c\u6392\u5e8f\u64cd\u4f5c\u65f6\uff0cmysql\u4f1a\u4f7f\u7528\u4e00\u4e2a\u6216\u591a\u4e2a\u4e34\u65f6\u6587\u4ef6\u3002\u4e00\u5b9a\u8981\u4fdd\u8bc1\u4e34\u65f6\u76ee\u5f55\u7a7a\u95f4\u591f\u7528\u3002\u4e00\u4e9b\u666e\u901a\u7684\u6570\u636e\u5e93\u64cd\u4f5c\u90fd\u6709\u53ef\u80fd\u521b\u5efa\u4e34\u65f6\u6587\u4ef6\u7528\u4e8e\u7ef4\u62a4\u6570\u636e\u5e93\u5185\u7684\u6570\u636e\u7ed3\u6784\uff0calter table\u4f1a\u5728\u539f\u8868\u6240\u5728\u76ee\u5f55\u4e0b\u521b\u5efa\u4e34\u65f6\u8868\u3002<\/p>\n

4\u3001mysql\u5957\u63a5\u5b57\u6587\u4ef6<\/p>\n

\u670d\u52a1\u5668\u7528\u6765\u4e0e\u672c\u5730\u5ba2\u6237\u7aef\u8fdb\u884c\u901a\u4fe1\u7684linux\u5957\u63a5\u5b57\u6587\u4ef6\uff08\u4e5f\u79f0\u4e3asocket\u6587\u4ef6\uff09\u9ed8\u8ba4\u4f4d\u7f6e\u662f\/tmp\/mysql.sock\u3002Scoket\u6587\u4ef6\u4e0d\u5efa\u8bae\u653e\u7f6e\u5728\/tmp\u76ee\u5f55\u4e0b\uff0c\u53ef\u4ee5\u5c06\u5176\u5355\u72ec\u6307\u5b9a\u5b58\u50a8\u4f4d\u7f6e\uff0c\u6211\u4eec\u53ef\u4ee5\u901a\u8fc7my.cnf\u914d\u7f6e\u6587\u4ef6\u6307\u5b9amysql socket\u6587\u4ef6\u5b58\u50a8\u8def\u5f84\uff0c\u4e0b\u9762\u662fmy.cnf\u4e2d\u5b9a\u4e49socket\u50a8\u5b58\u8def\u5f84\uff1a<\/p>\n

[mysqld]<\/p>\n

Socket=\/opt\/mysql\/mysql.socket<\/p>\n

[client]<\/p>\n

Socket=\/opt\/mysql\/mysql.socket<\/p>\n

Mysql\u670d\u52a1\u5668\u4e00\u5b9a\u8981\u4fdd\u7559root\u7684\u9664\u4e86socket\u5916\u7684\u5176\u4ed6\u767b\u5f55\u65b9\u5f0f\uff0c\u6bd4\u5982\u901a\u8fc7127.0.0.1\u7684root\u767b\u5f55\u8d26\u53f7\uff0c\u4ee5\u9632\u6b62socket\u6587\u4ef6\u88ab\u5220\u9664\u5bfc\u81f4\u65e0\u6cd5\u767b\u5f55mysql\u6570\u636e\u5e93\u7684\u7a98\u5883\u3002<\/p>\n

\u4e09\u3001MySQL\u707e\u96be\u6062\u590d\u8fc7\u7a0b<\/strong><\/h3>\n

1\u3001\u9884\u5199\u65e5\u5fd7\u548cundo\u8868\u7a7a\u95f4<\/p>\n

mysql\u9760\u9884\u5199\u65e5\u5fd7\uff08innodb\u4e8b\u52a1\u65e5\u5fd7\uff09\u6765\u4fdd\u8bc1\u6570\u636e\u6301\u4e45\u6027\uff0c\u4e5f\u5c31\u662f\u8bf4\u6570\u636e\u6587\u4ef6\u4e0d\u4f1a\u5148\u88ab\u5199\u5165\uff0c\u800c\u662f\u5148\u5199\u65e5\u5fd7\u6587\u4ef6\u3002innodb\u810f\u6570\u636e\u5b58\u5728\u4e8einnodb_buffer_pool\u91cc\uff0c\u5b83\u4f1a\u6309\u7167\u4e00\u5b9a\u7684\u673a\u5236\u6279\u91cf\u5199\u5165\u78c1\u76d8\uff0c\u8fd9\u6837\u53ef\u4ee5\u63d0\u9ad8\u541e\u5410\u7387\u3002<\/p>\n

mysql\u5b95\u673a\u540e\u7684\u5b9e\u4f8b\u6062\u590d\u8fc7\u7a0b\uff1a\u9996\u5148mysql\u627e\u5230\u4e8b\u52a1\u65e5\u5fd7\u4e2d\u7684\u67d0\u4e2a\u70b9\uff0c\u4ece\u8be5\u70b9\u5f00\u59cb\u91cd\u505aredo\u4e2d\u7684\u4e8b\u52a1\uff0c\u5728\u5e94\u7528\u4e86\u6240\u6709redo\u65e5\u5fd7\u540e\uff0c\u6839\u636eundo\u533a\u57df\u786e\u5b9a\u54ea\u4e9b\u4e8b\u52a1\u9700\u8981\u56de\u6eda\uff0c\u7136\u540e\u56de\u6eda\u54ea\u4e9b\u6ca1\u6709\u63d0\u4ea4\u7684\u4e8b\u52a1\uff0c\u7b80\u5355\u7406\u89e3\uff0cmysql\u707e\u96be\u6062\u590d\u8fc7\u7a0b\u5c31\u662f\u6839\u636eredo\u91cd\u505a\u65e5\u5fd7\uff0c\u7136\u540e\u6839\u636eundo\u56de\u9000\u4e8b\u52a1\u3002<\/p>\n

innodb\u4e8b\u52a1\u65e5\u5fd7\u5f88\u5927\u7a0b\u5ea6\u4e0a\u51b3\u5b9a\u4e86\u6570\u636e\u7684\u5b89\u5168\u6027\uff0c\u65e5\u5fd7\u7684\u6301\u4e45\u6027\u51b3\u5b9a\u4e86\u707e\u96be\u6062\u590d\u540e\u4e22\u5931\u591a\u5c11\u6570\u636e\uff01mysql\u53ef\u4ee5\u901a\u8fc7\u53c2\u6570\u63a7\u5236commit\u65f6\u5199\u5165\u4e8b\u52a1\u65e5\u5fd7\u7684\u9891\u7387\uff0c\u901a\u5e38\u6709\u4ee5\u4e0b3\u79cd\u60c5\u51b5\uff1a<\/p>\n

1\uff09innodb_flush_log_at_trx_commit=1<\/p>\n

\u6bcf\u6b21commit\u65f6\u90fd\u5199\u5165\u78c1\u76d8\uff0c\u8fd9\u6837\u7406\u8bba\u4e0a\u53d1\u751f\u6545\u969c\u65f6\u6211\u4eec\u53ea\u4e22\u5931\u4e00\u4e2a\u4e8b\u52a1\u3002<\/p>\n

2\uff09innodb_flush_log_at_trx_commit=2<\/p>\n

\u6bcf\u6b21commit\uff0c\u53ea\u5199\u65e5\u5fd7\u7f13\u5b58\u5230\u64cd\u4f5c\u7cfb\u7edf\u7f13\u51b2\uff0c\u4f46\u4e0d\u5237\u65b0\u78c1\u76d8\uff0cinnodb\u6bcf\u79d2\u5237\u65b0\u78c1\u76d8\u4e00\u6b21\uff0c\u6240\u4ee5\u6545\u969c\u4e22\u5931\u7684\u662f\u6700\u8fd11\u79d2\u7684\u6570\u636e\u3002\u751f\u4ea7\u73af\u5883\u5efa\u8bae\u8fd9\u6837\u8bbe\u7f6e\u3002<\/p>\n

3\uff09innodb_flush_log_at_trx_commit=0<\/p>\n

\u6bcf\u79d2\u628a\u65e5\u5fd7\u7f13\u51b2\u7684\u5185\u5bb9\u5199\u5165\u5230\u65e5\u5fd7\u6587\u4ef6\uff0c\u5e76\u4e14\u5237\u65b0\u5230\u78c1\u76d8\uff0c\u4f46commit\u65f6\u4ec0\u4e48\u4e5f\u4e0d\u505a\u3002<\/p>\n

2\u3001\u53cc\u5199\u7f13\u51b2<\/p>\n

\u6570\u636e\u6587\u4ef6\u7684\u5199\u64cd\u4f5c\uff0c\u53ef\u80fd\u4f1a\u5c06\u5757\u5199\u574f\uff0cmysql\u8bbe\u8ba1\u4e86\u4e00\u4e2a\u6570\u636e\u5b58\u50a8\u533a\u57df\u53cc\u5199\u7f13\u51b2\uff0cinnodb\u4f7f\u7528\u53cc\u5199\u7f13\u51b2\u6765\u786e\u4fdd\u6570\u636e\u7684\u5b89\u5168\uff0c\u907f\u514d\u5757\u635f\u574f\u3002\u53cc\u5199\u7f13\u51b2\u662finnodb\u8868\u7a7a\u95f4\u7684\u4e00\u4e2a\u7279\u6b8a\u533a\u57df\uff0c\u4e3b\u8981\u7528\u4e8e\u5199\u5165\u9875\u7684\u5907\u4efd\uff0c\u5e76\u4e14\u662f\u987a\u5e8f\u5199\u5165\u7684\u3002\u5f53innodb\u5237\u65b0\u6570\u636e\u65f6\uff0c\u9996\u5148\u5199\u5165\u53cc\u5199\u7f13\u51b2\uff0c\u7136\u540e\u5199\u5165\u6570\u636e\u6587\u4ef6\u3002\u8fd9\u6837\u65e2\u53ef\u786e\u4fdd\u6240\u6709\u5199\u64cd\u4f5c\u7684\u539f\u5b50\u6027\u548c\u6301\u4e45\u6027\u3002<\/p>\n

\u5d29\u6e83\u91cd\u542f\u540e\uff0cinnodb\u4f1a\u68c0\u67e5\u6bcf\u4e2a\u5757\u7684\u6821\u9a8c\u548c\uff0c\u5224\u65ad\u5757\u662f\u5426\u635f\u574f\uff0c\u5982\u679c\u5199\u5165\u53cc\u5199\u7f13\u51b2\u7684\u662f\u574f\u5757\uff0c\u90a3\u4e48\u663e\u7136\u6ca1\u6709\u5199\u5165\u5b9e\u9645\u6570\u636e\u6587\u4ef6\uff0c\u90a3\u4e48\u7528\u6570\u636e\u6587\u4ef6\u7684\u5757\u6062\u590d\u53cc\u5199\u7f13\u51b2\uff1b\u5982\u679c\u5199\u5165\u4e86\u53cc\u5199\u7f13\u51b2\uff0c\u4f46\u662f\u6570\u636e\u6587\u4ef6\u4e2d\u7684\u662f\u574f\u5757\uff0c\u90a3\u4e48\u4f7f\u7528\u53cc\u5199\u7f13\u51b2\u4e2d\u7684\u5757\u6062\u590d\u5b9e\u9645\u6570\u636e\u6587\u4ef6\u4e2d\u7684\u5757\u3002\u8fd9\u6837\u7684\u673a\u5236\u63d0\u4f9b\u4e86\u53cc\u5c42\u7684\u5b89\u5168\u4fdd\u969c\uff0c\u4f46\u662f\u7f3a\u70b9\u662f\u589e\u52a0\u4e86io\u3002<\/p>\n

\u5bf9\u4e8e\u8bfb\u53d6\u64cd\u4f5c\uff0cinnodb\u901a\u8fc7\u9875\u6821\u9a8c\u548c\u6765\u4fdd\u8bc1\u6570\u636e\u7684\u5b58\u53d6\uff0c\u6bcf\u9875\u5728\u5185\u5b58\u4e2d\u90fd\u5148\u7b97\u597d\u4e00\u4e2a\u6821\u9a8c\u503c\uff0c\u653e\u5728\u6587\u4ef6\u5934\u90e8\uff0c\u5199\u5165\u7684\u65f6\u5019\u5148\u5199\u6821\u9a8c\u503c\uff0c\u8bfb\u7684\u65f6\u5019\u4e5f\u4f1a\u6821\u9a8c\u4e00\u4e0b\u6821\u9a8c\u503c\u3002<\/p>\n

\u56db\u3001mysql\u53c2\u6570\u6587\u4ef6\u53ca\u53c2\u6570\u4fee\u6539\u65b9\u6cd5<\/strong><\/h3>\n

MySQL\u6570\u636e\u5e93\u521d\u59cb\u5316\u53c2\u6570\u7531\u53c2\u6570\u6587\u4ef6\u6765\u8bbe\u7f6e\uff0c\u5982\u679c\u6ca1\u6709\u8bbe\u7f6e\u53c2\u6570\u6587\u4ef6\uff0cmysql\u5c31\u6309\u7167\u7cfb\u7edf\u4e2d\u53c2\u6570\u7684\u9ed8\u8ba4\u503c\u6765\u542f\u52a8\u3002\u5728windows\u548clinux\u4e0a\uff0c\u53c2\u6570\u6587\u4ef6\u53ef\u4ee5\u88ab\u653e\u5728\u591a\u4e2a\u4f4d\u7f6e\uff0c\u6570\u636e\u5e93\u542f\u52a8\u65f6\u6309\u7167\u4e0d\u540c\u7684\u987a\u5e8f\u6765\u641c\u7d22\uff0c\u5982\u679c\u591a\u4e2a\u4f4d\u7f6e\u90fd\u6709\u53c2\u6570\u6587\u4ef6\uff0c\u5219\u641c\u7d22\u987a\u5e8f\u9760\u540e\u7684\u53c2\u6570\u6587\u4ef6\u4e2d\u7684\u53c2\u6570\u5c06\u8986\u76d6\u524d\u7684\u53c2\u6570\u3002<\/p>\n

\u8868\uff1alinux\u4e0bmysql\u53c2\u6570\u6587\u4ef6\u8bfb\u53d6\u987a\u5e8f\uff1a<\/p>\n\n\n\n\n\n\n\n
\u53c2\u6570\u6587\u4ef6\u540d<\/td>\n\u76ee\u7684<\/td>\n<\/tr>\n
\/etc\/my.cnf<\/td>\n\u5168\u5c40\u9009\u9879<\/td>\n<\/tr>\n
$MYSQL_HOME\/my.cnf<\/td>\n\u670d\u52a1\u5668\u76f8\u5173\u9009\u9879<\/td>\n<\/tr>\n
Default-extra-file<\/td>\n\u7528–Default-extra-file=path\u9009\u9879\u6307\u5b9a\u7684\u6587\u4ef6\uff0c\u5982\u679c\u8be5\u6587\u4ef6\u5b58\u5728\u7684\u8bdd<\/td>\n<\/tr>\n
~\/.my.cnf<\/td>\n\u7528\u6237\u76f8\u5173\u9009\u9879<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Mysql\u5b89\u88c5\u4e0a\u8ff0\u987a\u5e8f\u5bfb\u627e\u53c2\u6570\u6587\u4ef6\uff0c\u5982\u679c\u591a\u4e2a\u6587\u4ef6\u540c\u65f6\u5b58\u5728\uff0c\u90a3\u4e48\u6587\u4ef6\u4e2d\u6307\u5b9a\u7684\u540e\u8bfb\u53d6\u7684\u9009\u9879\u8981\u4f18\u5148\u4e8e\u5148\u8bfb\u53d6\u7684\u9009\u9879\uff0c\u6240\u4ee5\u6570\u636e\u76ee\u5f55\u6216\u5b89\u88c5\u76ee\u5f55\u4e0b\u7684\u914d\u7f6e\u6587\u4ef6\u90fd\u6709\u53ef\u80fd\u751f\u6548\uff0c\u6240\u4ee5\u7406\u8bba\u4e0a\u5728\u6570\u636e\u76ee\u5f55\u6216\u5b89\u88c5\u76ee\u5f55\u4e0b\u653e\u7f6e\u4e00\u4e2amy.cnf\u6587\u4ef6\u5373\u53ef\u3002Mysql\u542f\u52a8\u65f6\u53ef\u4ee5\u6307\u5b9a–datadir\u7528\u4e8e\u6307\u5b9a\u6570\u636e\u8def\u5f84\uff0c\u4f46\u662f\u6b64\u9009\u9879\u5bf9\u4e8e\u67e5\u8be2\u53c2\u6570\u6587\u4ef6\u65e0\u6548\uff0c\u56e0\u4e3a\u5728\u8bfb\u53d6–datadir\u4e4b\u524dmysql\u670d\u52a1\u5668\u5c31\u5df2\u7ecf\u8bfb\u53d6\u4e86\u914d\u7f6e\u6587\u4ef6\u4e86\u3002\u591a\u6b21\u6307\u5b9a\u4e00\u4e2a\u9009\u9879\uff0c\u540e\u51fa\u73b0\u7684\u5c06\u8986\u76d6\u5148\u51fa\u73b0\u7684\u503c\uff0c\u56e0\u6b64\u547d\u4ee4\u884c\u4e2d\u901a\u8fc7set\u4fee\u6539\u7684\u9009\u9879\u4f18\u5148\u7ea7\u6700\u9ad8\u3002<\/p>\n

\u901a\u8fc7\u5982\u4e0b\u547d\u4ee4\u53ef\u4ee5\u5217\u51famysqld\u8bfb\u53d6\u53c2\u6570\u6587\u4ef6\u7684\u4f18\u5148\u987a\u5e8f\uff1a\u8be5\u547d\u4ee4\u4e0d\u80fd\u5728\u751f\u4ea7\u73af\u5883\u968f\u610f\u8fd0\u884c<\/p>\n

$mysqld –verbose –help |grep my.cnf<\/p>\n

\/etc\/my.cnf \/etc\/mysql\/my.cnf \/usr\/local\/mysql\/etc\/my.cnf ~\/.my.cnf<\/p>\n

Mysql\u53c2\u6570\u53ef\u4ee5\u57283\u4e2a\u7ea7\u522b\u8fdb\u884c\u66f4\u6539\uff1a<\/p>\n

1\uff09session\u7ea7\u522b\uff1aset<\/p>\n

2\uff09\u5168\u5c40\u7ea7\u522b\uff1aset global<\/p>\n

3\uff09\u6c38\u4e45\u7ea7\u522b\uff1amy.cnf<\/p>\n\n\n\n\n
\u9644\uff1a\u4e00\u4efd\u751f\u4ea7\u53ef\u7528\u7684mysql\u914d\u7f6e\u6587\u4ef6\uff08my.cnf\uff09\u793a\u4f8b\uff08\u6d4b\u8bd5\u5e93mysql 5.6.24\uff09\uff1a<\/strong><\/td>\n<\/tr>\n
[client]<\/p>\n

#\u5ba2\u6237\u7aef\u9009\u9879\u8bbe\u7f6e<\/p>\n

port = 3306<\/p>\n

socket = \/opt\/mysql-5.6.24\/data\/mysql.socket<\/p>\n

#\u8bbe\u7f6e\u5ba2\u6237\u7aef\u548c\u8fde\u63a5\u5b57\u7b26\u96c6<\/p>\n

default_character_set = utf8<\/p>\n

[mysqld]<\/p>\n

#\u670d\u52a1\u5668\u7aef\u9009\u9879\u8bbe\u7f6e<\/p>\n

# innodb\u8bbe\u7f6e<\/p>\n

default_storage_engine = InnoDB<\/p>\n

innodb_strict_mode = 1<\/p>\n

innodb_buffer_pool_size = 256M\u00a0\u00a0\u00a0\u00a0\u00a0 #mysql\u6570\u636e\u5e93\u670d\u52a1\u5668\uff0c\u8be5\u503c\u53ef\u8bbe\u4e3a\u7269\u7406\u5185\u5b58\u768450%-80%\u4e4b\u95f4<\/p>\n

innodb_stats_on_metadata = 0<\/p>\n

innodb_file_format = Barracuda<\/p>\n

innodb_data_file_path=ibdata1:10m:autoextend<\/p>\n

innodb_flush_method = O_DIRECT<\/p>\n

innodb_log_files_in_group = 2<\/p>\n

innodb_log_file_size = 16M<\/p>\n

innodb_log_buffer_size = 8M<\/p>\n

innodb_file_per_table = 1<\/p>\n

innodb_max_dirty_pages_pct = 60<\/p>\n

innodb_io_capacity = 200<\/p>\n

innodb_flush_log_at_trx_commit = 2<\/p>\n

# \u57fa\u672c\u8bbe\u7f6e<\/p>\n

basedir = \/opt\/mysql-5.6.24<\/p>\n

datadir = \/opt\/mysql-5.6.24\/data<\/p>\n

port = 3306<\/p>\n

server_id = 19900315<\/p>\n

tmpdir = \/opt\/mysql-5.6.24\/tmp<\/p>\n

socket = \/opt\/mysql-5.6.24\/tmp\/mysql.socket<\/p>\n

Pid-file = \/opt\/mysql-5.6.24\/data\/mysql.pid<\/p>\n

skip-name-resolve = 1<\/p>\n

skip-external-locking = 1<\/p>\n

max_connect_errors = 500<\/p>\n

max_connections = 1000<\/p>\n

relay-log = mysql-relay-bin<\/p>\n

log-slave-updates = 1<\/p>\n

skip_slave_start = 1<\/p>\n

read_only = 0<\/p>\n

key_buffer_size = 8M<\/p>\n

tmp_table_size = 8M<\/p>\n

max_heap_table_size = 8M<\/p>\n

query_cache_type = 0<\/p>\n

query_cache_size = 0<\/p>\n

thread_cache_size = 1024<\/p>\n

open_files_limit = 65535<\/p>\n

table_open_cache = 1024<\/p>\n

max_allowed_packet = 16M<\/p>\n

gtid-mode = ON<\/p>\n

enforce-gtid-consistency = 1<\/p>\n

lower_case_table_names=1<\/p>\n

log-bin-trust-function-creators<\/p>\n

plugin-load = “rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so”<\/p>\n

replicate-ignore-table = mysql.ibbackup_binlog_marker<\/p>\n

slave-skip-errors = ddl_exist_errors<\/p>\n

relay-log-info-repository = TABLE<\/p>\n

relay_log_recovery = 1<\/p>\n

master_info_repository = TABLE<\/p>\n

# \u670d\u52a1\u5668\u5b57\u7b26\u96c6\u8bbe\u7f6e<\/p>\n

character_set_server = utf8<\/p>\n

# error log\u8bbe\u7f6e<\/p>\n

log_error = \/opt\/mysql-5.6.24\/data\/mysql.err<\/p>\n

# slow log\u8bbe\u7f6e<\/p>\n

slow_query_log = 1<\/p>\n

slow_query_log_file = \/opt\/mysql-5.6.24\/data\/mysql-slow.log<\/p>\n

long_query_time = 0.5<\/p>\n

# binlog\u8bbe\u7f6e<\/p>\n

binlog_format = mixed<\/p>\n

log-bin = \/opt\/mysql-5.6.24\/logs\/mysql-bin<\/p>\n

sync_binlog = 2<\/p>\n

max_binlog_size = 16M<\/p>\n

expire_logs_days = 10<\/p>\n

#others\u8bbe\u7f6e<\/p>\n

join_buffer_size = 128M<\/p>\n

sort_buffer_size = 2M<\/p>\n

read_rnd_buffer_size = 2M<\/p>\n

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n","protected":false},"excerpt":{"rendered":"

\u4e00\u3001MySQL\u65e5\u5fd7\u6587\u4ef6 mysql\u65e5\u5fd7\u6587\u4ef6\u53ca\u529f\u80fd\uff1a \u65e5\u5fd7\u6587\u4ef6 \u529f\u80fd \u9519\u8bef\u65e5\u5fd7 \u8bb0…
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-894","post","type-post","status-publish","format-standard","hentry","category-mysql"],"_links":{"self":[{"href":"https:\/\/freesilo.com\/index.php?rest_route=\/wp\/v2\/posts\/894","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=894"}],"version-history":[{"count":1,"href":"https:\/\/freesilo.com\/index.php?rest_route=\/wp\/v2\/posts\/894\/revisions"}],"predecessor-version":[{"id":895,"href":"https:\/\/freesilo.com\/index.php?rest_route=\/wp\/v2\/posts\/894\/revisions\/895"}],"wp:attachment":[{"href":"https:\/\/freesilo.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=894"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/freesilo.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=894"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/freesilo.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=894"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}