Exporting MySQL Data to CSV for Local Transfer

If you’re a developer working with MySQL databases, you might need to export data from your database and transfer it to your local system. In this tutorial, we’ll show you how to export data from a MySQL database to a CSV file and transfer it to your local system using SSH.

Step 1: Login to the Server

First, you need to login to the server where your MySQL database is located. Open your terminal and use the following command to log in:

ssh <user>@<ip> -p <port>

For example:

ssh test_user@172.0.0.2 -p 2505

Note: There are multiple ways to connect to any server from the terminal. We’ve used the most simple one in this tutorial.

Step 2: Export Data to CSV

Once you’ve logged in, you can use the following command to export data from your MySQL database to a CSV file:

mysql -u <user_name> <database_name> -p -B -e "<Query>" | sed "s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" > <~/path-to-folder/file-name.csv>

Here’s an example:

mysql -u test-user test_database -p -B -e "SELECT id, job_id, t_token, t_event_id, t_event_type, t_token, t_submitted_at, e_status_code, e_message, e_response, status, created_on FROM job_logs where status = 'Success' order by created_on desc;" | sed "s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" > ~/backup/logs.csv

This command exports data from the job_logs table with the status “Success” and orders it by created_on in descending order. The data is exported to a CSV file named logs.csv in the backup directory. Here is the breakdown of the command used –

mysql: This command is used to access MySQL and execute queries.
-u <user_name>: This parameter specifies the MySQL username used for authentication.
<database_name>: This parameter specifies the name of the MySQL database to be queried.
-p: This parameter prompts for a password for the MySQL user specified with -u.
-B: This parameter formats query output into a tabular format without additional formatting.
-e "<Query>": This parameter specifies the MySQL query to be executed. Replace <Query> with the desired query.
| sed "s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g": This pipeline passes the output of the MySQL command to the sed command, which performs several substitutions to convert the output into CSV format. Specifically, it replaces single quotes with escaped single quotes, tabs with commas, and adds quotation marks at the beginning and end of each line.

Step 3: Transfer the File to Local System

Once you’ve exported the CSV file, you can use the following command to transfer it to your local system:

scp -P <port_number> <username>@<ip/domain>:~<source-file-path> <destination-file-path>

For example:

scp -P 2505 test_user@172.0.0.2:~/backup/logs.csv .

This command transfers the logs.csv file from the backup directory on the server to your current directory on your local system.

That’s it! You’ve successfully exported data from a MySQL database to a CSV file and transferred it to your local system using SSH. This process can be used to export and transfer data from any MySQL database to any system that supports SSH.