Disabling foreign key checks on the command line
MysqlCommand LineForeign KeysMysqlimportMysql Problem Overview
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?
Mysql Solutions
Solution 1 - Mysql
You can also use --init-command
parameter of mysql
command.
I.e.: mysql --init-command="SET SESSION FOREIGN_KEY_CHECKS=0;" ...
Solution 2 - Mysql
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.
Solution 3 - Mysql
Login to mysql command line:
mysql -u <username> -p -h <host_name or ip>
Then run
1 SET FOREIGN_KEY_CHECKS=0;
2.use database <database_name>
3 SOURCE /pathToFile/backup.sql;
4 SET FOREIGN_KEY_CHECKS=1;
Solution 4 - Mysql
Just another one to do the same:
{ echo "SET FOREIGN_KEY_CHECKS=0;" ; cat imports.sql ; } | mysql
Solution 5 - Mysql
Another way with .gz files:
gunzip < backup.sql.gz | mysql --init-command="SET SESSION FOREIGN_KEY_CHECKS=0;" -u <username> -p
Solution 6 - Mysql
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