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:

  1. Copy the batch file to a directory of your choice
    i.e. C:/Program Files/Scripts
  2. Place 7z.exe into the same directory or add it to the PATH environment variable
  3. Replace the placeholders YOUR_BACKUP_PATH and YOUR_ROOT_PASSWORD
  4. 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.

  1. @ECHO OFF
  2.  
  3. set backuppath="YOUR_BACKUP_PATH"
  4. set password="YOUR_ROOT_PASSWORD"
  5. set daystokeep=30
  6.  
  7. REM get current date in YYYYMMDD format
  8. for /F "tokens=1-4 delims=/ " %%I in ('date /t') do set curdate=%%L%%J%%K
  9.  
  10. REM export database into temporary file
  11. mysqldump -u root -p%password% –all-databases > %backuppath%\backup.sql
  12.  
  13. REM compress and delete temporary file
  14. start "Compressing Backup…" /min /wait 7z.exe a -bd -y -tzip -mx9 "%backuppath%\%curdate%.zip" "%backuppath%\backup.sql"
  15. del %backuppath%\backup.sql
  16.  
  17. REM delete older backups
  18. forfiles /P %backuppath% /S /M *.zip /D -%daystokeep% /C "cmd /C del /Q @PATH"
2010
02/06
CATEGORY
Databases
TAGS


5 comments
    • Charles3
    • February 8th, 2010 8:35am

    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.

    • Charles3
    • February 9th, 2010 6:24am

    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

    • Charles3
    • February 9th, 2010 6:29am

    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

  4. 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:

    show variables like 'have_openssl'

    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.

  1. No trackbacks yet.