Restoring mysql backup through windows batch file, which calls the .sql file

Question

Im trying to restore a mysql database from a back up. I was able to do it on command prompt directly using

mysql> -u username -ppassword < E:\replication\DestinationTest\restore.sql.

This worked fine.

I'm trying to put the same line in a .txt file, along with other mysql statements and call that. txt file from mysql command prompt.I have few delete staments and then the restore statement in the .txt file. It looks like this :

enter code here
Delete from Db.tbl1;
Delete from Db.tbl2;
Delete from Db.tbl3;
Delete from Db.tbl4;
Delete from Db.tbl5;

-h Server -D DB -u username -ppassword < E:\replication\DestinationTest\Db.sql;

When i call this above .txt file from mysql prompt, it executes the delete statements but errors out on the restore part. It says Unknown database 'ereplicationdestinationtestdb.sql'. Please let me know , what is the right way to do it.

Answer 1

Your delete statements are executing since each table is preceded by schema name like below.

Delete from Db.tbl1; Delete from Db.tbl2;

But please confirm if you have missed schema name to which the tables needed to be restored ? As per error you mentioned, It seems your restore.sql does not knows into which schema the tables need to be imported.

Answer 2

This is a "prompt, not a command:

mysql>

So this is invalid:

mysql> -u username -ppassword < E:\replication\DestinationTest\restore.sql.

Also, the period at the end is probably a typo.

This would be a potentially valid line in a batch file:

mysql -u username -ppassword < E:\replication\DestinationTest\restore.sql

If you want to do some other things in the batch file, then first build a text file with SQL statements, such as

Delete from Db.tbl1;
Delete from Db.tbl2;
Delete from Db.tbl3;
Delete from Db.tbl4;
Delete from Db.tbl5;

Put nothing else in that file. Let's call the files deletes.sql. Now, we will put the two together -- two lines:

mysql -u username -ppassword < E:\replication\DestinationTest\restore.sql
mysql -u username -ppassword < deletes.sql

PS: TRUNCATE TABLE Db.tbl4; runs faster than DELETE if you just want to remove all the rows.