When you move an application to another server, you need a way to carry its database with it. mysqldump creates a text-based SQL backup that the mysql client can import on a server.
This article explains how to choose which databases to include and how to restore a plain or compressed dump.
TL;DR
Use mysqldump for a logical MySQL backup, then load the SQL file with mysql and check the restored database before relying on it.
- For one database, run mysqldump -u backup_user -p appdb > appdb.sql.
- Several named databases: add –databases appdb reports.
- All databases: use –all-databases.
- Restore a plain dump with mysql -u backup_user -p appdb < appdb.sql.
What is a MySQL database backup with mysqldump?
mysqldump is a command-line client that reads database objects and rows from a MySQL server and writes SQL statements to standard output. Redirecting that output to a file creates a logical backup, a text representation that the mysql client can execute later.
A logical dump is different from a physical backup, which copies database files in their storage format. SQL dumps are inspectable and can be loaded into another server, but replaying a large dump can take time. The right command depends on whether the destination file should contain one database, several, or the full server.
| Backup scope | Command form | Database statements in dump |
|---|---|---|
| One database | mysqldump appdb | No CREATE DATABASE or USE by default |
| Selected databases | mysqldump –databases appdb reports | Includes database selection statements |
| Every database | mysqldump –all-databases | Includes multiple database sections |
The difference matters at restore time. A one-database dump without those statements needs a destination database selected on the mysql command line, while a dump made with –databases can create and select its named databases when imported.
If an import reports an unknown database, check whether the dump contains CREATE DATABASE and USE statements or create the target schema first. If mysqldump reports access denied, verify which MySQL user connected and whether it can read the requested objects. Adding sudo changes Linux permissions, not the privileges of the MySQL account named with -u.
How to back up and restore MySQL databases step by step
Use a MySQL account allowed to read the objects being exported and a directory where your Linux user can write. The commands below use appdb and reports as sample database names. Replace them with names on your server.
Step 1: Back up one database
Pass the database name after the connection options and redirect standard output to a .sql file. The -p flag prompts for the MySQL password, so the password does not appear in the command history.
mysqldump -u backup_user -p appdb > appdb.sql
The shell creates or replaces appdb.sql before mysqldump starts. A file appearing does not prove the export succeeded, so check the command’s exit status and inspect its size before treating it as a backup. The existing terminal capture below illustrates the older command form and password prompt, not a successful dump result.
For InnoDB tables, –single-transaction requests a consistent transaction snapshot. It avoids holding table locks for the full dump, which matters when applications continue writing while the export runs.
mysqldump --single-transaction -u backup_user -p appdb > appdb.sql
This option does not make MyISAM or other nontransactional tables consistent, and concurrent schema changes can still interfere. Check the storage engines and the MySQL documentation before choosing a live backup method for mixed tables.
Step 2: Back up selected databases
Put –databases before the names when one file should carry more than one named database. Separate database names with spaces, and use one output file for the combined dump.
mysqldump -u backup_user -p --databases appdb reports > selected-databases.sql
This form includes database creation and selection statements in the output. The existing screenshot uses different sample names but shows the same –databases option. It illustrates syntax, not a verified result.
Step 3: Back up every database
Use –all-databases when the backup scope is the full MySQL instance rather than a short list. Confirm the account has privileges for every object included in that export.
mysqldump -u backup_user -p --all-databases > all-databases.sql
An all-database file can include system schemas, so importing it may recreate objects beyond the application database you meant to recover.
Tables and rows are not the only objects an application may depend on. Triggers are included by default, while stored procedures, functions and scheduled events need their corresponding dump options when those objects are part of the recovery scope.
mysqldump --single-transaction --routines --events -u backup_user -p appdb > appdb-complete.sql
Choose those options deliberately and check the MySQL version’s privilege requirements. Extra objects can need extra permissions, so a dump that omits them may take less time to run but leave out objects the application relies on.
Step 4: Compress the dump as it is written
Pipe mysqldump output into gzip when you want a compressed SQL stream without first writing the uncompressed file. Redirect the compressed stream to a filename ending in .sql.gz.
set -o pipefail
mysqldump --single-transaction -u backup_user -p appdb | gzip > appdb.sql.gz
pipefail makes the pipeline report a failed mysqldump instead of only the status of gzip. A file may remain after a failed pipeline, so check the status and test the compressed stream with gzip -t before moving it. The existing screenshot shows the pipe but does not test its output.
gzip -t appdb.sql.gz
gzip -dc appdb.sql.gz | mysql -u backup_user -p appdb
gzip -dc streams the decompressed SQL to standard output, which mysql reads as input. The database selected after the connection options is the restore target for a plain single-database dump.
Step 5: Restore into a chosen database
A plain dump made without –databases does not select or create its destination database. Create an empty target first, then redirect the SQL file into mysql with that target selected.
mysql -u backup_user -p -e 'CREATE DATABASE appdb_restore'
mysql -u backup_user -p appdb_restore < appdb.sql
For a dump created with –databases, import without naming a single target so its statements can select and create the included schemas. Restore to a test instance or a deliberately empty target first when production data must remain untouched.
mysql -u backup_user -p < selected-databases.sql
The older screenshot illustrates a single-database import command, but a prompt returning without an error does not prove that the restored schema is complete.
After import, query the restored schema or compare expected tables and application behavior, because gzip -t checks only whether the compressed stream can be read.
For a combined dump, inspect the CREATE DATABASE statements before importing because they may target the original names, then choose a test server or adjust the restoration plan.
Common errors with MySQL dumps
MySQL dumps can fail because the account lacks access, the source tables cannot share one transaction snapshot, or the output file is incomplete. Check the command status and the server message before treating a dump as usable.
- Access denied: Grant only the MySQL privileges the chosen dump options need, then rerun the export. Linux file permissions and sudo do not grant privileges inside MySQL.
- Inconsistent live data: –single-transaction gives a consistent snapshot for InnoDB, not nontransactional tables. Check the table engines and avoid schema changes during the dump.
- Partial output: A redirected file can exist even when mysqldump fails. Check its exit status, and use pipefail for a gzip pipeline so the dump command’s failure reaches the shell.
- Unknown database on import: A plain single-database dump does not select or create its target. Create the target first, or use the restore form that matches a dump made with –databases.
What should you check before relying on a MySQL dump?
A dump is useful only when its contents can be read back into a suitable server. Check access, consistency, file handling and restore behavior rather than relying on the filename or a command that returned to the prompt.
- Access denied: mysqldump needs privileges for the objects it reads. SELECT covers table data, while views and triggers can require SHOW VIEW and TRIGGER. Options can add further privilege requirements.
- Live writes: –single-transaction is intended for InnoDB tables. It does not provide a consistent snapshot for MyISAM tables, and DDL changes during a dump may cause problems.
- Credentials: Avoid putting a password directly after -p. MySQL client option files can hold connection settings, but keep a manually created ~/.my.cnf readable only by its owner, for example with chmod 600 ~/.my.cnf.
- Partial output: Use a temporary destination and rename it only after mysqldump exits successfully. For a pipeline, enable pipefail, otherwise the shell may report gzip’s status rather than mysqldump’s.
- Recovery scope: A selected-database dump has CREATE DATABASE statements, a plain one-database dump normally needs a pre-created target. Verify which format you have before importing.
mysqldump is a logical backup tool, not a full disaster-recovery plan. MySQL documents binary-log-based point-in-time recovery separately, using binary logs as well as a suitable base backup. For very large databases, compare logical export and physical backup methods using the official MySQL documentation.
Keep the backup account limited to the access its dump options require. MySQL lists SELECT for table data and additional privileges for objects such as views and triggers. Options that inspect tablespace metadata or change global state can require more privileges, so check the requirements for the exact command instead of granting broad administrative rights by default.
A restore drill is the check that connects the file to an actual recovery path. Load a copy into a separate database or server, confirm the expected schemas and tables exist, then test a representative application read. Do not run a test import against a live database that contains the only current copy of the data.
Conclusion: keep the restore path with the backup
Choose a positional database name for one schema, –databases for a selected set, or –all-databases for the server-wide dump. Then test the restore in a separate target. That is how you find out whether the file can do the job you saved it for.
For compression, see the gzip command in Linux. A separate-file automation loop can build on the Bash for loop or Bash while loop.
If the backup directory needs ownership changes, review the chown command in Linux. The tar command in Linux covers packaging files for transfer, which is separate from creating the SQL dump itself.
How do I back up one MySQL database on Linux?
Run mysqldump -u backup_user -p appdb > appdb.sql, replacing the user and database names. The -p option prompts for the MySQL password.
How do I back up multiple MySQL databases in one file?
Use mysqldump -u backup_user -p –databases appdb reports > selected-databases.sql. Separate database names with spaces.
How do I restore a MySQL dump on Linux?
For a plain one-database dump, create or select the target database and run mysql -u backup_user -p appdb_restore < appdb.sql. Dumps made with –databases contain database selection statements.
How do I create and restore a compressed MySQL backup?
Pipe mysqldump output into gzip and redirect it to a .sql.gz file. Check it with gzip -t, then stream it into mysql with gzip -dc file.sql.gz | mysql -u backup_user -p appdb.