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.

mysql> is the prompt. Typing in -u at that point makes no sense. Likewise, you need to use mysql ... < restore.sql to properly restore, you can't just run two commands like that. — tadman
– tadman, Commented Jul 14, 2020 at 17:17
"C:\MySQL\MySQL Server 5.7\bin\mysql.exe" -uroot database_name -pmypassword < E:\replication\DestinationTest\Db.sql — Nadeem Taj
– Nadeem Taj, Commented Jul 27, 2020 at 8:38

srikanth · Accepted Answer · 2020-07-20 17:26:34Z

0

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.

answered Jul 20, 2020 at 17:26

srikanth

111 bronze badge

Sign up to request clarification or add additional context in comments.

1 Comment

SaraDob Over a year ago

-D DB in the restore statement, DB after - D is the database im specifying.

Rick James · Accepted Answer · 2020-07-20 19:50:38Z

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.

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related