How to Back Up and Restore MySQL Databases with Mysqldump

This tutorial explains how to back up and restore MySQL or MariaDB databases from the command line using the mysqldump utility.

The backup files created by mysqldump are a set of SQL statements that can be used to recreate the original database. The mysqldump command can also generate files in CSV and XML format.

You can also use mysqldump to transfer your MySQL database to another MySQL server.

Quick Reference #

Mysqldump Command Syntax #

Let us start by reviewing the basic syntax before going into the examples.

The mysqldump command syntax is:

mysqldump [options] > file.sql

• options - The mysqldump options
• file.sql - The dump (backup) file

To use the mysqldump command, the MySQL server must be accessible and running.

Common mysqldump Options #

The mysqldump command accepts many options that control what is included in the dump and how the output is formatted. The table below lists the options you will use most often.

By default, mysqldump includes triggers but not stored procedures or events. To create a complete backup that also includes routines and events, combine the options:

mysqldump -u root -p --single-transaction --routines --events database_name > database_name.sql

The resulting dump file recreates the database together with its stored procedures, functions, and scheduled events.

If you only need a few tables instead of the whole database, list the table names after the database name:

mysqldump -u root -p database_name table1 table2 > tables.sql

This command backs up only table1 and table2 from database_name, which is faster when you do not need a full dump.

Back Up a Single MySQL Database #

The most common use case of the mysqldump tool is to back up a single database.

For example, to create a backup of the database named database_name using the user root and save it to a file named database_name.sql, run the following command:

mysqldump -u root -p database_name > database_name.sql

You will be prompted to enter the root password. After successful authentication, the dump process will start. Depending on the database size, the process can take some time.

If you are logged in as the same user that you are using to perform the export and that user does not require a password, you can omit the -u and -p options:

mysqldump database_name > database_name.sql

If your database uses InnoDB tables (the default engine in MySQL 5.5 and later), add the --single-transaction option to produce a consistent snapshot with minimal locking impact:

mysqldump --single-transaction -u root -p database_name > database_name.sql

Without this option, mysqldump can lock tables during parts of the dump process, which may block writes on busy servers depending on storage engine and dump options.

Back Up Multiple MySQL Databases #

To back up multiple MySQL databases with one command, use the --databases option followed by the list of databases you want to back up. Each database name must be separated by a space.

mysqldump -u root -p --databases database_name_a database_name_b > databases_a_b.sql

The command above will create a dump file containing both databases.

Back Up All MySQL Databases #

Use the --all-databases option to back up all the MySQL databases:

mysqldump -u root -p --all-databases > all_databases.sql

Same as with the previous example, the command above will create a single dump file containing all the databases. To list all available databases before running the backup, see How to Show Databases in MySQL .

Back Up All MySQL Databases to Separate Files #

The mysqldump utility does not provide an option to back up all user databases to separate files, but we can do that with a simple bash for loop . The command below skips MySQL system schemas and quotes the database name before passing it to mysqldump. You can save this as a script and run it :

mysql -e 'SHOW DATABASES' -s --skip-column-names |
while read -r DB; do
    case "$DB" in
        information_schema|performance_schema|mysql|sys) continue ;;
    esac

    mysqldump --single-transaction --routines --events "$DB" > "$DB.sql"
done

The command above creates a separate dump file for each user database using the database name as the filename.

Create a Compressed MySQL Database Backup #

If the database size is very large, it is a good idea to compress the output. To do that, pipe the output to the gzip utility and redirect it to a file:

mysqldump database_name | gzip > database_name.sql.gz

Create a Backup with Timestamp #

If you want to keep more than one backup in the same location, you can add the current date to the backup filename:

mysqldump database_name > database_name-$(date +%Y%m%d).sql

The command above will create a file with the following format: database_name-20240101.sql.

Restoring a MySQL Dump #

You can restore a MySQL dump using the mysql tool. The general syntax of the command is as follows:

mysql database_name < file.sql

In most cases you will need to create a database to import into. If the database already exists, first you need to delete it.

In the following example, the first command will create a database named database_name and then import the dump database_name.sql into it:

mysql -u root -p -e "create database database_name";
mysql -u root -p database_name < database_name.sql

Restore a Single MySQL Database from a Full MySQL Dump #

If you backed up all your databases using the --all-databases option and you want to restore a single database from a backup file that contains multiple databases, use the --one-database option as shown below:

mysql --one-database database_name < all_databases.sql

Export and Import a MySQL Database in One Command #

Instead of creating a dump file from one database and then importing the backup into another MySQL database, you can use the following one-liner:

mysqldump -u root -p database_name | mysql -h remote_host -u root -p remote_database_name

The command above will pipe the output to a mysql client on the remote host and import it into a database named remote_database_name. Before running the command, make sure the database already exists on the remote server. For more on connecting MySQL to a remote host, see How to Allow Remote Connections to MySQL .

Automate Backups with Cron #

Automating the process of backing up the databases is as simple as creating a cron job that will run the mysqldump command at a specified time.

To set up automated backups of a MySQL database using a cron job, follow the steps below:

1. Create a file named .my.cnf in your user home directory:

   Terminal

   sudo nano ~/.my.cnf

   Copy and paste the following text into the .my.cnf file:

   ini

   [client]
   user = dbuser
   password = dbpasswd

   Do not forget to replace dbuser and dbpasswd with the database user and user’s password. For instructions on creating a dedicated backup user, see How to Create MySQL User Accounts and Grant Privileges .

2. Restrict permissions of the credentials file so that only your user has access to it:

   Terminal

   chmod 600 ~/.my.cnf

3. Create a directory to store the backups:

   Terminal

   mkdir ~/db_backups

4. Open your user crontab file:

   Terminal

   crontab -e

   Add the following cron job that will create a backup of a database named mydb every day at 3am:

   Terminal

   0 3 * * * /usr/bin/mysqldump -u dbuser mydb > /home/username/db_backups/mydb-$(date +\%Y\%m\%d).sql

   Do not forget to replace username with your actual username. We are also escaping the percent signs (%) because they have special meaning in crontab (they are treated as newline characters).

You can also create another cron job to delete any backups older than 30 days:

find /path/to/backups -type f -name "*.sql" -mtime +30 -delete

You need to adjust the command according to your backup location and file names. To learn more about the find command, see How to Find Files in Linux Using the Command Line .

Troubleshooting #

Access denied error when running mysqldump
The MySQL user must have the privileges required for the objects and options being dumped. At a minimum, it needs SELECT on dumped tables. Add SHOW VIEW for views, TRIGGER for triggers, EVENT for events, and LOCK TABLES if you are not using --single-transaction. MySQL also requires PROCESS for tablespace information unless you add --no-tablespaces.

mysqldump: Got error: 2002: Can’t connect to MySQL server
MySQL is not running or the socket path is wrong. Start the service with sudo systemctl start mysql (Debian/Ubuntu) or sudo systemctl start mysqld (Fedora/RHEL) and try again.

ERROR 1153: Got a packet bigger than ‘max_allowed_packet’
This occurs when restoring a dump that contains large rows. Increase the limit by passing --max_allowed_packet=512M to both mysqldump when creating the dump and to mysql when restoring it.

Charset or collation mismatch after restore
Add --default-character-set=utf8mb4 to the mysql restore command to ensure the client uses the same character set as the dump file.

FAQ #

Can I back up a single table instead of a whole database?
Yes. Specify the table name after the database name: mysqldump -u root -p database_name table_name > table_name.sql.

How do I back up only the database structure without the data?
Use the --no-data option: mysqldump --no-data -u root -p database_name > schema.sql. This exports only the CREATE TABLE statements.

What privileges does the backup user need?
For a basic dump, the user needs SELECT on the tables being backed up. Add SHOW VIEW if the database has views, TRIGGER for triggers, and EVENT for scheduled events. If you are not using --single-transaction, add LOCK TABLES. On MySQL 8, add PROCESS unless you use --no-tablespaces.

What is the difference between mysqldump and mysqlpump?
mysqldump is single-threaded and processes databases sequentially. mysqlpump supports parallel processing and can be faster for some large exports, but many production workflows still standardize on mysqldump because it is widely available and predictable across MySQL and MariaDB environments.

Is it safe to store the backup file on the same server?
No. A disk failure or server compromise would also affect the backup. Store backups on a separate server, remote storage, or an offsite location.

Conclusion #

This tutorial covers the most common mysqldump use cases for backing up and restoring MySQL databases from the command line. It is a good starting point for anyone who wants to manage MySQL backups reliably.

If you want to learn more about working with MySQL from the command line, take a look at our How to Manage MySQL User Accounts and Databases guide. You can also learn how to delete a MySQL database or query data across multiple tables with our MySQL JOIN tutorial.

You can also check the tutorial about how to reset a MySQL root password in case you have forgotten it.

The safest backup routine is one you test regularly. After creating a new dump script, restore one backup into a temporary database and confirm that the schema and data are usable.

About the authors

Dejan Panovski

Dejan Panovski is the founder of Linuxize, an RHCSA-certified Linux system administrator and DevOps engineer based in Skopje, Macedonia. Author of 800+ Linux tutorials with 20+ years of experience turning complex Linux tasks into clear, reliable guides.

View author page