Access Your MySQL Server Remotely Over SSH

When working on a client’s site, I usually have the privilege of having access to something like cPanel. From cPanel I then can access things like phpmyadmin and file manager. Using tools like this has always kept my life simple.

A problem arose, however, last week where I needed to get ahold of a database so that I could set up a local staging site. The issue was that the only credentials the client was able to give me was their SSH credentials. My gut instinct was to email them back saying that I needed more access, but then my inner adventuring self came out.

Over the last couple of years I have been pushing myself more and more to use the command line instead of GUI’s. The main reason for that is because I believe using command line gives you a better understanding of the things you are actually working on. This is what led me to my plan in this situation: to access the MySQL server remotely over SSH.

Luckily I work with incredibly smart people who I know if I don’t know how to do something, one of them will. Due to that I sought out one of the guys that I know is strongest in command line. In the matter of minutes he was able to help me get what I needed with only TWO COMMANDS!

In case anyone is wondering how to do this, please keep reading.

Before you Start

Before you start you will want to make sure you have the following information:

• SSH host
• SSH username
• SSH password
• SSH port number (if applicable)
• MySQL database name
• MySQL username
• MySQL password

Running the Commands

Once you have that information you will want to launch your command line application of choice (I prefer iTerm 2 for Mac). After you have your application running, you need to run this command:

ssh username@host -L 3307:localhost:3306 -p (port_number)

view raw

SSH Tunnel

hosted with ❤ by GitHub

The syntax is ssh <username>@<servername> -L <localport>hostname<remoteport> -p <portnumber>. The reason we use 3307 for the localport here is due to the fact that you might be already using port 3306 depending on your setup.

Things that you need to replace:

• username – this should be replaced with your SSH username
• host – this should be replaced with your SSH host
• (port_number) – this should be replaced with your SSH port number

Once you have done this you should be asked for your SSH password. When that comes up, insert your password and hit enter.

Now that you are in, in order to get a MySQL dump you can run:

mysqldump -u mysql_username -p database_name > filename.sql

view raw

MySQL Dump

hosted with ❤ by GitHub

Things that you need to replace:

• mysql_username – this should be replaced with your MySQL username<
• database_name – this should be replaced with your MySQL database name
• filename.sql – this should be replaced with the name of the file you want the data to be dumped into

Once you run that command you will be prompted for your password. This is now referring to the MySQL password.

WAHLA! You now should be able to find that file inside the current directory. In most cases that is /home/(mysqlusername)/(db_name)/. You can access this via SFTP.

In the End

In the end this was a great solution for me. I’m sure there are even more advanced command line wizards that know of even better ways to do it. With that said, this worked for me. I believe that due to the simplicity of it, it works pretty well. I hope you find it helpful!