Export / Import a MySQL database between remote server and local machine

Upload or download a MySQL database between a remote server and localhost using the Linux command line.

Download a remote MySQL database

Download a remote MySQL database using mysqldump dump. The output of the dump is compressed using gzip. This compressed file is then unzipped and downloaded into the file remotedb.sql.

# Download remote DB to local. Will ask for remote MySQL user's password
ssh username@server.com "mysqldump -u dbuser -p dbname | gzip -c" | gunzip > remotedb.sql
Code language: PHP (php)

Copy local database to remote server

Upload a local MySQL using SSH. The remote database dbname and the database user (dbuser) must have permissions to the database. The remote database must also be empty.

# Upload & import local DB into remote DB. Can be run with -pPasswd but will expose remote DB password to shell history
ssh username@server.com "mysql -u dbuser -p dbname" < /local/sqlfile/location/localdb.sql
Code language: PHP (php)