Above all, this blogpost is a reminder for myself.

Yesterday I rehoused some Magento based online-shops to a new server. While restoring the database on the new server I had some trouble with the foreign keys.

Here are the few steps to backup and restore databases with foreign keys on console.
Not only for Magento databases.

Create the backup file:

$ mysqldump -u MY_DATABASEUSER -p MY_DATABASE > backup.sql

After entering the database password, the backup file will be created.

Restore the backup file with an existing database:

Open the MySQL shell on the existing database.

$ mysql -u MY_DATABASEUSER -p MY_DATABASE

In shell mode enter:

SET FOREIGN_KEY_CHECKS=0;
SOURCE /pathToFile/backup.sql;
SET FOREIGN_KEY_CHECKS=1;

Done.

Create a new database and import the backup:

Open the MySQL shell.

$ mysql -u MY_DATABASEUSER -p

In shell mode:

CREATE my_database default character set UTF8;
USE my_database;
SET FOREIGN_KEY_CHECKS=0;
SOURCE /pathToFile/backup.sql;
SET FOREIGN_KEY_CHECKS=1;

Done.