Issue¶

You want to perform a large MySQL database import, and you need a reliable way to read and write data across network connections.

Resolution¶

Netcat is a useful tool for reading doing just that. You can utilize it to work directly, or over an SSH tunnel.

Dumping from a source database¶

In the case where you already have a database and you want to put it on a particular host in a specific database, follow these steps:

1. Open a command prompt on both the target host and source host computers.
2. On the target host (the computer you're exporting to), open a Netcat connection on an arbitrary unused port and pipe its output to the MySQL command-line client. To do this, use a command similar to the following:
   copy

   netcat -l 48151 | mysql -u [username] --password=[password] targetdb

   Where [username] is your MySQL username, and [password] is the username's password.

3. On the source host (the computer you're importing from), pipe a mysqldump command into a Netcat sender that's pointed to the port on the target host that you configured in the previous step:
   copy

   mysqldump -u [username] --password=[password] sourcedb | netcat targethost 48151

After the the target host command finishes you should have a complete database dump.

SSH tunneling¶

You can read general information about SSH tunnels in the article SSH tunneling for server-side applications. The following instructions are directly suited to Netcat.

If the target host is behind a firewall, you may need to tunnel the Netcat connection through it using SSH port forwarding. To do this:

1. Open a command prompt on both the target host and source host computers.
2. On the source host (the computer you're importing from), set up the tunnel using a command similar to the following:

   Note

   This example assumes that your Netcat listener is using port 48151 as in the preceding example.

   copy

   ssh targethost -L 48151:localhost:48151 -f sleep 600m

3. On the target host (the computer you're exporting to), set up your Netcat listener, using the same port as the previous step:
   copy

   netcat -l 48151 | mysql -u [username] --password=[password] targetdb

   Where [username] is your MySQL username, and [password] is the username's password.

4. Switching back to the source host, you'll need to use the tunnel to complete the import. Change the Netcat command to point at either the localhost or the source host (instead of the target host) and H will handle the rest:
   copy

   mysqldump -u [username] --password=[password] sourcedb | netcat localhost 48151