Any way to disable foreign key checks with mysqlimport tab option?

Question

I asked this question before and I am just coming back to the issue.

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.

The answer on the linked question works when importing SQL files (the table structure), but I also get the foreign key constraint error when importing some tables (because the table has a foreign key to itself). The two commands are like this:

cat <(echo "SET FOREIGN_KEY_CHECKS=0;") "table.sql" | mysql [user/pass] database
mysqlimport [user/pass] --local --fields-terminated-by="|" database "table.txt"

It looks like this has been requested years ago on their bug tracker but never implemented. Is there any way around it?

Note that I want to continue using the tab option for various reasons (it's faster, and works better with git as each row is on a separate line).

Frail

Answer 1

You can always set temporarily the mysql global system variable 'foreign_key_checks' to off/zero value by following command line before running the database creation script.

mysql --host=localhost --user=USER --password -e "SET GLOBAL foreign_key_checks=0"

It is not secure to type the password on the command line. Omitting it and only specifying --password rather than --password=MYPATH will force the system to ask the user for it before proceeding.

Subclavius