Mysql – Use federated table to sync only selected columns

federated-tableMySQL

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?

Best Answer

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.

Related Solutions

Mysql – How to change data structure in thesql using thesqldump without deleting files

As long as you don't rename tables and columns and you are not using partitions you might use schemasync .

Basic usage is:

schemasync mysql://user:pass@dev-host:3306/dev_db mysql://user:pass@prod-host:3306/prod_db

This will produce two SQL scripts - one for "patching" the production database, and another one for reverting the changes.

Please note that schema altering will lock the tables being affected, so you will have some downtime anyway. There are tools to avoid this: pt-online-schema-change from Percona and oak-online-alter-table from openarkkit.

Also, as with any possibly destructive changes, test the procedure on a staging (or other non-production use, but production data and schema) database.

Mysql – pt-table-sync error: Called not_in_left in state 0

You could try:

Checking the MySQL error log after you attempt the sync. It may reveal some consistency issues with the table that you weren't aware of
Working around this table for now (using --exclude-table), and then coming back to it later
Trying different combinations of checksum options (--algorithms, --chunk-size) for the problem table - this might fix the problem, or shed more light on the underlying issue

I received this error recently, and the underlying issue turned out to be a corrupt table in need of repair on the master server.

I discovered the problem by changing the behaviour of pt-table-sync to using the "nibble" algorithm for my problem table (--algorithms nibble). Instead of the cryptic error, I got a specific error along with the SQL Statement that caused it, when I then attempted to execute directly on the server. This led me straight to my problem.

I could have also checked the error log and discovered the same thing.

Alternatively, you could try changing algorithms or reducing the chunk size from 1,000 (e.g. --chunk-size 100). This may require a bit of trial and error, and it's likely to slow down the checksums significantly, which is why I suggest skipping your largest tables to start with.

For a sufficiently large table (and 57GB certainly qualifies) with a poorly distributed primary key, you might also experience problems with query or wait timeouts depending on your MySQL configuration - I'm not sure if this would also result in the same error. The pt-table-sync documentation offers a little bit more information on this.