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
• Status: Verified
• Category: MySQL Server: Replication
• Severity: S4 (Feature request)
• Version: 5.5.16
• OS: Any
• CPU Architecture: Any
• Tags: database, feature, replication, 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.

[12 Apr 2019 15:23] Krzysztof Harpula

I believe I have requested a similar thing. In my request, it seems no special changes are needed to setup replication per database, as this is controlled by added replication filters per channel in slaves. To implement what is requested in this request in my solution is possible only after adding identification of slave on masters not only by SlaveID but also by channel name.

See https://bugs.mysql.com/bug.php?id=94885