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:

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