Simple mysql backup script for windows

This is a very simple batch script for mysql backup on windows.

@echo off
 
REM "MySQL username"
SET muser="dba"
 
REM "MySQL password"
SET mpass="password"
 
REM "Source destination for mysqldump (executable)"
SET mbackup="C:\My Documents\mariadb-5.5.29-win32\bin"
 
REM "Backup destination for mysql"
SET mysqlbackup="C:\My Documents\backup\"
 
REM "Exclude database backup"
SET dbname="('information_schema','performance_schema')"
 
REM "Dump database to file"
for /f %%i in ('"%mbackup%\mysql -u%muser% -p%mpass% -e "select distinct table_schema from information_schema.tables where table_schema NOT IN %dbname%" --skip-column-name "') do (
    %mbackup%\mysqldump %%i -u%muser% -p%mpass% > %mysqlbackup%%%i.sql
)

It can be automated using scheduler in windows.

Comments

We would recommend our backup tool [MySql Backup And Ftp (MySqlBF)][1] or [MySql Backup Online][2] service. MySqlBF allow to schedule backups using a Windows Task Scheduler or a custom windows service for a complex backup scenarios.

It is support connecting securely over an SSH connection and have a such unique feature as to create backups via phpMyAdmin. Tool allow to archive, encript and save backups to HDD, FTP, Network or most popular cloud storage services (Dropbox, Amazon S3, Google Drive, SkyDrive, Box) and send email notifications on success or failture.

Hope that will help you.

[1]: http://mysqlbackupftp.com/
[2]: http://mysqlbackuponline.com/

I see that it is a very usefull script, but can you please explain me how it works??

@echo off

REM "MySQL username"
SET /p muser="MySQL username:" %=%

REM "MySQL password"
SET /p mpass="MySQL password:" %=%

REM "Source destination for mysqldump (executable)"
SET /p mbackup="Source destination for mysqldump (executable)(like C:\Program Files\MySQL\MySQL Server 5.0\bin):" %=%

REM "Backup destination for mysql"
mkdir C:\SQL_DUMP_%date%
SET mysqlbackup="C:\SQL_DUMP_%date%"

REM "Database Name"
SET /p dbname="Database Name:" %=%

REM "Dump database to file"
for /f %%i in ('"%mbackup%\mysql -u%muser% -p%mpass% -e "show tables from %dbname%""') do (
%mbackup%\mysqldump -u%muser% -p%mpass% --add-drop-database=FALSE --tz-utc=FALSE --order-by-primary=TRUE --default-character-set=utf8 --max_allowed_packet=1G --host=localhost --hex-blob=TRUE --allow-keywords=TRUE --lock-tables=FALSE --no-create-db=TRUE --comments=FALSE --complete-insert=TRUE --extended-insert=FALSE %dbname% %%i > %mysqlbackup%%dbname%_%%i.sql
)

Add new comment