Retrieving Remote MySQL Data as a CSV File Locally

Here's a practical method to extract data from a remote MySQL database and save it in CSV format on our local machine, all with a single command.

The Scenario

Imagine you are managing a web application that stores user registration data. Periodically, you want to export this data to analyze it locally. For this, we'll create a secure and effective command-line process.

The Command Strategy

We will use SSH to securely connect to the remote server, invoke the MySQL command to retrieve the data, and then format and save this data as a CSV file on our local machine.

The Step-by-Step Process

1. Establish a Secure SSH Connection

We utilize SSH to securely connect to the remote server. SSH not only allows us to log in to the server but also to execute commands on it.

Example Command Part:

ssh [SSH_USER]@[REMOTE_SERVER]

2. Retrieve Data using MySQL

Once connected, we run a MySQL command to retrieve the data we need from the remote database. Ensure you have access to the database and are aware of the structure to form your SQL query effectively.

Example Query Part:

mysql -u [DB_USER] -p[DB_PASSWORD] [DB_NAME] -e "SELECT * FROM users;"

A Note on Security: Embedding passwords directly in scripts or command lines, as shown above, is discouraged due to the potential exposure of sensitive information to logs or unauthorized parties.

3. Convert Data to CSV Format

The MySQL data is tab-separated by default. To convert it into CSV format, we will pipe (|) the output into a sed command to replace tabs with commas.

| sed 's/\t/,/g'

4. Save Data to a Local CSV File

Finally, we will redirect (>) the data into a CSV file on our local machine.

> users_data.csv

Putting it Together

Combine all the components into a single command, ensuring you replace the placeholder values with actual data.

ssh [SSH_USER]@[REMOTE_SERVER] "mysql -u [DB_USER] -p[DB_PASSWORD] [DB_NAME] -e \"
SELECT * FROM users;\"" | sed 's/\t/,/g' > users_data.csv

What I'm building

Delegate tasks. Get software.

Give Vroni a GitHub issue, bug report, spec, or rough idea. It reads the repo, plans the change, writes code, runs checks, and works toward a review-ready pull request.

Take a look at vroni.com