[SOLVED] Script for MySQL backup to multiple files

Script for MySQL backup to multiple files

I am creating a script to back up the MySQL running on a Windows 2012 server, using PowerShell. Unlike other tips found here, I want to generate a .sql file for each of the databases.

This post shows how to create multiple files. I adapted it to PowerShell:

Get-ChildItem -Path "$($MYSQL_HOME)\data" | cmd /C "$($MYSQL_HOME)\bin\ mysql -u -s -r $($dbuser) -p$($dbpass) -e 'databases show' '| while read dbname; cmd /C "$($MYSQL_HOME)\bin\mysqldump.exe --user = $($dbuser) --password = $($dbpass) --databases $dbname> $($BKP_FOLDER)\$dbname($BACKUPDATE).sql "

but it returns error in while.

What should I change so that you can generate multiple .sql, one for each database?

Solution

Your entire commandline is b0rken. Throw it away and start over. Try something like this:

Set-Location "$MYSQL_HOME\bin"
& .\mysql.exe -N -s -r -u $dbuser -p$dbpass -e 'show databases' | % {
  & .\mysqldump.exe -u $dbuser -p$dbpass --single-transaction $_ |
    Out-File "$BKP_FOLDER\${_}$BACKUPDATE.sql" -Encoding Ascii
}