The Ultimate Guide to mysqldump

All complex applications require a database of some sort to store information. MySQL is one of the most popular databases used on the web. Most of a business's time and value is put into a database which is key to securing that data.

SnapShooter uses MySQL to help manage and backup customers' servers and databases.

In this guide, we will explore the mysqldump utility in depth. Finally, we cover how to make a backup and the common errors users experience when using it.

Table Of Contents

What is mysqldump command?

MySQL is an open-source and one of the most widely used relational database management systems. It stores anything from a simple name to a picture gallery or a large amount of information in a network. mysqldump is a part of the MySQL relational database package used to dump (backup) a database or a collection of databases into a simple text file.

The text file is a set of SQL statements used to reconstruct the database to its original state.

Mysqldump is generally used to export the database to a backup file (like we do at SnapShooter) and to move the database to the other host. The mysqldump command generates output in XML, CSV, and other delimited text formats.

Basic How to use MySQLDump

The basic syntax of Mysqldump command is shown below:

mysqldump -u USERNAME -pPASSWORD DBNAME > DBBACKUP.sql mysqldump -u USERNAME -pPASSWORD --databases DB1 DB2 DB3.. >DBBACKUP.sql mysqldump -u USERNAME -pPASSWORD --all-databases > ALLDBBACKUP.sql

A brief explanation of each mysqldump options is shown below:

-u

It specifies your MySQL username.

-p

It determines your MySQL password.

DBNAME

The name of the database that you want to backup.

DBBACKUP.sql

The name of the backup file you want to generate.

-h

It specifies the hostname of the MySQL database server.

--databases

It is used to determine the database.

-all-databases

It is used to backup all databases.

--default-auth=plugin

It is used to specify the client-side authentication plugin to use.

--compress

It is used to enable compression in server/client protocol.

-P

It is used to specify the port number to use for MySQL connection.

How to Backup a Single MySQL Database

The simple and easiest way to take a backup of a single database, run the following command:

mysqldump -u root -ppassword wpdb > wpdb_backup.sql

This command will dump the database named wpdb and generate a single file named wpdb_backup.sql.

How to Backup Multiple MySQL Databases

With mysqldump, you can also take a backup of multiple databases to a single file.

For example, dump the databases wpdb1, wpdb2, and wpdb3 and generate a single backup file named wpdb_backup3.sql. Run the following command:

mysqldump -u root -ppassword --databases wpdb1 wpdb2 wpdb3 > wpdb_backup3.sql
How to Backup All MySQL Databases

The mysqldump all databases option allows you to backup all databases in your system.

For example, backup all databases and generate a single backup file named alldb_backup.sql. Run the following command:

mysqldump -u root -ppassword --all-databases > alldb_backup.sql
How to Backup Only Structure MySQL Databases

If you want to generate the backup of the database structure, then you must use the –no-data option in the mysqldump command.

For example, it generates the backup of the database structure of the wpdb database run the following command:

mysqldump -u root -ppassword --no-data wpdb > wpdb_backup.sql
How to Backup a single MySQL table

To generate the backup of the specific table, you need to specify the table name after the database name in the mysqldump table.

For example, generate the backup of the wptable table of the wpdb database and run the following command:

mysqldump -u root -ppassword --no-data wpdb wptable > wpdbtable_backup.sql
How to Backup MySQL with no structure

To generate the data backup without the database structure, you must use the –no-create-info option in the mysqldump command.

For example, generates the backup of data of the wpdb database and run the following command:

mysqldump -u root -ppassword wpdb --no-create-info > wpdb_backup.sql

Basic How to Import a mysqldump

The simple and easiest way to import the database is using the following command:

mysql -u root -ppassword wpdb < wpdb_backup.sql

Note: You will need to create an empty database on the target machine before importing the database.

You can also import the database with mysqlimport command if you want to restore a database that already exists on the targeted machine.

mysqlimport -u root -ppassword wpdb < wpdb_backup.sql

If you want to import all databases, run the following command:

mysql -u root -ppassword < alldb_backup.sql

MySQLDump, what does the --quick flag do?

mysqldump works by retrieving and dumping the content of a table row by row or retrieving the entire range from a table and buffering it in memory before dumping it. It will cause a problem if you are exporting large tables.

Use the mysqldump command with --quick flag reads out large tables in a way that does not require large amounts of RAM to fit the entire table in memory. This will provides extra safety for systems with little RAM and large databases.

Can you use the Where clause with MySQLDump?

Of course, you are using a where clause with the mysqldump command. For example, if you have a database with many tables, each table has a billion records. So if you want the records for a specific year, you can use the where clause with the mysqldump command where clause allows you to pass a string for the condition and fetch the particular records you need.

For example, fetch all records from the table named mytable and database called wpdb where the field date_created is more significant than '2018-02-01', run the following command:

mysqldump -u root -ppassword wpdb --tables mytable --where="date_created > '2018-02-01' " > wpdb_myrecord.sql

mysqldump Common Errors and Fixes

mysqldump: error 2013: lost connection to mysql server during query when dumping table?

Still, having mysqldump issues? Let us automate your MySQL backups - get a free trial today

You should increase the max_allowed_packet, net_write_timeout, net_read_timeout and innodb_buffer_pool_size value to appropriate levels to fix the error.

To set the above value, edit the MySQL configuration file:

nano /etc/mysql/my.cnf

Add the following lines under the [mysqld] and [mysqldump] section:

[mysqld] innodb_buffer_pool_size=100M max_allowed_packet=1024M [mysqldump] max_allowed_packet=1024M net_read_timeout=3600 net_write_timeout=3600

Save and close the file when you are finished. Then, restart the MySQL service to effect the changes.

service mysql restart Or systemctl restart mysqld

mysqldump: error 2020: got packet bigger than 'max_allowed_packet' bytes when dumping table?

When the database you are trying to backup is vast, the max_allowed_packet value was set to a low value. However, it will cause the above error.

You can fix the error by editing the MySQL configuration file:

nano /etc/mysql/my.cnf

Increase the max_allowed_packet value the under the [mysqld] and [mysqldump] section

[mysqld] max_allowed_packet=your-desired-value [mysqldump] max_allowed_packet=your-desired-value

Save and close the file when you are finished. Then, restart the MySQL service to effect the changes.

service mysql restart Or systemctl restart mysqld

Backup MySQL database using mysqldump on lock tables?

By default, the mysqldump command will perform a lock on all tables until the backup is complete. But this is a poor option in a live environment where the database is extensive, and uptime is necessary.

Primarily it is used to protect data integrity when dumping MyISAM tables. MyISAM tables require this locking because they don't support transactions. So, for example, if you use a mix of MyISAM and InnoDB tables, dump your MyISAM tables separately from InnoDB tables using a the-lock-tables option.

InnoDB is the default table storage engine nowadays. So you can skip the lock tables option by using --skip-lock-tables to stop the behavior and --single-transaction to run mysqldump within a transaction.

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

Want to backup while easily ignoring tables - get a free trial today

Sometimes the table was deleted during the backup process. You can restrict specific tables from the mysqldump command using the --ignore-table option.

You will need to specify both database and table names as shown below:

mysqldump -u root -ppassword dbname --ignore-table=tablename > db_backup.sql

You can also ignore the multiple tables by specifying the option per table as shown below:

mysqldump -u root -ppassword dbname --ignore-table=table1 --ignore-table=table2 > db_backup.sql

mysqldump: error 1049: 'unknown database'

Usually, this error occurs when you specify the password on the command line with the -p flag, and there is a space between -p and password.

For example, if you want to dump the database named wpdb using the user "root" and password "your-password," use the following command:

The above command will produce an error says ""Unknown database your-password" when selecting the database".

You must specify the password with no space after -p switch as shown below:

mysqldump -u root -pyour-password wpdb > wpdb_backup.sql

mysqldump: Got error 1044: when selecting the database

You will get this error if you are trying to dump the database with a user without enough privileges to access the selected database.

To fix this error, assign proper privileges to the user to access the database.

First, login to MySQL with the following command:

mysql -u root -p

Provide your MySQL root password, then grant all privileges on the database to your user:

GRANT ALL PRIVILEGES ON wpdb.* TO 'your_user'@'localhost';

Next, flush the privileges and exit from MySQL with the following command:

FLUSH PRIVILEGES; EXIT;

mysqldump error 1045 : Access denied for the user when trying to connect

There are several reasons for this error. The most common reasons for this error are listed below:

1. Wrong mysqldump Command

One of the common reasons for this error is when you use the wrong format of mysqldump command.

For example, the general syntax for taking database backup using mysqldump is shown below:

mysqldump -u user -ppasword database > database.sql

You will get the above error if you take a database backup without specifying a username and password, as shown below:

mysqldump database > database.sql

You should get the following error:

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

2. Wrong user credentials

You will also get the above error if you use the wrong username and password while connecting to the database. Therefore, MySQL can't verify the account's authenticity and throws this error.

3. Remote host does not allow to connect to database

You will also see this error if you try to backup the database on the remote server. Because remote hosts disallow external connections, they only allow connecting from the localhost.

To fix this, configure your MySQL server to allow connections from the remote host.

How to backup large database using mysqldump?

Some valuable tips and tricks while using the mysqldump for an extensive database.

1. How to Compress mysqldump Output

It is a good idea to compress the database backup in gzip format to reduce the database size.

Run the following command to dump the database named wpdb and gzip it at the same time:

mysqldump -u root -ppassword wpdb | gzip > wpdb_backup.sql.gz

If you want to restore the compressed database, run the following command:

gunzip < wpdb_backup.sql.gz | mysql -u root -ppassword wpdb

2. How to import the large MySQL database

First, login to the MySQL shell using the following command:

mysql -u root -p

Provide your MySQL root password, then set network buffer length to a large byte number as shown below:

set global net_buffer_length=1000000;

Next, also set the maximum allowed packet size to a large byte number as shown below:

set global max_allowed_packet=1000000000;

Next, disable foreign essential checking to avoid delays, errors, and unwanted behavior as shown below:

SET foreign_key_checks = 0; SET UNIQUE_CHECKS = 0; SET AUTOCOMMIT = 0;

Next, import your dump file with the following command:

source /backup-path/wpdb.sql

Once you are done, enable foreign critical checks with the following command:

SET foreign_key_checks = 1; SET UNIQUE_CHECKS = 1; SET AUTOCOMMIT = 1;

3. Separate databases into separate data files

You can also split them into separate data files if you have an extensive database.

You can create a new file with a list of all databases using the following command:

mysql -u root -ppassword -A --skip-column-names -e"SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN ('information_schema','mysql')" > db_list.txt

Once you have the list of databases, you can run a loop with mysqldump command through the list as shown below:

for DB in `cat db_list.txt` do mysqldump -u root -ppassword --hex-blob --routines --triggers ${DB} | gzip > ${DB}.sql.gz & done wait

How to work with mysqldump without password prompt

When you dump a database with mysqldump, you need to specify a username and password with the command. If you don't want to specify a username and password with mysqldump command every time, you just need to create a file in your home directory with MySQL credential. This will disable the mysqldump password prompting.

To do so, create a new file in your home directory:

nano ~/mysql.txt

Add your MySQL root credential, as shown below:

[mysqldump] user=root password=password

Save and close the file when you are finished.

Now, you can specify your MySQL credential file using the option --defaults-file as shown below:

mysqldump --defaults-file=~/mysql.txt wpdb > wpdb_backup.sql

Frequently asked questions

1. Is mysqldump a hot backup?

By default, the mysqldump is not a hot backup if your tables are locked or if you are using storage engines. But it is a hot backup if you use the 'single transaction' option in the InnoDB.

2. How to run the mysqldump command on Windows?

Go to the Windows command prompt and write "mysqldump -u username -p dbname > filename.sql". It will then ask you to enter your password, and you are good to go.

3. Can mysqldump lock tables?

Mysqldump command by default locks all the tables in the database while performing the backup.

Scheduled MySQL All Databases Backups SnapShooter

Backup all of the databases on a MySQL in one smooth backup

Learn more about MySQL All Databases Backups

Get started for free
No credit card required. Cancel anytime!

Conclusion

MySQL is a viral database for storing and retrieving data from any website. The mysqldump command is of the best tools of MySQL as it serves as the backup of the databases. The secret to the popularity of the mysqldump command is that it can split the whole database into a single text file.

Mysqldump is a multiskilled command if you want to backup only certain parts of the database. There are plenty of options that you can use to save the data.

Was this page helpful?

Thank you for helping us improve!