0

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?

Image: Replicating partitions

JuanV
  • 13
  • 4
  • Please don't use "partition" -- it has a specific technical implementation that you are not talking about. – Rick James Mar 07 '16 at 02:27
  • "Replication" is the technique normally used to keep "Slave" servers in sync with a "Master" server. But you seem to want each slave to have only a subset of the data? That can be achieved _if_ the split is by database or table, not by some key. – Rick James Mar 07 '16 at 02:28
  • Bottom line: MySQL does not quite have the tools you ask for; either invent your own tool, or re-think your design. – Rick James Mar 07 '16 at 02:29
  • I am missing something. As far as I understand, partitioning a table generates several tables. Isn't it possible to replicate just one of them on a remote server? – JuanV Mar 07 '16 at 06:22
  • No. Those tables cannot really be handled as separate tables. You can "export" a partition to remove it from the table, then treat that as a table. But that is several steps, and does not apply to the Replication you desire. – Rick James Mar 07 '16 at 17:59
  • 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? – JuanV Jun 07 '16 at 21:03
  • It is not possible to filter replication by `PARTITION`. – Rick James Jun 07 '16 at 21:57
  • Is it possible to synchronize a partition with a table within the same database (not a replicating slave)? – JuanV Jun 07 '16 at 22:30
  • No. You can replace a table or partition with the other. See "transportable tablespaces". – Rick James Jun 08 '16 at 00:40
  • Thank you but transportable tablespaces does not solve my problem. I need to minimize data traffic from a replicating table from the master to the slaves. That is why I was wondering if possible to replicate only partitions. For example, if there is an update on only one column of a record on the master, Do all data for all the colummns of the entire record travel to the slave or just the updated column? I know it may sound silly but I think I can deal with data traffic replicating with the whole table if I only update TinyInt columns. – JuanV Jun 08 '16 at 10:39
  • You can filter on database to limit what is coming out of the Master. But it applies to all Slaves. I have trouble imagining that you are replicating so much data that the network is a bottleneck. Please explain why your pipe is so narrow. – Rick James Jun 08 '16 at 17:14
  • The slave databases are running on embedded devices and the number of these grows over time depending on requirements. It is a three level tree topology where the master is the head and the slaves, at the second level, only need to handle the subsets of data of the third level devices which correspond to their branch. The system needs to be scalable so as more slaves are added, the replicated table becomes proportinally bigger. Is only one column in an specific row is updated in the master, Are all columns transmited to the slaves or just the updated one? – JuanV Jun 08 '16 at 20:45

1 Answers1

0

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.

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • I didnt describe it correctly when I wrote that the devices at the second level need to "handle" the data that corresponds to their branch. They actually need to store the data and pass it over RF to the end devices because end devices do not have enough computing power to support a database server. – JuanV Jun 09 '16 at 21:55
  • All data needs to be stored in one table at the Master, but there are not restrictions on adding more tables to the database if needed for replication. I expain it better in a figure: http://i.imgur.com/F66H1Vy.jpg – JuanV Jun 09 '16 at 22:16
  • Got it, Thank you for your help – JuanV Jun 13 '16 at 05:00