Use federated table to sync only selected columns

Question

Scenario: I need to sync a table in a remote server with the table in my production server. The problem being that the table structures are not same.

I have looked into the concepts of federated table only to find out that the two tables are exactly same, structure wise, which is not the case here.

So basically, is there any way I can sync only few columns,the matching columns,of those tables?

Answer 1

The data in federated tables are not stored locally. These tables just provide a way to access the data on the remote server through a local table. So if you want to truly sync with a table on a remote server, then you need to actually copy the data across.

Option 1 - copy data using federated table and event scheduler

To do this, create the federated table with identical column definitions, and then e.g. enable the event scheduler and create an EVENT to sync from there to your local, non-identical table. The details of how you sync the two tables will depend on how the table is being modified on the remote server. (E.g. is it a table that is only ever INSERTed to, not UPDATEd? That will make your job easier. Or if it's UPDATEd, is there at least a timestamp column which will tell you when each row was last updated? In the worst case you'll have to delete all the local rows and re-populate the local table every time the EVENT runs. The EVENT code you write could involve a SELECT into a CURSOR and then a loop where you fetch from the cursor and INSERT/UPDATE to your local table.)

Option 2 - don't copy data, use federated table together with a view

Another option, if you don't mind that the data stays in the remote table, may be to create a local table with any extra columns you need that are not found in the remote table, and then create a VIEW that joins the two tables together. You can then name the view columns whatever you like. Then use this VIEW when you need to SELECT from the remote table. The disadvantage with this is that SELECTs against this view will be slower than using a purely local table, and also this isn't as robust because you depend on the remote database and the connection to it being available at all times.

Option 3 - use replicated table together with a view

A third option could be to use master-slave replication. Make your local database a slave and the remote a master. Then only replicate the particular table. Then create a VIEW similar to option 2.