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:

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:

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!

Advertisements

3 comments

  1. Frankie Jarrett · March 31, 2015

    Nice tutorial! Gotta love how simple working in the shell actually is once you get used to it.

    However, I’m also a huge fan of Sequel Pro. I use it frequently.

    It’s a beautiful standalone app for OS X that can be quite useful if you want to visualize your tables but think phpmyadmin sucks 🙂

    Like

    • Jono Herrington · April 1, 2015

      I would agree … Sequel Pro is great for visualization and much better then phpmyadmin. When you need to do simple quick things like this, I think I would still default to the shell personally.

      Like

  2. Feso · April 1, 2015

    Reblogged this on Кодирај and commented:
    Добра идеа за продуктивно користење на алатите. Знам дека оваа дтатија ми помогна мене, па пробајте и вие 😃

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s