{"id":462,"date":"2007-12-07T00:00:00","date_gmt":"2007-12-06T16:00:00","guid":{"rendered":"http:\/\/www.strongd.net\/?p=462"},"modified":"2011-07-15T09:52:39","modified_gmt":"2011-07-15T01:52:39","slug":"mysqlbinlog-%e8%b3%87%e6%96%99%e5%ba%ab%e8%99%95%e7%90%86%e4%ba%8c%e9%80%b2%e5%88%b6%e6%97%a5%e8%aa%8c%e6%aa%94%e6%a1%88%e7%9a%84%e5%af%a6%e7%94%a8%e5%b7%a5%e5%85%b7","status":"publish","type":"post","link":"https:\/\/www.strongd.net\/?p=462","title":{"rendered":"mysqlbinlog \u8cc7\u6599\u5eab\u8655\u7406\u4e8c\u9032\u5236\u65e5\u8a8c\u6a94\u6848\u7684\u5be6\u7528\u5de5\u5177"},"content":{"rendered":"

\u7576\u7136\uff0c\u7cfb\u7d71\u525b\u5f04\u597d\u662f\u6c92\u6709\u958b\u555f mysqlbinlog \u7684\u529f\u80fd\uff0c\u81f3\u5c11\u5728 CentOS4.4 \u4e0a\u9762\u6211\u9084\u8981\u53bb\u958b\u555f\uff0c\u4e0d\u904e ubuntu \u7cfb\u7d71\u9810\u8a2d\u5c31\u5df2\u7d93\u958b\u555f\u4e86\uff0c\u4e0d\u904e\u6c92\u95dc\u4fc2\uff0c\u53ea\u8981\u5229\u7528\u4e0b\u9762\u65b9\u6cd5\u5c31\u53ef\u4ee5\u9054\u5230\u4e86<\/P>
\n

\u4fee\u6539 my.cnf [ CentOS: \/etc\/my.cnf Ubuntu: \/etc\/mysql\/my.cnf ]\uff0c\u52a0\u5165\u4e0b\u9762\u8a9e\u6cd5<\/P>
\n


\n
# Replication Master Server (default)<\/SPAN>
# binary logging is required for replication<\/SPAN>
log-BIN<\/SPAN>=mysql-BIN<\/SPAN><\/DIV><\/DIV>
\n

<\/SPAN><\/P>
\n

\u4e0d\u904e\u8a71\u8aaa\u7576\u4f60\u958b\u555f\u9019\u500b\u529f\u80fd\u4e4b\u5f8c\uff0c\u4f60\u6703\u767c\u73fe\u5728 \/var\/lib\/mysql\/ \u5e95\u4e0b\u591a\u51fa\u5f88\u591a\u6a94\u6848<\/P>
\n


\n
-rw-rw—-   1<\/SPAN> mysql mysql  33164904<\/SPAN>  1<\/SPAN>\u6708 17<\/SPAN> 15<\/SPAN>:44<\/SPAN> mysql-bin.000001<\/SPAN>
-rw-rw—-   1<\/SPAN> mysql mysql      4007<\/SPAN>  1<\/SPAN>\u6708 17<\/SPAN> 15<\/SPAN>:50<\/SPAN> mysql-bin.000002<\/SPAN>
-rw-rw—-   1<\/SPAN> mysql mysql  70288989<\/SPAN>  1<\/SPAN>\u6708 29<\/SPAN> 22<\/SPAN>:38<\/SPAN> mysql-bin.000003<\/SPAN>
-rw-rw—-   1<\/SPAN> mysql mysql     16665<\/SPAN>  1<\/SPAN>\u6708 29<\/SPAN> 22<\/SPAN>:41<\/SPAN> mysql-bin.000004<\/SPAN>
-rw-rw—-   1<\/SPAN> mysql mysql      4792<\/SPAN>  1<\/SPAN>\u6708 29<\/SPAN> 22<\/SPAN>:42<\/SPAN> mysql-bin.000005<\/SPAN>
-rw-rw—-   1<\/SPAN> mysql mysql  56274069<\/SPAN>  2<\/SPAN>\u6708 10<\/SPAN> 06<\/SPAN>:25<\/SPAN> mysql-bin.000006<\/SPAN>
-rw-rw—-   1<\/SPAN> mysql mysql 893963240<\/SPAN>  3<\/SPAN>\u6708 29<\/SPAN> 09<\/SPAN>:21<\/SPAN> mysql-bin.000007<\/SPAN>
-rw-rw—-   1<\/SPAN> mysql mysql 666605284<\/SPAN>  3<\/SPAN>\u6708 29<\/SPAN> 09<\/SPAN>:39<\/SPAN> mysql-bin.000008<\/SPAN>
-rw-rw—-   1<\/SPAN> mysql mysql    151946<\/SPAN>  3<\/SPAN>\u6708 29<\/SPAN> 10<\/SPAN>:02<\/SPAN> mysql-bin.000009<\/SPAN>
-rw-rw—-   1<\/SPAN> mysql mysql   3450785<\/SPAN>  3<\/SPAN>\u6708 29<\/SPAN> 22<\/SPAN>:38<\/SPAN> mysql-bin.000010<\/SPAN><\/DIV><\/DIV>
\n

\u4e0a\u9762\u5c31\u662f\u7522\u751f mysqlbinlog \u6a94\u6848\uff0c\u7576\u7136\u5982\u679c\u4f60\u8981\u89c0\u770b\u90a3\u4e00\u500b\u6a94\u6848\u4e0b\u6307\u4ee4\u5427<\/P>
\n


\n

shell> mysqlbinlog binlog.0000003<\/P><\/BLOCKQUOTE>
\n

\u88cf\u9762\u7684\u8a9e\u6cd5\u5305\u8de8 \u6bcf\u500b\u8a9e\u53e5\u82b1\u8cbb\u7684\u6642\u9593\u3001\u5ba2\u6236\u767c\u51fa\u7684\u7dda\u7a0bID\u3001\u767c\u51fa\u7dda\u7a0b\u6642\u7684\u6642\u9593\u6233\uff0c\u4e5f\u53ef\u4ee5\u9060\u7aef\u89c0\u770b<\/P>
\n

\u7576\u8b80\u53d6\u9060\u7a0b\u4e8c\u9032\u5236\u65e5\u8a8c\u6642\uff0c\u53ef\u4ee5\u901a\u904e\u9023\u63a5\u53c3\u6578\u9078\u9805\u4f86\u6307\u793a\u5982\u4f55\u9023\u63a5\u4f3a\u670d\u5668\uff0c\u4f46\u5b83\u5011\u7d93\u5e38\u88ab\u5ffd\u7565\u6389\uff0c\u9664\u975e\u60a8\u9084\u6307\u5b9a\u4e86\u2013read-from-remote-server\u9078\u9805\u3002\u9019\u4e9b\u9078\u9805\u662f\u2013host\u3001\u2013password\u3001\u2013port\u3001\u2013protocol\u3001\u2013socket\u548c\u2013user\u3002<\/P>
\n

\u5e95\u4e0b\u4f86\u8aaa\u660e\u4e00\u4e0b\u7528\u6cd5~<\/P>
\n

1. \u6307\u5b9a\u6062\u5fa9\u6642\u9593\u8a9e\u6cd5<\/P>
\n

\u5047\u5982\u4f60\u4eca\u5929\u65e9\u4e0a9\u9ede\u4e0d\u5c0f\u5fc3\u780d\u6389\u54ea\u500b\u8cc7\u6599\u5eab\u7684\u8cc7\u6599\u8868\uff0c\u4f60\u53ef\u4ee5\u5229\u7528\u4e0b\u9762\u8a9e\u6cd5\u4f86\u6062\u5fa9<\/P>
\n


\n
mysqlbinlog –stop-date=<\/SPAN>“2007-03-29 8:59:59”<\/SPAN>  \/var\/lib\/mysql\/bin.000001<\/SPAN> | mysql -u root -p<\/DIV><\/DIV>
\n

\u5982\u679c\u4f60\u60f3\u6062\u5fa9\u5f8c\u97629\u9ede\u4ee5\u5f8csql\u8a9e\u6cd5 \u53ef\u4ee5\u4f7f\u7528<\/P>
\n


\n
mysqlbinlog –start-date=<\/SPAN>“2007-03-29 9:00:00”<\/SPAN>  \/var\/lib\/mysql\/bin.000001<\/SPAN> | mysql -u root -p<\/DIV><\/DIV>
\n

\u6216\u8005\u662f \u4f60\u60f3\u6062\u5fa9 9\u9ede\u523010\u9ede\u4e4b\u9593\u7684sql\u8a9e\u6cd5\uff0c\u5247\u4e0b\u9762\u8a9e\u6cd5\u662f\u60a8\u60f3\u8981\u7684<\/P>
\n


\n
mysqlbinlog –start-date=<\/SPAN>“2007-03-29 9:00:00”<\/SPAN>  –stop-date=<\/SPAN>“2007-03-29 10:00:00”<\/SPAN> \/var\/lib\/mysql\/bin.000001<\/SPAN> | mysql -u root -p<\/DIV><\/DIV>
\n

\u5176\u5be6\u4f60\u4e5f\u53ef\u4ee5\u4e0d\u8981\u57f7\u884c\uff0c\u5148\u628asql\u8a9e\u6cd5\u8f38\u51fa\u5230 \/tmp\/restore.sql<\/P>
\n


\n
mysqlbinlog –start-date=<\/SPAN>“2007-03-29 9:00:00”<\/SPAN>  –stop-date=<\/SPAN>“2007-03-29 10:00:00”<\/SPAN> \/var\/lib\/mysql\/bin.000001<\/SPAN> &gt; \/tmp\/restore.sql<\/DIV><\/DIV>
\n

\u7576\u7136 \u4f60\u4e5f\u53ef\u4ee5\u6307\u5b9a\u4f60\u8981\u8f38\u51fa\u7684 database\uff0c\u514d\u7684\u6a94\u6848\u5f88\u5927<\/P>
\n


\n
—database=<\/SPAN>db_name\uff0c-d db_name
—host=<\/SPAN>host_name\uff0c-h host_name<\/DIV><\/DIV>
\n
<\/DIV><\/p>\n","protected":false},"excerpt":{"rendered":"

\u7576\u7136\uff0c\u7cfb\u7d71\u525b\u5f04\u597d\u662f\u6c92\u6709\u958b\u555f mysqlbinlog \u7684\u529f\u80fd\uff0c\u81f3\u5c11\u5728 CentOS4.4 \u4e0a\u9762\u6211\u9084\u8981\u53bb\u958b\u555f\uff0c\u4e0d\u904e ubuntu \u7cfb\u7d71\u9810\u8a2d\u5c31\u5df2\u7d93\u958b\u555f\u4e86\uff0c\u4e0d\u904e\u6c92\u95dc\u4fc2\uff0c\u53ea\u8981\u5229\u7528\u4e0b\u9762\u65b9\u6cd5\u5c31\u53ef\u4ee5\u9054\u5230\u4e86 \u4fee\u6539 my.cnf [ CentOS: \/etc\/my.cnf Ubuntu: \/etc\/mysql\/my.cnf ]\uff0c\u52a0\u5165\u4e0b\u9762\u8a9e\u6cd5 # Replication Master Server (default)# binary logging is required for replicationlog-BIN=mysql-BIN \u4e0d\u904e\u8a71\u8aaa\u7576\u4f60\u958b\u555f\u9019\u500b\u529f\u80fd\u4e4b\u5f8c\uff0c\u4f60\u6703\u767c\u73fe\u5728 \/var\/lib\/mysql\/ \u5e95\u4e0b\u591a\u51fa\u5f88\u591a\u6a94\u6848 -rw-rw—-   1 mysql mysql  33164904  1\u6708 17 15:44 mysql-bin.000001-rw-rw—-   1 mysql mysql      4007  1\u6708 17 15:50 mysql-bin.000002-rw-rw—-   1 mysql mysql  70288989  1\u6708 29 22:38 … Continue reading mysqlbinlog \u8cc7\u6599\u5eab\u8655\u7406\u4e8c\u9032\u5236\u65e5\u8a8c\u6a94\u6848\u7684\u5be6\u7528\u5de5\u5177<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[19],"tags":[123],"class_list":["post-462","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-mysqlbinlog"],"_links":{"self":[{"href":"https:\/\/www.strongd.net\/index.php?rest_route=\/wp\/v2\/posts\/462","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.strongd.net\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.strongd.net\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.strongd.net\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.strongd.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=462"}],"version-history":[{"count":1,"href":"https:\/\/www.strongd.net\/index.php?rest_route=\/wp\/v2\/posts\/462\/revisions"}],"predecessor-version":[{"id":912,"href":"https:\/\/www.strongd.net\/index.php?rest_route=\/wp\/v2\/posts\/462\/revisions\/912"}],"wp:attachment":[{"href":"https:\/\/www.strongd.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=462"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.strongd.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=462"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.strongd.net\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=462"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}