Search code examples
mysqldatabase-replicationpercona

MySQL Replication - Percona Toolkit Table Sync - Foreign Key Constraint Fails

I am trying to keep my development database up-to-date with data from my production database. I discovered pt-table-sync in the Percona Toolkit.

When I run it, I frequently get the error Cannot add or update a child row: a foreign key constraint fails. This happens on tables that are frequently updated and have foreign keys.

Is there a way to make use of this tool that avoids this problem? Some other tool I'm missing? The database is quite large. The largest table has nearly eight million rows.

Solution

  • You can temporarily disable foreign key checks on your local dev database:

    mysql> SET GLOBAL FOREIGN_KEY_CHECKS=0;

    Then run pt-table-sync to change data so it's back in sync with your production database.

    Don't forget to reactivate the FK checks:

    mysql> SET GLOBAL FOREIGN_KEY_CHECKS=1;

    Edit: Paul Campbell rightly points out in the comments above that pt-table-sync has a --no-foreign-key-checks option. This disables FOREIGN_KEY_CHECKS in the session for the pt-table-sync connection.

    But honestly, I wouldn't use pt-table-sync. Here's what I would do:

    Take a full backup of my production database and download the whole backup to my dev local platform. Use Percona XtraBackup to acquire a physical backup of production.

    Then I can reinitialize the dev database repeatedly using the backup:

    1. Shut down MySQL Server
    2. Copy the backup files over the MySQL datadir
    3. Start MySQL Server

    You may reinitialize your dev database this way many times for each backup you copy from production. You could acquire a new backup once a day.

    As a side benefit, you'd be in the habit of backing up your database regularly (which you should do anyway).

    Whereas using pt-table-sync would require reading data from your production database every time you reinitialize your dev database.

    I don't consider tables of 8 million rows to be very large.