{"id":2077,"date":"2010-03-02T21:03:50","date_gmt":"2010-03-02T19:03:50","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=2077"},"modified":"2010-03-02T21:03:50","modified_gmt":"2010-03-02T19:03:50","slug":"quick-reminder-avoid-using-binlog-do-db","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/quick-reminder-avoid-using-binlog-do-db","title":{"rendered":"Quick reminder: avoid using binlog-do-db"},"content":{"rendered":"

Nothing new about this warning; but it’s worth repeating:<\/p>\n

Using binlog-do-db<\/strong><\/a> is dangerous to your replication. It means the master will not write to binary logs any statement not in the given database.<\/p>\n

Ahem. Not exactly. It will not write to binary logs any statement which did not originate from the given database.<\/p>\n

Which is why a customer, who was using Toad for MySQL<\/strong> as client interface to MySQL, and by default connected to the mysql<\/strong> schema, did not see his queries being replicated. In fact, he later on got replication errors. If you do:<\/p>\n

\n
USE test;\r\nINSERT INTO world.City VALUES (...)<\/pre>\n<\/blockquote>\n
Then the statement is assumed to be in the test<\/strong> database, not in the world<\/strong> database.<\/p>\n
Slightly better is using replicate-do-db<\/strong> on the slave machines. At least we allow the master to write everything. But still, for the same reasons, slaves may fail to repeat a perfectly valid query, just because it has been issued in the context of the wrong database. replicate-ignore-db<\/strong> is somewhat safer yet, but the trap is still there.<\/p>\n
My advice is that replication should replicate everything<\/em>. Make sure you and everyone else you work with understand the implications of binlog-do-db<\/strong> and replicate-do-db<\/strong> before implementing it.<\/p>\n","protected":false},"excerpt":{"rendered":"
Nothing new about this warning; but it’s worth repeating: Using binlog-do-db is dangerous to your replication. It means the master will not write to binary logs any statement not in the given database. Ahem. Not exactly. It will not write to binary logs any statement which did not originate from the given database. Which is […]<\/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":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"enabled":false},"version":2}},"categories":[5],"tags":[11,8],"class_list":["post-2077","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-configuration","tag-replication"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-xv","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/2077","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/comments?post=2077"}],"version-history":[{"count":9,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/2077\/revisions"}],"predecessor-version":[{"id":2086,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/2077\/revisions\/2086"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=2077"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=2077"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=2077"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}