Updated May 29, 2023
Introduction to MySQL Export Database
In MySQL, we store the data in the database in table format. Many times, it becomes necessary to obtain the data stored in MySQL database tables in formats such as CSV, JSON, or SQL. Such conversions are often required when data needs to be transferred and shared among multiple companies and individuals.
Suppose we have the exported data of the database and its table contents in CSV format. In that case, this data can be further used in other platforms such as google docs, open office, or Microsoft Excel for further analysis of the resultset and concluding the necessary statistics by performing various operations on the exported data to get certain conclusions. When exporting MySQL data in JSON format, we can transfer this data to other applications because JSON is the most preferred method for data transfer. Moreover, you can export or dump MySQL data to SQL files, which can function as backup files. You can execute the exported commands for the specific database or table contents on either a different or the same MySQL server to restore the database to a state that closely resembles the state at the time of data export.
In this article, we will learn how we can export the data of MySQL to various formats when using the MySQL Workbench editor. The mysqldump command can also export data to SQL format through the command line. Other than this, we can use the INTO OUTFILE statement to export the data of MySQL database and its contents to any other format like .csv or .txt using the command-line MySQL shell.
Exporting Database and tables in Workbench in SQL format
You can choose the option “Data Export” provided in the Management wizard on the first screen on MySQL workbench or go to the Server menu in the upper portion and then choose the option Data Export to open the Export wizard of MySQL as shown below
Here, you can select the database whose contents or the whole database you want to export. After you click on the desired database name, you will notice that all the tables inside it are automatically selected in the right-side window, as depicted in the image below. Clicking on the “educba” database will result in the selection of all the tables contained within in the right window. You can keep it as it is if you want to export entire database contents or choose the table or tables you wish to export and click the start export button to initialize the export process.
After clicking start export, the MySQL dump file in .sql format will be created on the specified export options path.
Exporting data to any other format in Workbench
You can export the table contents and structure by going to the schemas tab in the Workbench tool’s left sidebar. Then you can right-click on the table of the database you wish to export and then click on “Table Data Export Wizard,” which will lead to the opening of the export wizard as shown in the below image. You will observe that the list of all the columns of that table is automatically selected for exporting. You can choose the columns you want to export by changing the selected columns and then click on the Next button at the bottom, as shown below.
If you click the advanced button below, you can type the equivalent query whose resultant data and content will be exported. Once you click on the next button, you will have the option to select the path where the file should be exported and choose the desired format, either CSV or JSON. If you choose CSV, you can select specific options related to the data to be exported and specify various properties for the export process. These properties may include the field separator to be used and how to interpret the NULL value, among others. The image below demonstrates these options.
After clicking Next, if the same file with the same name and format exists on the specified path, it asks whether you want to overwrite the file or mention the steps that will be followed and executed while exporting, as shown below.
After clicking next, you will see a message that the file has been exported successfully, as shown below.
After running the export process successfully, you can observe that the exported file in the desired format will be created in the specified path.
Exporting the Query Resultset
There is also the provision to export the resultset of any select query executed in Workbench by simply clicking on the export button and then specifying the path and name of the file. Note that in MySQL, using the command line, we can export the query results by using the INTO OUTFILE statement. Let us look at how we can export the resultset of the query in the workbench with the help of an example. Consider the following select query that retrieves the names of all developers that begin with the letter R, as shown below –
select * from developers where name like "R%";that gives the following output after the execution of the query –
Now, we can click on the export button that turns into opening the files, and we can choose the path and name of the file by typing in it as shown below
You can choose the report format from CSV, HTML, XML, JSON, SQL, or Excel whichever you want, from the choice list provided below in the left-hand corner. , click on the save, and your file will be exported with the retrieved result set.
Conclusion – MySQL Export Database
We can export the database, table contents, and structure or the output resultset of the MySQL query in MySQL using the client tools of MySQL like Workbench or even using the command-line mysqldump command to create .sql exported files or using INTO OUTFILE statement.
Recommended Articles
We hope that this EDUCBA information on “MySQL Export Database” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
