How to Export MySQL Database Using Mysqldump
This tutorial will teach how to export MySQL databases using the mysqldump utility.
We will see the syntax of using mysqldump, its parameters, and the reasons to use it. We will also practice this utility by taking backups with/without data and exporting tables and database(s).
For this guide, we are using MySQL Version 8.0.27. You may get the latest version from here (if available).
Export/Backup MySQL Database Using mysqldump
In this era, DATA is everything. That is why the database administrators make our data safe and available 24/7; there are some required and unexpected situations where they have to export the data (data loss or data failure).
Keep in mind that the methods of taking backup vary in various database platforms. Here, we will learn about mysqldump, a command-line utility for MySQL database.
Reasons to Use mysqldump Utility
- The primary reason is to take backup of the database whenever needed.
- You can export database structure (database without data).
- It also allows you to take backup of single/many/all databases in one go.
mysqldumpalso let you take backup for a particular table in a specific database.- It is very easy to use for exporting databases with/without data.
- It has various parameters that we can tweak according to the requirements to get the job done.
Parameters to Use mysqldump Utility
We will be using a few parameters for the commands used in this tutorial. -u [username] and -p [password] are used to provide username and password to connect with the MySQL database. It is suggested that you enter the password later when it asks rather than giving after -p (see the examples given below).
Then [option] comes where you can specify your needs. For example, you want backup without data using the --no-data option.
After that, [database name] is required. You can write [table name] after the [database name] if you want to export a certain table from a database.
Next, you can use the > (greater than) sign to export into a file or the < (less than) sign to restore if you want. You can learn many other parameters of mysqldump here.
Backup Database Structure Only
You may find some scenarios where you only want to export the database structure. Then, you can use the mysqldump command with the --no-data option.
The following command will export the person database structure.
mysqldump -u root -p --no-data person > E:\Backup\person_database_structure.sql
Backup Single/Multiple Tables From a Database
Sometimes, it is needed to export a particular table from a specific database. In that case, mysqldump allows you to backup a single or many tables from that database.
You have to write the table name after the database name for exporting a single table. After the database name, you can write many table names to export more than one table.
# export one table named customer from database named person
mysqldump -u root -p person customer > E:\Backup\customer_table_from_person.sql
# export two tables named customer and employee from database named person
mysqldump -u root -p person customer employee > E:\Backup\customer_employee_from_person.sql
Backup Single/Multiple/All Databases
What if you focus on taking backup of all databases rather than only one? You can use the mysqldump in the following way with the --all-database option.
mysqldump -u root -p --all-databases > E:\Backup\all_databases_backup.sql
If you want to export only one or multiple databases, you can write the database names separated with a single space after the --databases option in the following manner.
mysqldump -u root -p --databases person courses > E:\Backup\person_courses_backup.sql
Use the following command to export one database.
mysqldump -u root -p person > E:\Backup\person_backup.sql
Conclusion
We learned about the mysqldump command-line utility in detail. We talked about its parameters and the reasons to use it. We also learned how to export single or many tables and databases with/without data using different parameters.
