Skip to content
DBAExperts
MySQL Backups & recovery

How to back up MySQL with mysqldump

mysqldump produces a logical backup: a text file with the SQL statements needed to recreate your data. It is the simplest and most portable way to back up, ideal for small and medium databases. For large databases or strict RPO requirements, physical backups are worth evaluating.

  1. 1

    Back up a complete database

    Use --single-transaction to get a consistent snapshot without locking your InnoDB tables. Add --routines and --triggers to include stored procedures and triggers.

    mysqldump --single-transaction --routines --triggers \
  -u usuario -p mi_base > mi_base_2026-07-04.sql
  2. 2

    Back up every database on the server

    With --all-databases you also include the system databases. Handy for migrating an entire server.

    mysqldump --all-databases --single-transaction --routines --triggers \
  -u root -p > servidor_completo.sql
  3. 3

    Compress the backup on the fly

    A dump compresses very well because it is plain text. This saves space and transfer time.

    mysqldump --single-transaction -u usuario -p mi_base | gzip > mi_base.sql.gz
  4. 4

    Verify that the backup actually works

    A backup you never tested is not a backup. Check that the file is not empty and, whenever you can, restore it on a test server.

    ls -lh mi_base_2026-07-04.sql
tail -n 5 mi_base_2026-07-04.sql   # debe terminar con "-- Dump completed"

// common mistake

Do not pass the password on the command line with -pMYPASSWORD: it ends up exposed in your shell history and in the process list. Use -p with no value (it will prompt you) or an options file with 600 permissions.

// when it's worth an expert

Once your database is into the hundreds of GB, mysqldump gets slow to back up and very slow to restore. At dba.mx we design strategies with physical backups and automated validation, at a fixed price and with proven recovery times.

Book an assessment — from USD $550

This guide is for reference and uses example commands. In production, adapt to your version and test in a safe environment first.