Backingup and Restoring a MySQL Database between 2 Remote Hosts in Bash

Generally, backing up an SQL DB and restoring it is relatively straightforward. Most of the guides talk about backing up and restoring a DB on the same host. Some might talk about pulling down a backup from a remote location and restoring it locally. But, what about when both of your servers are remote and you're SSH'd into one of them? This took me a bit of digging the first time I did this so I wanted to put this information in one place, whether it's for someone like you who's Googling or for myself in a couple years when I have to do this again.

In my situation, I'm migrating an SQL database from one remote server to another. I'm using my workstation to remote into the web server that connects to one of the remote SQL servers. The DB is currently residing on a remote MySQL server on my employer's older DB server farm. I need to migrate it to the new farm and point the web server at the DB in the new location.

The primary reason this took me as long as it did was 1) I've never had to do it before and 2)I'm still learning how to do a lot of things in Linux. If you're in the same situation, and you're trying to accomplish something similar, below are the commands you'll want to tweak.

Backing up a MySQL Database from a Remote Server

A lot of the guides I read on performing this made it sound like you're supposed to login to the MySQL Command Line Interface to run the mysqldump command. That is NOT the case. You'll do this from BASH: Not the MySQL CLI. The command will look something like:

mysqldump -h [source FQDN] -P [source hostname port #] -u (username for the DB] -p [DB name] > /dir/to/place/backup.sql

If I were taking a backup of our sales DB from our company's SQL server, as root, and pulling the resulting file into the web server I'm running the command from, a sample command would look like:

mysqldump -h sql01.company.com -P 3309 -u root -p sales >/home/admin/salesbak.sql

This command doesn't copy or transfer the tables from a DB. Instead, it copies the SQL queries necessary to rebuild the DB (whether that be at a later time or "moving" a DB to another location).

Restoring a MySQL Database to a Remote Server

Restoring a DB is relatively simple. The command will look something like:

mysql -h [destination FQDN] -P [remote TCP/IP port #] -D [DB name] -u [username for the DB] -p

You'll be prompted for the password for the username specified in the command. If I were restoring the sales DB to the SQL server test.company.com on TCP/IP port 3366 as the root user then my command would look like:

mysql -h sql02.company.com -P 3366 -D sales -u root -p

NOTE: The -P option is only needed if you're running these commands against servers with custom ports. The default port for a MySQL instance is 3306.