Bug #62825 Option to replicate at the database level instead of the server level
Submitted: 19 Oct 2011 17:02 Modified: 31 May 2012 16:07
Reporter: Van Stokes Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Replication Severity:S4 (Feature request)
Version:5.5.16 OS:Any
Assigned to: CPU Architecture:Any
Tags: database, feature, replication, request
Triage: Needs Triage: D5 (Feature request)

[19 Oct 2011 17:02] Van Stokes
Description:
It would be nice to have the option configure replication at the database level instead of just the server level. In this scenario you would have have "masters" of "databases" instead of a "master" server. This would be very helpful for company's that have multiple entities running on the same hardware. 

How to repeat:
Typical configuration:
S1(master) -> S2(slave), S3(slave), S4(slave)

Desired configuration, to configure masters and slaves to replicate at the database level. Therefore, a server can be a slave to multiple masters.

S1 is the master of database db1 and replicates to S2, S3, S4.
S1(db1) -> S2(slave), S3(slave), S4(slave)

S2 is the master of database db2 and replicates to S1, S3, S4.
S2(db2) -> S1(slave), S3(slave), S4(slave)

S3 is the master of database db3 and replicates to S1, S2, S4.
S2(db3) -> S1(slave), S2(slave), S4(slave)

Suggested fix:
********** CURRENT COMMAND ***************
CHANGE MASTER TO
  MASTER_HOST='s1.mycompany.com',
  MASTER_USER='replication',
  MASTER_PASSWORD='bigs3cret',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='master2-bin.001',
  MASTER_LOG_POS=4,
  MASTER_CONNECT_RETRY=10;

********** NEW COMMANDS (ex: issued on server S4) ***************
CHANGE MASTER TO
  MASTER_HOST='s1.mycompany.com',
  MASTER_USER='replication',
  MASTER_PASSWORD='bigs3cret',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='master2-bin.001',
  MASTER_LOG_POS=4,
  MASTER_CONNECT_RETRY=10
  MASTER_DATABASE = 'db1'

CHANGE MASTER TO
  MASTER_HOST='s2.mycompany.com',
  MASTER_USER='replication',
  MASTER_PASSWORD='bigs3cret',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='master2-bin.001',
  MASTER_LOG_POS=4,
  MASTER_CONNECT_RETRY=10
  MASTER_DATABASE = 'db2'

CHANGE MASTER TO
  MASTER_HOST='s3.mycompany.com',
  MASTER_USER='replication',
  MASTER_PASSWORD='bigs3cret',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='master2-bin.001',
  MASTER_LOG_POS=4,
  MASTER_CONNECT_RETRY=10
  MASTER_DATABASE = 'db3'

As you can see, server S4 is now a slave to three different masters because it's now at the DATABASE level vs the SERVER level. A lot more flexible.
[20 Feb 2012 7:48] Simon Mudd
I have also mentioned this both to MySQL support and commented on it in my blog. See http://blog.wl0.org/2009/03/whats-missing-in-mysql-replication/. This still appears to be relevant.  I guess I should look around and actually reference all the tickets that request this in one place so that it is more visible.
[31 May 2012 16:07] Valeriy Kravchuk
Thank you for the feature request.