How to Schedule MySQL Backups on Windows with a BAT Script

On a Windows Server running MySQL, you can automate logical backups by invoking the built‑in mysqldump utility from a batch (.bat) script that runs in a timed loop.

Understanding mysqldump

mysqldump connects to the MySQL server via the client protocol, extracts the selected data, and converts it into INSERT statements. Restoring the data simply requires executing those statements.

Typical command syntax:

mysqldump [options] --databases db_name [options table_name] > backup_file.sql

Key options include: --host ( -h) – server IP address --port ( -P) – server port --user ( -u) – MySQL username --password ( -p) – MySQL password --databases – specify databases to back up --all-databases – back up every database on the server --compact – produce compact output --complete-insert – generate full INSERT statements --lock-tables – lock tables before dumping --no-create-db / --no-create-info – omit CREATE statements --force – continue on errors --default-character-set – set default charset --add-locks – add LOCK TABLES statements

Backup Command Examples

Backup all databases:

mysqldump -uroot -p --all-databases > /backup/mysqldump/all.db

Backup a single database (e.g., test):

mysqldump -uroot -p test > /backup/mysqldump/test.db

Backup specific tables from a database (tables separated by spaces):

mysqldump -uroot -p mysql db event > /backup/mysqldump/2table.db

Backup a database while excluding certain tables:

mysqldump -uroot -p test --ignore-table=test.t1 --ignore-table=test.t2 > /backup/mysqldump/test2.db

Batch Script for Periodic Execution

A simple loop that runs every 5 seconds demonstrates the scheduling mechanism:

@echo off
title "badaodechengxvyuan"
set INTERVAL=5
:Again
  echo %date% %time:~0,8%
  timeout %INTERVAL%
  goto Again

The INTERVAL value defines the pause (in seconds) between iterations, creating a dead‑loop that repeatedly executes the enclosed commands.

Integrating mysqldump into the Loop

Insert the backup command inside the loop to perform the dump each cycle. The example below backs up a database named fzys to D:\fzysbak.sql using the full path to mysqldump (the executable is not added to the system PATH), and wraps the command in double quotes as required:

@echo off
title "fzys-data-bak-task"
set INTERVAL=20
:Again
  echo "fzys-bak-begin:"
  echo %date% %time:~0,8%
  "C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqldump" -h127.0.0.1 -uroot -p123456 fzys > D:\fzysbak.sql
  echo "fzys-bak-end:"
  echo %date% %time:~0,8%
  timeout %INTERVAL%
  goto Again

Backing Up Multiple Databases

To back up more than one database in the same script, repeat the mysqldump line for each target and add a ping command that acts as an 8‑second sleep, preventing the previous dump from affecting the next one:

@echo off
title "fzys-data-bak-task"
set INTERVAL=20
:Again
  echo "fzys-bak-begin:"
  echo %date% %time:~0,8%
  "C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqldump" -h127.0.0.1 -uroot -p123456 fzys > D:\fzysbak.sql
  echo "fzys-bak-end:"
  echo %date% %time:~0,8%
  ping 127.0.0.1 -n 8 > nul
  echo "fzysnacos-bak-begin:"
  echo %date% %time:~0,8%
  "C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqldump" -h127.0.0.1 -uroot -p123456 fzys-nacos > D:\fzys-nacosbak.sql
  timeout %INTERVAL%
  goto Again

The ping line creates a short pause (8 seconds) before the next dump starts, reducing the risk of resource contention.

Key Points

The batch file must use the full path to mysqldump if the executable is not in the system PATH.

Enclose the full path and its arguments in double quotes.

Output timestamps before and after each dump to track execution times.

Adjust INTERVAL to control how often backups run.