The only way I can think of to achieve this is to create a separate schema and use triggers that selectively copy only the records you want into this new schema and replicate only this new schema. You will have to use binlog_format=ROW.

On Thu, 13 Jul 2023, 04:25 Andrew Gable via discuss, < discuss@lists.mariadb.org> wrote:

Hi Everyone,

I hope someone can guide me.

here is my issue

I have a central database that is running in my back office and each of my front end machines have a database that is used just for the front end.

most of the data that is stored in the back office is not needed on the front end but the product table is.

the product table hold more information then what is needed on the front end (the front end system does not need to know who the supplier is of the item for example)

so I have the following feilds on the front end database (called posdatabase) barcodenumber posdeacription pricetype salelocation Systemprice.

both database are using the same field names

is it possible to use replication on select Feilds on the master to the slave? I have seen on YouTube lots of videos showing replication of every table and field in a database but I don't need that)

what I want to do is that possible?

Thank you for your time with this matter

Andrew Gable Owner / Software developer algPoS,Northampton http://www.algpos.co.uk 01604 289431

Send from iPhone Email.

This e-mail is private, confidential and is for the intended recipient only. If misdirected, please notify the sender by telephone and confirm, by return e-mail, that it has been deleted from your system and any copies destroyed. If you are not the intended recipient you are strictly prohibited from using, printing, copying, distributing or disseminating this e-mail or any information contained in or attached to it.

_______________________________________________ discuss mailing list -- discuss@lists.mariadb.org To unsubscribe send an email to discuss-leave@lists.mariadb.org

On 13.07.2023 03:25, Andrew Gable via discuss wrote:

I have a central database that is running in my back office and each of my front end machines have a database that is used just for the front end.

most of the data that is stored in the back office is not needed on the front end but the product table is.

the product table hold more information then what is needed on the front end (the front end system does not need to know who the supplier is of the item for example)   so I have the following feilds on the front end database (called posdatabase) barcodenumber posdeacription pricetype salelocation Systemprice.

both database are using the same field names    is it possible to use replication on select Feilds on the master to the slave? I have seen on YouTube lots of videos showing replication of every table and field in a database but I don't need that)

It's not achievable directly. You could however use the approach suggested by Gordon: replicate into a hidden schema and transfer the fields by trigger from there to the schema used by the front end. On the other hand - you can create permissions on field basis. So if you create a database account for the front end, just deny it access to the extra fields.

But there is more than that. If your front ends have limited networking capabilities, then you want to restrict the amount of data that is written to the binary log. In general the replication slave (front end) transfers everything in the binary log and filters only later. That's why you should consider logging only needed tables. Of course this also limits the usefulness of the binary log for point-in-time recovery.

The are two sets of replication filters:

* binlog filters; those apply to what is written to the binary log * replication slave filters; those determine what the slave events from the shipped binary log the replication slave applies

Both filters are available on schema and/or on table basis. RTFM: https://mariadb.com/kb/en/replication-filters/

Finally: you can also combine both techniques. Use triggers on the database in the back office to create a copy of your data that contains only the fields needed by the front ends. Then use replication filters to replicate only those tables. This will give the best results in terms of network bandwidth used for replication. But it will also slightly increase load on the back office server.

HTH, XL