How to do a MySQL Database Dump Remotely

Mysqldump is a command-line utility that is used to generate the logical backup of a MySQL database. It produces the SQL Statements that can be used to recreate the database objects and data. In some cases you might find yourself needing to do a dump of your MySQL database from a remote server. SSH is an excellent tool to establish a secure connection to do exactly that. 

Let’s say you are ready to go live with your staging site with Servebolt, but the database has to be updated with new data from your existing live site. In that case it can be useful to take a copy (dump) of your MySQL database remotely. This is an easy guide on how to do so by using SSH to log onto the remote server.

Syntax:

ssh -p [PORT] [USER]@[REMOTE_SERVER] mysqldump -u [REMOTE_DATABASE_USER] -p [REMOTE_DATABASE_NAME] > ~/private/db_dump_filename.sql

mysql dump done with ssh from remote server

When hitting enter, you are first asked for the SSH password for the remote server and thereafter the database password. This places a copy of your database in the /private/-directory of your site. If you need to upload it to your existing database with Servebolt you can use this file to sync it.

mysql -u [USERNAME] -p [DATABASENAME] < ~/PATH/TO/FILE/FILENAME.sql

When hitting enter, you are asked to enter the database password. After filling in the correct password, the server will do the rest. Once the process is finished you can find a copy of your database in the file that you’ve created this way.