Batch-file for mysqldump to backup each database into a separate file

Question

Trying to create a batch (cmd) file for backing up each database into a separate file. Databases are created/deleted often, so batch file needs to grab current db names everytime it runs and backup each one of them.

Here is how I want it to be:

mysql -e "show databases" -u root --password=1234
mysqldump %dbname% -u root --password=1234 > S:\Backup\MySQL\%dbname%.sql

Is it possible to do in a batch file?

Please help. Thanks.

Answer 1

This can be run directly in cmd (I wrapped the line but it should not be wrapped):

mysql.exe -uroot -p1234 -s -N -e "SHOW DATABASES" |
  for /F "usebackq" %D in (`findstr /V "information_schema performance_schema"`)
    do mysqldump %D -uroot -p1234 > S:\Backup\MySQL\%D.sql

In a batch file you will need to escape % with an additional %, that is use %%D.

Batch File

mysql.exe -uroot -p1234 -s -N -e "SHOW DATABASES" |
  for /F "usebackq" %%D in (`findstr /V "information_schema performance_schema"`)
    do mysqldump %%D -uroot -p1234 > S:\Backup\MySQL\%%D.sql