Mysql Dailybakup of all databases with same name one by one using script on windows
Justin Jacob on January 12th, 2012
MySQL backup of all database is done by mysqldump with –all-database,but using this method we can only backup all db to one sqldump file,here we use one script to backup all database with same name,
@ECHO OFF
SET BACKUPS_DIR=E:\mysqldailybackup
SET MYSQL_DIR=D:\Program Files\MySQL\MySQL Server 5.0\bin
SET USER=”root”
SET PASSWORD=”root”
SET PORT=”3306″
REM ============================
SET OLDDIR=%CD%
CD %TEMP%
REM ====== Get current time =======
For /f “tokens=2-4 delims=/ ” %%a in (‘date /t’) do (set dt=%%c-%%a-%%b)
For /f “tokens=1-4 delims=:.” %%a in (‘echo %time%’) do (set tm=%%a%%b%%c%%d)
SET TODAY=%1 %dt% %tm%
REM =====Get a list of all databases hosted on the server =====
“%MYSQL_DIR%\mysql” -u %USER% -p%PASSWORD% -P %PORT% -B -s -e”show databases” > mysqldblist.tmp
FOR /F %%D IN (mysqldblist.tmp) DO (
ECHO Creating backup for database ”%%D”
“%MYSQL_DIR%\mysqldump” –routines -u %USER% -p%PASSWORD% -P %PORT% –result-file=”%BACKUPS_DIR%\%%D.%TODAY%.sql” “%%D”
)
DEL mysqldblist.tmp
CD %OLDDIR%
REM ====Delete 2 days old sql dump files =====
forfiles /p %BACKUPS_DIR% /s /m *.sql /d -2 /c “cmd /c del @file : date 2days”
Change the BACKUPS_DIR,MYSQL_DIR,user,password,port