The Complete mysqldump Guide (with examples)

There are many database solutions out there, but MySQL is one of the most popular. Using MySQL is vital to keeping these databases secure and running smoothly.

Managing and backing up servers and databases can all be done in MySQL.
To help these complex processes run smoothly, utilities have been developed. Mysqldump is one of those tools meant to make the lives of developers easier.

In this guide, we are going to walk you through what mysqldump is, how to use it, identify some common errors, and provide some clear examples along the way so that you will be able to use mysqldump effectively.

💡 This guide is part of our "MySQL Guides" section where we bundle all the MySQL related guides we have on our blog.

🧑‍💻 All the code in this article is bundled in this Gist.

Table of Contents

The importance of backing up MySQL database

Let's first dive into the world of MySQL and why 'mysqldump' should be your trusty sidekick. You already know how vital it is to safeguard your data in the digital realm, and 'mysqldump' is your ally in achieving just that.

In the tech realm, data can be unpredictable. It's not just major disasters; even the little hiccups can throw your code for a loop. This is where 'mysqldump' steps in.

Think of it as your 'Ctrl+Z' for databases. When things take a wrong turn, you can effortlessly roll back to a safer point. It's like having a version history for your data, ensuring you're never stuck with unintended changes.

Plus, whether you're shifting to new servers or setting up different development environments, 'mysqldump' is your assurance against data loss. It's like moving your tech world without misplacing a single piece. Your digital realm stays intact and always ready for action. Make 'mysqldump' your go-to companion in the world of data security.

What is mysqldump?

MySQL is a database system that has been around for years and continues to be one of the most popular choices for websites. It's open-source and agile. Developers can use these databases to store anything a website may need. The information stored in online databases can range from consumer info and simple text to picture galleries to network information.

Mysqldump is part of the relational database package for MySQL.
It is used to back up all the data in a database into a single text file. These files or "dumps" can be generated for individual databases or a collection of them. The text in the file is displayed as a set of SQL statements that can later be reconstructed into its original state.

The purpose of this tool is to export a database to a backup file or to move the database to another web host. Other delimited text formats like XML and CSV can also be generated with mysqldump. These simple queries make the backup process easier.

What should you use mysqldump for?

• MySQL Database Backup: Use mysqldump to create secure backups, protecting your data against loss or corruption.
• MySQL Database Versioning: create versions of your database, kind of like GIT tags you can revert to at any stage
• MySQL Database Migration: mysqldump simplifies database transfers, ensuring data integrity during server or environment transitions.
• Setting up Development Environments: mysqldump aids in replicating production data for consistent and reliable development and testing setups.

Format of exported data

• SQL Statements: The default format generates a SQL script that can be used to recreate the database structure and data.
• Compressed Files: mysqldump can produce compressed output files in formats like .gz (gzip) or .bz2 (bzip2), which save space.
• XML Format: It can also generate XML-formatted data for easier integration with other systems or data interchange.
• Tab-Delimited Text: You can use --tab option to create tab-delimited text files, which are useful for data export and import.
• CSV (Comma-Separated Values): By using the --fields-terminated-by and --lines-terminated-by options, mysqldump can produce CSV files, which are commonly used for data exchange.
• Custom User-Defined Formats: You can define your own custom output format using the --tab option and specifying custom delimiters and formats.

How to use mysqldump?

As a developer, you can leverage mysqldump to get a hold of the .sql file, which acts as a comprehensive database backup.
To use this tool, you'll require access to the MySQL server running the database instance, along with the necessary privileges for exporting data. Don't forget to have your database user credentials handy, including the username and password.

How to access mysqldump?

The mysqldump command-line tool typically comes bundled with either the MySQL client or MySQL server installation. To verify if mysqldump is available on your local system, you can run the following command:

which mysqldump

If the tool is not installed, this command will yield an error message.

To check the version of mysqldump installed on your system, use the following command:

mysqldump --version

For guidance on using the mysqldump command, you can access its help documentation by running:

mysqldump --help

What are the privileges required to use mysqldump?

You will need a valid database user with -at minimum- full read access privileges. This should do for basic options, but more advanced commands may require additional privileges.

To use mysqldump, you typically need the following privileges:

1. SELECT privilege: Required for reading data.
2. SHOW VIEW privilege: Needed for accessing view definitions.
3. LOCK TABLES privilege: Necessary for table locking.
4. RELOAD privilege: Used with --flush-privileges option.

Export your MySQL database using mysqldump

We'll see below how to use mysqldump to perform the following MySQL tasks:

1. Backup specific tables in a MySQL database
2. Backup a single table in a MySQL database
3. Backup specific MySQL databases
4. Backup a single MySQL database
5. Backup the entire MySQL server (all databases)
6. Backup MySQL database excluding specific tables
7. Backup MySQL database structure only

For each of these tasks, we will provide the command line to use, and we will also explain the different options you can use to customize your mysqldump command.
Make sure you are on a machine that has mysqldump is installed.

ℹ️ By default, mysqldump dumps all tables in a MySQL database, but there are certain types of tables that it does not include in the dump. These are:

1. Temporary Tables: mysqldump does not include temporary tables in the dump.

2. System Tables: It also excludes system tables, which are used internally by MySQL and are not meant for regular user data.

3. Performance Schema Tables: mysqldump does not include tables from the Performance Schema database.

4. Information Schema Tables: Tables from the Information Schema database are also not included.

   It's important to note that mysqldump provides options that allow you to customize the dump behavior. For example, you can use the --ignore-table option to exclude specific tables from the dump, including regular user-created tables. You can also use the --no-data option to exclude table data while preserving the table structure.

Backup specific MySQL tables via mysqldump

Match your inputs to conform to the following mysqldump command structure:

mysqldump [options] db_name [table_name ...]

For the live command, replace [options] with the valid option names or flags.
These will most likely include -u and -p, which stands for user and password.

ℹ️ Check out the comprehensive table of all the options that mysqldump supports.

⚠️ When using more than one option, be careful of the order they are listed in because they will be processed in order from first to last.
Here, [table_name…] will be replaced by the name of the table you are exporting.

⚠️ Different tables must be separated by spaces.
The following example is for backing up tables called mystery and cash, and the name of the database is db_cooper.

mysqldump -u username -p db_cooper mystery cash > file_name.sql

You will then provide the password for the database user because it is not passed along with the -p flag.
The > character indicates the output redirection that will create the dump file. Here, file_name is what the final file will be called.

Backup a single MySQL table via mysqldump

As described in the previous section, you can export a single table by listing it after the database name.

The following example is for backing up tables called mystery and the name of the database is db_cooper.

mysqldump -u username -p db_cooper mystery > file_name.sql

Backup specific MySQL databases via mysqldump

The steps for exporting a database are very close to those for exporting a table. There is just a small change in the format of the command.

Match your inputs to conform to the following mysqldump command structure:

mysqldump -u username -p --databases db_larry db_curly db_moe > file_name.sql

The databases you will export comes after the --databases option.

⚠️ The space character separates multiple databases.

Backup a single MySQL database via mysqldump

Very similar to exporting multiple databases, you'll just need to specify one database name.

Match your inputs to conform to the following mysqldump command structure:

mysqldump -u username -p --databases db_larry > file_name.sql

The database you will export comes after the --databases option.

Backup a MySQL server (all databases)

The command is very similar for entire servers as well.

Match your inputs to conform to the following mysqldump command structure:

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

The command itself is pretty basic, with --all-databases indicating that everything on the server should be dumped.

💡 Adding -compatible will make the file that gets exported compatible with older MySQL servers or database systems.
💡 Developers using PowerShell on Windows will need to include -result-file as an option. This will specify the file name and make sure that the output is in ASCII format so that it will load correctly later.

Other common options include adding --no-data will only back up the database structure, using --no-create-info backs up the database without any structure.

Backup MySQL database excluding specific tables

To back up a MySQL database while excluding specific tables, you can use the mysqldump command with the --ignore-table option. Here's the command format:

mysqldump -u username -p database_name --ignore-table=database_name.table1 --ignore-table=database_name.table2 > database_backup.sql

Replace the placeholders with the following:

• database_name: The name of the database you want to back up.
• table1, table2, etc.: The names of the specific tables you want to exclude from the backup.

By using the --ignore-table option and specifying the database name and table names to be excluded, you can create a backup of the entire database while leaving out the specified tables.

Backup MySQL database structure only

To back up the structure of a MySQL database (without data), you can use the mysqldump command with the --no-data option. Here's the command to do this:

mysqldump --no-data -u username -p database_name > database_structure.sql

Replace the following placeholders:

• database_name: The name of the database you want to back up.

When you run this command, it will create an SQL file (database_structure.sql) that contains the database's structure, including table schemas, indexes, and other structural elements, but without the actual data.

How to restore / import a MySQL mysqldump dump file?

There are different methods and tools to import your MySQL dump file into a database.

Check our mini guide on how to import SQL files for more dump restore options.

We'll here cover how to do it using the mysql command line tool.

Importing a .sql file is straight forward, the only kink is to make sure the target server has a blank database before importing anything.

Step 1: Make sure you have a MySQL database created on the target machine.

mysql -u root -pYOUR_PASSWORD -e "CREATE DATABASE destination_db

This command will create your database on the target machine.

Step 2: Import the dump file into the database you just created.

mysql -u root -pYOUR_PASSWORD destination_db < db_backup.sql

⚠️ Note that this command will overwrite the content of the destination_db database.

Mysql Backup complete examples

Backup and restore all MySQL databases

Step1: First, let's create a mysql backup by generating a dump file for all databases on the server.

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

Now that we have our dump file ready, let's restore it to a new server.

Step 2: We'll first create a new database on the target server.

mysql -u root -pYOUR_PASSWORD -e "CREATE DATABASE destination_db

Step 3: End finally, we'll import the dump to that new database.

mysql -u root -pYOUR_PASSWORD destination_db < db_backup.sql

That's it! You now have a full backup of your MySQL server.

Backup and restore a single MySQL table

Step1: First, let's create a mysql backup by generating a dump file of the single table my_table from the database db_cooper.

mysqldump -u username -p db_cooper my_table > single_table_dump.sql

Now that we have our dump file ready, let's restore it to a new server.

In this case we won't create any database, as we're willing to only restore that single table into an existing database.

Step 2: Let's import the dump to that new database.

mysql -u root -pYOUR_PASSWORD destination_db < single_table_dump.sql

That's it! You now have a full backup of your MySQL server.

Restore a MySQL database on a remote MySQL server

Ever needed to restore a MySQL backup on a remote server? Well the simple solution is to use simplerestore.io, a free tool that allows you to privately restore a MySQL database from a backup dump directly to a remote MySQL server.

This doesn't require you to have access to import tools and builds up a temporary SSH tunnel to the remote server to restore the database.

Advanced mysqldump tips and tricks

What does the mysqldump --quick flag do?

Mysqldump can operate in one of two ways.

1. The tool can go grab all data at once, buffer it in memory, and then dump it.
2. It dumps the tables row by row.

The second method is important when dealing with large tables.
By using the --quick flag, mysqldump reads large databases without needing large amounts of RAM to fit the full table into the memory.
This ensures that the databases will be read and copied correctly on systems with small amounts of RAM and large data sets.

Dump without locking tables and the --skip-lock-tables flag

Using --skip-lock-tables prevents table locking during the dump process. This is important when backing up a production database that you cannot lock it down during the dump.

Generally it is recommended to use --skip-lock-tables whenever you are dumping InnoDB tables. However, for MyISAM tables, you may need to lock tables for the sake of data consistency.

So should I use --skip-lock-tables?

• If you are backing up InnoDB tables, yes. Combine it with --single-transaction for best results.
• If you are backing up MyISAM tables, on a production server, do not use --skip-lock-tables unless you can keep your database tables locked during the backup process.

mysqldump -u root -pmypassword my_database --skip-lock-tables > my_database.sql

What does the --single-transaction flag do?

In short, the --single-transaction allows MySQL InnoDB dumps to have a consistent state of the database. It tells MySQL that we are about to dump the database, thus, breaking changes like table structure queries will be blocked to preserve data consistency. Note that this only applies for InnoDB tables.

mysqldump -u root -pmypassword my_database --single-transaction > my_database.sql

Note: MyISAM tables will not benefit from this flag and should be locked if you want to preserve their dump integrity.

How to dump large tables?

To dump large tables, you could combine the following two flags, --single-transaction and --quick.

mysqldump -u root -pmypassword my_large_db --single-transaction --quick > my_large_db.sql

Note: This is ideal for InnoDB tables. Since it will use less RAM and also produce consistent dumps without locking tables.

How to ignore tables using mysqldump?

Using the –-ignore-table option, you can ignore a table when using mysqldump.

Here is an example that will just allow you to ignore one table:

mysqldump -u root -pmypassword my_db –-ignore-table=my_db.table_to_ignore > my_db.sql

As you seen, the format is as following: –-ignore-table=DATABASE_NAME.TABLE_TO_IGNORE.

To ignore all tables in a database (or a whole database when dumping all your databases), you have to repeat the argument to include all the tables you want to ignore.

mysqldump -u root -pmypassword –-ignore-table="my_db.table1" –-ignore-table="my_db.table2" –-ignore-table="my_db.table3" > all_databases.sql

How to dump binary BLOB data?

Sometimes you may face issue with the resulting dump if it has binary data. For this reason, you could use the following mysqldump flag --hex-blob when you dump a MySQL database having binary data.

Under the hood, it dumps the binary strings it finds (BINARY, VARBINARY, BLOB) in a hexadecimal format which represents these data structure in a reliable way.

Here is a mysqldump example to dump binary data correctly:

mysqldump -u root -pmypassword my_bin_db --hex-blob > my_bin_db.sql

Does the "where" clause work with mysqldump?

Yes, this clause works with the command line.
This makes it easy to set conditions on the data you need to dump from the database.
If there is a large enterprise that has been in business for decades that wants to pull the information after April 27, 2017, then this clause allows that to happen.
The where clause passes a string for the condition and grabs the specific records requested.

mysqldump -u root -pmypassword wpdb --tables thetable --where="date_created > '2017-04-27'" > wpdb_myrecord.sql

MySQL command without password prompt

While there is a more secure way to do this (hint: updating your .my.cnf file), you can use the following command to run MySQL commands without being promped to type in the password every time.

mysql -u root -pYOUR_PASSWORD`

You'll need to replace YOUR_PASSWORD with your actual password.

Troubleshooting common errors

Along the way you may face some MySQL common errors that are -to some degree- easy to mitigate. We will share below some of these errors and how to solve them.

Error 2013: lost connection to mysql server during query when dumping table

To fix this issue, you need to go into the MySQL configuration file and increase some values. When those are added, save and close the file, then restart MySQL for the changes to take effect.

The values you need to adjust are:

• max_allowed_packet
• net_write_timeout
• net_read_timeout
• innodb_buffer_pool_size

The adjustments to the file will be under the [mysqld] and [mysqldump] sections and will look like this:

[mysqld]
innodb_buffer_pool_size=100M
max_allowed_packet=1024M

[mysqldump]
max_allowed_packet=1024M
net_read_timeout=3600
net_write_timeout=3600

Error 2020: got packet bigger than 'max_allowed_packet' bytes when dumping table

If the database you need to back up is large, and the file size ends up bigger thant the maximum allowed packet size, this error pops up.

This error can be fixed by going into the MySQL configuration file and increasing max_allowed_packet value in the [mysqld] and [mysqldump] sections. Save and close the file when finished, then restart MySQL for the changes to take effect.

The changes will look like this:

[mysqld]
max_allowed_packet=desired-value

[mysqldump]
max_allowed_packet=desired-value

Table does not exist (1146), couldn't execute 'show create table x'

There may be times when you delete a table during backing up. If this is the case, you can restrict certain tables from the mysqldump command with the --ignore-table option. To identify the table, you will have to state both the database and table names.

mysqldump -u root -pmypassword example_db --ignore-table=name_of_table > db_backup.sql

By listing the option multiple times, you can ignore multiple tables:

mysqldump -u root -pmypassword example_db --ignore-table=table --ignore-table=tableaux > db_backup.sql

Selecting the database returns 'unknown database'

This error happens most often when you use the -p flag in the command line with the password and there is a space in between -p and mypassword. If this happens when using "root" as the user with a password of "base-face", there will be an error stating "Unknown database base-face."

The correct input would look like this:

mysqldump -u root -pbase-face wpdb > wpdb_backup.sql

Error 1044 when selecting the database

If the user trying to do the dump doesn't have the privileges necessary to access the database, this error occurs. Logging into MySQL and assigning those privileges to the user will fix the issue. Enter command:

mysql -u root -p

Then enter the correct password, and proceed to grant privileges to the selected user.

GRANT ALL PRIVILEGES ON wpdb.* TO 'not_a_hacker'@'inconspicuous_host';

After that, flush the privileges and exit from MySQL by entering the command:

FLUSH PRIVILEGES;

EXIT;

Access denied for user when trying to connect to mysqldump

This error has several possible causes. Here's three of the most common causes of the issue.

1. Wrong mysqldump command

If you are using the wrong command, then this error will appear. The command may be mostly correct but it's missing a critical ingredient in the mysqldump format. The basic command will look like this:

mysqldump -u user -pmypasword database > database.sql

If you fail to specify a username or password then it will spit back the following message:

mysqldump: Got error: 1045: "Access denied for user 'user' @ 'localhost' (using password: NO)" when trying to connect

2. Remote host not allowed to connect to database

This error comes up if the backup is trying to be done on a remote server. The configurations for MySQL are set to disallow external connections. Here, the localhost is the only one allowed to make a backup. This is a security measure, so it's a good measure to have but if you need to change this, go to configurations and change MySQL to allow connections from a remote host.

3. Wrong user credentials

If you try to use the wrong username and password combination while connecting to the database, this error happens. MySQL can't verify that the request is authentic and returns the error. You'll have to make the request again with proper credentials, make sure there aren't any typos in your original command as that is the easiest mistake to make.

Conclusion

Mysqldump is a useful tool to help back up databases with minimal commands. One command allows the entire database to be spit out into a single text file. The tool is versatile enough to back up the parts of the database that is needed and comes with a variety of options to change the data you need to save.

Automated MySQL Backups as a service

SimpleBackups is a database and website backup automation tool that offloads all the backups tasks.
It automates MySQL backups and sends them to the cloud storage of your choice.
SimpleBackups helps you make sure your backups are running well, on time, securely, with all the bells and whistles.