Windows: Backup all databases in a MySQL server.

backup-mysql.bat

@ECHO off

SETLOCAL ENABLEDELAYEDEXPANSION

REM Windows Batch script to backup all mysql databases in a server
REM @author: Lenin Meza <lenin.meza.externo@prodecon.gob.mx>

REM Database information
SET dbhost="10.0.0.2"
SET dbuser="exporter"
SET dbpass="password"
SET dbport="3306"

REM Paths
SET startdir=%cd%
SET binaries="C:\wamp64\bin\mysql\mysql5.7.9\bin"
SET destination="E:\Backups"

REM Dates
SET DAYMONTHYEAR=%DATE:/=-%
SET HOUR=%TIME:~0,2%
IF "%HOUR:~0,1%" == " " SET HOUR=0%HOUR:~1,1%
SET MINUTE=%time:~3,2%
SET SECOND=%time:~6,2%

SET _date="%DAYMONTHYEAR%_%HOUR%-%MINUTE%-%SECOND%"
ECHO "Fecha del respaldo"
ECHO %_date%

REM Final directory
IF NOT EXIST %destination%\%_date% mkdir "%destination%\%_date%"

REM MySQL Binary files
:: cd %binaries%
mysql --port=%dbport% --host=%dbhost% --user=%dbuser% --password=%dbpass% -s -N -e "SHOW DATABASES" | for /F "usebackq" %%D in (`findstr /V "information_schema performance_schema"`) do (
  :: Dump each database in separate files
  mysqldump --no-data --port=%dbport% --host=%dbhost% --user=%dbuser% --password=%dbpass% %%D > "%destination%\%_date%\%%D_structure.sql"
  mysqldump --lock-tables=false --no-create-info --port=%dbport% --host=%dbhost% --user=%dbuser% --password=%dbpass% %%D > "%destination%\%_date%\%%D_data.sql"
)

ECHO "Se ha completado el respaldo! :-O"

cd %startdir%

@pause

REM Batch-file for mysqldump to backup each database into a separate file
REM http://stackoverflow.com/a/9749003/1006079

:: Comments with REM
:: http://stackoverflow.com/a/12408045/1006079

REM How to automatically backup all MySQL databases zip them and delete backups older than n days on windows with a batch file
REM http://www.redolive.com/utah-web-designers-blog/automated-mysql-backup-for-windows/

:: Create folder with batch but only if it doesn't already exist
:: http://stackoverflow.com/a/4165472/1006079

:: Windows Batch File (.bat) to get current date in MMDDYYYY format:
:: http://stackoverflow.com/a/203116/1006079

REM Windows command-line: create a file with the current date in its name
REM http://superuser.com/a/47889/275986

:: Need leading zero for batch script using %time% variable
:: http://serverfault.com/a/220462/343812