Quick and Dirty Backup Script for MySQL on Windows
The following batch file is a simple backup script for MySQL. It exports all databases into a temporary SQL dump file and compresses it using 7Zip into a file with the current date as its name. It also automatically deletes backups that are older than 30 days.
Here is how to get started:
- Copy the batch file to a directory of your choice
i.e. C:/Program Files/Scripts - Place 7z.exe into the same directory or add it to the PATH environment variable
- Replace the placeholders YOUR_BACKUP_PATH and YOUR_ROOT_PASSWORD
- Add a scheduled task in Windows to run the batch file once a day
Feel free to modify the script to use a different packer tool or different output file names.
-
@ECHO OFF
-
-
set backuppath="YOUR_BACKUP_PATH"
-
set password="YOUR_ROOT_PASSWORD"
-
set daystokeep=30
-
-
REM get current date in YYYYMMDD format
-
for /F "tokens=1-4 delims=/ " %%I in ('date /t') do set curdate=%%L%%J%%K
-
-
REM export database into temporary file
-
mysqldump -u root -p%password% –all-databases > %backuppath%\backup.sql
-
-
REM compress and delete temporary file
-
start "Compressing Backup…" /min /wait 7z.exe a -bd -y -tzip -mx9 "%backuppath%\%curdate%.zip" "%backuppath%\backup.sql"
-
del %backuppath%\backup.sql
-
-
REM delete older backups
-
forfiles /P %backuppath% /S /M *.zip /D -%daystokeep% /C "cmd /C del /Q @PATH"
Your script is great for my local server. But I have remote sql servers in a bladecenter room. How can I edit your script to backup up remote dbs. For example one of the dbs is on 10.1.10.189. Would it be like so:
mysql -uUSER -p%password% -h10.1.10.189 DATABASENAME > %backuppath%\backup.sql
Or is there other commands/scripts?
Charles,
that command line should work, except that the ‘mysql’ should be a ‘mysqldump’, which is probably what you meant. The -h parameter specifies the host. You can also use -host=HOSTNAME as an alternative. Giving mysqldump a database name as in your case will only dump that particular database.
As for database security, I usually allow access only on localhost through named pipes. Exposing your MySQL over the internet is probably not a good idea. It might be better to run the backup script on the computers that are hosting the databases and having them copy out the results to a location on the local network or the internet instead.
Also, the user name and password will be sent as plain text. You may want to look into mysqldump’s -ssl option to encrypt the transfer.
Great thanks, yes I forgot mysqldump. I have the suse mysql only allowing certain IP access remotely, so I got that covered. Also, the SQL server is not directly exposed to the public. It’s just that the blades only have limited HD Space (scsi server rated), so I have to run the dump on the auto backup server. As far as the SSL, excellent recommendation. Would it look like this:
mysqldump -ssl -uUSER -p%password% -h10.1.10.189 DATABASENAME > %backuppath%\backup.sql
One more thing. The other reason for not running the dump and compression on the master sql server, is to save on resources. I definitely would like to use the ssl option. Can you please tell me if the above option will work?
Thanks
For SSL to work you need to have it enabled in MySQL. By default, it is off in the Windows version. You can check whether SSL is enabled by running the following SQL query on your database:
You will also need a valid SSL certificate on the server. For starters you could possibly use a self-signed certificate.
I have never used SSL with MySQL, but you should be able to find plenty of information on the internet.