Developing, maintaining and debugging web-applications often involves copying a remote database to a local or another remote machine. This post lists a number of methods that I find useful. It acts as a reference for myself which I happily update and improve based on comments and experience. My favourite method:  stream the database dump directly to the target machine.

I assume a MySQL or MariaDB database on a Unix or Mac OS machine but by adjusting the appropriate commands most of these methods apply to other databases as well.

When using SSH and MySQL on a regular basis you could take a look at the following posts to simplify your work/life:

Export to a file, copy with rsync, import into MySQL

At the source machine, dump the relevant database to a file.

mysqldump -udbuser -p dbname > ~/dbname.sql

If the default username and password are supplied in .my.cnf, the -u and -p options can be omitted. It is assumed that default credentials are supplied. Otherwise, just provide the -u and -p arguments to the mysql and mysqldump command.

Now, connect to the target machine. If the target database does not exist, create it:

mysql -e 'create database `dbname`'

Download the dump and import it to the target database:

rsync -z user@source.machine.net:dbname.sql .
mysql dbname < dbname.sql

Rsync synchronises files over an SSH connection. The -z option enables compression on the connection. If you don’t use key-based authentication, provide the -p option to rsync to supply a password.

Stream a database directly from source to target machine.

This method is faster and more elegant but somewhat harder to remember. The output of the mysqldump is streamed over ssh and piped to the target mysql server.

Assuming default credentials have been provided in .my.cnf, otherwise, provide the -u (user), -p (password) and -h (host) options to the mysql command.

If the target database does not exist, create it:

mysql -e 'create database `dbname`'

Now, stream the remote database dump directly into the target mysql server.

ssh -C user@source.machine.net 'mysqldump source_dbname' | mysql target_dbname

The -C option enables compression.

Using pipe viewer to display progress

Additionally, pipe the dump through the pv (pipe-view) utility to show the dump progress. Install pipeview if it is not available on your system.

Mac OS (Homebrew): brew install pv
Debian/Ubuntu: apt-get install pv

ssh -C user@source.machine.net 'mysqldump source_dbname' | pv | mysql target_dbname

Piping through GZIP

Applying explicit gzip compression to the mysqldump might improve performance if there’s no compression used on the SSH connection already.

ssh user@source.machine.net 'mysqldump source_dbname | gzip' | gunzip | pv | mysql target_dbname

Sponsored content