The Ultimate Guide to mysqldump

Introduction

All complex applications require a database of some sort to store information. MySQL is one of the most popular databases that is used on the web. Most of a businesses time and value is put into a database and its key to keep that data secure. SnapShooter uses MySQL to help manage and backup customers servers and databases.

In this guide we will explore the mysqldump utility in depth. We cover the ways you can make a backup as well as the common errors users experience when using it.

Table of Contents

What is mysqldump?

MySQL is an open-source and one of the most widely used relational database management system. It is used to store 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 that is 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 that are used to reconstruct the database to its original state.

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

Basic How to use MySQL Dump

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 option is shown below:

-u It specifies your MySQL username.
-p It specifies your MySQL password.
DBNAME A 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 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 database named 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

Mysqldump also allows you to backup all databases in your system.

For example, take a backup of 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 single MySQL table

If you want to generate the backup of the specific table, you will need to specify the table name after the database name.

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

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

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

For example, generates the backup of data of the wpdb database, 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 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 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 table contents row by row or retrieving the entire content from a table and buffer it in memory before dumping it. This will cause a problem if you are exporting large tables.

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

Can you use Where clause with MySQLDump?

Of course, it's easy to use a where clause with mysqldump command. If you have a database with lots of tables and each table has a billion records, and if you want the records for a specific year, then you can use where clause with mysqldump command. Where clause allows you to pass a string for the condition and fetch the specific records as you need.

For example, fetch all records from the table named mytable and database named wpdb where the field date_created is greater 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?

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 very large, and the max_allowed_packet value was set to a low value. This 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

mysqldump when using lock tables?

By default, 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's used for protection of data integrity when dumping MyISAM tables. MyISAM tables require this locking because they don't support transactions. If you use a mix of MyISAM and InnoDB tables, then dump your MyISAM tables separately from InnoDB tables using --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 behaviour and --single-transaction to run mysqldump within a transaction.

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

Some times the table was deleted during the backup process. In that case, you can restrict certain 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 unknown database when selecting the database

Usually, this error occurs when you specify the password on the command line with -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", and use the following command:

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

Actually, 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 the user which has not 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 the MySQL with the following command:

FLUSH PRIVILEGES;
EXIT;

mysqldump access denied for user when trying to connect

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

1. Wrong mysqldump Command

One of the common reasons for this error is that 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 authenticity of the account and throws this error.

3. Remote host does not allowed to connect to database

You will also see this error if you are trying to backup the database on the remote server. Because, remote host disallow external connections and they only allow to connect from the localhost.

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

How to mysqldump backup large database

Some useful tips and tricks while using the mysqldump for a very large database.

1. How to Compress mysqldump Output

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

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 maximum allowed packet size to a large byte number as shown below:

set global max_allowed_packet=1000000000;

Next, disable foreign key checking to avoid delays, errors and unwanted behaviour 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 key checks with the following command:

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

3. Separate databases into separate data files

If you have a large database, then you can also split them by separate into separate data files.

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

Mysqldump without password

When you dump a database with mysqldump, you will 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
Don't want to manage MySQL backup yourself?

Secure your MySQL with SnapShooter today

SnapShooter Backup Config Example