MySQL Dump GZIP: Export a Table or Database Safely

How I export a MySQL table or database with mysqldump and gzip, plus a 2026 update for safer passwords, consistent snapshots and verified backups.

MySQL Dump GZIP: Export a Table or Database Safely

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.

The original commands (2019)

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.

What is wrong with the old version

  • The password is visible. Writing -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.
  • No consistent snapshot. Without --single-transaction, a busy InnoDB database can change while the dump runs.
  • Missing objects. Stored procedures and events are not exported by default (triggers are).
  • Silent failures. If mysqldump fails, gzip still produces a valid but useless .gz file.

Update 2026: a safer MySQL dump with gzip, step by step

  1. Create a protected option file for the backup user, so the password never appears on the command line:

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.

  1. Write a small script that stops on errors and keeps the file private:

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

  1. For a single table, add the table name after the database name, exactly as in the original:

bash mysqldump --defaults-extra-file="$HOME/.my-backup.cnf" \ --single-transaction --no-tablespaces \ database_name tablename | gzip > "/path/to/table-$(date +%F).sql.gz"

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

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

Check it worked

  • gzip -t file.sql.gz returns nothing on success. Any message means the file is damaged.
  • Look inside without extracting: 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.
  • Test a real restore on a separate server or test database:
gunzip < backup.sql.gz | mysql --defaults-extra-file="$HOME/.my-backup.cnf" test_database

A backup you have never restored is only a hope.

Cautions

  • Restoring overwrites data. A dump normally contains DROP TABLE statements. Never restore into a live database without taking a fresh backup of it first, and double-check the target database name.
  • Protect the file. A dump contains all your data, including personal information. Keep permissions tight and encrypt copies stored off the server (for example with gpg or age).
  • Check your version. MySQL 5.7 is end of life. Plan for current supported releases such as 8.4 LTS. Do not rely on mysqlpump; it was removed in 8.4.
  • Large databases. For very big databases, MySQL Shell's dump utilities (util.dumpSchemas(), util.dumpInstance()) work in parallel and compress automatically. For small and medium databases, mysqldump with gzip is still perfectly fine.

Quick checklist

  • Password in a chmod 600 option file, not on the command line
  • --single-transaction --routines --events --no-tablespaces
  • set -euo pipefail and umask 077 in the script
  • gzip -t after every run
  • A regular test restore to a non-production database

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

Sources & further reading

  1. MySQL 8.4 Reference Manual: mysqldumpdev.mysql.com
  2. MySQL 8.4: End-User Guidelines for Password Securitydev.mysql.com
  3. MySQL 8.4: Using Option Filesdev.mysql.com
  4. MySQL 8.4: mysql_config_editordev.mysql.com
  5. MySQL Shell: Instance and Schema Dump Utilitiesdev.mysql.com
  6. MariaDB: mariadb-dumpmariadb.com

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

Akam Omer
Written by

Akam Omer

IT leader, engineer and security specialist from Erbil with 20+ years across higher education, telecoms, healthcare and government. Founder of BulkSMSIraq, VerifyWay and Kurd.ai.