MySQL Master <=(Slave,Master)=> Slave

1
votes

I want to know if a server can be a slave and master at the same time. Our problem is that we have lots of mobile units that need to be synced to the master but they only need 6 out of the 100s of tables on the master. All the extra tables serve no purpose on the slave except for delaying synchronization and adding data costs.

We want to create a smaller schema say mobileSchema that contains only 6 tables that are sync'd to their counterparts in the masterSchema. Is this possible? To have schemas sync internally or have some master/slave-master/slave configuration where the middle server is slave to the bigger server and master to the mobile units?

If the answer is no would anyone have any alternate solutions to propose. We're trying to avoid having to sync the different schemas/databases manually as that can get real ugly real fast.

Raza

2
mysqlreplicationmasterslave
Database Administrators would be a better fit for this question. – user1864610
Much thanks, that definitely does seem a lot more relevant. Following is a potential answer for any wanderers dba.stackexchange.com/questions/33911/… – Raza Jamil

2 Answers

0
votes

AFAIK you can't natively sync schemas internally.

In your case you can do something like this:

  1. Enable binary logging on your main server.
  2. Create another server to act as a proxy and configure it to replicate from the main.
  3. Configure the 'proxy' to only replicate the tables you need for the remote servers (replicate-do-table).
  4. Enable binary logging and log-slave-updates on the 'proxy'.
  5. Configure your remote units to replicate from the proxy.

You will probably also need to enable encryption for the remote connections.

0
votes

You might like to look at replication filters.

You can do filtering on the master, so it only logs part of the changes.

Or you can do filtering on the replica(s), so the master would log all changes, and the replica would download all the logs, but the replica would only apply a subset of changes. Good if you want some replicas to replay some changes but other replicas to replay a different subset of changes.