CODEWITHSUNDEEP
mysql
Slowcoder
Slowcoder

Reputation: 2120

Restore database in mysql command line

I have taken a backup of a database from one server and trying to restore it in another server.

Mysql version is 5.5

When I try to restore the database using the following command, screen -r

mysql -u root -p password mydb < mydump.sql
ERROR 1005 (HY000) at line 356: Can't create table 'mydb.mytable' (errno: 150)

I understand this is foreignkey constraint problem. The dump file has the following statement inside.

/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

But it is still failing. My dumo file is very large is size, so opening it and editing is not possible. So instead of adding SET FOREIGN_KEY_CHECKS=0; in the dump, can I directly set it inside the mysql commandline like the following?

mysql> SET FOREIGN_KEY_CHECKS=0;
...
mysql> source "mydump.sql";
...
mysql> SET FOREIGN_KEY_CHECKS=1;

Will it work? My database reload takes hours to complete. So I am asking the help here before spending hours on this.

Thanks for the help.

Upvotes: 4

Views: 11161

Answers (1)

fenway
fenway

Reputation: 446

Usually, I'll just pipe in 2 sql files -- first, a one-liner with SET FOREIGN_KEY_CHECKS=0;, and then dump file. You needn't worry about setting it back; it will only last for the current session (which will terminate when the files have been loaded).

FYI, you can also use sed -i 1i"SET FOREIGN_KEY_CHECKS=0;" dump.sql if you want to permanently prepend this line without visually editing the file.

Upvotes: 1

Related Questions