Mysql – How to load a database with foreign keys on the master without out breaking replication

innodbMySQLmysql-replication

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?

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/

Related Solutions

MySQL Incremental backups with Master-Slave replication

Well a packed question, let me try to answer all the issues you raised there.

Binary backups vs dumps

Right now you're using mysqldump to do full backups of your database, that's a good practice indeed but it can get a bit endearing when dealing with huge MySQL installations. Binary backups are good point in time backups, but you need two parts, one full binary backup (which needs to be consistent) and the binary diff logs. Of course the time for recovery for one an the other is drastically different too, it all depends as said on the size of your database and your turnover times.

How flushing the binlogs will affect replication

It won't affect replication if you flush the logs always after they've been transmitted to the slave, otherwise you'll run into some nasty issues due to the slave not being able to request the exact block of the binlog that it needs

Mysql – Do InnoDB tables got locked during thesqldump when mixed with MyISAM

mysqldumps with InnoDB and MyISAM together are treated as mutually exclusive. Here is why:

If you can login to mysql while a mysqldump is in progress, you will see something like this:

SELECT /* SQL_NO_CACHE */ * FROM tblname

By default, mysqldump will do the following:

Every database is dumped in alphabetical order
Every table dumped per database is dumped in alphabetical order (regardless of storage engine)

This should be fine for a MySQL Instance that has no other database activity. InnoDB tables and MyISAM tables do not affect each other.

Using --single-transaction against an all-InnoDB MySQL Instance creates a checkpoint and dumps all tables from the same point-in-time. Once a MyISAM table is encountered, all bets are off. It could cause all InnoDB tables after the MyISAM to be dumped from a different point-in-time.

To have a consistent point-in-time dump for a mixture of InnoDB and MyISAM you have there options

OPTION #1

Restart mysql so that no one else can login via TCP/IP and then mysqldump

service mysql restart --skip-networking --skip-grant-tables
mysqldump --routines --triggers --all-databases > MySQLData.sql
service mysql restart

OPTION #2

If all MyISAM tables are for reading only, just mysqldump using --single-transaction

OPTION #3

If any MyISAM tables are are being written, --single-transaction is not enough

You will have do the following:

mysql -u... -p... -e"FLUSH TABLES WITH READ LOCK; SELECT SLEEP(86400)"
sleep 30
mysqldump --routines --triggers --all-databases > MySQLData.sql

Immediately after the mysqldump is done, login to mysql and do show processlist;. Look for the query SELECT SLEEP(86400), find process ID, and run KILL <procidnumn>;