Home / server / Questions / 244753
Accepted
txyoji
Asked: 2011-03-09 08:38:25 +0800 CST

How do you load a database with foreign keys on the master without out breaking replication?

  • 772

I have a MySQL master-slave setup. One master; One slave; Lots of databases.

I'm moving an app into this setup from third party. They provided a mysqldump file of the data and database structure. It contains a single database with innodb tables.

The dump file loads just fine on the master. But while is replicating to the slave, the slave gets hung up on a foreign key error.

It seems like the slave is ignoring the 'set foreign key checks' in the mysqldump.

How do I load this thing without breaking replication?

mysql mysql-replication innodb

1 Answers

  1. Best Answer

    Usually when you do a mysql load of a dump, you disable foreign key checks, then re-enable them, in fact it might do this by default. You simply need to do the same thing on the slave for the duration of the replication.

    This is an import export guide:
    http://technocation.org/files/doc/2010_06_BulkLoad.pdf

    Disabling foreign keys:
    http://gauravsohoni.wordpress.com/2009/03/09/mysql-disable-foreign-key-checks-or-constraints/

    • 4