{"id":29,"date":"2005-03-25T15:55:49","date_gmt":"2005-03-25T07:55:49","guid":{"rendered":"http:\/\/blog.urdada.net\/2005\/03\/25\/29\/"},"modified":"2007-09-13T13:09:21","modified_gmt":"2007-09-13T05:09:21","slug":"replication-of-mysql-database","status":"publish","type":"post","link":"https:\/\/dada.tw\/blog\/2005\/03\/25\/29\/","title":{"rendered":"Replication of MySQL database"},"content":{"rendered":"

MySQL database \u81ea 3.23.15 \u7248\u4ee5\u5f8c\u5c31<\/font>\u652f\u63f4 Replication \u529f\u80fd\uff0c<\/font><\/span>\u4f7f\u7528 Replication \u53ef\u4ee5\u62ff\u4f86\u7576\u5099\u63f4\u53ca\u4f5c\u6d41\u91cf\u5206\u6563<\/font><\/span><\/p>\n

\u5176\u5be6\u8a2d\u5b9a\u5341\u5206\u7c21\u55ae\uff0c\u6574\u7406\u8a2d\u5b9a\u65b9\u6cd5\u8aaa\u660e\u5982\u4e0b:<\/span><\/font><\/p>\n

\u5047\u8a2d Master \u662f A (192.168.100.1<\/font>) \uff0c Slave \u662f B (192.168.100.2<\/font>) \uff0c\u8981\u8907\u88fd\u7684 database \u70ba hello<\/strong><\/font><\/font><\/span><\/p>\n

<\/span><\/p>\n

[\u6b65\u9a5f\u4e00]<\/strong> A \u6a5f\u5668\u4e0a\u9762..<\/font><\/span><\/p>\n

<\/span><\/p>\n

\u9996\u5148\u7de8\u8f2f \/etc\/my.cnf\uff0c\u52a0\u5165\u4e0b\u5217\u5e7e\u884c<\/font><\/span><\/p>\n

<\/span><\/p>\n

[mysqld]
\nserver-id=1
\nlog-bin<\/strong> <\/font><\/span><\/font><\/font><\/span><\/p>\n

binlog-do-db=hello<\/font><\/strong> <\/font><\/span><\/font><\/font><\/span><\/p>\n

<\/span><\/p>\n

log-bin \u662f\u544a\u8a34 MySQL server \u628a Binary Log \u6a5f\u5236\u555f\u52d5\uff0c\u628a\u5c0d hello \u9019\u500b DB \u7684<\/span>\u6240\u6709\u66f4\u52d5 LOG \u7d00\u9304\u4e0b\u4f86\uff0c\u7136\u5f8c\u624d\u6709\u8fa6\u6cd5\u8b93 Slave \u904e\u4f86\u540c\u6b65<\/font><\/span><\/p>\n

<\/span><\/p>\n

\u63a5\u4e0b\u4f86\u91cd\u65b0\u555f\u52d5 MySQL server<\/font><\/span><\/p>\n

<\/span><\/p>\n

[\u6b65\u9a5f\u4e8c<\/strong><\/font><\/span><\/span>]<\/strong><\/font><\/span><\/span><\/p>\n

\u4f7f\u7528 phpMyAdmin \u9023\u63a5\u5230 A\uff0c\u589e\u52a0\u4e00\u500b\u9023\u7dda\u5e33\u865f\uff0c\u8981\u64c1\u6709 replication \u7684\u6b0a\u9650\uff0c\u8a18\u5f97\u8981\u8a2d\u5b9a\u6b63\u78ba\u7684\u4f86\u6e90\u4f4d\u5740 <\/font><\/span>(\u5047\u8a2d\u662f\u5e33\u865f: replication<\/font> \u5bc6\u78bc: simba<\/font>)<\/font><\/span><\/span><\/p>\n

<\/span><\/p>\n

[\u6b65\u9a5f\u4e09]<\/strong><\/font><\/span><\/span><\/p>\n

\u628a A \u6a5f\u5668\u7684 \/home\/mysql\/ (\u6216\u662f \/var\/db\/) tar \u4e0b\u4f86\uff0c\u4e26\u65bc B \u6a5f\u5668\u4e0a\u9762\u56de\u5b58\uff0c<\/font><\/span><\/p>\n

\u9019\u500b\u6b65\u9a5f\u662f\u8981\u8b93\u4e00\u958b\u59cb B \u64c1\u6709\u548c A \u76f8\u540c\u7684\u8cc7\u6599\uff0c\u5f8c\u7e8c\u7684\u8907\u88fd\u5de5\u4f5c\u624d\u6709\u8fa6\u6cd5\u9032\u884c<\/font><\/span><\/p>\n

<\/span><\/p>\n

[\u6b65\u9a5f\u56db]<\/strong><\/font><\/span><\/span><\/span><\/p>\n

B \u6a5f\u5668\u4e0a\u9762..<\/font><\/span><\/span><\/p>\n

<\/span><\/p>\n

\u7de8\u8f2f \/etc\/my.cnf\uff0c\u52a0\u5165\u4e0b\u5217\u5e7e\u884c<\/font><\/span><\/p>\n

<\/span><\/p>\n

[mysqld]
\nmaster-host=192.168.100.1<\/font>
\nmaster-user=replication<\/font>
\nmaster-password=simba
\n<\/font>master-port=3306
\nserver-id=2
\nmaster-connect-retry=60
\nreplicate-do-db=hello
\n<\/font>log-slave-updates<\/strong><\/font><\/span><\/p>\n

<\/span><\/p>\n

[\u6b65\u9a5f\u4e94]<\/strong><\/font><\/span><\/span><\/font><\/span><\/p>\n

\u91cd\u65b0\u555f\u52d5 B \u4e0a\u9762\u7684 MySQL database<\/span><\/font><\/p>\n

[\u6b65\u9a5f\u516d]<\/strong><\/font><\/span><\/span><\/font><\/p>\n

\u6e2c\u8a66\u5728 A \u4e0a\u9762\u7684 hello database \u5167\u5efa\u7acb\u4e00\u500b table\uff0c\u7136\u5f8c\u53bb B \u770b\u770b\u662f\u5426\u6709\u76f8\u540c\u7684 table \u51fa\u73fe<\/span><\/font><\/p>\n

<\/span><\/font><\/p>\n

HINT:<\/strong> \u82e5\u6709\u591a\u500b DB \u8981\u505a Replication \u7684\u8a71\uff0c\u5206\u5225\u6307\u5b9a\u591a\u884c binlog-do-db <\/strong><\/font><\/span><\/font><\/span><\/span><\/span><\/font>\u53ca replicate-do-db <\/font><\/strong><\/span><\/font><\/span>\u5373\u53ef
\n<\/span><\/font><\/span><\/p>\n","protected":false},"excerpt":{"rendered":"

MySQL database \u81ea 3.23.15 \u7248\u4ee5\u5f8c\u5c31\u652f\u63f4 Replication \u529f\u80fd\uff0c\u4f7f\u7528 Repli […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[11],"tags":[],"class_list":["post-29","post","type-post","status-publish","format-standard","hentry","category-software"],"views":3453,"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/pubdi-t","jetpack_sharing_enabled":true,"jetpack-related-posts":[{"id":45,"url":"https:\/\/dada.tw\/blog\/2005\/06\/05\/45\/","url_meta":{"origin":29,"position":0},"title":"\u5728 FreeBSD \u4e0b\u5b89\u88dd MySQL 4.1 …","author":"dada","date":"2005-06-05","format":false,"excerpt":"\u5728 FreeBSD \u4e0b\u9762\u7528 ports \u5b89\u88dd MySQL 4.1 \u5982\u679c\u4f60\u53ea\u8981\u7528 UTF-8 \u7684\u8a71\u5c31\u7b97\u2026","rel":"","context":"\u5728\u300c\u8edf\u9ad4\u300d\u4e2d","block_context":{"text":"\u8edf\u9ad4","link":"https:\/\/dada.tw\/blog\/category\/comp\/software\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":95,"url":"https:\/\/dada.tw\/blog\/2008\/11\/11\/95\/","url_meta":{"origin":29,"position":1},"title":"Mantis Mail Gateway (perl script)","author":"dada","date":"2008-11-11","format":false,"excerpt":"This is a simple Mantis Mail Gateway for mail-in t\u2026","rel":"","context":"\u5728\u300c\u8edf\u9ad4\u300d\u4e2d","block_context":{"text":"\u8edf\u9ad4","link":"https:\/\/dada.tw\/blog\/category\/comp\/software\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":41,"url":"https:\/\/dada.tw\/blog\/2007\/07\/23\/41\/","url_meta":{"origin":29,"position":2},"title":"[\u7834\u89e3] \u6253\u958b La Fonera \u7684 SSH \u5c01\u5370","author":"dada","date":"2007-07-23","format":false,"excerpt":"\u53bb\u5e74\u5e95\u8cb7\u4e86\u5169\u53f0 La Fonera\uff0c\u5e78\u904b\u7684\u662f\u5169\u53f0\u90fd\u662f\u7528 165\u5143\u5c31\u8cb7\u5230\u4e86\uff0c\u4f46\u4e0d\u5e78\u7684\u662f\u7576\u4e86\u767d\u8001\u9f20\uff0c\u9019\u5169\u2026","rel":"","context":"\u5728\u300c\u786c\u9ad4\u300d\u4e2d","block_context":{"text":"\u786c\u9ad4","link":"https:\/\/dada.tw\/blog\/category\/comp\/hardware\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":66,"url":"https:\/\/dada.tw\/blog\/2007\/11\/26\/66\/","url_meta":{"origin":29,"position":3},"title":"Wanted: \u8aa0\u5fb5\u8cc7\u6df1\u8edf\u9ad4\u5de5\u7a0b\u5e2b [\u7db2\u8def\u904a\u6232\u696d]","author":"dada","date":"2007-11-26","format":false,"excerpt":"\u9019\u7bc7\u662f\u5ee3\u544a\u6587.... \u66ff\u516c\u53f8\u5fb5\u4e00\u4e0b\u4eba.. \u8aa0\u5fb5: \u8cc7\u6df1\u8edf\u9ad4\u5de5\u7a0b\u5e2b \/ 2~5\u4eba \u516c\u53f8\u540d\u7a31\uff1a \u548c\u4fe1\u8d85\u5a92\u2026","rel":"","context":"\u5728\u300c\u7db2\u8def\u79d1\u6280\u300d\u4e2d","block_context":{"text":"\u7db2\u8def\u79d1\u6280","link":"https:\/\/dada.tw\/blog\/category\/network\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":35,"url":"https:\/\/dada.tw\/blog\/2005\/03\/29\/35\/","url_meta":{"origin":29,"position":4},"title":"\u4f7f\u7528 SSH \u914d\u5408\u6191\u8b49\u4f86\u9060\u7aef\u5099\u4efd\u6a94\u6848 (rsync)","author":"dada","date":"2005-03-29","format":false,"excerpt":"\u524d\u9762\u5beb\u4e86\u4e00\u7bc7\u300c\u4f7f\u7528 SSH \u914d\u5408\u6191\u8b49\u4f86\u9060\u7aef\u8907\u88fd\u6a94\u6848\u300d \u7c21\u4ecb\u4f7f\u7528 scp \u914d\u5408 ssh \u4f86\u9060\u7aef\u8907\u88fd\u6a94\u6848\u2026","rel":"","context":"\u5728\u300c\u8cc7\u8a0a\u5b89\u5168\u300d\u4e2d","block_context":{"text":"\u8cc7\u8a0a\u5b89\u5168","link":"https:\/\/dada.tw\/blog\/category\/comp\/security\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":168,"url":"https:\/\/dada.tw\/blog\/2009\/02\/20\/168\/","url_meta":{"origin":29,"position":5},"title":"AdSense for Domains (\u5728\u672a\u4f7f\u7528\u7684\u7db2\u5740\u4e0a\u653e\u5ee3\u544a)","author":"dada","date":"2009-02-20","format":false,"excerpt":"Google \u63a8\u51fa\u4e86 AdSense for Domains (AdSense \u7db2\u57df\u5ee3\u544a) \u5f15\u7528 G\u2026","rel":"","context":"\u5728\u300c\u7db2\u8def\u79d1\u6280\u300d\u4e2d","block_context":{"text":"\u7db2\u8def\u79d1\u6280","link":"https:\/\/dada.tw\/blog\/category\/network\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/dada.tw\/blog\/wp-json\/wp\/v2\/posts\/29","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dada.tw\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dada.tw\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dada.tw\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/dada.tw\/blog\/wp-json\/wp\/v2\/comments?post=29"}],"version-history":[{"count":0,"href":"https:\/\/dada.tw\/blog\/wp-json\/wp\/v2\/posts\/29\/revisions"}],"wp:attachment":[{"href":"https:\/\/dada.tw\/blog\/wp-json\/wp\/v2\/media?parent=29"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dada.tw\/blog\/wp-json\/wp\/v2\/categories?post=29"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dada.tw\/blog\/wp-json\/wp\/v2\/tags?post=29"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}