How to add a new database on replication?

Question

I have a mysql replication with two servers. There are actually 3 databases on it.

On master :

server-id = 1 log_bin = /var/log/mysql/mysql-bin.log expire_logs_days = 4 max_binlog_size = 1G bind-address = 0.0.0.0 port = 3306 binlog_do_db = database1 binlog_do_db = database2 binlog_do_db = database3 

And on slave:

server-id = 2 log_bin = /var/log/mysql/mysql-bin.log expire_logs_days = 4 max_binlog_size = 1G bind-address = 127.0.0.1 port = 3306 replicate_do_db = database1 replicate_do_db = database2 replicate_do_db = database3 

But i need to add a new one, database4. Do I have to stop servers for this ? By simply adding the base in the cnf file, can this be taken into account dynamically?

Thanks

Answer 1

There is no way to ask the Primary to send a database that it has not always been configured to send. So...

Plan A: Add the rows to configs before creating the database. Yes, a restart of each is needed.

Plan B (you already have the database on the Primary):

1. stop writing to the Primary
2. Dump db4
3. update both config files
4. Load the dump on Replica
5. Restart both

Notes on config:

• If you have no binlog/replica_do/ignore configuration, all will be replicated.
• binlog_do says what to send out from the Primary; replicate_do says what to allow into the Replica. That is, they have the same effect. (The settings become useful when only some Replicas need to receive the data.)