On-the-fly database migration between two hosts (no dump file)

Hi, world!

Yesterday I had to migrate a MySQL database from on host to another. I didn't want to follow the standard process of (1) dumping a file, (2) transferring it and then (3) loading it in the new host for a few reasons:

• The source host was low on space and the database was big
• This is a waste of time because dump and load does not run in parallel
• I like to do complicated stuff ;)

So, the idea was to use some pipes and redirect the data flow through SSH to the target host, which would receive it and load on the new database.
I did it with MySQL, but it should work with any database that supports dumping to the standard output and loading dumps from standard input.

Requirements

• Have two Linux hosts with SSH available
• Have pv and gzip installed (pv is not actually required)
• Need to transfer a huge database between them

Transfering

You have to run one command in each host. I'll call the host that currently contains the database to be transferred source host and the host that will receive the new database as target host. You need to start the with the target host as it will wait from the connection from the source host.
I recommend you to run both sides inside a screen shell to avoid disruption by network problems if you are accessing the hosts remotely.

Target host

The command in the target host is the following (broken in lines for the ease of reading):

nc -l 3456 | \
  gunzip | \
  pv | \
  mysql -u tdb_user -ptdb_pass targetdatabase

This command receives the data from the source host and then flows it through some process. The nc command waits a connection listening on port 1234. When it receives a connection, it starts sending data to gunzip, which decompress the data. pv is just to show how much data has passed through it. Finally, mysql loads the data it receives from standard input in the targetdatabase. Keep in mind that this only starts when the other side connects and sends data.

Source host

The command in the source host is the following (broken in lines for the ease of reading):

mysqldump -u sdb_user -psdb_pass sourcedatabase | \
  pv | \
  gzip | \
  ssh sshuser@targethost nc 127.0.0.1 3456

This command sends data to the target host. Firstly, mysqldump extracts the data from sourcedatabase and sends it to the standard output. pv receives it and only shows how much data is passing through. Then, gzip process compresses the data and ssh sends it to the other side. Notice that SSH is running a nc remotely. This nc is responsible for connecting in the nc you started on the target host and plug the two sides.

That's it!

If everything is all right, in some time the database will be already copied to the target host! In almost half the time (actually, the total time is expected to be the load time on the target host).

If you have any doubt or suggestion, please post in the comments!