MariaDB Replication on selected columns

Question

I would like to ask a question that I am sure someone who is a lot smart then me can answer.

I have a main MariaDB that holds all my tables and columns etc (total of 64 tables) and this is stored on my server called dbserver

I then have 2-40 local servers running on each computer in the customers shop (Linux Point of sale systems)

But the local databases don't have as meany tables and the main server and don't have as meany columns in each table (the local PoS database holds information JUST needed for the PoS software so for example would be the supplier details would not be stored on the PoS database)

Would replication still work in this case as I would need to only replicate data that is needed for the PoS system or am I better off running my own software that pulls data from the server?

I look forward to what you all recommend and I'm new to replication (at the moment we are only syncing database at the start of the day so if anything is added during the opening hours of the store each PoS terminal would need to be rebooted to re sync the data)

All my thanks in advance Andy

Answer 1

If this is still being built then the easiest way to solve the problem would be to split the data into separate databases. MyQL replication operates at the database level. But do be aware that async replication only works between two master nodes (and even then there can be race conditions). You certainly cannot selectively replicate individual attributes using mysql replication (nor should you - this implies the schema is not normalized).

However, I don't think that mysql replication is a good model for either publishing "static" data to satellite sites nor for aggregating transactional data from satellite sites. It is intended for maintaining a replica of a database.