0

Hi I would like to create a multi source replication topology where a few different master databases replicate to one big slave database. I want to know for each and every row on the slave from which master it came from. My first instinct would be to add a "system_id" column on each master table but I wonder if there is a better way to track the origin of the rows.

Thanks

Improve this question
1
  • Can you use a 64 bit INTEGER and take the first, say 5 (for 16 masters) bits of those 64 for the master and then have a 59 bit INTEGER as your PK in each centre?
    – Vérace
    Commented Oct 22, 2017 at 16:26

1 Answer 1

Reset to default
0

Are the rows going into the same table(s)? If so, your suggestion of an extra column from the Master is probably the only way.

Or are the rows going into different databases (or tables)? If so, then nothing is needed -- the different dbs/tables provide the clue.

Another approach may be "replicate rewrite" -- this turns same table into different table. If you then need to "combine" the data (on the unified Slave), use UNION ALL and/or a VIEW. See here.

Improve this answer
2
  • Thanks for the answer. The rows are going into the same tables. The database schema will be the same as in the master. Data will come from an (theoretically) unlimited number of systems thus creating a separate table for each system seems not to be the best solution.
    – Mati
    Commented Nov 5, 2017 at 14:17
  • Only rarely is it wise to have multiple tables with 'identical' schema. Keep in mind that that one Slave is probably the bottleneck -- all writes to all Masters must be replayed on that one Slave. For data collection, I would focus on how to get lots of clients writing to a single Master. Here are some of my thoughts on such: mysql.rjweb.org/doc.php/staging_table
    – Rick James
    Commented Nov 5, 2017 at 19:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.