Export a Single Database to a SQL File
To export a single MySQL database in a SQL file, use this syntax:
In the syntax provide, your MySQL username, name of the database that you want to export and name of new SQL file that will be created. Let’s see an example; for this post, the username is “md”, the name of the database is “linuxhint” and the name for the SQL file is “exported_db.sql” so the command would become this:
To confirm if the file is created or not after the execution of the previous command, run:
Note: This syntax will be used to verify the existence of newly created SQL files, throughout this post.
The output will display that the MySQL database is exported in an SQL file successfully:
Export Multiple Specified Databases to a SQL File
The mysqldump also provides the facility of exporting multiple databases in a single SQL file by using the “–databases” flag. The syntax for exporting multiple databases is given below:
If you want to export “2” or more databases in the file, provide their names with a space in between. For this post let’s export “linuxhint” and “newlinuxhint” databases in a file named “exported_db_databases.sql” by typing this command:
The error-free output indicates the successful execution the process, use the “where” command to verify whether the file is created or not:
Your multiple databases are exported in a single MySQL file.
Export All Databases to a SQL File
Users might feel a need to export all databases available in MySQL server to a SQL file. The mysqldump allows you to do that by using the “–all-databases” flag. The syntax is given below:
Provide the username and name of the SQL file. For this post, the username is “md” and the name of the SQL file is “exported_db_all_databases.sql”, so the command would look like this:
The output displays that file is created successfully:
Export Only Structure of a Database to a SQL File:
The “–no-data” flag of mysqldump aids the user to export only the structure of the database without exporting the data. Use the given below syntax:
For this post, Let’s export the “linuxhint” database with only structure in a SQL file “exported_db_structure.sql”, by running this command:
To verify if the file is created or not:
The output shows that the SQL file is created successfully.
Export Only Data From a Specific Table to a SQL File
Sometimes, the user wants to export only data of a specific table without the information of the “CREATE” statements, to o so use the “–no-create-info” flag in the mysqldump, as shown in the syntax:
If you want to export data of “supplier” in a SQL file named “exported_db_specific_table.sql” by running this command:
To verify if the file created successfully or not use the “where” command:
The output is displaying that SQL file is created successfully.
Export Multiple Specified Tables to a SQL File
The mysqldump can be used to export multiple specified tables by using the “–tables” flag in a SQL file using this syntax:
For example, if the user wants to export “Supplier” and “items” table from the database “linuxhint” in a SQL file named “exported_db_specific_tables.sql”, run the given below command:
The error-free output displays that the command is executed successfully, you can use the “where” command to confirm the creation of the SQL file:
Flags That can be Used With mysqldump
The rest of the flags will be discussed using the syntax only throughout this post. Replace “db-name” with the name of your database, “table-name” with the name of the table and “output-file-name” with the name for your SQL file that will be created on successful execution of the export command.
Export Multiple Rows of a Table Using a Single Insert Statement in a SQL File
While working with a database containing large tables, the “–extended-insert” flag can be used to export them efficiently as it uses multiple rows “INSERT” statements, instead of a single row “INSERT” command which is the default method while exporting tables. It speeds up the export time. Use this syntax for using the “–extended-insert” flag:
Export Records From a Specific Table that matches a Condition
To export the records from a table where a certain condition is fulfilled, use the “–where” flag that defines a condition for filtering the records that will be exported. To do so, use this syntax:
The condition can be anything, for example, “id < 30”.
Export Binary Data by Converting Into Hexadecimal Format
The “–hex-blob” flag aids in exporting binary data in hexadecimal format. By default, the binary data is formatted in binary format. Using this flag is beneficial if the data accuracy is concerned; otherwise, it consumes more time than the normal export. The syntax is given below:
Export a Database in an XML Format
To export a database in the XML format, use the “–xml” flag in the mysqldump command as shown in the syntax:
Export a Database With “DROP DATABASE” Statement in a SQL File
To create the export file by adding the “DROP DATABASE” statement before the “CREATE DATABASE” statement so that in case of import it drops the database if it already exists by using “–add-drop-database”. Use this syntax:
Export a Database With “DROP TABLE” Statement in a SQL File
To create the export file by adding the “DROP TABLE” statement before the “CREATE TABLE” statement so that in case of import it drops the table if it already exists by using “–add-drop-table”. Use this syntax:
Export a Database by Excluding a Certain Table in a SQL File
To export a database by excluding the specified table by using the “–ignore-table” flag in the mysqldump command using this syntax:
Export a Database and Compress the SQL File
To save disk space, the user can use the gzip tool to compress the SQL file that is containing the exported database, using the “–compress” flag. The syntax for compressing the SQL file is:
You have learned about different flags of mysqldump.
Conclusion
The mysqldump client utility aids in generating the logical backup of the databases into a SQL file. It is used to export single and multiple databases with their data and structures even. The user can also format and compress the SQL files. This post demonstrated different flags of mysqldump while exporting the MySQL database.
