Mysql – Adding a db for replication into thesql

MySQLreplication

Currently I have these settings. I am adding a new db called mydb3. How to enable the replication for this new db? Currently I have copied the table schema into both master and slave. What other changes must I do ? Can I just add binlog_do_db=mydb3 and replicate-do-db=mydb3 on both master and slave?

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

innodb_file_per_table
innodb_lock_wait_timeout=120
innodb_buffer_pool=5G
innodb_log_file_size=512M


server-id=1
log-bin=mysql-bin
log-error=mysql-bin.err
binlog_do_db=mydb1

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

For the slave here is the settings.

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0


innodb_file_per_table
server-id=1339951115
master-host = ***
master-user = ***
master-password =***
master-port = 3306
replicate-do-db=mydb1
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Best Answer

Short answer to what I think you are asking... Yes, you can have multiple binlog_do_db statements.

Long answer...

The binlog and replicate commands can be a bit confusing. Roughly this is what happens:

  • If the Master has one or more binlog_do_... statements, they limit what is written to the binlog. See Bug below.
  • If the Master has one or more binlog_ignore_... statements, they list what is not written to the binlog; everything else is.
  • If you have a mixture of the two, I don't know what happens.

For Replication, the binlog is copied to the Slave. (The binlog could be just sitting there for a form of backup -- No slave involved. In particular, a Slave can have its own binlog.)

On a Slave, replicate_do_... allows only those things in; replicate_ignore_ tosses the listed things. A mixture is a mystery.

What you listed (binlog_do_db=mydb3 and replicate-do-db=mydb3) is redundant in that the replicate_do does nothing that the binlog_do didn't already do. (Namely replicate only mydb3.) It is more efficient to filter on the Master than on the Slave. (There are multiple use cases where this is not practical.) That is, you should have several binlog_do on the master, and nothing on the Slave.

A server that is not being used as a Slave has no use for replicate_*. (So you should not add it to the Master.)

I am trying to phrase everything carefully because of various topologies that you might not be interested in. For example...

A "dual-Master" topology has two Masters replicating from each other. That is, each is both the Master to the other, and the Slave to the other. I that case each is both a Master and a Slave. But I digress.

Bug

If the USE command in effect matches any of the binlog_do*, the command will be written. Note: USE x; INSERT INTO y.z ... depends on x, not y! This "bug" (aka 'feature') has tripped up many a DBA.

Side note:

For security, do not include the master-password in my.cnf; do it only when you execute CHANGE MASTER to establish the Master-Slave connection. (Future versions may disallow it anyway.)

Related Question