MySQL – Incomplete Mysqldump Files Inside For Loop

mysql-5.7mysqldumpwamp

I am running a Windows 10 Machine with WAMP installed. I have also a Git Bash Installed. I was trying to backup all my databases in each separate file using the code below. 

for DB in $(mysql -uroot -proot -e 'show databases'); do mysqldump -uroot -proot --complete-insert $DB > "$DB".sql; done;

The Problem is I am only getting incomplete dump files for all databases except the last one. The last dump is complete.

I have tried gzip the sql file by the following command 

for DB in $(mysql -uroot -proot -e 'show databases'); do mysqldump -uroot -proot --complete-insert $DB > "$DB".sql; [[ $? -eq 0 ]] && gzip "$DB".sql; done;

Got the same result. Only the last database is getting dumped and gzipped completely.

Note: It seems the next iteration happens before the previous finishes. I have tried using wait and sleep inside but nothing works. I have also tried using mysqldump options –single-transaction, –force, –quick but none seem to work.

Best Answer

I have created a batchfile which runs under Windows. It will create the .sql files in the current directory. I left out Zipping the output.

The parameter '-N' will delete the header in the output from "show databases".

@echo off

set mysql=C:\Program Files\MySQL\MySQL Server 8.0\bin\
set mysql=c:\progra~1\mysql\mysqls~2.0\bin\
set password=********

FOR /F "usebackq" %%F IN (`%mysql%mysql -N -uroot -p%password% -e "show databases"`) do (
    ECHO %%F
    "%mysql%mysqldump" -u root -p%password% %%F >%%F.sql
)

I only got 1 error running this script (besides the warnings... ?):

performance_schema
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: Got error: 1142: SELECT, LOCK TABLES command denied to user 'root'@'localhost' for table 'accounts' when using LOCK TABLES
Related Question