How to Backup MySQL with mysqldump and Restore a Database on Linux

Take a simple dump of a specific database:

mysqldump -u root -p mydb > mydb-2026-05-29.sql

The -u root sets the user, -p prompts for the password interactively (safer than passing -ppassword on the command line, which leaks into the shell history and into ps aux). The > redirection saves the output to the named file.

Include the date in the file name from the start — it helps with rotation and prevents accidental overwrites.

Add the transactional consistency flags for production:

mysqldump -u root -p \
  --single-transaction \
  --routines \
  --triggers \
  --events \
  --quick \
  mydb > mydb-2026-05-29.sql

--single-transaction wraps the dump in START TRANSACTION with REPEATABLE READ isolation, producing a consistent snapshot without blocking writes on InnoDB tables. That is the critical flag in production: without it, parallel writes can leave the dump inconsistent.

The --routines, --triggers, and --events flags include stored procedures, triggers, and scheduled events — things that are easy to forget and that you only notice are missing when you restore and the system breaks. --quick does the dump row-by-row instead of loading whole tables into RAM, important for large tables.

Compress on the fly to save space:

mysqldump -u root -p \
  --single-transaction --routines --triggers --events --quick \
  mydb | gzip > mydb-2026-05-29.sql.gz

The pipe to gzip shrinks the file by 70-85% for typical dumps (SQL text compresses very well). For 10 GB databases, that is the difference between 2 GB and 10 GB taking space on your backup disk.

For better (slower) compression, swap gzip for zstd -19 or xz -9.

Back up all databases at once (useful for a daily snapshot of the entire server):

mysqldump -u root -p \
  --all-databases \
  --single-transaction \
  --routines --triggers --events \
  --master-data=2 \
  | gzip > full-2026-05-29.sql.gz

--all-databases also includes the system databases (mysql, sys, performance_schema) — preserving users and grants. --master-data=2 records the binary log position in the dump as a comment, useful for point-in-time recovery if you have binlogs enabled.

Check that the file was not truncated:

ls -lh mydb-2026-05-29.sql.gz
gzip -t mydb-2026-05-29.sql.gz && echo "OK: file is intact"

gzip -t tests the compression integrity without decompressing. If it fails, the file is corrupted (usually disk full during the dump or a crash mid-way). Re-run the dump.

Inspect the dump content:

zcat mydb-2026-05-29.sql.gz | head -30
zcat mydb-2026-05-29.sql.gz | grep -c "^INSERT INTO"
zcat mydb-2026-05-29.sql.gz | grep "^CREATE TABLE" | wc -l

head shows the header with the mysqldump version and settings. grep -c INSERT counts insert lines (a sanity check on data volume). grep CREATE TABLE | wc -l counts tables — does it match what you expect to have in the database?

To compare against the original server:

mysql -u root -p -e "SELECT COUNT(*) AS tables FROM information_schema.tables WHERE table_schema='mydb';"

The numbers should match.

Create the empty destination database (only if it does not already exist):

mysql -u root -p -e "CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"

Always use utf8mb4 — it supports emoji and full Asian characters. You can skip this step if the dump was made with --databases (which includes CREATE DATABASE in the file) or --all-databases.

Restore the compressed dump directly through a pipe:

zcat mydb-2026-05-29.sql.gz | mysql -u root -p mydb

zcat decompresses in memory and sends it to the mysql client, which executes each command against the mydb database. For an uncompressed .sql file, use redirection:

mysql -u root -p mydb < mydb-2026-05-29.sql

The operation takes time proportional to the size (each INSERT runs individually by default). For large dumps, leave it running inside tmux or screen so it does not stop if the SSH session drops.

Track progress on large dumps:

pv mydb-2026-05-29.sql.gz | zcat | mysql -u root -p mydb

pv (pipe viewer) shows a progress bar and speed. Install it with sudo apt install -y pv. Without it, the restore runs silently — you have no idea whether you are at 10% or 90%.

Confirm structure and counts:

mysql -u root -p mydb -e "SHOW TABLES;"
mysql -u root -p mydb -e "SELECT COUNT(*) FROM users;"

The first lists the tables; the second counts rows on a table for which you know the expected count. Discrepancies indicate an incomplete dump (a missing flag) or an interrupted restore.