How to Stop MySQL from Replicating CREATE TABLE on Slave for Ignored Databases

MySQLreplication

Mine is a simple MySQL Master-Slave setup, with --binlog-do-db option as discussed in this article.

As per the documentation, logs are written for DDL statements for all databases irrespective of '--binlog-do-db'.

Is there any way to stop this??

Best Answer

binlog_do_db = dbname says to replicate any statement when the default database is dbname. So be careful what USE statement precedes the statement:

USE non_repl_db;
CREATE TABLE ...;

Related Solutions

Does MySQL Support Replicating All Databases?

To answer your question about how MySQL replicates all databases, here is how it works...

Master has Binary Logging enabled
Master records completed SQL Statements
Master will inject 'USE dbname;' among the SQL Statements it records
Slave connects to Master (IO Thread)
Slave IO Thread requests next SQL statement from the Binary Logs
Slave IO Thread copies it to its Relay Logs
Slave SQL Thread Processes its Relay Log Entries FIFO (Queue)

Once USE dbname; is issued out of the relay logs, multiple SQL statements will be processes chronologically until another USE dbname; (a different database) is issued from the Relay Logs.

To see this, pick any Binary Log (except the Current One) on the Master, and run this:

mysqlbinlog binarylogname > SQLStatements.sql

To see all the binary logs and the current binary log on the Master, run these:

SHOW BINARY LOGS;
SHOW MASTER STATUS;

Long story short: By default, all DBs are replicated due to the USE dbname; command within the binary logs on the master and the relay logs on the slave.

Abdul's answer shows how to filter in or filter out databases from being processed by the slave.

Mysql – How to eradicate Mysql Slave Replication delays when huge updates from master

There's a stack of system related questions here. We don't know what the hardware looks like for your master or slave. Maybe you're lacking RAM on the slave, or any number of other differences.

The master is probably receiving bursts of updates in parallel from many client connections, and for whatever reason, the slave can't keep up since all replication is done in serial.

If you have a lot of InnoDB activity on the master, you can probably disable InnoDB on the slave and gain some speed that way. That option and a few others are given here:

16.4.4.7: How can I use replication to improve performance of my system? http://dev.mysql.com/doc/refman/5.0/en/replication-faq.html#qandaitem-16-4-4-1-7

Or there could be configuration tweaks in my.cnf that could help you. I'd start by looking at your memory usage on the slave, and looking up buffer and memory related options for innodb and/or myisam depending on which of those engines you're using (or others).

You can also look through your binlogs with the mysqlbinlog tool to see what database and tables have the most activity. Then start working with your application developers and start chipping away at the problem.

Also check your mysqld error log on the slave. There might be clues in there.

Best Answer

Related Solutions

Does MySQL Support Replicating All Databases?

Mysql – How to eradicate Mysql Slave Replication delays when huge updates from master

Related Question