CODEWITHSUNDEEP
mysqldatabasereplicationprivileges
ivorytux
ivorytux

Reputation: 359

Replicating Different Databases to Different Slaves

I need to replicate some databases as shown below.

         -DB1---replicate to---> SLAVE_A
        /
MASTER <--DB2---replicate to---> SLAVE_B
        \
         -DB3---replicate to---> SLAVE_C

The process described here would work well if I controlled both the master and all slave servers, but unfortunately I only control the master in this case. The slaves will potentially be in different countries, run by other admins.

My concern is that the configuration on SLAVE_A could easily be changed to replicate not only the intended DB1, but DB2 and DB3 as well, which is not good.

The only hope I have of limiting which slave replicates which database is if I can somehow control it from the master, which, from what I've been able to figure out, can't be done. A user has replication privileges either to all databases or none, which is unfortunate.

Am I missing something here, or is this just a limitation in MySQL? Any thoughts on how this could be accomplished?

Thanks.

Upvotes: 1

Views: 1431

Answers (2)

Jaredj
Jaredj

Reputation: 21

On the slave server there is possibility to filter transactions to apply by replicate-do-db or replicate-ignore-db parameters, included in my.cnf file or in command line as option. The same can be done on master with binlog-do-db or binlog-ignore-db but then - it limits replication do some certain databases on the master. So for you better solution is to filter transactions on the slave.

Upvotes: 2

cegfault
cegfault

Reputation: 6632

Create a different MySQL user for each replication slave, and give that user access only to the database you want it to have replication access to.

You can read more in the MySQL Documentation: 16.1.1.3. Creating a User for Replication

CREATE USER 'slave_a'@'some_slave_server' IDENTIFIED BY 'slavepass';
GRANT REPLICATION SLAVE ON `DB1`.* TO 'slave_a'@'some_slave_server';
FLUSH PRIVILEGES;

And then repeat for Slaves B and C on DB 2 and 3 respectively.

Upvotes: -1

Related Questions