{"id":1427,"date":"2022-07-03T15:02:37","date_gmt":"2022-07-03T07:02:37","guid":{"rendered":"http:\/\/dba.qishuo.xin\/?p=1427"},"modified":"2024-03-17T20:15:11","modified_gmt":"2024-03-17T12:15:11","slug":"mariadb%e5%8f%82%e6%95%b0%e6%9f%a5%e7%9c%8b%e6%8c%81%e7%bb%ad%e6%9b%b4%e6%96%b0%e4%b8%ad","status":"publish","type":"post","link":"http:\/\/dba.qishuo.xin\/?p=1427","title":{"rendered":"MariaDB\u53c2\u6570\u67e5\u770b(\u6301\u7eed\u66f4\u65b0\u4e2d)"},"content":{"rendered":"<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_76 ez-toc-wrap-right counter-hierarchy ez-toc-counter ez-toc-light-blue ez-toc-container-direction\">\n<label for=\"ez-toc-cssicon-toggle-item-69f5bf548d42d\" class=\"ez-toc-cssicon-toggle-label\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #999;color:#999\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #999;color:#999\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/label><input type=\"checkbox\"  id=\"ez-toc-cssicon-toggle-item-69f5bf548d42d\" checked aria-label=\"Toggle\" \/><nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"http:\/\/dba.qishuo.xin\/?p=1427\/#1_%E6%95%B0%E6%8D%AE%E5%BA%93%E6%80%BB%E4%BD%93%E8%BF%90%E8%A1%8C%E6%83%85%E5%86%B5\" >1. \u6570\u636e\u5e93\u603b\u4f53\u8fd0\u884c\u60c5\u51b5<\/a><ul class='ez-toc-list-level-4' ><li class='ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"http:\/\/dba.qishuo.xin\/?p=1427\/#11_%E6%95%B0%E6%8D%AE%E5%BA%93%E5%AD%97%E7%AC%A6%E9%9B%86%E4%B8%8ECollation\" >1.1. \u6570\u636e\u5e93\u5b57\u7b26\u96c6\u4e0eCollation<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"http:\/\/dba.qishuo.xin\/?p=1427\/#12_%E6%95%B0%E6%8D%AE%E5%BA%93%E4%B8%AD%E6%9F%90%E5%BC%A0%E8%A1%A8%E6%89%80%E7%94%A8%E7%9A%84%E5%AD%97%E7%AC%A6%E9%9B%86\" >1.2. \u6570\u636e\u5e93\u4e2d\u67d0\u5f20\u8868\u6240\u7528\u7684\u5b57\u7b26\u96c6<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"http:\/\/dba.qishuo.xin\/?p=1427\/#13_%E6%9F%A5%E7%9C%8B%E8%A1%A8%E4%B8%AD%E6%89%80%E6%9C%89%E5%88%97%E7%9A%84%E5%AD%97%E7%AC%A6%E9%9B%86\" >1.3. \u67e5\u770b\u8868\u4e2d\u6240\u6709\u5217\u7684\u5b57\u7b26\u96c6<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"http:\/\/dba.qishuo.xin\/?p=1427\/#14_%E6%9F%A5%E7%9C%8B%E6%95%B0%E6%8D%AE%E5%BA%93%E4%BD%BF%E7%94%A8%E7%AB%AF%E5%8F%A3\" >1.4. \u67e5\u770b\u6570\u636e\u5e93\u4f7f\u7528\u7aef\u53e3<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-6\" href=\"http:\/\/dba.qishuo.xin\/?p=1427\/#15_%E6%9F%A5%E7%9C%8B%E5%BD%93%E5%89%8D%E6%95%B0%E6%8D%AE%E5%BA%93%E6%95%B0%E6%8D%AE%E5%A4%A7%E5%B0%8F%E3%80%81%E7%B4%A2%E5%BC%95%E5%A4%A7%E5%B0%8F%E4%BB%A5%E5%8F%8A%E6%80%BB%E5%A4%A7%E5%B0%8F\" >1.5. \u67e5\u770b\u5f53\u524d\u6570\u636e\u5e93\u6570\u636e\u5927\u5c0f\u3001\u7d22\u5f15\u5927\u5c0f\u4ee5\u53ca\u603b\u5927\u5c0f<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-7\" href=\"http:\/\/dba.qishuo.xin\/?p=1427\/#16_%E6%9F%A5%E7%9C%8B%E6%95%B0%E6%8D%AE%E5%BA%93%E7%9A%84%E6%89%80%E6%9C%89%E7%94%A8%E6%88%B7%E4%BF%A1%E6%81%AF\" >1.6. \u67e5\u770b\u6570\u636e\u5e93\u7684\u6240\u6709\u7528\u6237\u4fe1\u606f<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-8\" href=\"http:\/\/dba.qishuo.xin\/?p=1427\/#17_%E6%9F%A5%E7%9C%8B%E6%9F%90%E4%B8%AA%E5%85%B7%E4%BD%93%E7%94%A8%E6%88%B7%E7%9A%84%E6%9D%83%E9%99%90\" >1.7. \u67e5\u770b\u67d0\u4e2a\u5177\u4f53\u7528\u6237\u7684\u6743\u9650<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-9\" href=\"http:\/\/dba.qishuo.xin\/?p=1427\/#18_%E8%8E%B7%E5%8F%96%E5%BD%93%E5%89%8D%E4%BC%9A%E8%AF%9D%E8%BF%9B%E7%A8%8B%E7%94%A8%E6%88%B7%E5%90%8D\" >1.8. \u83b7\u53d6\u5f53\u524d\u4f1a\u8bdd\/\u8fdb\u7a0b\u7528\u6237\u540d<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-10\" href=\"http:\/\/dba.qishuo.xin\/?p=1427\/#19_%E8%8E%B7%E5%8F%96%E5%BD%93%E5%89%8D%E4%BC%9A%E8%AF%9D%E8%BF%9B%E7%A8%8BID\" >1.9. \u83b7\u53d6\u5f53\u524d\u4f1a\u8bdd\/\u8fdb\u7a0bID<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-11\" href=\"http:\/\/dba.qishuo.xin\/?p=1427\/#110_%E6%9F%A5%E7%9C%8B%E6%95%B0%E6%8D%AE%E5%BA%93%E8%BF%9E%E6%8E%A5%E7%9A%84%E7%94%A8%E6%88%B7%E6%83%85%E5%86%B5\" >1.10. \u67e5\u770b\u6570\u636e\u5e93\u8fde\u63a5\u7684\u7528\u6237\u60c5\u51b5<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-12\" href=\"http:\/\/dba.qishuo.xin\/?p=1427\/#111_%E6%9F%A5%E8%AF%A2%E7%94%A8%E6%88%B7%E6%AD%A3%E5%9C%A8%E6%89%A7%E8%A1%8C%E7%9A%84SQL%E8%AF%AD%E5%8F%A5\" >1.11. \u67e5\u8be2\u7528\u6237\u6b63\u5728\u6267\u884c\u7684SQL\u8bed\u53e5<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-13\" href=\"http:\/\/dba.qishuo.xin\/?p=1427\/#112_kill%E6%AD%A3%E5%9C%A8%E6%89%A7%E8%A1%8CSQL%E7%9A%84%E8%BF%9B%E7%A8%8B\" >1.12. kill\u6b63\u5728\u6267\u884cSQL\u7684\u8fdb\u7a0b<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-14\" href=\"http:\/\/dba.qishuo.xin\/?p=1427\/#2_%E6%95%B0%E6%8D%AE%E5%BA%93%E7%94%A8%E6%88%B7%E3%80%81%E6%9D%83%E9%99%90%E5%8F%8A%E9%85%8D%E7%BD%AE\" >2. \u6570\u636e\u5e93\u7528\u6237\u3001\u6743\u9650\u53ca\u914d\u7f6e<\/a><ul class='ez-toc-list-level-4' ><li class='ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-15\" href=\"http:\/\/dba.qishuo.xin\/?p=1427\/#21_%E6%9F%A5%E8%AF%A2%E6%89%80%E6%9C%89%E7%94%A8%E6%88%B7\" >2.1. \u67e5\u8be2\u6240\u6709\u7528\u6237<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-16\" href=\"http:\/\/dba.qishuo.xin\/?p=1427\/#22_%E6%9F%A5%E7%9C%8B%E6%95%B0%E6%8D%AE%E5%BA%93%E4%B8%AD%E5%85%B7%E4%BD%93%E6%9F%90%E4%B8%AA%E7%94%A8%E6%88%B7%E7%9A%84%E6%9D%83%E9%99%90\" >2.2. \u67e5\u770b\u6570\u636e\u5e93\u4e2d\u5177\u4f53\u67d0\u4e2a\u7528\u6237\u7684\u6743\u9650<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-17\" href=\"http:\/\/dba.qishuo.xin\/?p=1427\/#23_%E8%B5%8B%E4%BA%88%E5%92%8C%E5%9B%9E%E6%94%B6%E5%88%A0%E9%99%A4%E6%95%B0%E6%8D%AE%E5%BA%93%E7%9A%84%E6%9D%83%E9%99%90\" >2.3. \u8d4b\u4e88\u548c\u56de\u6536\u5220\u9664\u6570\u636e\u5e93\u7684\u6743\u9650<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-18\" href=\"http:\/\/dba.qishuo.xin\/?p=1427\/#24_%E8%B5%8B%E4%BA%88%E7%94%A8%E6%88%B7%E6%9F%A5%E8%AF%A2information_schema%E6%95%B0%E6%8D%AE%E5%BA%93%E6%9D%83%E9%99%90\" >2.4. \u8d4b\u4e88\u7528\u6237\u67e5\u8be2information_schema\u6570\u636e\u5e93\u6743\u9650<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-19\" href=\"http:\/\/dba.qishuo.xin\/?p=1427\/#3_%E6%97%A5%E5%BF%97\" >3. \u65e5\u5fd7<\/a><ul class='ez-toc-list-level-4' ><li class='ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-20\" href=\"http:\/\/dba.qishuo.xin\/?p=1427\/#31_%E6%9F%A5%E7%9C%8B%E6%97%A5%E5%BF%97%E5%BC%80%E5%90%AF%E7%8A%B6%E6%80%81\" >3.1. \u67e5\u770b\u65e5\u5fd7\u5f00\u542f\u72b6\u6001<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-21\" href=\"http:\/\/dba.qishuo.xin\/?p=1427\/#32_%E6%9F%A5%E7%9C%8B%E6%89%80%E6%9C%89binlog%E6%97%A5%E5%BF%97%E5%88%97%E8%A1%A8\" >3.2. \u67e5\u770b\u6240\u6709binlog\u65e5\u5fd7\u5217\u8868<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-22\" href=\"http:\/\/dba.qishuo.xin\/?p=1427\/#33_%E6%9F%A5%E7%9C%8B%E6%9C%80%E6%96%B0%E4%B8%80%E4%B8%AAbinlog%E6%97%A5%E5%BF%97%E7%9A%84%E7%BC%96%E5%8F%B7%E5%90%8D%E7%A7%B0%EF%BC%8C%E5%8F%8A%E5%85%B6%E6%9C%80%E5%90%8E%E4%B8%80%E4%B8%AA%E6%93%8D%E4%BD%9C%E4%BA%8B%E4%BB%B6%E7%BB%93%E6%9D%9F%E7%82%B9\" >3.3. \u67e5\u770b\u6700\u65b0\u4e00\u4e2abinlog\u65e5\u5fd7\u7684\u7f16\u53f7\u540d\u79f0\uff0c\u53ca\u5176\u6700\u540e\u4e00\u4e2a\u64cd\u4f5c\u4e8b\u4ef6\u7ed3\u675f\u70b9<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-23\" href=\"http:\/\/dba.qishuo.xin\/?p=1427\/#34_%E5%88%B7%E6%96%B0log%E6%97%A5%E5%BF%97%EF%BC%8C%E7%AB%8B%E5%88%BB%E4%BA%A7%E7%94%9F%E4%B8%80%E4%B8%AA%E6%96%B0%E7%BC%96%E5%8F%B7%E7%9A%84binlog%E6%97%A5%E5%BF%97%E6%96%87%E4%BB%B6%EF%BC%8C%E8%B7%9F%E9%87%8D%E5%90%AF%E4%B8%80%E4%B8%AA%E6%95%88%E6%9E%9C\" >3.4. \u5237\u65b0log\u65e5\u5fd7\uff0c\u7acb\u523b\u4ea7\u751f\u4e00\u4e2a\u65b0\u7f16\u53f7\u7684binlog\u65e5\u5fd7\u6587\u4ef6\uff0c\u8ddf\u91cd\u542f\u4e00\u4e2a\u6548\u679c<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-24\" href=\"http:\/\/dba.qishuo.xin\/?p=1427\/#35_%E4%BD%BF%E7%94%A8%E5%91%BD%E4%BB%A4mysqlbinlog%E5%8E%BB%E8%A7%A3%E6%9E%90mysql%E6%97%A5%E5%BF%97%E6%96%87%E4%BB%B6\" >3.5. \u4f7f\u7528\u547d\u4ee4mysqlbinlog\u53bb\u89e3\u6790mysql\u65e5\u5fd7\u6587\u4ef6<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-25\" href=\"http:\/\/dba.qishuo.xin\/?p=1427\/#36_%E6%85%A2%E6%9F%A5%E8%AF%A2%E6%97%A5%E5%BF%97%E5%8F%82%E6%95%B0\" >3.6. \u6162\u67e5\u8be2\u65e5\u5fd7\u53c2\u6570<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-26\" href=\"http:\/\/dba.qishuo.xin\/?p=1427\/#37_%E9%94%99%E8%AF%AF%E6%97%A5%E5%BF%97%E6%96%87%E4%BB%B6%E8%B7%AF%E5%BE%84\" >3.7. \u9519\u8bef\u65e5\u5fd7\u6587\u4ef6\u8def\u5f84<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-27\" href=\"http:\/\/dba.qishuo.xin\/?p=1427\/#4_%E6%80%A7%E8%83%BD%E5%8F%82%E6%95%B0\" >4. \u6027\u80fd\u53c2\u6570<\/a><ul class='ez-toc-list-level-4' ><li class='ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-28\" href=\"http:\/\/dba.qishuo.xin\/?p=1427\/#41_%E8%AF%BB%E5%86%99%E6%AF%94%E4%BE%8B\" >4.1. \u8bfb\u5199\u6bd4\u4f8b<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-29\" href=\"http:\/\/dba.qishuo.xin\/?p=1427\/#42_%E6%85%A2%E6%9F%A5%E8%AF%A2%E6%AF%94%E4%BE%8B\" >4.2. \u6162\u67e5\u8be2\u6bd4\u4f8b<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-30\" href=\"http:\/\/dba.qishuo.xin\/?p=1427\/#43_%E8%BF%9E%E6%8E%A5%E6%95%B0%E6%A3%80%E6%9F%A5\" >4.3. \u8fde\u63a5\u6570\u68c0\u67e5<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-31\" href=\"http:\/\/dba.qishuo.xin\/?p=1427\/#44_%E7%BA%BF%E7%A8%8B%E7%BC%93%E5%AD%98\" >4.4. \u7ebf\u7a0b\u7f13\u5b58<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-32\" href=\"http:\/\/dba.qishuo.xin\/?p=1427\/#45_%E8%A1%A8%E7%BC%93%E5%AD%98\" >4.5. \u8868\u7f13\u5b58<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-33\" href=\"http:\/\/dba.qishuo.xin\/?p=1427\/#46_%E4%B8%B4%E6%97%B6%E8%A1%A8\" >4.6. \u4e34\u65f6\u8868<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-34\" href=\"http:\/\/dba.qishuo.xin\/?p=1427\/#47_%E9%A2%9D%E5%A4%96%E7%9A%84%E6%8E%92%E5%BA%8F\" >4.7. \u989d\u5916\u7684\u6392\u5e8f<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-35\" href=\"http:\/\/dba.qishuo.xin\/?p=1427\/#48_binlog_%E7%BC%93%E5%86%B2\" >4.8. binlog \u7f13\u51b2<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-36\" href=\"http:\/\/dba.qishuo.xin\/?p=1427\/#49_redo_%E6%97%A5%E5%BF%97\" >4.9. redo \u65e5\u5fd7<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-37\" href=\"http:\/\/dba.qishuo.xin\/?p=1427\/#410_InnoDB_%E7%BC%93%E5%AD%98\" >4.10. InnoDB \u7f13\u5b58<\/a><\/li><\/ul><\/li><\/ul><\/nav><\/div>\n<h3><span class=\"ez-toc-section\" id=\"1_%E6%95%B0%E6%8D%AE%E5%BA%93%E6%80%BB%E4%BD%93%E8%BF%90%E8%A1%8C%E6%83%85%E5%86%B5\"><\/span>1. \u6570\u636e\u5e93\u603b\u4f53\u8fd0\u884c\u60c5\u51b5<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<h4><span class=\"ez-toc-section\" id=\"11_%E6%95%B0%E6%8D%AE%E5%BA%93%E5%AD%97%E7%AC%A6%E9%9B%86%E4%B8%8ECollation\"><\/span>1.1. \u6570\u636e\u5e93\u5b57\u7b26\u96c6\u4e0eCollation<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<pre><code class=\"language-sql\">SELECT schema_name,\n       default_character_set_name AS &#039;charset&#039;,\n       default_collation_name AS &#039;collation&#039;\n  FROM information_schema.schemata\n WHERE schema_name = SCHEMA();\n\/* WHERE SCHEMA_NAME = &#039;database_name&#039;; *\/<\/code><\/pre>\n<p><a href=\"http:\/\/dba.qishuo.xin\/wp-content\/uploads\/2022\/04\/wp_editor_md_277605e5fdb15e1c0cc7a899a820896c.jpg\" data-fancybox=\"images\" data-fancybox=\"images\"><img decoding=\"async\" src=\"http:\/\/dba.qishuo.xin\/wp-content\/uploads\/2022\/04\/wp_editor_md_277605e5fdb15e1c0cc7a899a820896c.jpg\" alt=\"\" \/><\/a><\/p>\n<ul>\n<li>\n<p>\u4fee\u6539\u6570\u636e\u5e93\u5b57\u7b26\u96c6<\/p>\n<pre><code class=\"language-sql\">ALTER DATABASE \u6570\u636e\u5e93\u540d(\u5b9e\u4f8b\u540d) DEFAULT CHARACTER SET \u5b57\u7b26\u96c6 COLLATE \u6392\u5e8f\u89c4\u5219;<\/code><\/pre>\n<\/li>\n<li>\n<p>\u793a\u4f8b<\/p>\n<pre><code class=\"language-sql\">ALTER DATABASE easytongdb DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;<\/code><\/pre>\n<\/li>\n<\/ul>\n<h4><span class=\"ez-toc-section\" id=\"12_%E6%95%B0%E6%8D%AE%E5%BA%93%E4%B8%AD%E6%9F%90%E5%BC%A0%E8%A1%A8%E6%89%80%E7%94%A8%E7%9A%84%E5%AD%97%E7%AC%A6%E9%9B%86\"><\/span>1.2. \u6570\u636e\u5e93\u4e2d\u67d0\u5f20\u8868\u6240\u7528\u7684\u5b57\u7b26\u96c6<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<pre><code class=\"language-sql\">SHOW CREATE TABLE \u8868\u540d(\u6216 \u5b9e\u4f8b\u540d.\u8868\u540d);<\/code><\/pre>\n<ul>\n<li>\u4fee\u6539\u5f53\u524d\u6570\u636e\u5e93\u4e2d\u6240\u6709\u8868\u7684\u5b57\u7b26\u96c6\u548c\u6392\u5e8f\u89c4\u5219(\u4fee\u6539\u6240\u6709\u8868\u7684\u5b57\u7b26\u96c6\u4e3autf8mb4)\n<pre><code class=\"language-sql\">SELECT CONCAT(&#039;ALTER TABLE &#039;,\n   t.table_schema, &#039;.&#039;, t.table_name,\n   &#039; CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;&#039;)\nFROM information_schema.tables t\nWHERE t.table_schema = SCHEMA();<\/code><\/pre>\n<\/li>\n<\/ul>\n<h4><span class=\"ez-toc-section\" id=\"13_%E6%9F%A5%E7%9C%8B%E8%A1%A8%E4%B8%AD%E6%89%80%E6%9C%89%E5%88%97%E7%9A%84%E5%AD%97%E7%AC%A6%E9%9B%86\"><\/span>1.3. \u67e5\u770b\u8868\u4e2d\u6240\u6709\u5217\u7684\u5b57\u7b26\u96c6<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<pre><code class=\"language-sql\">SHOW FULL COLUMNS FROM \u8868\u540d(\u6216 \u5b9e\u4f8b\u540d.\u8868\u540d);<\/code><\/pre>\n<ul>\n<li>\n<p>\u67e5\u8be2\u5f53\u524d\u6570\u636e\u5e93\u4e2d\u6240\u6709\u5217\u7684\u5b57\u7b26\u96c6\u548c\u6392\u5e8f\u89c4\u5219<\/p>\n<pre><code class=\"language-sql\">SELECT c.table_name AS &#039;\u8868\u540d\u79f0&#039;,\n   c.column_name AS &#039;\u5b57\u6bb5\u540d\u79f0&#039;,\n   c.character_set_name AS &#039;\u5b57\u7b26\u96c6&#039;,\n   c.collation_name AS &#039;\u6392\u5e8f\u89c4\u5219&#039;\nFROM information_schema.`columns` c\nWHERE c.table_schema = SCHEMA();<\/code><\/pre>\n<\/li>\n<li>\n<p>\u4fee\u6539\u5f53\u524d\u6570\u636e\u5e93\u4e2d\u6240\u6709\u5217\u7684\u5b57\u7b26\u96c6\u548c\u6392\u5e8f\u89c4\u5219(\u5c06gbk\u7684\u5b57\u7b26\u96c6\u8f6c\u6362\u6210utf8mb4\u5b57\u7b26\u96c6)<\/p>\n<pre><code class=\"language-sql\">SELECT c.table_schema       AS &#039;\u6570\u636e\u5e93&#039;,\n   c.table_name         AS &#039;\u8868\u540d&#039;,\n   c.column_name        AS &#039;\u5b57\u6bb5\u540d&#039;,\n   c.character_set_name AS &#039;\u5b57\u7b26\u96c6&#039;,\n   c.collation_name     AS &#039;\u6392\u5e8f\u89c4\u5219&#039;,\n   CONCAT(&#039;ALTER TABLE &#039;,\n          c.table_schema, &#039;.&#039;, c.table_name,\n          &#039; MODIFY COLUMN &#039;, c.column_name, &#039; &#039;,\n          c.column_type,\n          &#039; CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci &#039;,\n           CASE WHEN c.column_default IS NOT NULL THEN CONCAT(&#039; DEFAULT &#039;,  c.column_default) ELSE &#039; &#039; END,\n           CASE WHEN c.is_nullable = &#039;NO&#039; THEN &#039; NOT NULL &#039; ELSE &#039; &#039; END ,\n           &#039;;&#039;\n          ) AS&#039;\u4fee\u6b63SQL&#039;\nFROM information_schema.columns c\nWHERE c.table_schema = SCHEMA()\nAND c.character_set_name = &#039;gbk&#039;;<\/code><\/pre>\n<\/li>\n<\/ul>\n<h4><span class=\"ez-toc-section\" id=\"14_%E6%9F%A5%E7%9C%8B%E6%95%B0%E6%8D%AE%E5%BA%93%E4%BD%BF%E7%94%A8%E7%AB%AF%E5%8F%A3\"><\/span>1.4. \u67e5\u770b\u6570\u636e\u5e93\u4f7f\u7528\u7aef\u53e3<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<pre><code class=\"language-sql\">show variables  like &#039;port&#039;;<\/code><\/pre>\n<h4><span class=\"ez-toc-section\" id=\"15_%E6%9F%A5%E7%9C%8B%E5%BD%93%E5%89%8D%E6%95%B0%E6%8D%AE%E5%BA%93%E6%95%B0%E6%8D%AE%E5%A4%A7%E5%B0%8F%E3%80%81%E7%B4%A2%E5%BC%95%E5%A4%A7%E5%B0%8F%E4%BB%A5%E5%8F%8A%E6%80%BB%E5%A4%A7%E5%B0%8F\"><\/span>1.5. \u67e5\u770b\u5f53\u524d\u6570\u636e\u5e93\u6570\u636e\u5927\u5c0f\u3001\u7d22\u5f15\u5927\u5c0f\u4ee5\u53ca\u603b\u5927\u5c0f<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<pre><code>SELECT concat(round(sum(data_length)\/(1024*1024),2),&#039; MB&#039;) AS &#039;DbData Size&#039;,\n       concat(round(sum(index_length)\/(1024*1024),2),&#039; MB&#039;) AS &#039;DbIndex Size&#039;,\n       concat(round(sum(a.data_length)\/(1024*1024),2) +\n       round(sum(a.index_length)\/(1024*1024),2),&#039; MB&#039;) AS &#039;DbTotal Size&#039;\n  FROM information_schema.tables a\n WHERE a.table_schema=SCHEMA();<\/code><\/pre>\n<h4><span class=\"ez-toc-section\" id=\"16_%E6%9F%A5%E7%9C%8B%E6%95%B0%E6%8D%AE%E5%BA%93%E7%9A%84%E6%89%80%E6%9C%89%E7%94%A8%E6%88%B7%E4%BF%A1%E6%81%AF\"><\/span>1.6. \u67e5\u770b\u6570\u636e\u5e93\u7684\u6240\u6709\u7528\u6237\u4fe1\u606f<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<pre><code class=\"language-sql\">SELECT DISTINCT concat(&#039;&#039;&#039;&#039;,user,&#039;&#039;&#039;@&#039;&#039;&#039;,host,&#039;&#039;&#039;;&#039;) AS users\n  FROM mysql.user;<\/code><\/pre>\n<h4><span class=\"ez-toc-section\" id=\"17_%E6%9F%A5%E7%9C%8B%E6%9F%90%E4%B8%AA%E5%85%B7%E4%BD%93%E7%94%A8%E6%88%B7%E7%9A%84%E6%9D%83%E9%99%90\"><\/span>1.7. \u67e5\u770b\u67d0\u4e2a\u5177\u4f53\u7528\u6237\u7684\u6743\u9650<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<pre><code class=\"language-sql\">show grants for &#039;root&#039;@&#039;localhost&#039;;<\/code><\/pre>\n<h4><span class=\"ez-toc-section\" id=\"18_%E8%8E%B7%E5%8F%96%E5%BD%93%E5%89%8D%E4%BC%9A%E8%AF%9D%E8%BF%9B%E7%A8%8B%E7%94%A8%E6%88%B7%E5%90%8D\"><\/span>1.8. \u83b7\u53d6\u5f53\u524d\u4f1a\u8bdd\/\u8fdb\u7a0b\u7528\u6237\u540d<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<pre><code class=\"language-sql\">SELECT  CURRENT_USER();\n\u6216\uff1a\nSELECT SESSION_USER();<\/code><\/pre>\n<h4><span class=\"ez-toc-section\" id=\"19_%E8%8E%B7%E5%8F%96%E5%BD%93%E5%89%8D%E4%BC%9A%E8%AF%9D%E8%BF%9B%E7%A8%8BID\"><\/span>1.9. \u83b7\u53d6\u5f53\u524d\u4f1a\u8bdd\/\u8fdb\u7a0bID<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<pre><code class=\"language-sql\">SELECT connection_id();<\/code><\/pre>\n<p><a href=\"http:\/\/dba.qishuo.xin\/wp-content\/uploads\/2022\/05\/image-1655384308766.png\" data-fancybox=\"images\" data-fancybox=\"images\"><img decoding=\"async\" src=\"http:\/\/dba.qishuo.xin\/wp-content\/uploads\/2022\/05\/image-1655384308766.png\" alt=\"file\" \/><\/a><\/p>\n<h4><span class=\"ez-toc-section\" id=\"110_%E6%9F%A5%E7%9C%8B%E6%95%B0%E6%8D%AE%E5%BA%93%E8%BF%9E%E6%8E%A5%E7%9A%84%E7%94%A8%E6%88%B7%E6%83%85%E5%86%B5\"><\/span>1.10. \u67e5\u770b\u6570\u636e\u5e93\u8fde\u63a5\u7684\u7528\u6237\u60c5\u51b5<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<pre><code class=\"language-sql\"><\/code><\/pre>\n<h4><span class=\"ez-toc-section\" id=\"111_%E6%9F%A5%E8%AF%A2%E7%94%A8%E6%88%B7%E6%AD%A3%E5%9C%A8%E6%89%A7%E8%A1%8C%E7%9A%84SQL%E8%AF%AD%E5%8F%A5\"><\/span>1.11. \u67e5\u8be2\u7528\u6237\u6b63\u5728\u6267\u884c\u7684SQL\u8bed\u53e5<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<pre><code class=\"language-sql\">SELECT *\n  FROM information_schema.PROCESSLIST\n WHERE info IS NOT NULL;<\/code><\/pre>\n<p><a href=\"http:\/\/dba.qishuo.xin\/wp-content\/uploads\/2022\/07\/image-1698909178156.png\" data-fancybox=\"images\" data-fancybox=\"images\"><img decoding=\"async\" src=\"http:\/\/dba.qishuo.xin\/wp-content\/uploads\/2022\/07\/image-1698909178156.png\" alt=\"file\" \/><\/a><\/p>\n<h4><span class=\"ez-toc-section\" id=\"112_kill%E6%AD%A3%E5%9C%A8%E6%89%A7%E8%A1%8CSQL%E7%9A%84%E8%BF%9B%E7%A8%8B\"><\/span>1.12. kill\u6b63\u5728\u6267\u884cSQL\u7684\u8fdb\u7a0b<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<pre><code class=\"language-sql\">KILL [CONNECTION | QUERY] thread_id;<\/code><\/pre>\n<p><a href=\"http:\/\/dba.qishuo.xin\/wp-content\/uploads\/2022\/07\/image-1698909297450.png\" data-fancybox=\"images\" data-fancybox=\"images\"><img decoding=\"async\" src=\"http:\/\/dba.qishuo.xin\/wp-content\/uploads\/2022\/07\/image-1698909297450.png\" alt=\"file\" \/><\/a><\/p>\n<h3><span class=\"ez-toc-section\" id=\"2_%E6%95%B0%E6%8D%AE%E5%BA%93%E7%94%A8%E6%88%B7%E3%80%81%E6%9D%83%E9%99%90%E5%8F%8A%E9%85%8D%E7%BD%AE\"><\/span>2. \u6570\u636e\u5e93\u7528\u6237\u3001\u6743\u9650\u53ca\u914d\u7f6e<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<h4><span class=\"ez-toc-section\" id=\"21_%E6%9F%A5%E8%AF%A2%E6%89%80%E6%9C%89%E7%94%A8%E6%88%B7\"><\/span>2.1. \u67e5\u8be2\u6240\u6709\u7528\u6237<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<pre><code class=\"language-sql\">SELECT user,host, CONCAT(&#039;User: &#039;&#039;&#039;,user,&#039;&#039;&#039;@&#039;&#039;&#039;,host,&#039;&#039;&#039;;&#039;) AS user_detail\n  FROM mysql.user;<\/code><\/pre>\n<h4><span class=\"ez-toc-section\" id=\"22_%E6%9F%A5%E7%9C%8B%E6%95%B0%E6%8D%AE%E5%BA%93%E4%B8%AD%E5%85%B7%E4%BD%93%E6%9F%90%E4%B8%AA%E7%94%A8%E6%88%B7%E7%9A%84%E6%9D%83%E9%99%90\"><\/span>2.2. \u67e5\u770b\u6570\u636e\u5e93\u4e2d\u5177\u4f53\u67d0\u4e2a\u7528\u6237\u7684\u6743\u9650<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<pre><code class=\"language-sql\">SHOW GRANTS FOR &#039;username&#039;@&#039;host&#039;;<\/code><\/pre>\n<p>\u6388\u4e88\u67d0\u4e2a\u5b57\u6bb5\u7684\u6743\u9650\uff0c\u6743\u9650\u4fe1\u606f\u4fdd\u5b58\u5728mysql.columns_priv\u8868\u4e2d<\/p>\n<pre><code class=\"language-sql\">SELECT * FROM mysql.columns_priv;<\/code><\/pre>\n<h4><span class=\"ez-toc-section\" id=\"23_%E8%B5%8B%E4%BA%88%E5%92%8C%E5%9B%9E%E6%94%B6%E5%88%A0%E9%99%A4%E6%95%B0%E6%8D%AE%E5%BA%93%E7%9A%84%E6%9D%83%E9%99%90\"><\/span>2.3. \u8d4b\u4e88\u548c\u56de\u6536\u5220\u9664\u6570\u636e\u5e93\u7684\u6743\u9650<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<pre><code class=\"language-sql\">-- \u8d4b\u4e88\u5220\u9664\u6570\u636e\u5e93\u6743\u9650\nGRANT DROP ON databasename.* TO &#039;username&#039;@&#039;host&#039;;\n-- \u56de\u6536\u5220\u9664\u6570\u636e\u5e93\u6743\u9650\nREVOKE DROP ON databasename.* FROM &#039;username&#039;@&#039;host&#039;;<\/code><\/pre>\n<p><a href=\"http:\/\/dba.qishuo.xin\/wp-content\/uploads\/2022\/07\/image-1710677285037.png\" data-fancybox=\"images\" data-fancybox=\"images\"><img decoding=\"async\" src=\"http:\/\/dba.qishuo.xin\/wp-content\/uploads\/2022\/07\/image-1710677285037.png\" alt=\"file\" \/><\/a><\/p>\n<h4><span class=\"ez-toc-section\" id=\"24_%E8%B5%8B%E4%BA%88%E7%94%A8%E6%88%B7%E6%9F%A5%E8%AF%A2information_schema%E6%95%B0%E6%8D%AE%E5%BA%93%E6%9D%83%E9%99%90\"><\/span>2.4. \u8d4b\u4e88\u7528\u6237\u67e5\u8be2information_schema\u6570\u636e\u5e93\u6743\u9650<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<p>\u4efb\u4f55\u7528\u6237\u90fd\u6709<code>information_schema<\/code>\u4e0b\u7684\u8868\u6216\u8005\u89c6\u56fe\u7684\u8bbf\u95ee\u6743\u9650, \u4f46\u662f\u6709\u4e9b\u89c6\u56fe\u6216\u8005\u8868\u9700\u8981\u4e00\u4e9b\u7279\u6b8a\u7684\u6743\u9650\uff0c\u6bd4\u5982\u8bf4<code>innodb_locks<\/code>\u8868\u5c31\u9700\u8981\u7528\u6237\u5177\u6709 <code>process<\/code> \u6743\u9650\u624d\u80fd\u8bbf\u95ee\u3002<\/p>\n<p><a href=\"http:\/\/dba.qishuo.xin\/wp-content\/uploads\/2022\/07\/image-1710677566275.png\" data-fancybox=\"images\" data-fancybox=\"images\"><img decoding=\"async\" src=\"http:\/\/dba.qishuo.xin\/wp-content\/uploads\/2022\/07\/image-1710677566275.png\" alt=\"file\" \/><\/a><\/p>\n<pre><code class=\"language-sql\">GRANT PROCESS ON *.* TO &#039;username&#039;@&#039;host&#039;;<\/code><\/pre>\n<p><a href=\"http:\/\/dba.qishuo.xin\/wp-content\/uploads\/2022\/07\/image-1710677702262.png\" data-fancybox=\"images\" data-fancybox=\"images\"><img decoding=\"async\" src=\"http:\/\/dba.qishuo.xin\/wp-content\/uploads\/2022\/07\/image-1710677702262.png\" alt=\"file\" \/><\/a><\/p>\n<h3><span class=\"ez-toc-section\" id=\"3_%E6%97%A5%E5%BF%97\"><\/span>3. \u65e5\u5fd7<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<h4><span class=\"ez-toc-section\" id=\"31_%E6%9F%A5%E7%9C%8B%E6%97%A5%E5%BF%97%E5%BC%80%E5%90%AF%E7%8A%B6%E6%80%81\"><\/span>3.1. \u67e5\u770b\u65e5\u5fd7\u5f00\u542f\u72b6\u6001<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<pre><code class=\"language-sql\">show variables like &#039;log_%&#039;;<\/code><\/pre>\n<p><a href=\"http:\/\/dba.qishuo.xin\/wp-content\/uploads\/2022\/05\/image-1655382036146.png\" data-fancybox=\"images\" data-fancybox=\"images\"><img decoding=\"async\" src=\"http:\/\/dba.qishuo.xin\/wp-content\/uploads\/2022\/05\/image-1655382036146.png\" alt=\"file\" \/><\/a><\/p>\n<h4><span class=\"ez-toc-section\" id=\"32_%E6%9F%A5%E7%9C%8B%E6%89%80%E6%9C%89binlog%E6%97%A5%E5%BF%97%E5%88%97%E8%A1%A8\"><\/span>3.2. \u67e5\u770b\u6240\u6709binlog\u65e5\u5fd7\u5217\u8868<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<pre><code class=\"language-sql\">show master logs;<\/code><\/pre>\n<ul>\n<li>\u793a\u4f8b<\/li>\n<\/ul>\n<pre><code>MariaDB [(none)]&gt; show master logs;\n+------------------+-----------+\n| Log_name         | File_size |\n+------------------+-----------+\n| mysql-bin.000480 |       342 |\n+------------------+-----------+<\/code><\/pre>\n<h4><span class=\"ez-toc-section\" id=\"33_%E6%9F%A5%E7%9C%8B%E6%9C%80%E6%96%B0%E4%B8%80%E4%B8%AAbinlog%E6%97%A5%E5%BF%97%E7%9A%84%E7%BC%96%E5%8F%B7%E5%90%8D%E7%A7%B0%EF%BC%8C%E5%8F%8A%E5%85%B6%E6%9C%80%E5%90%8E%E4%B8%80%E4%B8%AA%E6%93%8D%E4%BD%9C%E4%BA%8B%E4%BB%B6%E7%BB%93%E6%9D%9F%E7%82%B9\"><\/span>3.3. \u67e5\u770b\u6700\u65b0\u4e00\u4e2abinlog\u65e5\u5fd7\u7684\u7f16\u53f7\u540d\u79f0\uff0c\u53ca\u5176\u6700\u540e\u4e00\u4e2a\u64cd\u4f5c\u4e8b\u4ef6\u7ed3\u675f\u70b9<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<pre><code class=\"language-sql\">show master status;<\/code><\/pre>\n<ul>\n<li>\u793a\u4f8b<\/li>\n<\/ul>\n<pre><code>MariaDB [(none)]&gt;  show master status;\n+------------------+----------+---------------+------------------+\n| File             | Position | Binlog_Do_DB  | Binlog_Ignore_DB |\n+------------------+----------+---------------+------------------+\n| mysql-bin.000480 |      342 | paymentdb_nsd |                  |\n+------------------+----------+---------------+------------------+<\/code><\/pre>\n<h4><span class=\"ez-toc-section\" id=\"34_%E5%88%B7%E6%96%B0log%E6%97%A5%E5%BF%97%EF%BC%8C%E7%AB%8B%E5%88%BB%E4%BA%A7%E7%94%9F%E4%B8%80%E4%B8%AA%E6%96%B0%E7%BC%96%E5%8F%B7%E7%9A%84binlog%E6%97%A5%E5%BF%97%E6%96%87%E4%BB%B6%EF%BC%8C%E8%B7%9F%E9%87%8D%E5%90%AF%E4%B8%80%E4%B8%AA%E6%95%88%E6%9E%9C\"><\/span>3.4. \u5237\u65b0log\u65e5\u5fd7\uff0c\u7acb\u523b\u4ea7\u751f\u4e00\u4e2a\u65b0\u7f16\u53f7\u7684binlog\u65e5\u5fd7\u6587\u4ef6\uff0c\u8ddf\u91cd\u542f\u4e00\u4e2a\u6548\u679c<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<pre><code class=\"language-sql\">flush logs;<\/code><\/pre>\n<ul>\n<li>\u793a\u4f8b<\/li>\n<\/ul>\n<pre><code>MariaDB [(none)]&gt; flush logs;\n\nMariaDB [(none)]&gt;  show master status;\n+------------------+----------+---------------+------------------+\n| File             | Position | Binlog_Do_DB  | Binlog_Ignore_DB |\n+------------------+----------+---------------+------------------+\n| mysql-bin.000481 |      385 | paymentdb_nsd |                  |\n+------------------+----------+---------------+------------------+\n\nMariaDB [(none)]&gt; show master logs;\n+------------------+-----------+\n| Log_name         | File_size |\n+------------------+-----------+\n| mysql-bin.000480 |       389 |\n| mysql-bin.000481 |       385 |\n+------------------+-----------+<\/code><\/pre>\n<h4><span class=\"ez-toc-section\" id=\"35_%E4%BD%BF%E7%94%A8%E5%91%BD%E4%BB%A4mysqlbinlog%E5%8E%BB%E8%A7%A3%E6%9E%90mysql%E6%97%A5%E5%BF%97%E6%96%87%E4%BB%B6\"><\/span>3.5. \u4f7f\u7528\u547d\u4ee4mysqlbinlog\u53bb\u89e3\u6790mysql\u65e5\u5fd7\u6587\u4ef6<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<pre><code class=\"language-shell\">mysqlbinlog --no-defaults --database=easytongwlwdb -vv --base64-output=decode-rows --start-datetime=&#039;2022-03-15 18:10:00&#039;  --stop-datetime=&#039;2022-03-15 18:40:00&#039; .\/mysql-bin.000572 &gt; \/tmp\/parse-mysql-bin.000572.txt<\/code><\/pre>\n<h4><span class=\"ez-toc-section\" id=\"36_%E6%85%A2%E6%9F%A5%E8%AF%A2%E6%97%A5%E5%BF%97%E5%8F%82%E6%95%B0\"><\/span>3.6. \u6162\u67e5\u8be2\u65e5\u5fd7\u53c2\u6570<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<pre><code class=\"language-sql\">-- \u6162\u67e5\u8be2\u65e5\u5fd7\u662f\u5426\u5f00\u542f\u4ee5\u53ca\u65e5\u5fd7\u8bb0\u5f55\u7684\u4f4d\u7f6e\nshow variables like &#039;%slow_query_log%&#039;;\n\n-- \u6162\u67e5\u8be2sql\u7684\u8bbe\u7f6e\u65f6\u95f4\uff0c\u9ed8\u8ba410s\uff0csql\u6267\u884c\u65f6\u95f4\u5927\u4e8e\u8be5\u65f6\u95f4\u65f6\u624d\u4f1a\u88ab\u8bb0\u5f55\u5230\u6162\u67e5\u8be2\u65e5\u5fd7\u4e2d\nshow variables like &#039;long_query_time&#039;;\n\n-- \u6162\u67e5\u8be2\u65e5\u5fd7\u5b58\u50a8\u65b9\u5f0f\nshow variables like &#039;%log_output%&#039;;\n\n-- \u672a\u4f7f\u7528\u7d22\u5f15\u7684\u67e5\u8be2\u662f\u5426\u8bb0\u5f55\u5230\u6162\u67e5\u8be2\u65e5\u5fd7\nshow variables like &#039;log_queries_not_using_indexes&#039;;<\/code><\/pre>\n<ul>\n<li>\u793a\u4f8b<\/li>\n<\/ul>\n<pre><code>MariaDB [(none)]&gt; show variables like &#039;%slow_query_log%&#039;;\n+---------------------+-----------------------------------+\n| Variable_name       | Value                             |\n+---------------------+-----------------------------------+\n| slow_query_log      | ON                                |\n| slow_query_log_file | \/data\/etmaridb\/log\/mysql-slow.log |\n+---------------------+-----------------------------------+\n\nMariaDB [(none)]&gt; show variables like &#039;long_query_time&#039;;\n+-----------------+----------+\n| Variable_name   | Value    |\n+-----------------+----------+\n| long_query_time | 0.500000 |\n+-----------------+----------+\n\nMariaDB [(none)]&gt; show variables like &#039;%log_output%&#039;;\n+---------------+-------+\n| Variable_name | Value |\n+---------------+-------+\n| log_output    | FILE  |\n+---------------+-------+\n\nMariaDB [(none)]&gt; show variables like &#039;log_queries_not_using_indexes&#039;;\n+-------------------------------+-------+\n| Variable_name                 | Value |\n+-------------------------------+-------+\n| log_queries_not_using_indexes | OFF   |\n+-------------------------------+-------+<\/code><\/pre>\n<h4><span class=\"ez-toc-section\" id=\"37_%E9%94%99%E8%AF%AF%E6%97%A5%E5%BF%97%E6%96%87%E4%BB%B6%E8%B7%AF%E5%BE%84\"><\/span>3.7. \u9519\u8bef\u65e5\u5fd7\u6587\u4ef6\u8def\u5f84<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<pre><code class=\"language-sql\">show variables like &#039;log_error&#039;;<\/code><\/pre>\n<ul>\n<li>\u793a\u4f8b<\/li>\n<\/ul>\n<pre><code>MariaDB [(none)]&gt; show variables like &#039;log_error&#039;;\n+---------------+------------------------------+\n| Variable_name | Value                        |\n+---------------+------------------------------+\n| log_error     | \/data\/etmaridb\/log\/error.log |\n+---------------+------------------------------+<\/code><\/pre>\n<h3><span class=\"ez-toc-section\" id=\"4_%E6%80%A7%E8%83%BD%E5%8F%82%E6%95%B0\"><\/span>4. \u6027\u80fd\u53c2\u6570<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>\u53c2\u8003\uff1a<a target=\"_blank\" href=\"https:\/\/www.cnblogs.com\/zengkefu\/p\/5608096.html\">https:\/\/www.cnblogs.com\/zengkefu\/p\/5608096.html<\/a><\/p>\n<h4><span class=\"ez-toc-section\" id=\"41_%E8%AF%BB%E5%86%99%E6%AF%94%E4%BE%8B\"><\/span>4.1. \u8bfb\u5199\u6bd4\u4f8b<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<pre><code class=\"language-sql\">show global status like &#039;com_select&#039;;  \u83b7\u5f97\u670d\u52a1\u5668\u542f\u52a8\u5230\u76ee\u524d\u67e5\u8be2\u64cd\u4f5c\u6267\u884c\u7684\u6b21\u6570\uff1b\nshow global status like &#039;com_insert&#039;;  \u83b7\u5f97\u670d\u52a1\u5668\u542f\u52a8\u5230\u76ee\u524d\u63d2\u5165\u64cd\u4f5c\u6267\u884c\u7684\u6b21\u6570\uff1b\nshow global status like &#039;com_update&#039;;  \u83b7\u5f97\u670d\u52a1\u5668\u542f\u52a8\u5230\u76ee\u524d\u66f4\u65b0\u64cd\u4f5c\u6267\u884c\u7684\u6b21\u6570\uff1b\nshow global status like &#039;com_delete&#039;;  \u83b7\u5f97\u670d\u52a1\u5668\u542f\u52a8\u5230\u76ee\u524d\u5220\u9664\u64cd\u4f5c\u6267\u884c\u7684\u6b21\u6570\uff1b<\/code><\/pre>\n<ul>\n<li>\u8ba1\u7b97\u8bfb\u767e\u5206\u6bd4<\/li>\n<\/ul>\n<pre><code class=\"language-sql\">select concat(com_select \/ (com_select+com_insert+com_update+com_delete)* 100,&#039;%&#039;);<\/code><\/pre>\n<ul>\n<li>\u8ba1\u7b97\u5199\u767e\u5206\u6bd4<\/li>\n<\/ul>\n<pre><code class=\"language-sql\">select concat(com_insert+com_update+com_delete \/ (com_select+com_insert+com_update+com_delete) * 100,&#039;%&#039;);<\/code><\/pre>\n<p>\u901a\u8fc7\u68c0\u67e5\u6570\u636e\u5e93\u8bfb\u5199\u6bd4\u4f8b\uff0c\u53ef\u53cd\u6620\u51fa\u5e94\u7528\u662f\u8bfb\u5bc6\u96c6\u578b\u8fd8\u662f\u5199\u5bc6\u96c6\u578b\u3002<\/p>\n<h4><span class=\"ez-toc-section\" id=\"42_%E6%85%A2%E6%9F%A5%E8%AF%A2%E6%AF%94%E4%BE%8B\"><\/span>4.2. \u6162\u67e5\u8be2\u6bd4\u4f8b<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<p>\u5f00\u542f\u6162\u67e5\u8be2\u65e5\u5fd7\uff1a<\/p>\n<pre><code>slow_query_log = 1 # \u5f00\u542f\u6162\u67e5\u8be2\u65e5\u5fd7\nlog_output = FILE|TABLE # \u6307\u5b9a\u65e5\u5fd7\u5b58\u50a8\u65b9\u5f0f\uff0c\u9ed8\u8ba4\u4e3a file\nslow_query_log_file = slow-query.log # \u6307\u5b9a\u6162\u67e5\u8be2\u65e5\u5fd7\u6587\u4ef6\u4f4d\u7f6e\nlong_query_time = 1 # \u6267\u884c\u53ca\u54cd\u5e94\u65f6\u95f4\u8d85\u8fc7\u8be5\u53c2\u6570\u8bbe\u7f6e\u7684\u503c\u8bb0\u5f55\u65e5\u5fd7\nshow global status like &#039;Slow_queries &#039;; # \u83b7\u5f97\u670d\u52a1\u5668\u542f\u52a8\u5230\u76ee\u524d\u6162\u67e5\u8be2\u64cd\u4f5c\u8bb0\u5f55\u7684\u6b21\u6570\uff1b<\/code><\/pre>\n<p>\u6ce8\u610f\uff0c\u6162\u67e5\u8be2\u5305\u62ec select \u3001 update \u4ee5\u53ca delete \uff0c\u6ca1\u6709 insert\u3002<\/p>\n<p>\u8ba1\u7b97\u6162\u67e5\u8be2\u6bd4\u4f8b\uff1a<\/p>\n<pre><code class=\"language-sql\">select concat(Slow_queries \/ (Com_select+Com_update+Com_delete) * 100,&#039;%&#039;);<\/code><\/pre>\n<p>\u901a\u8fc7\u8ba1\u7b97\u6162\u67e5\u8be2\u6bd4\u4f8b\uff0c\u53ef\u53cd\u6620\u51fa\u6570\u636e\u5e93\u8fd0\u884c\u6548\u80fd\u3002<\/p>\n<h4><span class=\"ez-toc-section\" id=\"43_%E8%BF%9E%E6%8E%A5%E6%95%B0%E6%A3%80%E6%9F%A5\"><\/span>4.3. \u8fde\u63a5\u6570\u68c0\u67e5<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<pre><code class=\"language-sql\">show global status like &#039;max_connections&#039;; # \u83b7\u5f97\u6570\u636e\u5e93\u8fd0\u884c\u7684\u6700\u5927\u8fde\u63a5\u6570  \/\/\u5141\u8bb8\u7684\u6700\u5927\u8fde\u63a5\u6570\nshow global status like &#039;Max_used_connections&#039;; # \u83b7\u5f97\u6700\u5927\u4e00\u6b21\u7684\u8fde\u63a5\u6570 \/\/\u6700\u5927\u7a81\u53d1\u5e76\u884c\u8fde\u63a5\u6570\nshow global status like &#039;connections&#039;; # \u83b7\u5f97\u6570\u636e\u5e93\u8fd0\u884c\u5230\u76ee\u524d\uff0c\u603b\u5171\u88ab\u8fde\u63a5\u4e86\u591a\u5c11\u6b21 \/\/\u767b\u9646\u7684\u6b21\u6570\nshow global status like &#039;Threads_connected&#039;; # \u83b7\u5f97\u5f53\u524d\u8fde\u63a5\u6570\nshow global status like &#039;Threads_running&#039;; # \u83b7\u5f97\u5f53\u524d\u6b63\u5728\u8fd0\u884c\u7684\u8fde\u63a5\u6570<\/code><\/pre>\n<p>\u8ba1\u7b97\u5f53\u524d\u8fde\u63a5\u6570\u7684\u6bd4\u4f8b\uff1a<\/p>\n<pre><code class=\"language-sql\">select concat(Threads_connected \/ max_connections *100,&#039;%&#039;); # \u8ba1\u7b97\u6700\u5927\u4e00\u6b21\u7684\u8fde\u63a5\u6570\u6bd4\u4f8b\uff1a\nselect concat(Max_used_connections \/ max_connections *100,&#039;%&#039;);# \u901a\u8fc7\u8fde\u63a5\u6570\u68c0\u67e5\uff0c\u53ef\u5f97\u77e5\u6570\u636e\u5e93\u5728\u4e0d\u540c\u65f6\u95f4\u6bb5\u88ab\u8bf7\u6c42\u7684\u538b\u529b<\/code><\/pre>\n<h4><span class=\"ez-toc-section\" id=\"44_%E7%BA%BF%E7%A8%8B%E7%BC%93%E5%AD%98\"><\/span>4.4. \u7ebf\u7a0b\u7f13\u5b58<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<pre><code class=\"language-sql\">show global status like &#039;Connections&#039;; # \u83b7\u5f97\u6570\u636e\u5e93\u8fd0\u884c\u5230\u76ee\u524d\uff0c\u603b\u5171\u88ab\u8fde\u63a5\u4e86\u591a\u5c11\u6b21\nshow global status like &#039;Threads_created&#039;; # \u83b7\u5f97\u6570\u636e\u5e93\u8fd0\u884c\u5230\u76ee\u524d\uff0c\u521b\u5efa\u8fde\u63a5\u7ebf\u7a0b\u7684\u6b21\u6570<\/code><\/pre>\n<p>\u8ba1\u7b97\u8fde\u63a5\u7ebf\u7a0b\u7f13\u5b58\u547d\u4e2d\u7387:<\/p>\n<pre><code class=\"language-sql\">select concat((Connections-Threads_created) \/ Connections \u00d7 100,&#039;%&#039;);<\/code><\/pre>\n<p>\u901a\u8fc7\u8ba1\u7b97\u8fde\u63a5\u7ebf\u7a0b\u7f13\u5b58\u547d\u4e2d\u7387\uff0c\u53ef\u53cd\u6620\u51fa\u8fde\u63a5\u7ebf\u7a0b\u7684\u547d\u4e2d\u60c5\u51b5\uff0c\u547d\u4e2d\u7387\u8d8a\u5927\u8d8a\u597d\u3002<br \/>\n\u5982\u679c\u547d\u4e2d\u7387\u8fc7\u4f4e\uff0c\u5219\u8868\u793a\u7f13\u5b58\u8fde\u63a5\u7ebf\u7a0b\u7684\u6570\u91cf\u8fc7\u5c11\uff0c\u53ef\u4ee5\u8003\u8651\u52a0\u5927 thread_cache_size \u7684\u503c\u3002<\/p>\n<h4><span class=\"ez-toc-section\" id=\"45_%E8%A1%A8%E7%BC%93%E5%AD%98\"><\/span>4.5. \u8868\u7f13\u5b58<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<pre><code class=\"language-sql\">show global status like &#039;Table_open_cache_misses&#039;; # \u65b0\u6253\u5f00\u7684\u8868\u7684\u6b21\u6570\u3002 \u4e0d\u547d\u4e2d\nshow global status like &#039;Table_open_cache_hits&#039;; # \u4ece\u8868\u7f13\u5b58\u4e2d\u62ff\u5df2\u6253\u5f00\u7684\u8868\u7684\u6b21\u6570\uff0c\u8be5\u72b6\u6001\u53d8\u91cf 5.6 \u624d\u5f00\u59cb\u5b58\u5728  \u547d\u4e2d\nshow global status like &#039;Opened_tables&#039;; # \u6253\u5f00\u8868\u7684\u603b\u6b21\u6570<\/code><\/pre>\n<p>\u901a\u8fc7\u5df2\u77e5\u81ea\u5df1\u6570\u636e\u5e93\u4e2d\u6709\u591a\u5c11\u8868\uff0c\u518d\u89c2\u5bdf Opened_tables \u7684\u503c\uff0c\u53ef\u4ee5\u5f97\u77e5\u8868\u7f13\u5b58\u7684\u6570\u91cf\u662f\u5426\u5408\u7406\uff0c\u5982\u679c\u6253\u5f00\u8868\u7684\u6b21\u6570\u5927\u4e8e\u6570\u636e\u5e93\u4e2d\u5df2\u6709\u7684\u8868\u6570\u91cf\uff0c\u5219\u8868\u793a table_open_cache \u7684\u503c\u4e0d\u591f\uff0c\u53ef\u4ee5\u8003\u8651\u52a0\u5927\u3002<\/p>\n<p>\u8ba1\u7b97\u8868\u7f13\u5b58\u7684\u547d\u4e2d\u7387\uff1a<\/p>\n<pre><code class=\"language-sql\">select concat(Table_open_cache_hits \/(Table_open_cache_misses + Table_open_cache_hits) * 100,&#039;%&#039;);<\/code><\/pre>\n<p>\u901a\u8fc7\u8ba1\u7b97\u8868\u7f13\u5b58\u7684\u547d\u4e2d\u7387\uff0c\u53ef\u53cd\u6620\u51fa\u8868\u7f13\u5b58\u7684\u60c5\u51b5\uff0c\u8be5\u6bd4\u4f8b\u8d8a\u5927\u8d8a\u597d\u3002<\/p>\n<h4><span class=\"ez-toc-section\" id=\"46_%E4%B8%B4%E6%97%B6%E8%A1%A8\"><\/span>4.6. \u4e34\u65f6\u8868<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<pre><code class=\"language-sql\">show global status like &#039;Created_tmp_disk_tables&#039;; # \u67e5\u770b\u5728\u78c1\u76d8\u4e0a\u521b\u5efa\u4e34\u65f6\u8868\u7684\u6b21\u6570\nshow global status like &#039;Created_tmp_tables&#039;; # \u67e5\u770b\u521b\u5efa\u4e34\u65f6\u8868\u7684\u603b\u6b21\u6570\uff0c\u5305\u62ec\u5728\u5185\u5b58\u4e2d\u548c\u78c1\u76d8\u3002<\/code><\/pre>\n<p>\u5982\u679c\u53d1\u73b0\u5728\u78c1\u76d8\u4e0a\u521b\u5efa\u4e34\u65f6\u8868\u7684\u6b21\u6570\u8fc7\u591a\uff0c\u5219\u8868\u793a\u4e34\u65f6\u8868\u7684\u7f13\u5b58\u533a\u5185\u5b58\u4e0d\u591f\uff0c\u53ef\u4ee5\u8003\u8651\u52a0\u5927 tmp_table_size \u548c max_heap_table_size \u7684\u503c\u3002<\/p>\n<p>\u8ba1\u7b97\u5728\u78c1\u76d8\u4e0a\u521b\u5efa\u4e34\u65f6\u8868\u7684\u6bd4\u4f8b\uff1a<\/p>\n<pre><code class=\"language-sql\">select concat(Created_tmp_disk_tables \/ Created_tmp_tables * 100,&#039;%&#039;);<\/code><\/pre>\n<p>\u901a\u8fc7\u8ba1\u7b97\u5728\u78c1\u76d8\u4e0a\u521b\u5efa\u4e34\u65f6\u8868\u7684\u6bd4\u4f8b\uff0c\u53ef\u53cd\u6620\u51fa\u6570\u636e\u5e93\u7684\u4f7f\u7528\u4e34\u65f6\u8868\u7684\u60c5\u51b5\uff0c\u8be5\u6bd4\u4f8b\u8d8a\u5c0f\u8d8a\u597d\u3002<\/p>\n<h4><span class=\"ez-toc-section\" id=\"47_%E9%A2%9D%E5%A4%96%E7%9A%84%E6%8E%92%E5%BA%8F\"><\/span>4.7. \u989d\u5916\u7684\u6392\u5e8f<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<pre><code class=\"language-sql\">show global status like &#039;Sort_merge_passes&#039;; # \u5728\u78c1\u76d8\u4e2d\u8fdb\u884c\u989d\u5916\u6392\u5e8f\u7684\u6b21\u6570\nshow global status like &#039;Sort_scan&#039;; # \u901a\u8fc7\u8868\u626b\u63cf\u8fdb\u884c\u6392\u5e8f\u7684\u603b\u6b21\u6570\uff0c\u4e5f\u5c31\u662f\u989d\u5916\u6392\u5e8f\u7684\u603b\u6b21\u6570<\/code><\/pre>\n<p>\u5982\u679c\u53d1\u73b0\u5728\u78c1\u76d8\u4e0a\u8fdb\u884c\u6392\u5e8f\u7684\u6b21\u6570\u8fc7\u591a\uff0c\u5219\u8868\u793a\u6392\u5e8f\u7f13\u51b2\u533a\u5185\u5b58\u4e0d\u591f\uff0c\u53ef\u4ee5\u8003\u8651\u52a0\u5927 sort_buffer_size \u7684\u503c\u3002<\/p>\n<p>\u8ba1\u7b97\u78c1\u76d8\u6392\u5e8f\u7684\u6bd4\u4f8b\uff1a<\/p>\n<pre><code class=\"language-sql\">select concat(Sort_merge_passes \/ Sort_scan * 100,&#039;%&#039;);<\/code><\/pre>\n<p>\u901a\u8fc7\u8ba1\u7b97\u5728\u78c1\u76d8\u4e0a\u8fdb\u884c\u989d\u5916\u6392\u5e8f\u7684\u6bd4\u4f8b\uff0c\u53ef\u53cd\u6620\u51fa\u6570\u636e\u5e93\u6392\u5e8f\u7684\u60c5\u51b5\uff0c\u8be5\u6bd4\u4f8b\u8d8a\u5c0f\u8d8a\u597d\u3002<\/p>\n<h4><span class=\"ez-toc-section\" id=\"48_binlog_%E7%BC%93%E5%86%B2\"><\/span>4.8. binlog \u7f13\u51b2<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<pre><code class=\"language-sql\">show global status like &#039;Binlog_cache_disk_use&#039;; # \u5728\u78c1\u76d8\u4e0a\u521b\u5efa\u4e34\u65f6\u6587\u4ef6\u7528\u4e8e\u4fdd\u5b58 binlog \u7684\u6b21\u6570\nshow global status like &#039;Binlog_cache_use&#039;; # \u7f13\u51b2 binlog \u7684\u603b\u6b21\u6570\uff0c\u5305\u62ec binlog \u7f13\u51b2\u533a\u548c\u5728\u78c1\u76d8\u4e0a\u521b\u5efa\u4e34\u65f6\u6587\u4ef6\u4fdd\u5b58 binlog \u7684\u603b\u6b21\u6570<\/code><\/pre>\n<p>\u5982\u679c\u53d1\u73b0\u5728\u78c1\u76d8\u4e0a\u521b\u5efa\u4e34\u65f6\u6587\u4ef6\u4fdd\u5b58 binlog \u7684\u6b21\u6570\u8fc7\u591a\uff0c\u5219\u8868\u793a binlog \u7f13\u51b2\u533a\u5185\u5b58\u4e0d\u591f\uff0c\u53ef\u4ee5\u8003\u8651\u52a0\u5927 binlog_cache_size \u7684\u503c\u3002<\/p>\n<p>\u8ba1\u7b97\u5728\u78c1\u76d8\u4e0a\u521b\u5efa\u4e34\u65f6\u6587\u4ef6\u4fdd\u5b58 binlog \u7684\u6bd4\u4f8b\uff1a<\/p>\n<pre><code class=\"language-sql\">select concat(Binlog_cache_disk_use \/ Binlog_cache_use * 100,&#039;%&#039;);<\/code><\/pre>\n<p>\u901a\u8fc7\u8ba1\u7b97\u5728\u78c1\u76d8\u4e0a\u521b\u5efa\u4e34\u65f6\u6587\u4ef6\u4fdd binlog \u7684\u6bd4\u4f8b\uff0c\u53ef\u53cd\u6620\u51fa\u6570\u636e\u5e93 binlog \u7684\u60c5\u51b5\uff0c\u8be5\u6bd4\u4f8b\u8d8a\u5c0f\u8d8a\u597d\u3002<\/p>\n<h4><span class=\"ez-toc-section\" id=\"49_redo_%E6%97%A5%E5%BF%97\"><\/span>4.9. redo \u65e5\u5fd7<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<pre><code class=\"language-sql\">select global status like &#039;Innodb_log_waits&#039;; # \u67e5\u770b innodb redo \u65e5\u5fd7\u7b49\u5f85\u7f13\u51b2\u533a\u5237\u65b0\u7684\u6b21\u6570\u3002<\/code><\/pre>\n<p>\u5f53 redo \u7f13\u51b2\u533a\u5bb9\u7eb3\u4e0d\u4e0b\u4e8b\u52a1\u4ea7\u751f\u7684 redo \u65e5\u5fd7\u65f6\uff0c\u672c\u6b21\u4e8b\u52a1\u4ea7\u751f\u7684 redo \u65e5\u5fd7\u5728\u5199\u5165 redo \u7f13\u51b2\u533a\u4e4b\u524d\u5c31\u5fc5\u987b\u7b49\u5f85 redo \u7f13\u51b2\u533a\u6709\u8db3\u591f\u7684\u7a7a\u95f4\u624d\u80fd\u5199\u5165\u3002<\/p>\n<p>\u5982\u679c\u53d1\u73b0 redo \u65e5\u5fd7\u7b49\u5f85\u5237\u65b0\u7684\u6b21\u6570\u8fc7\u591a\uff0c\u5219\u8868\u793a innodb redo \u65e5\u5fd7\u7f13\u51b2\u533a\u7684\u5927\u5c0f\u4e0d\u591f\uff0c\u53ef\u4ee5\u8003\u8651\u52a0\u5927 innodb_log_buffer_size \u7684\u503c\u3002<\/p>\n<h4><span class=\"ez-toc-section\" id=\"410_InnoDB_%E7%BC%93%E5%AD%98\"><\/span>4.10. InnoDB \u7f13\u5b58<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<pre><code class=\"language-sql\">show global status like &#039;Innodb_buffer_pool_read_requests&#039;; # \u8bfb\u53d6\u9875\u7684\u603b\u6b21\u6570\nshow global status like &#039;Innodb_buffer_pool_read&#039;; # \u4ece\u78c1\u76d8\u8bfb\u53d6\u9875\u7684\u6b21\u6570<\/code><\/pre>\n<p>\u5982\u679c\u53d1\u73b0\u4ece\u78c1\u76d8\u8bfb\u53d6\u9875\u7684\u6b21\u6570\u8fc7\u591a\uff0c\u5219\u6709\u53ef\u80fd\u662f\u56e0\u4e3a innodb \u7f13\u51b2\u6c60\u7684\u5927\u5c0f\u4e0d\u591f\uff0c\u6b64\u65f6\u53ef\u4ee5\u8003\u8651\u52a0\u5230 innodb_buffer_pool_size \u7684\u503c\u3002<\/p>\n<p>\u8ba1\u7b97 innodb \u7f13\u5b58\u547d\u4e2d\u7387\uff1a<\/p>\n<pre><code class=\"language-sql\">select concat((Innodb_buffer_pool_read_requests - Innodb_buffer_pool_read) \/ Innodb_buffer_pool_read_requests * 100,&#039;%&#039;;<\/code><\/pre>\n<p>\u901a\u8fc7\u8ba1\u7b97 innodb \u7f13\u5b58\u547d\u4e2d\u7387\uff0c\u53ef\u53cd\u6620\u51fa innodb \u7f13\u5b58\u7684\u6548\u7387\uff0c\u8be5\u6bd4\u4f8b\u8d8a\u5927\u8d8a\u597d\u3002<\/p>\n","protected":false},"excerpt":{"rendered":"<p>1. \u6570\u636e\u5e93\u603b\u4f53\u8fd0\u884c\u60c5\u51b5 1.1. \u6570\u636e\u5e93\u5b57\u7b26\u96c6\u4e0e&hellip;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":true,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3,32],"tags":[12,125],"class_list":["post-1427","post","type-post","status-publish","format-standard","hentry","category-mariadbmysql","category-mariadb","tag-mariadb","tag-125"],"_links":{"self":[{"href":"http:\/\/dba.qishuo.xin\/index.php?rest_route=\/wp\/v2\/posts\/1427","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/dba.qishuo.xin\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/dba.qishuo.xin\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/dba.qishuo.xin\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/dba.qishuo.xin\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1427"}],"version-history":[{"count":25,"href":"http:\/\/dba.qishuo.xin\/index.php?rest_route=\/wp\/v2\/posts\/1427\/revisions"}],"predecessor-version":[{"id":3637,"href":"http:\/\/dba.qishuo.xin\/index.php?rest_route=\/wp\/v2\/posts\/1427\/revisions\/3637"}],"wp:attachment":[{"href":"http:\/\/dba.qishuo.xin\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1427"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/dba.qishuo.xin\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1427"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/dba.qishuo.xin\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1427"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}