How to Export MySQL Database?

One of the most popular Linux-based database programs is MySQL. MySQL is versatile, working effectively as a database. It can either be implemented as simply as a product database or as something complex as a WordPress website.

In this article, we shall be exploring various methods by which one can easily use MySQL export database and export the database. Scenarios such as transferring or exporting MySQL database to another destination like a data warehouse. The MySQL export database could then be used for integrating the data from various other data sources to carry more comprehensive and holistic analysis.

To understand the context better, you must visit the link - MySQL and gain an understanding of the basics of the MySQL database, which will help you to understand the importance of exporting MySQL databases.

Exporting Data with the SELECT ... INTO OUTFILE Statement

Let us understand how you can implement MySQL export data using the SELECT ... INTO OUTFILE Statement.

The syntax used for MySQL export data via the SELECT ... INTO OUTFILE Statement is implemented via the combination of a regular SELECT command with the INTO OUTFILE filename at last. The output that one can expect is similar to how one would obtain it by implementing the LOAD DATA command. Hence, with the following command, one can export the database easily. Here we shall be exporting the test_table table into /test/test_table.csv. The /test/test_table.csv is a commonly separated file.

One can also implement the same command via various options. You can modify the format of the output to indicate how one can delimit and quote records as well as columns.

For instance, if you want to export the test_table table as a TEXT format having the CRLF-terminated lines, you can implement the below-given code. Here, the /test/test_table.csv is a tab-delimited, linefeed-terminated file.

Let us explore some of the widely popular properties offered by the SELECT ... INTO OUTFILE command, which one can use in MySQL export database.

The SELECT ... INTO OUTFILE has the Following Properties

Given below are the properties offered by the SELECT ... INTO OUTFILE statement:

• The MySQL server directly creates the output file by the MySQL server. Therefore, the filename must indicate where one wants the file to be written over the server host. It is so, as a LOCAL version of LOAD DATA is available, but in this case, no LOCAL version of the statement is created.
• The file gets created that is readable as well as owned by the MySQL server under UNIX. Hence, you could easily be able to read the file, while you might not be able to delete it.
• To retrieve the file from that host, One could either have a login account on the server host or some other way. If none of the methods are viable, then the SELECT ... INTO OUTFILE command won't fetch any value to you.
• For executing the SELECT ... INTO statement, one must have the MySQL FILE privilege.
• If the output file already exists, then it prevents MySQL from clobbering the important files. Hence, one should avoid already having the output files.

Exporting Tables as Raw Data

With this section of the article, we shall be learning how to export the tables as raw data. For copying or creating the backup of the databases or tables, one can implement the mysqldump program.

With the mysqldump program, one can easily write the table output either as a set of INSERT statements or as a Raw Datafile. This could then be used for recreating the records in the table. Now, if you are looking to dump the table as a data file, you must specify the --tab option. With this --tab option, it is indicated that the directory where the write must happen in the file by the MySQL server.

For instance, the below command could be implemented for dumping the test_table table from the TEST database into a file. And we want the file in the /test/sample/ directory.

Exporting Table Contents or Definitions in SQL Format

For scenarios where one wants the MySQL expert database, and the export of the table must happen in SQL format to a file, one must go for the below-given command.

Once the above command is executed in the MySQL export database, a file is created having the below-shown content.

The table structure for the table test_table where you have four columns, namely roll_id, test_title, test_name, and deadline. The primary key is roll_id, which can be something like the below:

The dumping data for table test_table can be something as seen below:

For dumping multiple tables, you first need to name all of them, followed by their database name argument. While for dumping an entire database, you don't need to name any of the tables after the database, as can be shown in the code block given below:

You can implement the following piece of code to back up all the databases that are available on your host. The --all-database's option could be used, which is only available in the MySQL 3.23.12 version. It is recommended to implement this method for database backup strategy.

Copying Tables or Databases to Another Host

If you are looking for ways to copy tables or databases to another host, you can continue reading this section of the article. One can use the mysqldump with the table name and database name for copying the tables or databases from one MySQL server to another host.

You must execute the below-mentioned command at the source host. With the execution of the below command, you can easily dump the entire database into the sample_dump.txt file.

If you are looking for ways to copy the complete database without using the specific table name, as we discussed above.

Also, you can ftp the sample_dump.txt file on another host via the below-mentioned command. But it is recommended that you implement the below command once you have created the database_name on the destination server.

When you run this command, you shall see the output as below, where you will be asked to enter the username and the password. Once the grant is successful, it gives us the ftp> prompt.

Once done, you can simply import the database to your destined host.

There is another way of achieving the process of copy tables or databases to another host without the usage of any intermediary file. This can be done by sending the output of the mysqldump over the network to the remote MySQL server directly. Once both the servers are connected from the host where validate that the source database must reside, then you are free to implement the below command.

It is to be noted that you must have access to both servers before you execute the above command, or else you will encounter an error. Half of the command in the mysqldump connects with the local server, while the remaining half of the command connects to the remote MySQL server on the other-host.com. The former writes the dump test output to the pipe, whereas the latter reads the pipe for the input received and simultaneously sends every statement to the other-sample-host.com server.