Script for MySQL backup to multiple files

Question

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?

Did you copy the mysql parts from a bash/zsh script? `| while read dbname;` is not valid syntax in Windows `cmd.exe` — Mathias R. Jessen, Mar 18 '16 at 18:19
Yes, I copied on the link mentioned above. As I found references to the use of the 'while' ([see here](http://ss64.com/ps/while.html)), I thought it would work. — Rogério Arantes, Mar 18 '16 at 18:58

score 1 · Accepted Answer · answered Mar 18 '16 at 18:34

1

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
}

answered Mar 18 '16 at 18:34

Ansgar Wiechers

193,178
25
254
328

1

I tried to eliminate the use of -u and -p, as [this post] (http://stackoverflow.com/questions/9293042/mysqldump-without-the-password-prompt), but without success. What do you suggest me? – Rogério Arantes Mar 18 '16 at 20:47
Please do not move the target. If you have a new or followup question: post a new question. Show what you have tried and the results that yielded. – Ansgar Wiechers Mar 18 '16 at 21:20

score 0 · Answer 2 · answered Jul 30 '17 at 14:08

Example using an input from file result of mysqldump.

#Variables#####
# Debe especificar la ruta completa, por ejemplo C:\Temp\archivo.txt
$file = $args[0]
$c = ""
$i = 0
$startSafe = 0;
###############

New-Item -ItemType Directory -Force -Path .\data
$x = 0;
foreach ($f in [System.IO.File]::ReadLines($file)) {
    if ($f -like '-- Dumping data for table *') {
        $startSafe = 1;
        $x = $f.split('`')[1];
        write $x;
    }
    if ($startSafe) {
        if($f -eq "UNLOCK TABLES;"){$i += 1; $f >> .\data\$x.out.sql; $startSafe = 0; $x = $i}
        if($f -ne "UNLOCK TABLES;"){ $f >> .\data\$x.out.sql;}
    }
}

Script for MySQL backup to multiple files

2 Answers2

Linked