How to Use mysqldump
What is the mysqldump command?
MySQL is an open-source relational database management system widely used for storing anything from a simple name to a picture gallery or a large amount of information in a network. The mysqldump command 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 that can be used to reconstruct the database to its original state.
The mysqldump backup command is generally used to export the database to a backup file or to move the database to another host. The mysqldump command can generate output in XML, CSV, and other delimited text formats.
How to Create a Backup with the MySQLDump Command
The mysqldump command-line application creates a logical backup of the MySQL database. It generates SQL statements to recreate database objects and data. The output of the application can be in CSV, delimited text, or XML format.
The only difficulty when restoring the database is the ease of use of this command. The SQL commands needed to rebuild or restore the MySQL database are included in the backup file generated when the database is backed up.
When restoring the database, the mysqldump command executes each SQL statement required to create tables and add data. If your database is large, it may take some time to restore.
By default, the mysqldump command does not dump the information schema database, the performance schema database, or the MySQL Cluster ndbinfo database.
To dump informational schema tables, you must specifically mention the database name and use the skip-lock-tables option in the mysqldump command.
Below is a brief explanation of each option available for mysqldump:
You can use a range of options and features with mysqldump. You can access the complete list of options here.
-u: Specifies your MySQL username.-p: 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: Specifies the hostname of the MySQL database server.-databases: Used to determine the database.all-databases: Used to backup all databases.-default-auth=plugin: Used to specify the client-side authentication plugin to use.-compress: Used to enable compression in server/client protocol.-P: Used to specify the port number to use for MySQL connection.
MySQLDump Example - Basic Usage
The basic syntax for using mysqldump 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
The mysqldump command can be used in a variety of ways, including:
- Backing up and restoring databases.
- Transferring data from one server to another.
- Migrating data between several managed MySQL service providers.
- Transferring data between different versions
Common MySQLDump Backup Tasks
The mysqldump command can perform the following common backup tasks:
How to Backup a Single MySQL Database
To back up a single database, run the following command:
mysqldump -u root -ppassword wpdb > wpdb_backup.sql
This command dumps the “wpdb” database and generates a single file named “wpdb_backup.sql.”
How to Backup Multiple MySQL Databases
With mysqldump, you can backup multiple databases to a single file.
For instance, to 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” tool provides an option to backup all databases in your system. This allows you to easily create a full backup of your MySQL databases.
To 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 the Structure of MySQL Databases
To generate a backup of only the database structure, use the --no-data option in the mysqldump command. For example, to generate a 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 a backup of a specific table, you need to specify the table name after the database name in the mysqldump command.
For example, to generate a backup of the “wptable” table from the “wpdb” database, run the following command:
mysqldump -u root -ppassword --no-data wpdb wptable > wpdbtable_backup.sql
How to Backup MySQL Without Structure
To generate a data backup without the database structure, use the --no-create-info option in the mysqldump command.
For example, to generate a backup of data for the wpdb database, run the following command:
mysqldump -u root -ppassword wpdb --no-create-info > wpdb_backup.sql
How to Import a mysqldump
The simplest and easiest way to import a database is by using the following command:
mysql -u root -ppassword wpdb < wpdb_backup.sql
Note: Before importing the database, you must create an empty database on the target machine.
Alternatively, you can use the mysqlimport command to restore a database that already exists on the target machine.
mysqlimport -u root -ppassword wpdb < wpdb_backup.sql
To import all databases, run the following command:
mysql -u root -ppassword < alldb_backup.sql
MySQLDump: What Does the –quick Flag Do?
When using mysqldump to export table content, it retrieves and dumps content row by row or buffers the entire table range in memory before dumping. This can cause problems when exporting large tables.
To avoid these problems, use the mysqldump command with the –quick flag. This reads large tables in a manner that doesn’t require large amounts of RAM to hold the entire table in memory. This provides additional safety for systems with little RAM and large databases.
Can You Use the WHERE Clause with MySQLDump?
Yes, you can use the WHERE clause with the mysqldump command. This is useful when you have a database with many tables, each containing a billion records. If you only need the records for a specific year, you can use the WHERE clause to fetch the particular records you need.
For example, to fetch all records from the table named “mytable” in the database called “wpdb” where the “date_created” field is greater than ‘February 1, 2018’, run the following command:
mysqldump -u root -ppassword wpdb --tables mytable --where="date_created > '2018-02-01'" > wpdb_myrecord.sql
Common Errors and Fixes for mysqldump
Error 2013: Lost Connection to MySQL Server During Query When Dumping Table
If you are still having issues with mysqldump, consider automating your MySQL backups with a free trial.
To fix the error, increase the values for max_allowed_packet, net_write_timeout, net_read_timeout, and innodb_buffer_pool_size to appropriate levels.
To set these values, edit the MySQL configuration file.
nano /etc/mysql/my.cnf
Add the following lines under the [mysqld] and [mysqldump] sections:
[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 have the changes take effect.
service mysql restart
##Or
systemctl restart mysqld
mysqldump: error 2020: got packet bigger than ‘max_allowed_packet’ bytes when dumping table
If you encounter the above error while trying to backup a large database, it may be because the max_allowed_packet value is set too low.
To fix this error, you can edit the MySQL configuration file:
nano /etc/mysql/my.cnf
To increase the max_allowed_packet value, go to the [mysqld] and [mysqldump] sections and make the necessary changes.
[mysqld] max_allowed_packet=your-desired-value
[mysqldump] max_allowed_packet=your-desired-value
Once finished, save and close the file. Next, restart the MySQL service in order for the changes to take effect.
service mysql restart
## Or
systemctl restart mysqld
How to Backup a MySQL Database Using mysqldump without Locking Tables
By default, the mysqldump command locks all tables until the backup is complete. However, this is not ideal in a live environment where the database is extensive and uptime is crucial.
This locking is primarily used to protect data integrity when dumping MyISAM tables since they don’t support transactions. If you use a mix of MyISAM and InnoDB tables, dump your MyISAM tables separately from InnoDB tables using the –lock-tables option.
Nowadays, InnoDB is the default table storage engine. To avoid locking tables, use the –skip-lock-tables option. Additionally, run mysqldump within a transaction by using the –single-transaction option.
Error: Couldn’t execute ‘show create table x’ Table does not exist (1146)
This error occurs when the table being referenced does not exist. One possible reason why this happens is that the table was deleted during the backup process.
To prevent this from happening, you can use the –ignore-table option when running the mysqldump command. This option allows you to exclude specific tables from the backup process.
To use this option, you need to specify both the database and table names as shown below:
mysqldump -u root -ppassword dbname --ignore-table=tablename > db_backup.sql
You can also ignore 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’
This error typically occurs when specifying the password on the command line with the -p flag and there is a space between -p and the password.
For instance, 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 immediately after the -p switch, with no space in between, as shown below:
mysqldump -u root -pyour-password wpdb > wpdb_backup.sql
mysqldump: Got error 1044: when selecting the database
You will receive this error if you attempt to dump the database with a user who lacks the necessary privileges to access the selected database.
To resolve this error, assign appropriate privileges to the user to access the database.
First, log in to MySQL with the following command:
mysql -u root -p
Provide your MySQL root password, and 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 using the following command:
FLUSH PRIVILEGES; EXIT;
Error 1045: Access Denied for the User When Trying to Connect to MySQL
There are several reasons why this error may occur. The most common reasons are listed below:
1. Incorrect mysqldump Command
One common reason for this error is using the wrong format of the mysqldump command.
For example, the general syntax for backing up a database using mysqldump is shown below:
mysqldump -u user -ppasword database > database.sql
If you take a database backup without specifying a username and password, as shown below, you will encounter the error message above:
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. Incorrect user credentials
If you use the wrong username and password to connect to the database, you will receive the above error. MySQL cannot verify the account’s authenticity and throws this error.
3. Remote host does not allow database connections
If you attempt to back up the database on a remote server, you may encounter this error. This is because remote hosts disallow external connections and only allow connections from the localhost.
To resolve this issue, configure your MySQL server to allow connections from the remote host.
How to Backup a Large Database Using mysqldump
Here are some valuable tips and tricks to keep in mind when using mysqldump for an extensive database.
1. Compressing mysqldump Output
To reduce the size of the database backup, it is a good idea to compress it in gzip format. To do this, run the following command to dump and gzip the database named wpdb at the same time:
mysqldump -u root -ppassword wpdb | gzip > wpdb_backup.sql.gz
To restore the compressed database, run the following command:
gunzip < wpdb_backup.sql.gz | mysql -u root -ppassword wpdb
2. Importing a Large MySQL Database
First, log in to the MySQL shell using the following command:
mysql -u root -p
Provide your MySQL root password, then set the 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 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 an extensive database, you can split it into separate data files.
To create a new file with a list of all databases, use 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 the 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 the mysqldump command every time, you can create a file in your home directory with your MySQL credentials. This will disable the mysqldump password prompt.
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 --defaults-file option as shown below:
mysqldump --defaults-file=~/mysql.txt wpdb > wpdb_backup.sql
Conclusion
MySQL is a popular database for storing and retrieving data from any website. The mysqldump command is one 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 versatile 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.