In the Linux operating system, a mysqldump utility is available to create SQL files by exporting a MySQL database for backup purposes or transferring databases between servers. These files can be used to reproduce the databases along with their data. The process of exporting databases is also known as dumping data.
This guide will be the method to dump data into SQL format using the mysqldump command.
Prerequisites
Make sure that MySQL and mysqldump are installed in your system. To verify whether MySQL is installed or not, run this command:
The output displays version number of the installed mysqldump:
To see the basic option for mysqldump, type:
The output is displaying options for mysqldump:
How to Use mysqldump to export a database in SQL File?
The syntax for mysqldump for exporting a single database:
Note: The “[output-file-name].sql” represents the file that will be created in the system to save the exported database.
Let’s give a few examples of mysqldump.
Example 1: Use Simple mysqldump Command
The mysqldump provides the facility of exporting single and multiple databases. Let’s see an example to export the “linuxhint” database from the local MySQL server with username “md”, to a file “linuxhint_dump.sql” use this command:
After the error-free output of this command, let’s verify if the file is created successfully or not, using the command:
The output is showing that the file is created successfully:
Example 2: Use mysqldump to Dump Data With Extended Insert Option
The “–extended-insert” or “-e” option is enabled by default for mysqldump, it inserts multiple rows in the file at a time. Let’s use “–extended-insert” in the mysqldump command:
Verify if the file is created or not by using the “WHERE” command, and it is visible in the output that database is exported successfully:
Example 3: Use mysqldump to Generate Backup of the Database Data
If the user wants to create the backup of the data available in the database only. So the solution is to use “–no-create-info” in the mysqldump command to exclude the information of “CREATE” statements, use this syntax:
Let’s see an example of mysqldump command with “–no-create-info”, to back up the data of “linuxhint” database in the file “linuxhint_dump_info.sql”, run this command:
After getting the error-free output, use the “WHERE” command to verify the file:
Example 4: Use mysqldump to Generate a Backup of the Database Structure
If you want to back up the structure information of the database without the data use the “–no-data” option in the mysqldump. Let’s see an example with the “–no-data” option to back up the structure of the “linuxhint” database into the file “linuxhint_dump_data.sql”:
Verify if the file is created or not using the “WHERE” command:
Example 5: Use mysqldump to Dump Specific Tables to a File
The mysqldump command can be used with “–tables” option to only backup the data of a specific table or tables. Let’s see an example to back up the data of “customer” and “product” from the database “linuxhint” to a file “linuxhint_dump_table.sql”, run this query:
Let’s verify if the file is created by using the “WHERE” command:
Example 6: Use mysqldump to Dump Binary Data in Hexadecimal Format:
The “–hex-blob” option in the mysqldump command is used to dump the binary data in the database to a file in hexadecimal format. Let’s see an example for dumping the data of “linuxhint” binary data in the hexadecimal format, use this query:
The output is showing the file created using mysqldump command:
This guide provided examples to understand the usage of mysqladmin command.
Command
The mysqldump is used to generate the logical backup of the databases into a SQL file. These files can be used to transfer data between servers and backup databases. The syntax of the mysqldump “mysqldump -u [username] -p [db-name] > [output-file-name].sql”. It can be used with various options. This guide demonstrated the usage of mysqldump.
