Backup MySQL database

Sep 17

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


Filed under:
Misc
Posted by:
James Griffin
Dated:
September 17, 2003