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>
ssh email@example.com -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
-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
sedcommand, 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>
scp -P 2505 firstname.lastname@example.org:~/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.
- OpenSSH documentation: https://www.openssh.com/manual.html
- MySQL documentation: https://dev.mysql.com/doc/
- Bash documentation: https://www.gnu.org/software/bash/manual/bash.html
- Sed documentation: https://www.gnu.org/software/sed/manual/sed.html
- SCP documentation: https://man.openbsd.org/scp
- SSH documentation: https://man.openbsd.org/ssh