Import a MySQL Database Dump Remotely Using an SSH Tunnel



When managing databases deployed inside a private Virtual Private Cloud (VPC), direct public access is usually disabled for security reasons. Often, the bastion or jump server connecting to this VPC doesn't have mysql-client installed—and you might not have sudo privileges or want to bloat the server by installing extra packages.

The good news? You don't need mysql-client installed on your remote server at all.

By leveraging SSH Port Forwarding, you can securely bridge your local computer to the private MySQL instance inside the VPC. This lets you execute database imports directly from your local terminal or GUI tool without transferring large .sql files to the intermediate server first.

Technical Overview

+------------------+                    +--------------------+                    +---------------------+
|  Local Computer  | -- (SSH Tunnel) -->| Remote VPC Server  | -- (Private Net) -->|   VPC MySQL DB      |
|  (mysql-client)  |  Port 3307 -> 3306 |   (Bastion Host)   |                    | (Private IP / Host) |
+------------------+                    +--------------------+                    +---------------------+

  • Local Machine: Runs the mysql CLI client and stores the .sql dump file.

  • Remote Server: Acts purely as an SSH proxy/bastion host with VPC access to the database.

  • VPC MySQL Instance: The target database endpoint reachable only from inside the private network.

Step 1: Establish the SSH Tunnel

Run the following command on your local machine to build an encrypted bridge to the private database through your SSH server.

ssh -L 3307:<private_db_host>:3306 <ssh_user>@<server_public_ip> -N

Argument Breakdown:

  • -L 3307:<private_db_host>:3306: Forwards traffic sent to your local machine's port 3307 through the SSH connection to <private_db_host> on port 3306. (Using 3307 locally prevents port conflicts if you already have a local MySQL instance running).

  • <private_db_host>: The private IP address or private DNS endpoint of your MySQL instance inside the VPC (e.g., 10.0.1.50 or mydb.internal.cloud).

  • <ssh_user>@<server_public_ip>: The SSH credentials and public IP address of your remote server.

  • -N: Directs SSH to establish the connection without opening a remote shell execution session.

Keep this terminal window open. Closing this process will destroy the tunnel.


Step 2: Import the .sql Dump File

Open a second terminal window on your local machine and execute the standard import command, routing the request through 127.0.0.1 on local port 3307:

mysql -h 127.0.0.1 -P 3307 -u <db_user> -p <target_database_name> < /path/to/your/database_dump.sql

Parameter Details:

  • -h 127.0.0.1: Connects to localhost, directing traffic straight into the active SSH tunnel.

  • -P 3307: Specifies the local forwarded port created in Step 1.

  • -u <db_user>: Your remote MySQL user name.

  • <target_database_name>: The name of the target database inside the MySQL server.

  • < /path/to/your/database_dump.sql: Streams your local .sql file straight into the remote database.

Enter your database password when prompted. The import will execute seamlessly over the encrypted channel. 


Troubleshooting & Best Practices

  • Connection Refused on 127.0.0.1: Verify that your SSH tunnel terminal is still open and running in the background.

  • Large File Imports Hanging: If you are importing multi-gigabyte dump files over an SSH tunnel, increase network timeout settings or disable foreign key checks at the beginning of your dump file to prevent timeouts:

  • Gzip Compression: If your .sql file is compressed, you don't need to extract it first. Stream it directly through thetunnel:

    gunzip < database_dump.sql.gz | mysql -h 127.0.0.1 -P 3307 -u <db_user> -p <target_database_name>
Labels:

Comments

Post a Comment