{"id":2897,"date":"2016-09-25T18:24:29","date_gmt":"2016-09-25T10:24:29","guid":{"rendered":"http:\/\/ixyzero.com\/blog\/?p=2897"},"modified":"2016-09-25T18:24:29","modified_gmt":"2016-09-25T10:24:29","slug":"mysql%e7%9a%84binlog%e8%bf%87%e5%a4%a7%e5%af%bc%e8%87%b4%e7%a3%81%e7%9b%98%e6%8a%a5%e8%ad%a6","status":"publish","type":"post","link":"https:\/\/ixyzero.com\/blog\/archives\/2897.html","title":{"rendered":"MySQL\u7684binlog\u8fc7\u5927\u5bfc\u81f4\u78c1\u76d8\u62a5\u8b66"},"content":{"rendered":"<p>=Start=<\/p>\n<h5>\u7f18\u7531\uff1a<\/h5>\n<p>\u5728\u542f\u7528MySQL\u7684\u4e3b\u4ece\u590d\u5236\u4e4b\u540e\uff0c\u53d1\u73b0\u5728Master\u4e0a\u7684MySQL\u78c1\u76d8\u5360\u7528\u8fc5\u901f\u4e0a\u5347\u5bfc\u81f4\u78c1\u76d8\u62a5\u8b66\uff08\u4e3b\u8981\u662fmysql-bin.000xyz\u6587\u4ef6\uff0c\u4e5f\u5c31\u662f\u4fd7\u79f0\u7684binlog\u6587\u4ef6\uff09\uff1a<\/p>\n<pre class=\"lang:default decode:true\">$ sudo du -sh \/var\/lib\/mysql\/* | sort -hr\n3.3G \/var\/lib\/mysql\/ibdata1\n1.1G \/var\/lib\/mysql\/mysql-bin.000001\n1.1G \/var\/lib\/mysql\/mysql-bin.000002\n...<\/pre>\n<h5>\u53c2\u8003\u89e3\u7b54\uff1a<\/h5>\n<h6>\u53ef\u80fd\u7684\u539f\u56e0\uff1a<\/h6>\n<p>\u539f\u56e0\u4e00\uff1a<br \/>\n\u542f\u7528\u4e86MySQL\u7684binlog\u8bb0\u5f55(\u5728\u914d\u7f6e\u6587\u4ef6\u4e2d\u8bbe\u7f6e\u4e86 <code>log-bin=mysql-bin<\/code>)<\/p>\n<p>\u539f\u56e0\u4e8c\uff1a<br \/>\n\u5c06binlog_format\u8bbe\u7f6e\u4e3a\u4e86mixed\u6216row\uff08\u9ed8\u8ba4\u7684\u683c\u5f0f\u4e3astatement\uff09\uff1a<\/p>\n<pre class=\"lang:default decode:true\">binlog_format = mixed\n\u6216\nbinlog_format = row<\/pre>\n<h6>\u89e3\u51b3\u529e\u6cd5\uff1a<\/h6>\n<p>0.\u5728\u4e0d\u9700\u8981binlog\u7684\u60c5\u51b5\u4e0b\uff0c\u53ef\u4ee5\u901a\u8fc7\u6ce8\u91ca\u300clog-bin=mysql-bin\u300d\u8fd9\u4e00\u884c\u6765\u5173\u95ed\u8be5\u529f\u80fd\uff1b<\/p>\n<p>1.\u4fee\u6539 binlog_format = statement (\u5360\u7528\u7a7a\u95f4\u76f8\u5bf9 row \u548c mixed \u6765\u8bf4\u4f1a\u6bd4\u8f83\u5c0f)\uff0c\u4f46\u662f\u5bf9\u4e8e\u67d0\u4e9bSQL\u6765\u8bf4\u53ef\u80fd\u5c31\u4e0d\u652f\u6301\uff0c\u9700\u8981\u6539SQL\uff1b<\/p>\n<p>2.<span style=\"color: #0000ff;\"><strong>\u4e34\u65f6\u5220\u9664\u90e8\u5206binlog<\/strong><\/span><\/p>\n<pre class=\"lang:default decode:true\">mysql&gt; PURGE BINARY LOGS TO 'mysql-bin.000039';\n\u6216\nmysql&gt; PURGE BINARY LOGS BEFORE '2016-09-20 22:46:26';<\/pre>\n<p>3.\u5728\u914d\u7f6e\u6587\u4ef6my.cnf\u4e2d<span style=\"color: #0000ff;\"><strong>\u8bbe\u7f6e binlog \u5b9a\u671f\u5220\u9664<\/strong><\/span>\u9009\u9879(\u4fee\u6539\u540e\u9700\u8981\u91cd\u542fMySQL)<\/p>\n<pre class=\"lang:default decode:true\">expire_logs_days = 2<\/pre>\n<p>4.<span style=\"color: #0000ff;\"><strong>\u4fee\u6539MySQL\u7684\u6570\u636e\u76ee\u5f55<\/strong><\/span><\/p>\n<p>\u5c06MySQL\u7684\u6570\u636e\u76ee\u5f55\u4fee\u6539\u81f3\u4e00\u4e2a\u6709\u66f4\u5927\u7a7a\u95f4\u7684\u78c1\u76d8\u4e0a\uff0c\u5e76\u8bbe\u7f6e expire_logs_days \u9009\u9879\uff0c\u907f\u514d\u78c1\u76d8\u62a5\u8b66\u3002<\/p>\n<h5>\u53c2\u8003\u94fe\u63a5\uff1a<\/h5>\n<ul>\n<li><a href=\"http:\/\/serverfault.com\/questions\/405726\/max-binlog-size-log-bin-size\">http:\/\/serverfault.com\/questions\/405726\/max-binlog-size-log-bin-size<\/a><\/li>\n<li><a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.5\/en\/purge-binary-logs.html\">http:\/\/dev.mysql.com\/doc\/refman\/5.5\/en\/purge-binary-logs.html<\/a><\/li>\n<li><a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.5\/en\/server-system-variables.html#sysvar_expire_logs_days\">http:\/\/dev.mysql.com\/doc\/refman\/5.5\/en\/server-system-variables.html#sysvar_expire_logs_days<\/a><\/li>\n<li><a href=\"https:\/\/www.percona.com\/blog\/2012\/05\/24\/binary-log-file-size-matters\/\">https:\/\/www.percona.com\/blog\/2012\/05\/24\/binary-log-file-size-matters\/<\/a><\/li>\n<\/ul>\n<p>=END=<\/p>\n","protected":false},"excerpt":{"rendered":"<p>=Start= \u7f18\u7531\uff1a \u5728\u542f\u7528MySQL\u7684\u4e3b\u4ece\u590d\u5236\u4e4b\u540e\uff0c\u53d1\u73b0\u5728Master\u4e0a\u7684MySQL\u78c1\u76d8\u5360\u7528\u8fc5\u901f\u4e0a\u5347\u5bfc\u81f4\u78c1 [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[14,23],"tags":[32,16],"class_list":["post-2897","post","type-post","status-publish","format-standard","hentry","category-database","category-knowledgebase-2","tag-binlog","tag-mysql"],"views":4498,"_links":{"self":[{"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/posts\/2897","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/comments?post=2897"}],"version-history":[{"count":0,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/posts\/2897\/revisions"}],"wp:attachment":[{"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/media?parent=2897"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/categories?post=2897"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/tags?post=2897"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}