0

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.

  • Which shell are you using? this does not look like a 'Windows Command Prompt' – Luuk Nov 16 '19 at 10:24
  • a wait, 'Git Bash', I cannot access MySQL from 'Git Bash'... 😁 – Luuk Nov 16 '19 at 10:38
  • I am running this command from Git Bash. I could successfully connect and dump single database. But the above command do not work as expected. I have added mysql in environment variable. – Harish ST Nov 16 '19 at 11:46
0

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
  • I will check and get back to you here. – Harish ST Nov 16 '19 at 12:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.