mysqldump: Exporting Databases and Tables Made Easy

There are many an instance where you want to export data from your MySQL database using mysqldump to another destination like a Data Warehouse. You may want to integrate this data with that from other sources and perform a holistic analysis or you may want to format and publish your data in the way you want.

The article helps you to understand the basics of mysqldump, alternative solutions, and real-world applications, which help you to use MySQL effectively.

Steps to Export Database and Tables Using mysqldump

If you are a MySQL user looking for a way to export your data effortlessly, you have landed on the right page. In this blog, we will help you understand how to export databases and tables easily using mysqldump. Let’s get started!

There are three ways in which the mysqldump tool can be used:

1. To export specific tables in a MySQL database.
2. To export databases.
3. To export an entire MySQL server.

In the following sections, you will perform each of those actions.

1. To Export Tables

To use mysqldump to export tables, first, make sure that you are on the machine on which you have MySQL installed. Then make sure you have a valid database user that has at least full read access privileges. Do note that depending on the options that are passed with the command you may require additional privileges to use that particular option.

In most cases where the intention is a basic backup, full read access privileges should suffice. Next launch a terminal where you will issue the command to backup tables in a database. The command to backup tables is shown below:

mysqldump [options] your_db_name [tbl_name ...]

In the command above, [options] is to be replaced by a list of valid option names or flags, the most common of which is -u and -p for the user and password respectively. [tbl_name …] is to be replaced with table names separated by spaces. Below is a full example for backing up the tables orders and products for a database named store using the export table MySQL technique.

mysqldump -u your_username -p store orders products > name_of_file.sql

The terminal will prompt you to put in the password associated with the database user as it is not passed with the -p flag. The > character is the output redirection used to create the dump file. 

2. To Export Database

The steps to export a database are similar to those of exporting tables with a little change in the command semantics. Once again, make sure you are on the appropriate server and you have valid credentials. The command to export databases is shown below:

mysqldump -u your_username -p --databases db_name1 db_name2 db_name3 > name_of_file.sql

In the command above, the database to be exported is supplied after the –databases option. If multiple databases are to be exported, they are separated by spaces.

3. To Export MySQL Server

To export an entire MySQL server, you can issue the command below from a terminal:

mysqldump -u your_username -p --all-databases

There are some important options that you should be aware of such as the –compatible option, which is used when you want the export to be compatible with other database systems or older MySQL servers.

If you are using PowerShell on Windows, you should use the –result-file option, for example, –result-file=name_of_file.sql to specify the file name so that the output file is in ASCII format so it can load correctly subsequently.

mysqldump What Does the –quick flag Do?

Copying data from one place to another requires RAM, as it acts as temporary storage or buffer memory for some time. mysqldump also retrieves data and dump it in the table contents row by row. It takes up the entire dataset that needs to be moved in the buffer memory or RAM in a computer.

Exporting large datasets cause problem because RAM cannot load a large amount of data in a single go. To avoid errors and system failures –quick flag helps to read data from tables using a method that doesn’t require RAM to fit large tables in memory

mysqldump When Using Lock Tables?

Databases needed uptime to support all the necessary activities, handle data requests, and many more. By default, mysqldump command locks all the tables until the backup process is complete but it is not a good option as it will bring the Database offline.

mysqldump locks all the tables of the Database to avoid any changes in data while backing up and for the protection of data integrity. MyISAM tables need locking tables because that doesn’t support transactions.

If you also use MyISAM and InnoDB tables then you can use the –lock-tables command to dump your MyISAM from tables separately.

mysqldump Command Syntax

Let’s go through the basics of the mysqldump command before we get into how to use it.

The utility expressions for mysqldump are as follows:

mysqldump [options] > file.sql

• options – The options file for mysqldump.
• sql – The backup (dump) file

The MySQL server must be available and operating in order to use the mysqldump command.

How to Generate backup using mysqldump utility?

mysqldump is a command-line program that creates a logical backup of the MySQL database. It generates SQL statements that can be used to rebuild database objects and data. The program can also provide output in XML, delimited text, or CSV.

The only issue that arises while restoring the database is that this command is simple to use. When we create a backup of the MySQL database, it creates a backup file that contains SQL commands that are required to rebuild or restore the database, as I previously said.

When we restore the database, the command now runs all of the SQL statements necessary to build tables and enter data. The restoration process takes a long time to finish if you have a huge database.

The information schema database, performance schema database, and MySQL Cluster ndbinfo database are not dumped by default when using the mysqldump command.

You must specifically supply the database name in the mysqldump command, as well as the —skip-lock-tables option if you want to include the information schema tables.

With mysqldump, you can use a variety of settings and capabilities. The full list of possibilities can be found here. I’ll go through some of the fundamental aspects. The syntax for the mysqldump utility is as follows.

mysqldump -u [user name] –p [password] [options] [database_name] [tablename] > [dumpfilename.sql]

The Parameters are:

• -u [user name]: This is the username that will be used to connect to the MySQL server. To create a backup with mysqldump, select ‘Select’ to dump tables, ‘Show View’ to dump views, and ‘Trigger’ to dump triggers. If the —single-transaction option is not used, the user must be granted ‘Lock Tables’ access.
• –p [password]: The MySQL user’s valid password [option]: Customize the backup
• [database name] with the following configuration
• option: The name of the database you want to back up
• [name of table]: This is a non-mandatory parameter. If you want to take a backup of specific tables, use the command
• “<” OR “>” to provide the names: This character denotes whether we are creating a database backup or restoring a database. “>” can be used to create a backup and “<” can be used to restore a backup.
• [dumpfilename.sql]: The backup file’s path and name. As previously said, we can generate the backup in XML, delimited text, or SQL format, so we can specify the file extension accordingly.

Generate the backup of a single database

For example, if you wish to create a backup of a single database, use the command below. The command will create a backup of the “sakila” database in the sakila 20200424.sql file, complete with structure and data.

mysqldump -u root -p sakila > C:MySQLBackupsakila_20200424.sql

This command will prompt you for a password when you run it. Please enter the correct password. Consider the following illustration:

Let’s examine the backup file to see what’s inside it once it’s been successfully made. Double-click the “sakila 20200424.sql” file in the backup directory.

The backup file, as seen in the image above, contains the numerous T-SQL statements that can be used to recreate the objects.

How to mysqldump Backup Large Database?

In this section, you will learn different methods that you can use to backup large datasets using mysqldump.

• Method 1: How to Compress mysqldump Output?
• Method 2: How to Import General MySQL Database?
• Method 3: Separate Databases Into Separate Data Files

Method 1: How to Compress mysqldump Output?

1. One can use the file compression method to compress the database backup into gzip format to reduce the size of data to be backup. You can do the same by running the following command given below:

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

2. Here, 2 commands are executed together. One is dumping the database name wpdb and another command is to compress the dumped database into gzip format.

Method 2: How to Import the General MySQL Database?

1. Log in to your SQL shell using the command given below.

mysql -u root -p

2. Now, set the network buffer length to a large size as given in the code below.

set global net_buffer_length=1000000;

3. Set the maximum allowed packet size to a large byte number as given in the code below.

set global max_allowed_packet=1000000000;

4. To avoid any delays or errors, disable the foreign key checking by the following command given below.

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

5. Now, import your dump file with the command given below.

source /backup-path/wpdb.sql

6. After this don’t forget to enable the foreign key checking with the given command given below.

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

Method 3: Separate Databases Into Separate Data Files

1. In this method, one can slit the database backup into separate data files.
2. You can create a new file with a list of all databases using the following command given below.

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

3. Now you can create a loop in which all the databases will dump one by one by using mysqldump command given 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 Use mysqldump Without Password?

MySQL always asks for username and password when you back up the database with mysqldump command. To avoid repeated prompts and interruptions in backup one can follow this method. 

1. You have to create a file in your home directory with SQL credentials. Use the given command to create a file.

nano ~/mysql.txt

2. Now add your MySQL credentials as given below.

[mysqldump]
user=root
password=password

3. Save and close the file and every time you can provide a file in place of credentials like in the code given below.

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

Before wrapping up, let’s cover some basics as well.

What is mysqldump?

The MySQL database engine has a client utility tool called mysqldump that can be used to perform backups of a MySQL database by exporting a “.sql” file type that contains SQL statements that can be executed to recreate the original database.

In order to use the mysqldump tool, you must have access to a server running an instance of MySQL. You must also have user credentials with the required privileges for the database which you want to export. The mysqldump tool is capable of producing not only .sql output files but CSV, delimited text, and XML.

However, exporting data is not a one-time-only task, and doing it manually can be tedious.

How Do We Use mysqldump?

mysqldump is easy to use and learn. A few basic syntaxes of mysqldump commands are listed below:

mysqldump -u USERNAME -p PASSWORD DBNAME > DBBACKUP.sql

mysqldump -u USERNAME -p PASSWORD --databases DB1 DB2 DB3.. >DBBACKUP.sql

mysqldump -u USERNAME -p PASSWORD --all-databases > ALLDBBACKUP.sql

where,

Parameters	Signifies
-u	It specifies your MySQL username.
-p	It specifies 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.

What are the Benefits of Backing Up Data?

• The importance of having a pristine snapshot of the copy of your data at different points in time cannot be overemphasized for companies or organizations that have systems in production.
• A database installation that does not have a robust backup strategy is a disaster waiting to happen as any number of things could go wrong that could lead to corrupt data or permanent data loss. To avoid this doomsday scenario, organizations usually have a well-laid-down procedure to carry out a periodic backup of data.
• One of the main benefits of regularly backing up data is the ability to restore data operations in the case of a catastrophic failure of the system. Another benefit is data versioning, which means that specific versions of your data are stored when changes occur so that you can go back in time to restore older versions in order to get the state of your ground truths at that point in time.
• The practice of backing up data also means that in the event of a migration to a new server or development environment, data can be transferred without any fear of loss. This brings us to the crux of this article, where you will be introduced to mysqldump and you will use it to perform logical backups of your data.

Conclusion

Through this article, you have been able to understand the benefits of performing regular backups of data. You have also been introduced to the mysqldump tool and you used it to MySQL export tables, databases, and an entire MySQL server.

Although this is unarguably a good start as the mysqldump tool is flexible and easy to use, it is not meant to be a fast and scalable backup solution. This is because while backup may take a reasonable time, it typically takes a longer period to restore data, especially in cases where the data size is massive as is often the case with enterprises. To fill this void, MySQL provides MySQL Enterprise Backup which is a paid solution.

Learning about other methods to export a MySQL database is the next step ahead. An even simpler approach will be to leverage an online managed solution like the Hevo platform to transfer your data seamlessly to a data warehouse in real-time. Hevo is the only real-time ELT No-code Data Pipeline platform that cost-effectively automates data pipelines that are flexible to your needs. It allows integration with 150+ Data Sources (40+ free sources).

Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand. You may also have a look at the amazing Hevo Price, which will assist you in selecting the best plan for your requirements.

Have you used mysqldump export data from MySQL? Let us know in the comments below.