Disabling foreign key checks on the command line

问题:

I have a backup script for my MySQL database, using mysqldump with the --tab option so it produces a .sql file for the structure and a .txt file (pipe-separated) for the content.

Some tables have foreign keys, so when I import it I'm getting the error:

ERROR 1217 (23000) at line 8: Cannot delete or update a parent row: a foreign key constraint fails

I know about using SET FOREIGN_KEY_CHECKS=0 (and SET FOREIGN_KEY_CHECKS=1 afterward). If I add those to each .sql file then the import works. But then obviously on the next mysqldump those get overwritten.

I also tried running it as a separate command, like below but the error comes back:

echo "SET FOREIGN_KEY_CHECKS=0" | mysql [user/pass/database] 
[all the imports]
echo "SET FOREIGN_KEY_CHECKS=1" | mysql [user/pass/database] 

Is there some other way to disable FK checks on the command line?

回答1:

You can do this by concatenating the string to the file inline. I'm sure there's an easier way to concatenate strings and files, but it works.

cat <(echo "SET FOREIGN_KEY_CHECKS=0;") imports.sql | mysql

I don't think you need to set it back to 1 since it's just one session.

回答2:

You can also use --init-command parameter of mysql command.

I.e.: mysql --init-command="SET SESSION FOREIGN_KEY_CHECKS=0;" ...

MySQL 5.5 Documentation - mysql options

回答3:

Just another one to do the same:

{ echo "SET FOREIGN_KEY_CHECKS=0;" ; cat imports.sql ; } | mysql

回答4:

Login to mysql command line:

mysql -u <username> -p -h <host_name or ip> Then run

1 SET FOREIGN_KEY_CHECKS=0;

2 SOURCE /pathToFile/backup.sql;

3 SET FOREIGN_KEY_CHECKS=1;

回答5:

Based off the comments and answers, I ended up using this for a zipped database import with both InnoDB and MyISAM:

{ echo "SET FOREIGN_KEY_CHECKS=0;SET UNIQUE_CHECKS=0;" ; zcat dump.gz ; } | mysql