JIYIK CN >

This tutorial will teach you how to mysqldumpexport a MySQL database using the export utility.

We will see mysqldumpthe syntax of using , its parameters and reasons to use it. We will also practice this utility by taking backup with/without data and exporting tables and databases.

For this guide, we use MySQL version 8.0.27. You can get the latest version (if available) from here.

mysqldumpExport/backup MySQL database using

In this day and age, DATA is everything. That is why database administrators keep our data safe and available 24/7; there are necessary and unexpected situations when they have to export data (data loss or data failure).

Keep in mind that the method of backup varies for different database platforms. Here, we will learn about the command line utilities for the MySQL database mysqldump.

Reasons to use mysqldumpthe utility

• The main reason is to backup the database when needed.
• You can export the database structure (database without data).
• It also allows you to backup single/many/all databases at once.
• mysqldumpIt also allows you to back up specific tables in specific databases.
• It is very easy to use to export database with/without data.
• It has various parameters that we can adjust as needed to get the job done.

Using mysqldumpthe utility's parameters

We will use some parameters for the commands used in this tutorial. -u [username]_and_ -p [password]are used to provide the username and password for connecting to the MySQL database. It is recommended that you enter the password later when asked, rather than -ptyping it after _(see the example given below).

Then [option]appears where you can specify your requirements. For example, you want to use --no-datathe option to do a dataless backup.

After that, you need to [数据库名称]. If you want to export a certain table from the database, you can [database name]write it after [table name].

Next, you can >export to a file using the (greater than) symbol or <restore using the (less than) symbol as needed. You can learn about mysqldumpmany other parameters of here.

Back up only the database structure

You may find that there are situations where you only want to export the database structure. In that case, you can use the command with the -d --no-dataoption mysqldump.

The following command will export personthe database structure.

mysqldump -u root -p --no-data person > E:\Backup\person_database_structure.sql

Backup single/multiple tables from a database

Sometimes, you need to export specific tables from a specific database. In this case, mysqldumpit allows you to back up single or multiple tables from that database.

You must write the table name after the database name to export a single table. You can write multiple table names after the database name to export multiple tables.

# 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 backing up all databases instead of just one? You can --all-databaseuse the option in the following ways mysqldump.

mysqldump -u root -p --all-databases > E:\Backup\all_databases_backup.sql

If you only want to export one or more databases, you can write the database names after the option as follows --databases, separated by a space.

mysqldump -u root -p --databases person courses > E:\Backup\person_courses_backup.sql

Use the following command to export a database.

mysqldump -u root -p person > E:\Backup\person_backup.sql

in conclusion

We learned about mysqldumpthe command line utility in detail. We discussed its parameters and the reasons for using it. We also learned how to export single or multiple tables and databases with/without data using different parameters.