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