The Ultimate Guide to mysqldump

All complex applications require a database of some sort to store key 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.

The MySQL database backup tool mysqldump is reliable. It generates a *.sql file with SQL queries to drop, create, and insert tables in the source database. Run the *.sql file on the target database to restore the database.

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

In this guide, we will explore the mysqldump utility in depth.

Table Of Contents

• Importance of Data Backup
• What is Mysqldump command?
• How to Create Backup with MySQLDump Command
• A brief explanation of each mysqldump options is shown below:
• MySQLDump Example- Basic How to use MySQLDump
• Common MySQLDump Backup Tasks
• How to Backup a Single MySQL Database
• How to Backup Multiple MySQL Databases
• How to Backup All MySQL Databases
• How to Backup Only Structure MySQL Databases
• How to Backup a single MySQL table
• How to Backup MySQL with no structure
• Basic How to Import a mysqldump
• MySQLDump, what does the --quick flag do?
• Can you use the Where clause with MySQLDump?
• mysqldump Common Errors and Fixes
• mysqldump: error 2013: lost connection to mysql server during query when dumping table?
• mysqldump: error 2020: got packet bigger than 'max_allowed_packet' bytes when dumping table?
• Backup MySQL database using mysqldump on lock tables?
• Couldn't execute 'show create table x' Table does not exist (1146)
• mysqldump: error 1049: 'unknown database'
• mysqldump: Got error 1044: when selecting the database
• mysqldump error 1045 : Access denied for the user when trying to connect
• 1. Wrong mysqldump Command
• 2. Wrong user credentials
• 3. Remote host does not allow to connect to database
• How to backup large database using mysqldump?
• 1. How to Compress mysqldump Output
• 2. How to import the large MySQL database
• 3. Separate databases into separate data files
• How to work with mysqldump without password prompt
• Conclusion

Importance of Data Backup

It is true how crucial it is for businesses or organizations with systems in production to have a perfect duplicate of their data at various points in time. A database installation is a disaster without a solid backup plan because various issues could arise, resulting in corrupted or irrecoverable data loss. Organizations typically have a well-established system to carry out frequent data backups to prevent this dreadful scenario.

One of the key advantages of frequently backing up data is the capacity to recover data activities in the event of a severe system breakdown. Data versioning is another advantage since it allows you to travel back in time and restore earlier versions of your data to acquire the status of your underlying truths at that particular moment.

When changes are made, unique copies of your data are saved specifically for this purpose. You can transfer data without worry to a new server or development environment if data backups are regularly performed, which also means that it will not happen if you switch servers or domains.

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.

The mysqldump backup command 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.

How to Create Backup with MySQLDump Command

The command-line application mysqldump makes a logical backup of the MySQL database. In order to recreate database objects and data, it generates SQL statements. The output of the application can alternatively be in CSV, delimited text, or XML.

The fact that this command is easy to use is the only difficulty encountered when restoring the database. The SQL commands needed to rebuild or restore the MySQL database are included in the backup file that is generated when the database is backed up.

The command now executes each SQL statement required to create tables and add data when you restore the database. If your database is large, it will take time to restore it.

By default, the mysqldump command does not dump the information schema database, the performance schema database, or the MySQL Cluster ndbinfo database.

For informational schema tables, you must specifically mention the database name and the skip-lock-tables option in the mysqldump command.

A brief explanation of each mysqldump options is shown below:

You can use a range of options and features with mysqldump. You may access the complete list of options here.

-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.

MySQLDump Example- Basic How to use MySQLDump

The basic mysqldump syntax 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

Following are the general ways in which you can use mysqldump command:

• Database backup and restoration.

• Data transfer from one server to another.

• Data migration between several managed MySQL service providers.

• Transferring data between MySQL versions.

Common MySQLDump Backup Tasks

The following are the most common backup jobs that the mysqldump command can use:

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 will 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

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

.