801-545-0410

How to automatically backup all MySQL databases on Windows with a batch file.

February 21, 2018


How to automate MySQL backups with a .bat file on a Windows server FREE. 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.

Updated 10/14/2019

Platforms Tested:

  • Windows 2008 R2 x64 En with MySQL 5.5.x,
  • Windows 2012 Server with MySQL 5.6.x,
  • Windows 2012R2 with MySQL 5.7.x (x64)
  • Windows Server 2016 with MySQL 5.7.x and newer (x64)
  • Windows Server 2019 with MySQL 5.7.x AND MySQL 8.0.x (x64)

Features:

  • No cost DIY solution
  • 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 ( I have not tested other variants)
  3. Set the root u/p (or user with adequate permissions)
  4. Set the MySQL data directory to match your install
  5. Set the path to your 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.

 

Basic Troubleshooting tips:

  • 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.
  • 80% of the “Didn’t work for me” issues tend to be resolved by triple checking that your directory paths exist and 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.

DOWNLOAD MySQL-BACKUP-WIN-v1.5

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
:: 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=5

:: 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 30 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. Enjoy

138 Responses to “How to automatically backup all MySQL databases on Windows with a batch file.”

  1. Gav

    Hi Matt

    Just wanted to say thanks very much for writing such a good script. I have run into exactly the same problem with MySQL backups on windows for a little while now.
    Manually having to add newly created databases to my backup task : Complete pain and prone to user error.

    Do you have any information to the Date command, I was getting some obscure entries so have removed the majority and set the file name to just %DATE%

    But this is brilliant, thanks for sharing !

    Gav

    June 13, 2011 at 5:45 am
    • Matthew Moeller

      Ah, yes my example is formatted for the US date/time format on the server. If you’re using say a UK style date format “DD/MM/YYYY” then my substring count will be off and the resulting output will be incorrect. We are essentially just parsing a substring of the Date format. Just move the count to match your date format (i.e. 10,4 position 10 and 4 digits long)

      US FORMAT – The following will result in the file name: FullBackup.YYYY-DD-MM-HH-mm.zip
      SET backuptime=%DATE:~10,4%-%DATE:~7,2%-%DATE:~4,2%-%TIME:~0,2%-%TIME:~3,2%

      UK DATE FORMAT EXAMPLE:
      SET backuptime=%date:~6,4%-%date:~3,2%-%date:~0,2%-0%time:~1,1%-%time:~3,2%-%time:~6,2%

      Hope this answers your question.

      June 13, 2011 at 11:25 am
  2. Charles A.

    Well, praise the Lord and much thanks to you, Matthew! I really appreciate your script for the MySQL DBD backup process.
    God Bless,
    Charles

    June 15, 2011 at 8:21 am
  5. Ibis

    Thank you for this really useful script.

    If this is going to be used in any kind of shared environment, or where more security is needed, then these additional steps may be helpful:

    1. Instead of using Root account on MySQL, create an account with the BackupAdmin right. (MySQL Workbench -> Server Administration -> Accounts -> Add Account (at the bottom left) -> Administrative Roles tab -> BackupAdmin)

    2. You can restrict your new BackupAdmin account to only login from localhost (Limit Connectivity to Hosts Matching: Localhost)

    3. You can create a local user on the Windows computer to run the Scheduled Task. This user doesn’t need many rights, but it does need to have the ability to run CMD.exe. You can manually find CMD.exe and alter the permissions to grant access.

    June 27, 2011 at 11:32 am
  8. Jacob

    Thanks for the script.

    I was also able to add logic to automatically send the backup to Amazon S3 using S3Copy.exe.

    Here’s the information for those who are interested:
    1. Download S3Copy.exe from the following website: http://blog.tjitjing.com/index.php/2009/04/amazon-s3-command-line-copy-for-windows.html

    2. Add the following lines of code to the end of the script:
    echo “Send MySQL Backup to Amazon S3”
    “C:MySQLBackupss3copys3copy.exe” C:MySQLBackupsbackupfiles [Bucket] “FullBackup.%backupdate%.zip” [Access Key ID] [Secret Access Key]

    3. Replace [Bucket], [Access Key ID], [Secret Access Key]

    July 16, 2011 at 2:28 am
  9. Ryan T

    Matt, Great script. Thanks for sharing this on your blog. I implemented this in 15 minutes and utilized Jacob’s code with S3Copy.exe. Works fantastic. Thanks again and I’ll make sure to throw you guys a few links on my websites to Utah Web Design.

    August 5, 2011 at 9:46 am
  14. Ernesto Villanueva

    Hi Matthew.

    I’m working in a MySQL DB, but i have it in a Win XP PC, this solution could be work fine at XP?, I’m asking this because I already try it, however at backupdate, create something like -01-9.-01-9/-18.58, at the moment to create a sql file, have something like bk_-01-9.-01-9/-18.58.sql or test.-01-9.-01-9/-18.58.sql
    Thanks for your time and attention.

    September 7, 2011 at 6:13 pm
  17. Alex

    Thanks for this. Got it all working fine and running on a schedule.

    Can you tell me how i restore my databases using the .sql files?

    I have two databases which each get their own .sql file and then i get one extra .sql file.

    I am a total novice with mysql but can follow step by step instructions perfectly!

    Thanks

    September 25, 2011 at 9:15 am
  21. Alejandro Arauz

    I used a similar script for backups but now I’m working in a shared enviroment where I don’t have the necessary permissions and privileges for running a script. In my experience I have found that a tool can be helpful in this scenario. I use at work MySQLBackupFTP (http://mysqlbackupftp.com). The only disadvantage of this tool is that the free version only allows you to schedule 2 databases but other than that, it is very useful since you can connect to MySQL through phpMyAdmin.

    October 17, 2011 at 3:20 pm
    • Matt Moeller

      Jonas,

      Going to need specifics, there are a few steps to follow and if one is not done correctly it will fail. I would advise double checking the batch file for syntax, folder paths, permissions, etc. It works perfect assuming you get those right. As for workbench here is a link to the import functionality

      November 28, 2011 at 10:22 am
  25. Jonas

    Hi there. Tnx a million for the script! But it doesn’t backup data? Or is it just mine? And where in the Workbench should you import the files?

    But thanks again! Really awesome

    November 24, 2011 at 8:58 am
  26. MSH_jacob

    First I would like to thank you for the script it is most definately a lifesaver. I ran into a problem with it though, if a database has a hyphen in the name it fails because mysql creates the database folder in the following format firstword@002dsecondword then when it tries to backup the database it cant find it because its looking for @002d instead of a hyphen. Any ideas to get around this?

    December 7, 2011 at 2:41 pm
  27. Kamil

    I just wanted to thanx for a great script. I have customized it quite a lot, but the original pushed me in the right direction. I have now the MySQL backup fully automated.

    One thing I would like to point out though. When handling the zipped file further (eg. uploading it to ftp server) I have encountered problems with the file name. When the time was less then 10 it would leave a blank space in the file name and then it would not be possible to copy the file (using batch commands). It looked for example like this: FullBackup.2011-28-11- 9-34.zip -> the whole part after the space will be ignored when you use this file name for other operations. The same goes for days and month lower then 10.

    The solution to this is simply parse the date and time string, check if it is less then 10 add zero to it in case it is.

    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 backupdate=%year%-%day%-%mnt%-%hr%-%min%

    With this control sequence the file name would now be:
    FullBackup.2011-28-11-09-34.zip -> no spaces = no problems

    For those interested in uploading the backup file to some ftp server the commands are following:

    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. cdfolder1folder2]>>ftpup.dat
    echo binary>>ftpup.dat
    echo put [file name comes here; eg. FullBackup.%backupdate%.zip]>>ftpup.dat
    echo quit>>ftpup.dat
    ftp -n -s:ftpup.dat [insert ftp server here; eg. myserver.com]
    del ftpup.dat

    Thanks again for this wonderful and beautifully simple solution. I hope my hints will be of help to others.
    Matt feel free to include them in the article or the file itsefl.

    Have a happy New year everyone.

    December 29, 2011 at 8:04 am
  29. Robert

    Great job !! THX

    for German dateformat this would work

    set year=%DATE:~6,4%
    set day=%DATE:~0,2%
    set mnt=%DATE:~3,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 backupdate=%year%-%day%-%mnt%-%hr%-%min%

    —> FullBackup.2012-19-01-14-02

    January 19, 2012 at 7:09 am
  32. Michael

    Thank you for the script!!! Life saver!

    Having the MySQL directories as set variables would make setting up a little quicker with less chance of human error since there will be one place/area to set all the options.

    February 15, 2012 at 3:24 pm
  35. Robert Jaudon

    What if I only wanted to bkup a specific DB and not all of them? Could I just call it like this…

    FOR /D %%F IN (*) DO (
    “c:MySQLbinmysqldump.exe” –user=%dbuser% –password=%dbpass% –databases MyDB > “c:MySQLBackupsbackupfilesMyDB.%backupdate%.sql”
    )

    or instead could I just call the command such as this…

    “c:MySQLbinmysqldump.exe” –user=%dbuser% –password=%dbpass% –databases MyDB > “c:MySQLBackupsbackupfilesMyDB.%backupdate%.sql”

    Thanks
    Rob

    March 5, 2012 at 4:01 pm
  36. Gana

    Hi – While executing the below command “Forfiles -p C:test -s -m *.* -d -5 -c “cmd /c del /q @path”” i am getting the following error ” FORFILES v 1.1 – emmanubo@microsoft.com – 4/98
    Syntax : FORFILES [-pPath] [-mSearch Mask] [-ccommand] [-d]…. ” . Can you please help.

    March 9, 2012 at 2:28 pm
  37. Ken

    Thanks for the script.. ive been browsing and spent a lot of time searching for this script.. and
    finally {
    console.write(” Thanks for sharing. God Bless”);
    }

    March 26, 2012 at 12:39 am
  38. Vijay

    @ MSH_JACOB or anyone who needs it:

    Problem: MySQL uses @002d in folder names instead of – .

    Below is the modification for handling – in mysql database names:

    ::Change to capture the @002d & change into – in folder names
    @echo off
    setlocal enableextensions enabledelayedexpansion
    FOR /D %%F IN (*) DO (
    SET dbname=%%F
    SET dbname=!dbname:@002d=-!
    “c:MySQLbinmysqldump.exe” –user=%dbuser% –password=%dbpass% –databases !dbname! > “c:MySQLBackupsbackupfiles!dbname!.%backupdate%.sql”
    )

    June 1, 2012 at 2:02 am
  39. Marcellus Wallis

    Thanks for the script… It is just what i am looking for, for my local setup.

    However, I have a shared web hosting service where I need to backup about 20 different MySQL databases. Each of these databases are on a remote server, that i need to connect to. Is that possible? as I can not see a way to connect to a remote server IP Address.

    June 2, 2012 at 2:21 am
  42. Danival

    Hello,

    this script have a error, when one database have the “-” caracter in your name.

    Example:

    one database called my-db. The mysql will create folder my@002db to this database, because mysql replace the “-” caracter to @002.

    Databases when the “-” caracter in your names dont will be backups.

    June 8, 2012 at 2:04 pm
  45. Chan

    Thanks Matthew,
    I am not good with MySQL, I used your script, its very simple and working good for most of databases.
    but I am having problem with some databases, its not dumping any data, when I open backup (.sql) file , it has only below content

    ————————————————————————-
    — MySQL dump 10.11
    —
    — Host: localhost Database: cb-app1
    — ——————————————————
    — Server version 5.0.95-community-nt

    /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
    /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
    /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
    /*!40101 SET NAMES utf8 */;
    /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
    /*!40103 SET TIME_ZONE=’+00:00′ */;
    /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
    /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
    /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=’NO_AUTO_VALUE_ON_ZERO’ */;
    /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

    —
    — Current Database: `cb-app1`
    —

    CREATE DATABASE /*!32312 IF NOT EXISTS*/ `cb-app1` /*!40100 DEFAULT CHARACTER SET utf8 */;

    USE `cb-app1`;
    ————————————————— —————-

    I am sure there are tables and content in this database, I wonder why its not dumping database properly

    your help will be appreciated

    Regards,
    Chan

    June 28, 2012 at 3:13 am
  46. Angel

    Hi Mathew,

    Thank you for the script, it works great.

    The only suggestion I have is to add the –routines switch to the dump line, this assures the procedures and functions are also backed up, in case something breaks with the mySql severver.

    It would look like this:
    “c:MySQLbinmysqldump.exe” –user=%dbuser% –password=%dbpass% –databases –routines –log-error=”c:MySQLBackupsbackupfilesdumperrors.txt” %%F > “c:MySQLBackupsbackupfiles%%F.%backupdate%.sql”

    Thanks again! saved me tons of time!

    July 5, 2012 at 1:25 am
  48. Radek Dolezel

    Please add SETLOCAL at the beginning and ENDLOCAL at the end of the bat file. It will force script variables to exist only during script runtime and no longer. Otherwise your root pwd is stored in variable until logoff/restart.

    Chan – I met issues with not working backups too, it is caused by DATE function which is dependent on regional settings. I have changed the following:

    call getdate.cmd

    set year=%v_year%
    set day=%v_day%
    set mnt=%v_month%

    Batch file getdate.cmd can be found on http://groups.google.com/group/alt.msdos.batch.nt/msg/31e764b00fe7c8e?hl=en

    MySQL backups are created without any problem.

    July 13, 2012 at 5:39 pm
  49. Radek Dolezel

    I have to implement one IF ELSE construction because performance_schema backup wrote mi errors with LOCK table.

    I have combined it with another fix providing support for dash in db name and with –routines attribute added.

    FOR /D %%F IN (*) DO (
    SET dbname=%%F
    IF NOT [!dbname!]==[performance_schema] (
    SET dbname=!dbname:@002d=-!
    “C:Program FilesMySQLMySQL Server 5.5binmysqldump.exe” –user=%dbuser% –password=%dbpass% –databases –routines –log-error=”d:MySQLBackupsbackupfilesdumperrors.txt” !dbname! > “d:MySQLBackupsbackupfiles!dbname!.%backupdate%.sql”
    ) ELSE (
    echo Skipping DB backup for performance_schema
    )
    )

    July 14, 2012 at 5:36 am
  50. Nathan Hadfield

    Thanks for posting this! Unfortunately the download link does not seem to work, so I copied from the script you posted.

    I found a couple of minor issues:

    (1) A ‘popd’ is missing at the end of the script to return to the original directory the script was invoked from. Otherwise, when testing, you have to keep changing back to the directory containing the script.

    (2) The errorLogPath variable referenced in the mysqldump.exe call needs to be enclosed with percent signs (%).

    A nice improvement might be to consolidate all of the potential customizations into variables at the top of the script (e.g., username, password, MySQL binary and data paths, etc.).

    July 20, 2012 at 12:55 pm
  51. Casey

    Fantastic. This is just what I was looking for. Made the adjustments in the script and started backing up immediately. Now I can go home and relax! Thanks for writing/sharing this.

    July 23, 2012 at 3:33 pm
  52. Eric C

    Thanks so much for this script, very handy! In messing around with it I found that if you switch the -tzip to -t7z in the 7zip command line you will get a much better compression ratio (though I would understand why you would keep the zip for compatibility). My 32meg backup went to 5meg, very handy.

    Also you can use the standard windows 7zip GUI executable (32 or 64 bit), you no longer have to use the command line version specifically. 7z.exe vs. 7zc.exe. I don’t know when this changed but I use this with my vm backup jobs.

    July 24, 2012 at 2:41 pm
  53. Danny O'Neill

    The getdate doesnt seem to work when the server local is set to United Kingdom. It fails to return a date and the backup also fails.

    July 26, 2012 at 2:09 pm
  55. Sasha

    Thank you for posting this script. The backup and zip part worked great for me. The FTP part does not seem to work after I uncommented the relevant parts and entered my ftp server and credentials. Nothing got written to the dumpfile.txt.
    Any suggestions how to troubleshoot this?

    Many thanks!

    July 31, 2012 at 9:26 pm
  56. Sacha

    Hi there, thanks for the script, but I had to mod it a bit.
    1) the getdate script for an Italian locale gave errors, and I had to dump it and revert to
    set year=%DATE:~6,4%
    set day=%DATE:~0,2%
    set mnt=%DATE:~3,2%
    for a dd/mm/yyyy date
    2) got a lot of headaches with the :: comments in the FOR and IF blocks, and finally found a bug here
    http://ss64.com/nt/rem.html
    and changed the ::’s to REM’s , and everything works fine (Win2003 32bit fully updated)

    August 7, 2012 at 8:53 am
  58. Chris Dell

    Thanks Matthew so much for your script.

    Just a note, why are you using –databases argument? This is putting USE and CREATE DATABASE statements into the dump file which is dangerous if I ever wanted to restore a database for historical purposes under a different name.

    August 16, 2012 at 7:00 am
    • Matt Moeller

      Correct, our implementation assumes our server has died and we are setting up a bare bones machine and we need to put it back online within minutes, having create and use is perfect for us. We would never blindly just run a script without looking at it, but you are right the potential is there to cause some damage if you are not paying attention. Which I suppose is yet another reason to run the backup script often 😉

      Update the script to fit your needs, obviously tons of options for every scenario: http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html

      August 16, 2012 at 9:53 am
  59. Ian

    Hi,

    It only seems to be backing up the “mysql” database, yet I have about 30 databases in Workbench.
    I set the data directory to “c:Program FilesMySQLMySQL Server 5.5data”, is that wrong?

    Thanks, Ian

    September 20, 2012 at 10:06 am
  62. Greg

    For some reason, the hypen removal find/replace syntax didn’t work for me. I discovered I needed to turn on SETLOCAL EnableDelayedExpansion to use the exclamation marks, but even then the string substitution just wouldn’t work. Eventually I found a different syntax, and this worked for me:

    SETLOCAL EnableDelayedExpansion
    SET find=@002d
    SET replace=-
    SET dbname=%%F
    CALL SET dbname=%%dbname:!find!=!replace!%%

    October 15, 2012 at 1:39 pm
  64. GKoo

    Thanks a lot for this script. I was looking for something similar for a long time. I had to modify it a bit to work with mysql 4 but in the end it turned out great. Keep up the good work.

    October 27, 2012 at 4:49 am
  65. Søren Jensen

    getdate.cmd didn’t work for me. Gave some syntax error failures.
    I adjusted the main script to work with my date format, which is yyyy-mm-dd hh:ii (Danish format):
    set year=%DATE:~6,4%
    set day=%DATE:~0,2%
    set mnt=%DATE:~3,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%-%mnt%-%day%-%hr%-%min%
    echo %backuptime%

    October 31, 2012 at 6:46 am
  66. Kamlesh

    Thanks for your script.

    I want to run backup automatically through batch file but it is asking for password when through through batch file and as soon as I type password it takes backup. So I don’t want manual intervention for this. is that possible ?

    November 1, 2012 at 1:52 am
  67. Marco

    Windows 2008 R2 web edition:

    why in the script the redirector > is substituted with 1> when run making the script fail with “the system cannot find the path specified”?

    Please help…cannot find any info about this anywhere.

    November 4, 2012 at 7:32 am
  68. Johnny

    For those of you still having trouble with dashes in the database name, try this. It worked for me:

    SETLOCAL EnableDelayedExpansion

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

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

    November 8, 2012 at 4:45 pm
  71. Troy

    I was getting two errors in my dumperrors.txt

    mysqldump: Got error: 1049: Unknown database ‘backupfiles’ when selecting the database
    mysqldump: Got error: 1049: Unknown database ‘zip’ when selecting the database

    Turns out the
    pushd %datafldr%
    command on line 70 of mysqlbackup.bat didn’t work for me on server 2003. I replaced it with the ol’
    cd c:
    cd %datafldr%
    and all is well!

    Thanks for the script!

    December 11, 2012 at 9:33 pm
  72. Ricky

    Hi guys,

    First of all…awesome script! Works like a charm 🙂

    I’m from Portugal and i had some issues with the date and time format, so i googled around and optimized the script for Portuguese date/time format (dd-mm-yyyy):

    ::==========
    set hour=%time:~0,2%
    if “%hour:~0,1%” == ” ” set hour=0%hour:~1,1%
    echo hour=%hour%
    set min=%time:~3,2%
    if “%min:~0,1%” == ” ” set min=0%min:~1,1%
    echo min=%min%
    set time=%hour%-%min%

    set backuptime=%date:~0,2%-%date:~-7,2%-%date:~-4,4%-%time%
    echo %backuptime%
    ::===========

    The backuptime output is something like this: 18-12-2012-01-53

    Cheers!

    December 17, 2012 at 7:54 pm
  73. Mike

    I have an odd problem. I am using the script but its not dumping my databases. it dumps the Mysql db and one called test. not the dbs in my list in workbench.. very confused.

    January 3, 2013 at 8:43 am
  76. Marc

    mysqldump: Couldn’t execute ‘SHOW FUNCTION STATUS WHERE Db = ‘atlantic”: Cannot load from mysql.proc. The table is probably corrupted (1548)

    I get this error in the dumperrors.txt file for every database i have that is being backed up. The files back up fine but these errors keep showing up and i don’t know why.

    January 16, 2013 at 2:03 pm
  77. Daniel

    This is a great script! I had to tweak switches for the Forfiles command, change [-] to [/]. Then it worked very well. I have set up 2 backups with that script, one that dumps the whole DB as one [sql], and another that does every table as individual [sql]. I’m not too good at batch programming so had to do some research on using the commands. But if some one need to backup each table as a separate [sql]… Here it is:

    :: List your database tables to a text file
    mysql –user=%dbuser% –password=%dbpass% –database=%dbname% -e “show tables;” > %backupfldr%%dbname%_tables.txt

    :: Then go through all tables in the file and dump to sql
    FOR /F %%i IN (%backupfldr%%dbname%_tables.txt) DO (
    :: Skip first row
    if not [%%i]==[Tables_in_%dbname%] (
    %mysqldumpexe% –max_allowed_packet=1G –host=%dbhost% –user=%dbuser% –password=%dbpass% –complete-insert=TRUE –port=%dbport% –default-character-set=%dbcharset% –log-error=%errorLogPath% %dbname% %%i > “%backupfldr%%dbname%_%%i.%backuptime%.sql”
    ECHO %backupfldr%%dbname%_%%i.%backuptime%.sql
    )
    )

    February 5, 2013 at 10:47 pm
  78. Ray

    I’m using windows plesk 11 with mysql, I get this error 🙁

    Anyone come across this?

    mysqldump: unknown option ‘–routines’
    mysqldump: unknown option ‘–routines’
    mysqldump: unknown option ‘–routines’
    mysqldump: unknown option ‘–routines’

    February 7, 2013 at 8:01 am
  79. Ray

    Unfortunately not working for me on MySQL 5.1
    I get an error about the –routines being an invalid command, if I remove that, I get an error about the –log-error=%errorLogPath% and if I remove that, it still doesn’t work 🙁 any ideas?

    February 12, 2013 at 5:43 pm
  81. Alexander Kunin

    Awesome! Thanks for sharing this!

    You can add ability to upload zip files to Amazon S3
    just adding S3Sync utility into your package
    http://blog.jitbit.com/2012/03/automating-amazon-s3-backups-for-your.html
    or
    http://sprightlysoft.com/S3Sync/

    add line at the beginning
    :: S3Sync
    set s3sync=”c:MySQLBackupsS3SyncS3Sync.exe”

    and after FTP part we need to add a command line

    ::upload to Amazon S3
    %s3sync% -AWSAccessKeyId AMAZONACCESSKEID -AWSSecretAccessKey AMAZONSECRETACCESKEY -BucketName my_bucket -SyncDirection Upload -LocalFolderPath %backupfldr% -DeleteS3ItemsWhereNotInLocalList false -OutputLevel 2 -CompareFilesBy ETag

    You can configure S3Sync.exe.config instead of adding parameters to the command line.
    After S3Sync configuration my command line looks like this:

    ::upload to Amazon S3
    %s3sync% -LocalFolderPath %backupfldr%

    February 16, 2013 at 3:38 am
  85. Alexander

    We would recommend our GUI backup tool for MySql – MySQL Backup FTP (MySqlBF) http://mysqlbackupftp.com/.

    1. MySqlBF can work as a service.
    2. Run backups on a flexible schedule.
    3. Allow backup several databases from several websites.
    4. Tool have a completely Free and powerful version.
    5. MySqlBF allow to to save backups to the Network, on FTP and most popular cloud storage (Dropbox, Amazon S3, Google Drive, SkyDrive, Box).
    6. Connect to remote servers throws TCP, SSH or phpMyAdmin(unique feature)
    7. We offer a high quality support and detailed manuals.

    March 27, 2013 at 1:42 am
    • Matt Moeller

      There are multiple commercial offerings for backing up MySQL on a Microsoft box if you want to pay for it. Mine is as lightweight as you can get, secure, stable as a rock, can backup unlimited # of databases, open source so the end user can modify it to accomplish things a closed source offering can’t, and hell, it’s FREE.

      March 28, 2013 at 10:17 am
  89. Vince

    Hi All,
    The script didn’t work for me. I looked at mysql.xxx.sql file, I saw “..Host: localhost Database: mysql”. It backed up only mysql database only.

    Lets say I have a dabasename “ABC” and “XYZ”, how do I specify a specific dabasename in the script to backup?
    Please help. Thank you.

    April 11, 2013 at 11:03 am
  90. Polly013

    This script works really well except for the clearing of zip files. I’ve changed the Forfiles statement to read:

    Forfiles /P %backupfldr% /S /M*.* /D -%retaindays% /C “cmd /c del @path”

    but still no luck. Any ore ideas on how to get this part to work?

    Thanks very much!

    May 2, 2013 at 3:57 am
  91. David Cumps

    Right now you’re looking in the data dir for db names, meaning it’s restricted to run locally.

    I have no control over the db server (AWS), so there is no data dir to enumerate, only a database connection.

    How hard would it be to alter it to use SHOW DATABASES to get the names, and then enumerate those? Effectively changing your bat from a local-only backup, to having a remote backup possibility.

    May 5, 2013 at 5:32 am
  92. noonyuki

    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!

    May 8, 2013 at 5:02 pm
  94. JimC

    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!

    May 17, 2013 at 7:43 am
  96. UKCPirate

    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!

    June 6, 2013 at 9:05 am
  98. Chrysostomos

    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”

    June 8, 2013 at 10:24 am
  99. Neil Bloomer

    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….

    June 9, 2013 at 5:34 am
  101. LeaUK

    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.

    June 11, 2013 at 7:35 am
  103. de3v0

    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:wampbinmysqlmysql5.5.24data – witch is my data folder for localhost.

    Thanks —

    June 21, 2013 at 3:21 am
  105. Pablo Carrau

    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.

    July 1, 2013 at 2:56 pm
  108. Jay Erskine

    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.

    July 11, 2013 at 12:45 pm
  109. Ivan

    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.

    July 27, 2013 at 12:49 pm
  113. Asad

    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:ProgramDataMySQLMySQL Server 5.5data”

    set mysqldumpexe=”C:Program FilesMySQLMySQL Server 5.5binmysqldump.exe”

    Thanks In Advance

    August 23, 2013 at 7:21 am
  115. William

    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.

    September 14, 2013 at 2:26 pm
  117. Jeroen

    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

    October 4, 2013 at 1:07 am
  119. Vyacheslav

    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

    November 16, 2013 at 3:08 pm
  123. Solomon

    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…

    January 2, 2014 at 8:38 pm
  126. Mark

    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.

    February 25, 2014 at 2:36 pm
  128. azzurroverde

    In “/data/” only have two folders,” mysql” and “performance_schema”, plus loose files, “ibdata1”, “ib_logfile0” and “ib_logfile1”.
    I modified the script to dump those files as well, by adding:

    FOR %%F IN (*) DO (

    SET %%F=!%%F:@002d=-!
    %mysqldumpexe% –user=%dbuser% –password=%dbpass% –databases –routines –log-error=%errorLogPath% %%F > “%backupfldr%%%F.%backuptime%.sql”
    )

    What do you think? I’m not sure if those files are part of the db. I’m puzzled that once mysqldumped everything is so small, few KB.

    March 28, 2014 at 9:00 am
  129. tobi

    similar to Mark the backup files don’t get created and no error is thrown. I get a zip file created with no file including, shells says “everything is 0k”.
    Any ideas?

    April 8, 2014 at 4:21 pm