I have a table in my DB which I need to partitionate based on a foreign key attribute. So the number of partitioned tables is the same as the number of different values present for the foreign key.
EDIT: I have replication setp up, where a table in the master is replicated in four slave servers. The slaves do not need to replicate the WHOLE table in the master server but only a partition of it as shown in the figure. How can achieve this?
Split your data into separate tables or separate databases based on what needs to go to what Device. Then have 3 tiers:
Master -> Relays -> Devices
Each Relay is both Slave (to the Master) and Master to one (or more) Device. In the Relay, the tables you do not want to go to the device is declared ENGINE=BLACKHOLE. The other tables in the Relay are declared normally.
The Relays could be on the same server as the Master, but you would need separate mysqld instances, with a separate Port (other than 3306) for each. The overhead would be rather small, especially since the Relays are doing nothing other than handing off a subset of the data.
Edit (based on image)
To limit space in the Slaves, use "replicate_do/ignore" commands on the Slaves. This will not diminish the bandwidth out of the Master or into the Slaves. The replicate_* settings will then throw away most of the replicated data.
Introduce the above-mentioned Relays and Blackhole if you need to limit the bandwidth into the Slaves.
