Automatically Backup MySQL Databases on Windows
Schedule automatic MySQL backups on Windows with mysqldump and Task Scheduler, updated for MySQL 8.4: routines, events and safer password handling.
How I export a MySQL table or database with mysqldump and gzip, plus a 2026 update for safer passwords, consistent snapshots and verified backups.

Back in 2019 I kept a short note for exporting a MySQL dump with gzip, so backups came out compressed in one step. The commands still run today, but if you copy them exactly you will see this warning: Using a password on the command line interface can be insecure. That warning is right, and there are a few other gaps worth closing.
Below is my original method, then an updated version I would use now.
Export a single table, compressed:
mysqldump -u [username] -p[password] database_name tablename | gzip > /path/to/backup-name.sql.gz
Export a whole database, compressed:
mysqldump -u [username] -p[password] database_name | gzip > /path/to/backup-name.sql.gz
The idea is simple: mysqldump writes SQL to standard output, the pipe sends it to gzip, and the result lands in a .sql.gz file. That core idea has not changed.
-p[password] puts it in your shell history, and other users on the same server may see it in the process list. The MySQL password security guidance warns against this.--single-transaction, a busy InnoDB database can change while the dump runs.mysqldump fails, gzip still produces a valid but useless .gz file.ini
# ~/.my-backup.cnf
[client]
user=backup_user
password=your_password_here
bash
chmod 600 ~/.my-backup.cnf
On MySQL you can instead store credentials with mysql_config_editor set --login-path=backup and call mysqldump --login-path=backup. Note this obscures the password rather than truly encrypting it.
```bash #!/usr/bin/env bash set -euo pipefail umask 077
OUT="/path/to/backup-$(date +%F).sql.gz"
mysqldump --defaults-extra-file="$HOME/.my-backup.cnf" \ --single-transaction --routines --events --no-tablespaces \ database_name | gzip > "$OUT"
gzip -t "$OUT" ```
--defaults-extra-file must be the first option, as described in the MySQL option files documentation. set -o pipefail makes the script fail if mysqldump fails, not just gzip. umask 077 means only your user can read the backup.
bash
mysqldump --defaults-extra-file="$HOME/.my-backup.cnf" \
--single-transaction --no-tablespaces \
database_name tablename | gzip > "/path/to/table-$(date +%F).sql.gz"
If you get "Access denied" about the PROCESS privilege, keep --no-tablespaces. In recent MySQL 8 releases, dumping tablespace information needs that privilege, and most backup users do not need it.
On MariaDB, use mariadb-dump with the same options. mysqldump is kept as a compatibility name, but the mariadb-dump documentation treats mariadb-dump as the main tool.
gzip -t file.sql.gz returns nothing on success. Any message means the file is damaged.zcat file.sql.gz | head -n 20 should show the dump header, and zcat file.sql.gz | tail -n 3 should end with -- Dump completed.gunzip < backup.sql.gz | mysql --defaults-extra-file="$HOME/.my-backup.cnf" test_database
A backup you have never restored is only a hope.
DROP TABLE statements. Never restore into a live database without taking a fresh backup of it first, and double-check the target database name.gpg or age).mysqlpump; it was removed in 8.4.util.dumpSchemas(), util.dumpInstance()) work in parallel and compress automatically. For small and medium databases, mysqldump with gzip is still perfectly fine.chmod 600 option file, not on the command line--single-transaction --routines --events --no-tablespacesset -euo pipefail and umask 077 in the scriptgzip -t after every runThe pipe from mysqldump to gzip that I used in 2019 is still the heart of it. What has changed is the care around it: hide the password, take a consistent snapshot, and prove the backup can actually be restored.
📝 First written from my own work in 2019; updated and expanded in November 2026 with AI assistance, keeping my original fix. Spotted something wrong? Tell me.