How to automatically backup all MySQL databases zip them and delete backups older than n days on windows with a batch file

auto-mysql-backup-windows

How to automate MySQL backups with a .bat file on a Windows  server. Typically we have used the backup facility built into MySQL administrator, which unfortunately has now reached end of life.  While it worked well it had some major shortcomings. For example once you created a scheduled backup it would only include the databases you told it to at the time of creation.  Well, if you are like us and you constantly add new databases  the new ones simply wouldn't be backed up, Annoying. What replaced MySQL administrator?  Enter the new MySQL Workbench.  Great tool save one missing feature, they decided to no longer support scheduled MySQL backups.

Obviously there are lots of scripts out there and easier solutions for linux based systems. The only windows solutions seemed to use php which seemed overly complex, relied on unnecessary systems that could fail, and introduced security concerns when you could use a simple batch file with a few lines of code to accomplish the same task.  I had little luck Googling solutions on how to backup all the databases individually, zip them into one file, and delete the older archives so I sat down and wrote my own batch file.  It was so useful to us that I feel compelled to share it with others. It should work on any version of windows x86 or x64 and any version of MySQL, but it was only tested on 2008R2.

Platforms Tested: Windows 2008 R2 x64 En with MySQL 5.5.x   AND Windows 2012 Server with MySQL 5.6.x

Updated 11.7.2012

Features:

    • Backup all MySQl databases, including all newly created ones automatically
    • Create an individual .sql file for each database  (God send when restoring)
    • ZIP all the .sql files into one zip file and date/timestamp the file name to save space
    • Automatically delete MySQL backups older than n days (set to however many days you like)
    • FTP your backup zip to a remote location
    • Highly suggest you also setup a scheduled task to backup your MySQL directory and your new backup folder to an off site location

Setup Instructions:

  1. Right click and edit  mysqlbackup.bat file in notepad
  2. Set the backupdate format, whatever your preference,  mine is yyyy-mm-dd-m-s
  3. Set the root u/p
  4. Set the MySQL data directory to match your install
  5. Set the path to mysqldump.exe to match your install path
  6. Set the destination of the backups should go, make sure there are write permissions obviously
  7. Set the path to your zip application with it's flags/commands to zip an item, I am using the command line version of 7zip which is free.
  8. Update the path where your backups will be saved and then deleted once zipped
  9. Set the number of days to keep backups, using the win program  "Forfiles" for this,   mine is set to 30 days  "-30"
  10. Test your batch file on a dummy directory.  You'll see the backup directory fill up with .sql files, then a timestamped zip file is made, and the directory is cleared.  Put some files older than 30 days in there and they will be wiped at the end.
  11. Finally create a scheduled task in windows to run the batch file on a schedule, remember to choose "Run whether user is logged on or not" otherwise it will fail.
  12. IMPORTANT NOTE:  updated 3.14.2013  if you get an error in the command prompt stating "mysqldump: unkown option '--no-beep' this is due to your my.ini file having an invalid option under [client].  Open your my.ini file find the [client] section and comment  out #no-beep with a hash, re-run the bat file and it will work. This error has nothing to do with this script, you'd get the same error if you ran mysqldump.exe directly. I believe that MySQL Admin adds that line to the ini file when installed, thanks Oracle.
  13. 80% of the "Didn't work for me" comments tend to be resolved simply by double checking that your directory paths are correct.

To save you some time I zipped up all the necessary files including the 7zip.exe app with the folder structure to match the batch file.  Simply unzip and update your specific paths in the .bat file and you're done.

Here is a quick look at the .bat file:

:: Auto MySQL Backup For Windows Servers By Matt Moeller  v.1.5
:: RED OLIVE INC.  - www.redolive.com

:: Follow us on twitter for updates to this script  twitter.com/redolivedesign
:: coming soon:  email admin a synopsis of the backup with total file size(s) and time it took to execute

:: FILE HISTORY ----------------------------------------------
:: UPDATE 11.7.2012  Added setup all folder paths into variables at the top of the script to ease deployment added debug notes in various areas
:: UPDATE 7.16.2012  Added --routines, fix for dashes in filename, and fix for regional time settings
:: UPDATE 3.30.2012  Added error logging to help troubleshoot databases backup errors.   --log-error="c:\MySQLBackups\backupfiles\dumperrors.txt"
:: UPDATE 12.29.2011 Added time bug fix and remote FTP options - Thanks to Kamil Tomas
:: UPDATE 5.09.2011  v 1.0

:: If the time is less than two digits insert a zero so there is no space to break the filename

:: If you have any regional date/time issues call this include: getdate.cmd  credit: Simon Sheppard for this cmd - untested
:: call getdate.cmd

set year=%DATE:~10,4%
set day=%DATE:~7,2%
set mnt=%DATE:~4,2%
set hr=%TIME:~0,2%
set min=%TIME:~3,2%

IF %day% LSS 10 SET day=0%day:~1,1%
IF %mnt% LSS 10 SET mnt=0%mnt:~1,1%
IF %hr% LSS 10 SET hr=0%hr:~1,1%
IF %min% LSS 10 SET min=0%min:~1,1%

set backuptime=%year%-%day%-%mnt%-%hr%-%min%
echo %backuptime%

:: SETTINGS AND PATHS
:: Note: Do not put spaces before the equal signs or variables will fail

:: Name of the database user with rights to all tables
set dbuser=root

:: Password for the database user
set dbpass=youradminpassword

:: Error log path - Important in debugging your issues
set errorLogPath="c:\MySQLBackups\backupfiles\dumperrors.txt"

:: MySQL EXE Path
set mysqldumpexe="C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqldump.exe"

:: Error log path
set backupfldr=c:\MySQLBackups\backupfiles\

:: Path to data folder which may differ from install dir
set datafldr="C:\ProgramData\MySQL\MySQL Server 5.5\data"

:: Path to zip executable
set zipper="c:\MySQLBackups\zip\7za.exe"

:: Number of days to retain .zip backup files
set retaindays=30

:: DONE WITH SETTINGS

:: GO FORTH AND BACKUP EVERYTHING!

:: Switch to the data directory to enumerate the folders
pushd %datafldr%

echo "Pass each name to mysqldump.exe and output an individual .sql file for each"

:: Thanks to Radek Dolezel for adding the support for dashes in the db name
:: Added --routines thanks for the suggestion Angel

:: turn on if you are debugging
@echo off

FOR /D %%F IN (*) DO (

IF NOT [%%F]==[performance_schema] (
SET %%F=!%%F:@002d=-!
%mysqldumpexe% --user=%dbuser% --password=%dbpass% --databases --routines --log-error=%errorLogPath% %%F > "%backupfldr%%%F.%backuptime%.sql"
) ELSE (
echo Skipping DB backup for performance_schema
)
)

echo "Zipping all files ending in .sql in the folder"

:: .zip option clean but not as compressed
%zipper% a -tzip "%backupfldr%FullBackup.%backuptime%.zip" "%backupfldr%*.sql"

echo "Deleting all the files ending in .sql only"

del "%backupfldr%*.sql"

echo "Deleting zip files older than x days now"
Forfiles -p %backupfldr% -s -m *.* -d -%retaindays% -c "cmd /c del /q @path"

::FOR THOSE WHO WISH TO FTP YOUR FILE UNCOMMENT THESE LINES AND UPDATE - Thanks Kamil for this addition!

::cd\[path to directory where your file is saved]
::@echo off
::echo user [here comes your ftp username]>ftpup.dat
::echo [here comes ftp password]>>ftpup.dat
::echo [optional line; you can put "cd" command to navigate through the folders on the ftp server; eg. cd\folder1\folder2]>>ftpup.dat
::echo binary>>ftpup.dat
::echo put [file name comes here; eg. FullBackup.%backuptime%.zip]>>ftpup.dat
::echo quit>>ftpup.dat
::ftp -n -s:ftpup.dat [insert ftp server here; eg. myserver.com]
::del ftpup.dat

echo "done"

::return to the main script dir on end
popd

 

 

 

 

 

This script is free to use and update as needed. All we ask is that you like us on Facebook as a thanks.

136 Comments

  1. noonyuki says:

    hi there
    thanks for ur time and effort. i ve some problem with the script.. when i run the bat file, i can see the zip file but there is no content. The console closed really fast and i suspect there is some error during the run but i can't find any dumperror log too. I have a different data folder in drive d whereas mysql installation is in drive c. i m using innodb. Any suggestion?
    thanks again!

  2. noonyuki says:

    hi again
    please ignore my previous comment. I got sql data folder wrong. Sorry! Thanks!

  3. JimC says:

    Thank you for the script! one change I made was to add "--create-options" to the mysqldump command to have it add the create db commands to sql file. it works like a champ!

  4. Automatische MySQL-Backups | klein-gedruckt.de says:

    [...] das Programm mysqldump aus der MySQL Workbench für ein automatisiertes Backup verwendet kann auf redolivedesign.com heruntergeladen [...]

  5. UKCPirate says:

    Problem with windows 7, maybe others, is that the date and time format is different, from what I can make out.

    Replace
    set year=%DATE:~10,4%
    set day=%DATE:~7,2%
    set mnt=%DATE:~4,2%
    set hr=%TIME:~0,2%
    set min=%TIME:~3,2%

    IF %day% LSS 10 SET day=0%day:~1,1%
    IF %mnt% LSS 10 SET mnt=0%mnt:~1,1%
    IF %hr% LSS 10 SET hr=0%hr:~1,1%
    IF %min% LSS 10 SET min=0%min:~1,1%

    set backuptime=%year%-%day%-%mnt%-%hr%-%min%

    WITH:

    IF "%time:~0,1%" LSS "1" (
    SET BACKUPTIME=%date:~6,4%-%date:~3,2%-%date:~0,2%-0%time:~1,1%-%time:~3,2%-%time:~6,2%
    ) ELSE (
    SET BACKUPTIME=%date:~6,4%-%date:~3,2%-%date:~0,2%-%time:~0,2%-%time:~3,2%-%time:~6,2%
    )

    and all works.

    Thanks to the author!

  6. Chrysostomos says:

    What about the Mysql Port. if it is different from the default.

  7. Chrysostomos says:

    to add port and host functionality
    1. add with the other parameters this ->
    :: Name of host
    set host=localhost

    :: port number
    set port=6249

    2. change line 90 to this ->
    %mysqldumpexe% --user=%dbuser% --password=%dbpass% --host=%host% --port=%port% --databases --routines --log-error=%errorLogPath% %%F > "%backupfldr%%%F.%backuptime%.sql"

  8. Neil Bloomer says:

    I added --triggers to the dump command line to be sure that my triggers got backed up too !. Thanks a lot for the script, apart from having to massage the date handling slightly for my setup it worked straight out of the box....

  9. LeaUK says:

    Hi Guys, great script :-) :-) Had to adapt the date/time sections for the UK as both the original and getdate.cmd failed to retrive correctly.

    I commented out the date stuff and added this line:

    :: For UK
    set backuptime=%date:~6,4%-%date:~3,2%-%date:~0,2%_%time:~0,2%%time:~3,2%

    ref: http://stackoverflow.com/questions/203090/how-to-get-current-datetime-on-windows-command-line-in-a-suitable-format-for-us

    Cheers again!
    Lea

  10. LeaUK says:

    I added --lock-tables to the dump command line as all my DBs seem to be MyISAM based (default option for new DBs in MySQL). Seems to be the recomendation to lock for consistancy.

  11. Alan says:

    Great script! What is the minimum permissions need to run the script?

  12. de3v0 says:

    hey!
    Finally a script that get the job done.
    I have a problem with this, the script seems to backup my local databases instead of online server databases.
    Normally, when i run the script, it only backup the folders that exists on C:\wamp\bin\mysql\mysql5.5.24\data - witch is my data folder for localhost.

    Thanks --

  13. 12 Steps to Automate MySQL Backups | OrcsWeb Hosting says:

    [...] database(s) is just as import as having one for a Microsoft SQL database.  I would like to thank Mathew Moeller who authored the MySQL backup script I’m about to [...]

  14. Pablo Carrau says:

    If you're getting errors when trying to clean up the old zip files, update the bottom of the script to this:

    echo "Deleting zip files older than 30 days now"
    set backupfldr=%backupfldr:"=%
    Forfiles -p %backupfldr% -s -m *.* -d -%retaindays% -c "cmd /c del /q @path"

    The problem is due to the double quotes around the backup folder path. The code above removes the quotes. Will work as long as you don't have spaces in your full path.

  15. lc_ says:

    UKCPirate fix worked for me on Windows server 2k8.

    Thanks.

  16. Jose says:

    Hi, I use WAMP SERVER, which is what I have to put here ..?

    set datafldr="C:\ProgramData\MySQL\MySQL Server 5.5\data"

  17. Jay Erskine says:

    How would you go about setting this up so you can selectively define the databases?

    I'm assuming you would change the --databases

    For example, I want to back up Databases 1, 2, 3, 4, daily.
    But I only want to backup database 5 once a week.

    I'm pretty sure I'd need two different backup scripts, one for the 1,2,3,4 and one for 5, but I'm not sure how to define the specific databases.

  18. Ivan says:

    Might be useful to someone, if you get an error such as "the system cannot find the path specified" then you may have an issue with your date/time and the "backuptime" variable which gives a value that is not value in a path / directory, hence the error.

    I haven't corrected it otherwise would post, for my purposes I simply removed this from the path being used as I don't need it.

  19. Steve Munden says:

    Fantastic, just what I needed. I too had to adapt the timestamp code for UK but got there in the end. Thanks so much!

  20. sathish says:

    Hi,
    Thanks for your script.While i run a batch file dump creates with empty content only.can u help me. i am using xampp.

  21. XiXiMe says:

    Thx UKCPIRATE !!

    Your solution it's great for Windows Server 2008 R2 !

    This script work perfectly !

  22. Asad says:

    How to Take Backup of Database On Web-server to our Local Hard drive.... When this BATCH file run on our Local System

    Do we have to ask These Paths from the hosting guys...

    set datafldr="C:\ProgramData\MySQL\MySQL Server 5.5\data"

    set mysqldumpexe="C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqldump.exe"

    Thanks In Advance

  23. Simon Neubauer says:

    Nice and very usefull Script !
    Thank you

  24. William says:

    Thank you! That saved me a ton of work. I'm a MySQL novice but wouldn't the best thing to do be something like:

    lock tables
    dump table (this script)
    flush logs
    unlock tables

    That way you'd have also have a bin log between each backup.

  25. Bob Davies says:

    That is an epixellent script, thank you very much :D

  26. Jeroen says:

    Hi,

    Great script. Did the job right out of the box.
    Have just 1 problem. I have setup a scheduled task which runs daily. It finishes successfully but all the sql files are 0KB.
    Any ideas?

    Thanks,
    Jeroen

  27. Michael says:

    Great script!. After customizing it, the output .zip file contains each of my DBs but each .sql file is 0KB. What could be the problem?

  28. Vyacheslav says:

    The script generates an error if the database name is present point or dash. For example my-site.com => mysqldump: Got error: 1049: Unknown database 'my@002dsite@002ecom' when selecting the database

  29. sahil says:

    Hi,

    Thanks for the script. Is there any way to exclude only one database and it should take all other database backup.

  30. Uv says:

    Nice Script!!! Does it do incremental backups?

  31. hekta says:

    thanks bro, it's working for me...

  32. Solomon says:

    Thanks for this great script, it's very useful to me in backing up a small project database on a local windows 7 64-bit machine. However, one thing I've noticed is that when I execute the batch script as a test from command prompt, it successfully backs up the database (approx 105MB) in 1-2 minutes from start to finish. When executed from a scheduled task manager task, on the other hand, the same database requires 30+ minutes to backup. The entire procedure seems to crawl, but does ultimately finish with a successful backup. I can't figure out why this should be...

  33. Ivan Lanin says:

    Thanks for sharing. Your script save a lot of my time.

  34. Stephanie says:

    Thanks! but it is only backing up the mysql database not my others...any ideas?

  35. Mark says:

    Hi All,
    When I run the script, particularly this part:
    %mysqldumpexe% --user=%dbuser% --password=%dbpass% --routines --databases %%F --log-error=%errorLogPath% > "%backupfldr%%%F.%backuptime%.sql"

    ..the backup files don't get created and no error is thrown.
    If I do an ECHO beforehand to output that line, it looks fine and I can copy and paste the output into the command line and run it and get a backup. The only thing I had to do with the ECHO was place a '^' character before the '>' character otherwise the rest gets escaped.

    Any ideas why it's not creating the backup files?
    Thx in advance.

  36. David Weston says:

    Perfect! Had only to adjust date formats for Canada, and my day was made. Thanks.

Leave a Comment

  • Social

  • Tweets

    Follow
  • Categories

  • FACEBOOK

  • The past

Request a Quote

Close










  • Web
  • SEO
  • Identity
  • Print
  • Consulting
  • Mobile Development
  • Tradeshow Booth


Or call us now - 801-545-0410