I’ve been doing some investigation into properly configuring MySQL replication.
From what I understand after reading the documentation, you can only replicate specific databases, and each database name must be specified: In the master configuration, in order for the relevant database writes to be written to the binary logs, and in the slave configuration, in order to replay those logs onto the slave.
Is this correct, or is it possible to configure MySQL to replicate any and all databases?
Best Answer
To answer your question about how MySQL replicates all databases, here is how it works...
Once
USE dbname;
is issued out of the relay logs, multiple SQL statements will be processes chronologically until anotherUSE 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:
To see all the binary logs and the current binary log on the Master, run these:
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.