2

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?

  • Are you loading the dump manually on both systems? Or is the slave pulling the dump via replication and failing to replicate the binary log? – Shane Madden Mar 8 '11 at 17:13
  • I loaded it on to just the master and the slave pulled it over via replication. The error was presented on the slave with show slave status. – txyoji Mar 8 '11 at 18:31
4

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/

  • Thank Ben. Didn't think of manually disabling the checks on the slave. – txyoji Mar 16 '11 at 13:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.