Automatically Backup MySQL Databases on Windows (WAMP)

The best way to create and automate backups of MySQL databases is to:

1. Use the Windows Task Scheduler to automatically run a backup task every day or week.

2. Have the task run a BATCH file containing the “mysqldump” and “makecab” commands to export and compress the databases.

3. For additional recovery, use MySQL’s Binary Log files to record transactions between backup jobs (to rebuild the database to the last transaction recorded).

This solution will work for everything from Windows 10, down to XP, and Server 2003. No external tools are required.

Backup MySQL Databases with Batch File

Create a mysql-backup.bat file to export all the databases (or only select databases), using a DATE-TIME file-name stamp, and compress the SQL file…

@ECHO OFF

set TIMESTAMP=%DATE:~10,4%%DATE:~4,2%%DATE:~7,2%

REM Export all databases into file C:\path\backup\databases.[year][month][day].sql
"C:\path-to\mysql\bin\mysqldump.exe" --all-databases --result-file="C:\path-to\backup\databases.%TIMESTAMP%.sql" --user=username --password=password

REM Change working directory to the location of the DB dump file.
C:
CD \path-to\backup\

REM Compress DB dump file into CAB file (use "EXPAND file.cab" to decompress).
MAKECAB "databases.%TIMESTAMP%.sql" "databases.%TIMESTAMP%.sql.cab"

REM Delete uncompressed DB dump file.
DEL /q /f "databases.%TIMESTAMP%.sql"

Make sure to update all the paths used, and the MySQL’s username (root) and password (if no password is used, leave the “--password” switch out).

Why use CAB instead of ZIP?…

MS-CAB files have almost 50% better compression ratios over ZIP (especially for single files), and the MAKECAB/EXPAND commands exist on all Windows versions.

Scheduled MySQL Backup Task

Create a Windows Task to run the above BATCH file every day or every week.

Make sure that:

1. The user has rights to Log on as a batch job.

2. If the drive/path you are exporting to is a mapped drive, to use the UNC path.

3. If the drive/path is a shared folder, the user the task is ran on has the correct permissions.

Use MySQL Binary Log

Verify that my.ini has the Binary Log enabled, set to either a MIXED or ROW mode, and does not expire between backup tasks (make it 2x the frequency of the backup task schedule +1 day).

log-bin=mysql-bin
binlog-format=MIXED
expire_logs_days=15

This way you can restore to the last transaction recorded by replaying the log over the last backup job.

16 thoughts on “Automatically Backup MySQL Databases on Windows (WAMP)”

    1. Yes… The above mysqldump command does not work unless "--user=username --password=password" is at the very end. This seems to be a problem when using switch “all-databases” and/or “result-file”.

      I’ve updated it to the correct switch order (and also made a minor edit to correct a few non-standard/ASCII dashes that made there way in somehow).

      For WampDeveloper Pro, you would also not include the "--password=" switch, since root has no password set by default (* root is secured to local network access only).

  1. Well done good explanation

    Now, you can set the cherry on the cake just adding some automatically mail the .cab or .zip archive to, why not, self gmail account :-)

    Roberto

  2. Good explanation. If i try to above code everything Working Fine.
    If i want take only one database. How i need write. I tried but i didn’t find the solution. Please some idea how to write

    1. To backup just 1 or 2 databases, try –

      "C:\path-to\mysql\bin\mysqldump.exe" --databases database-name1 database-name2 result-file="C:\path-to\backup\databases.%TIMESTAMP%.sql" --user=username --password=password
  3. If I want to run batch from client computer to make backup of mysql database running on server computer, what should the batch to be?

    1. The mysql client/binary can take parameters for connecting to an outside system/IP database server….

      You would place a copy of mysql (and its needed DLLs) on the client system, and use that.

  4. I’m a little confused. If you run this batch to dump the whole database on a schedule, how does using the binary log change things? I was researching how to do a full backup weekly and do incremental backups hourly and I thought I was going to have to flush the binary logs on a schedule and sync to a backup location?

  5. In testing my bat file from the command line, it rejected the use of UNC addresses for my network drives – will running it through the windows task scheduler allow UNC addresses, or do I need to use the mapped drive as well?

    1. Use the pushd command to change to the your UNC path (and then popd to undo the auto mapping pushd does behind the scene)…
      pushd "\\network-drive\dir\"
      …commands…
      popd

  6. Hi, I am running this locally on my computer and watching the cmd prompt window as it executes the commands. It creates the .SQL files, then the makecab runs and finishes, however I do not see the .cab file anywhere? I assume it supposed to go to the same directory as the .SQL file? Am I wrong or where is the .cab file?

  7. How about Encoding while backing up for UTF-8, when i’m check with backup file it’s warning me about that file it’s not UTF-8 encoded..
    Best Regards,
    KG

Leave a Reply to robert Cancel reply

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>