Don’t ask me why scripting in batch is still necessary on the verge of the year 2010, however, what can you do when some clients still use Windows to host their MySQL servers…
REM Simple MySQL backup script per database
@echo off
REM Set some variables
set mysqlcmd="C:\Program Files\MySQL\MySQL Server 5.0\bin\mysql.exe"
set mysqlpwd=amazingsecretsamplepassword
set mysqlconnect=%mysqlcmd% -u root --password=%mysqlpwd%
set mysqldumper="C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqldump.exe" -v -u root --password=%mysqlpwd%
set backupdir="d:\backup_mysql"
set logfile="d:\installs\backup_script\backup_log"
REM Loop over list of databases and dump
date /t > %logfile%
time /t >> %logfile%
echo Starting Script Run >> %logfile%
for /f %%i in ('"%mysqlconnect% -e "show databases" --skip-column-names"') do (
echo ---------------------------------------------- >> %logfile%
date /t >> %logfile%
time /t >> %logfile%
echo Now handling %%i >> %logfile%
%mysqldumper% %%i > %backupdir%\%%i.sql 2>> %logfile%
)
@echo off
REM Set some variables
set mysqlcmd="C:\Program Files\MySQL\MySQL Server 5.0\bin\mysql.exe"
set mysqlpwd=amazingsecretsamplepassword
set mysqlconnect=%mysqlcmd% -u root --password=%mysqlpwd%
set mysqldumper="C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqldump.exe" -v -u root --password=%mysqlpwd%
set backupdir="d:\backup_mysql"
set logfile="d:\installs\backup_script\backup_log"
REM Loop over list of databases and dump
date /t > %logfile%
time /t >> %logfile%
echo Starting Script Run >> %logfile%
for /f %%i in ('"%mysqlconnect% -e "show databases" --skip-column-names"') do (
echo ---------------------------------------------- >> %logfile%
date /t >> %logfile%
time /t >> %logfile%
echo Now handling %%i >> %logfile%
%mysqldumper% %%i > %backupdir%\%%i.sql 2>> %logfile%
)
As this was a very quick and dirty script, I won’t go into detail, as it should be quite self explanatory – I think the only interesting part here is the stderr redirection to the logfile, that actually works in windows as should be expected!
See you soon!
Just a little contribution.
In windows 2008 just worked using “Progra~1″ instead “Program Files”
thanks a lot for the excellent batch.
Link | September 16th, 2010 at 18:08
Interesting! I would have thought (and from what I recall when using it) that the quotes would have solved that issue…
Thanks!
Link | March 1st, 2011 at 15:45