Backup MySQL database
Ive logged this one for future reference, its a little batch file used to automate the mysqldump command. It uses the 7-zip utility to compress the sql files http://www.7-zip.org.
If you want to do this in 'nix simply write a BASH script using the tar tool to compress the sql files, and add the script to your cron list.
#!/bin/sh
mysqldump -uroot -ppwd --opt db1 > /sqldata/db1.sql
mysqldump -uroot -ppwd --opt db2 > /sqldata/db2.sql
cd /sqldata/
tar -zcvf sqldata.tgz *.sql
rem -----------------------------------------------------------
rem backup.bat - No Zip File
rem Filename: C:\DATABASE-2003-09-23-1100.sql
rem -----------------------------------------------------------
@ECHO OFF
FOR /F "TOKENS=2-4 DELIMS=/ " %%A in ('Date /T') DO SET DV=%%C-%%A-%%B
FOR /F "TOKENS=5-6 DELIMS=:. " %%D IN ('ECHO.^|TIME') DO SET TV=%%D%%E
SET SAVPATH=C:\
SET FILENAME=DATABASE-%DV%-%TV%.sql
C:\MySQL\bin\mysqldump.exe --quote-names --opt -uuser -ppassword database > %SAVPATH%%FILENAME%
rem -----------------------------------------------------------
rem backup.bat - Zips SQLFile(s) using 7zip
rem Filename: C:\SQL-2003-09-23-1100.zip
rem -----------------------------------------------------------
@ECHO OFF
FOR /F "TOKENS=2-4 DELIMS=/ " %%A in ('Date /T') DO SET DV=%%C-%%A-%%B
FOR /F "TOKENS=5-6 DELIMS=:. " %%D IN ('ECHO.^|TIME') DO SET TV=%%D%%E
SET SAVPATH=C:\
SET FILENAME=SQL-%DV%-%TV%.zip
C:\MySQL\bin\mysqldump.exe --quote-names --opt -uuser -ppassword database > database.sql
C:\MySQL\bin\mysqldump.exe --quote-names --opt -uuser -ppassword database > database.sql
C:\MySQL\bin\mysqldump.exe --quote-names --opt -uuser -ppassword database > database.sql
IF EXIST %SAVPATH%%FILENAME% (
del %SAVPATH%%FILENAME%
C:\Progra~1\7-ZIP\7zn a -tzip %SAVPATH%%FILENAME% -mx *.sql
) ELSE (
C:\Progra~1\7-ZIP\7zn a -tzip %SAVPATH%%FILENAME% -mx *.sql
)
DEL *.sql
