Request a quote 801.545.0410
Web SEO Branding Marketing Mobile App Email Marketing Social Paid Search Video
May
23
Matt Moeller

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

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.

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)

Updated 4.28.2017

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 ( 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. All we ask is that you like us on Facebook as a thanks.

143 Comments

  • Gav says:

    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

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

  • Charles A. says:

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

  • Rick S says:

    Got this solution running within minutes… it just simply works. Very nice. Thanks for sharing!

  • Kyle says:

    Could you incorporate …

    http://www.beyondlogic.org/solutions/cmdlinemail/cmdlinemail.htm

    … to email a status report?

  • Ibis says:

    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.

  • WAWA says:

    Thanks! This was exactly what I was looking for!

  • Nabee says:

    Thanks, this is an awesome script.I was looking for exactly this one!

  • Jacob says:

    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]

  • Ryan T says:

    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.

  • David says:

    This was a great help for me as well. Sadly, there was no backup happening previously…

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

  • Tom Chapin says:

    Thank you for making this and sharing it! You’re a lifesaver!

  • olaf says:

    you are the man!

    thanx for sharing, works great!

  • Alex says:

    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

  • Thanks alex, yeah you can use the free mysql workbench import feature, http://www.mysql.com/downloads/workbench/

  • Prashant says:

    Thanks a lot after searching lot got ur script and it was too useful for me thank you again…..

  • Darren says:

    Amazing – works perfect. Thanks tons!

  • Alejandro Arauz says:

    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.

  • Dobri says:

    This date format worked for me:

    SET backupdate=%DATE:~9,4%-%DATE:~6,2%-%DATE:~3,2%-%TIME:~0,2%-%TIME:~3,2%

    Thank you!

  • Kyle says:

    You are awesome!!!

    This saved me so much time thanks!!!

  • Jonas says:

    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?

    • Matt Moeller says:

      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

  • Jonas says:

    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

  • MSH_jacob says:

    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?

  • Kamil says:

    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.

  • Scott says:

    Great program. Just what I needed. Unfortunalty I don’t use Facebook to “like” you on it.

  • Robert says:

    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

  • bgolfy says:

    Thanks a lot guys for this fantastic script!!

  • Michael says:

    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.

  • Patrick says:

    I’m getting an error (in the FOR /D command)
    %%F was unexpected at this time.

  • Walter says:

    Thanks, this really helps a lot. Nice day to all 🙂

  • 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

  • Gana says:

    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.

  • Ken says:

    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”);
    }

  • Vijay says:

    @ 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”
    )

  • Marcellus Wallis says:

    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.

  • Muro says:

    Thanks a lot for this!!

  • Pontus says:

    Hello!
    Thanks for a super script. Need some assistance though..
    Can you contact me pls?

    Cheers,
    pontus

  • Danival says:

    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.

  • Kenny says:

    Thanks for putting this out there. Worked great and fit the bill perfectly for my needs.

  • Tobias says:

    Thank you very much. It saves my friday evening 🙂

  • Chan says:

    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

  • Angel says:

    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!

  • Kevin says:

    Thanks so much for sharing this…had the basics of what I needed to automate backups.

    Best,
    Kevin

  • Radek Dolezel says:

    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.

  • Radek Dolezel says:

    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
    )
    )

  • Nathan Hadfield says:

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

  • Casey says:

    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.

  • Eric C says:

    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.

  • Danny O'Neill says:

    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.

  • Emanon says:

    Great script. Works like a dream! Thanks!!

  • Sasha says:

    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!

  • Sacha says:

    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)

  • Eure Murksel says:

    Thank you. Saved my day 🙂

  • Chris Dell says:

    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.

    • Matt Moeller says:

      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

  • Ian says:

    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

  • Daniel says:

    Hi,

    How to set the auto backup hours?

    Best Regards,
    ~Daniel

  • KrAziGuRl says:

    SET %%F=!%%F:@002d=-!

    does not work, is there another way to backup databases with “-” (dashes) in the name?

    Thanks

  • Greg says:

    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!%%

  • KrAziGuRl says:

    Thanks for your input but that still doesn’t work for me.. its still replacing the – with @200d 🙁

  • GKoo says:

    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.

  • Søren Jensen says:

    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%

  • Kamlesh says:

    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 ?

  • Marco says:

    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.

  • Johnny says:

    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
    )
    )

  • Marcelo says:

    It couldn’t be more useful!! Thanks a lot!!

  • Henry says:

    Hi I tried the scrip and it created the backup files but no sql data was written into the backup file… Can anyone help with what the problem may be

  • Troy says:

    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!

  • Ricky says:

    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!

  • Mike says:

    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.

  • Mike says:

    Please ignore my last comment from “JANUARY 3, 2013 @ 8:43 AM”

    I had the data directory wrong.

  • Marc says:

    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.

  • Daniel says:

    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
    )
    )

  • Ray says:

    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’

  • Ray says:

    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?

  • Ivan Lopez says:

    It Works. Only a minor adjusts in time and works like a charm. And with another adjust, I’m backing up remote hosts too. Thanks a lot!

  • Alexander Kunin says:

    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%

  • COLO says:

    Great!! thanks for share your work!

  • william says:

    Gracias voy a probarlo..

  • Rufy says:

    It works perfectly 🙂 Thanks

  • Alexander says:

    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.

    • Matt Moeller says:

      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.

  • Ade Jones says:

    Thanks for the really useful script. Here’s what worked for me using UK regional settings:

    set year=%DATE:~6,4%
    set day=%DATE:~0,2%
    set mnt=%DATE:~4,2%

    Cheers!

  • Ade Jones says:

    Typo! For UK region, that should be:
    set year=%DATE:~6,4%
    set day=%DATE:~0,2%
    set mnt=%DATE:~3,2%

  • Venus says:

    I’m having troubbles once i run it, it creates the error.txt file but did not make anything, neither the backup or write the errors on the file.

  • Vince says:

    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.

  • Polly013 says:

    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!

  • David Cumps says:

    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.

  • 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!

  • noonyuki says:

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

  • 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!

  • 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!

  • Chrysostomos says:

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

  • 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”

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

  • 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

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

  • Alan says:

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

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

    Thanks —

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

  • lc_ says:

    UKCPirate fix worked for me on Windows server 2k8.

    Thanks.

  • Jose says:

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

    set datafldr=”C:ProgramDataMySQLMySQL Server 5.5data”

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

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

  • 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!

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

  • XiXiMe says:

    Thx UKCPIRATE !!

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

    This script work perfectly !

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

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

    Thanks In Advance

  • Simon Neubauer says:

    Nice and very usefull Script !
    Thank you

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

  • Bob Davies says:

    That is an epixellent script, thank you very much 😀

  • 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

  • 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?

  • 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

  • sahil says:

    Hi,

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

  • Uv says:

    Nice Script!!! Does it do incremental backups?

  • hekta says:

    thanks bro, it’s working for me…

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

  • Ivan Lanin says:

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

  • Stephanie says:

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

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

  • David Weston says:

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

  • azzurroverde says:

    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.

  • tobi says:

    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?

  • Vaughan Leiberum says:

    Matthew, thank you for this great script, this will save so much time each day.
    I have an error however, when it runs through the database folders, then for each says has the message “The system cannot find the path specified”

    Any idea why? ( Can email screenshots if it would help)

  • Lewis says:

    I am having the same issue as another user… only backing up the MySQL. The script runs fine with no errors but it is not backing up all my Schemas (4 databases and the tables)? Do I need to modify the script to dump multiple databases?

  • mgm says:

    Hi,

    Thanks to the original author Matthew, this is an excellent piece of code.

    Unfortunately it initially did not work on my Windows 7/UK time format computer.

    Thanks to the “Windows 7 date/time fix” from UKCPirate in the comments section above,

    and also UK timeformat fix from LeaUK I got it working.

    Here is the fully modified script which might help any other UK/Windows 7 users out there.

    Please note the changes:
    1) you will need to update your MySQLBackups folder as mine is on drive D: (rather than C:).
    2) another non-standard change is the mysql path, you will need to update as per your installation path
    3) also remember to update sql password of course

    —————————–begin modified script——————————-
    :: Auto MySQL Backup For Windows Servers By Matt Moeller v.1.5
    :: RED OLIVE INC. – http://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=”d:MySQLBackupsbackupfilesdumperrors.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

    :::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
    :: original “set date/time” section
    :: 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%

    :: suggested date/time fix, as per UKCPirate’s comment on http://www.redolivedesign.com/utah-web-designers-blog/2011/05/23/automated-mysql-backup-for-windows/
    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%
    )
    :::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::

    :::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
    :: original “set backuptime” line
    :: set backuptime=%year%-%day%-%mnt%-%hr%-%min%

    :: fixed “set backuptime” section, as per LeaUK’s comment on http://www.redolivedesign.com/utah-web-designers-blog/2011/05/23/automated-mysql-backup-for-windows/
    :: For UK
    set backuptime=%date:~6,4%-%date:~3,2%-%date:~0,2%_%time:~0,2%%time:~3,2%
    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=MYPASSWORDHERE

    :: Error log path – Important in debugging your issues
    set errorLogPath=”d:MySQLBackupsbackupfilesdumperrors.txt”

    :: MySQL EXE Path
    set mysqldumpexe=”D:MySQLProgramMySQL Server 5.6binmysqldump.exe”

    :: Error log path
    set backupfldr=d:MySQLBackupsbackupfiles

    :: Path to data folder which may differ from install dir
    set datafldr=”D:MySQLDatadata”

    :: Path to zip executable
    set zipper=”d:MySQLBackupszip7za.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. cdfolder1folder2]>>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
    —————————–end modified script——————————-

  • Dan says:

    This script works when run from my elevated privilages account. I created a domain service account and put the account in the Backup Operators group and gave execute permission on cmd.exe, but it usually creates 0k sql files, although sometimes it works correctly, then goes back to the 0k files. What would cause that?

  • Excellent Script. This is exactly what I was about to start work on. Saved a lot of time. Only one suggestion – As of now script is hard coded to path “C:MySqlBackups”. It can be made configurable so that the complete folder can be copied to any location and will still work. For eg;
    set backupfldr=c:MySQLBackupsbackupfiles
    can be changed to
    set backupfldr=%~dp0backupfiles

    The above will pick up the current folder path and ready to run from anywhere.

    Raghav.

  • Gokul says:

    Hi,
    I have DB schema in same HOST,
    How to take a particular Database backup?
    Where to specify the name?
    Default it takes only the “mysql” database backup?

    Regards,
    Gokul

  • Dave says:

    I get errors here: mysqldump: Got error: 1049: Unknown database ‘backupfiles’ when selecting the database
    mysqldump: Got error: 1049: Unknown database ‘zip’ when selecting the database

    It acts like I did not change the variables to my database names, but I did change them in the bat file, and it still gives this error. Any clues?

  • KrZ says:

    Thank you a lot for your time and work !

  • Marc says:

    I have tried this and my ouput keeps giving this in the my sql file:

    LOCK TABLES `aumpercentages` WRITE;
    /*!40000 ALTER TABLE `aumpercentages` DISABLE KEYS */;
    INSERT INTO `aumpercentages` VALUES (‘MAX’,2.5,2.8,3.5,5),(‘MIN’,0.8,0.9,1,1.5),(‘WACC’,0.35,0.3,0.25,0.2);
    /*!40000 ALTER TABLE `aumpercentages` ENABLE KEYS */;
    UNLOCK TABLES;

    Is there away to have it output as a normal db dump will look.

  • mike says:

    sorry but i cant locate this file mysqlbackup.bat will please help me out? thanks

  • vayu says:

    From the above script how can i bakup only selected databases from the list of available databases

6 Trackbacks

Leave a Reply