Code Snippet

Access Your MySQL Server Remotely Over SSH

Posted on by Jono Herrington

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!

Vertically Align Anything

Posted on by Jono Herrington

For this first week I want to give total props to Sebastian Ekström over at zerosixthree.se. He wrote an amazing blog post that has spurred me on to share this snippet even more.  Shall we begin?

…… Dramatic Pause ……

I think so!

Have you ever been in a situation where you just wanted to vertically align an element in the center of something and didn’t want to write out a bunch of code to do so? A year ago I found one of the most helpful code snippets I have ever found. Sebastian Ekström zerosixthree.se wrote an amazing post that shows by using the power of transform: translateY, you can vertically center align the way you have always dreamed of … with three lines of code (plus vendor prefixes)!

To do this we write:

.awesome-element {
position: relative;
top: 50%;
-webkit-transform: translateY(-50%);
-ms-transform: translateY(-50%);
transform: translateY(-50%);
}

To take this a step further we can use the power of SASS and write it as a mixin.

@mixin vertical-align {
position: relative;
top: 50%;
-webkit-transform: translateY(-50%);
-ms-transform: translateY(-50%);
transform: translateY(-50%);
}

Then the only step you need to make after that is to add this to it’s parent container (this keeps things crisp):

.parent-element {
-webkit-transform-style: preserve-3d;
-moz-transform-style: preserve-3d;
transform-style: preserve-3d;
}

Again an even better way to do this would be to make it a mixin:

@mixin parent-vertical-align {
-webkit-transform-style: preserve-3d;
-moz-transform-style: preserve-3d;
transform-style: preserve-3d;
}

Taking it a step further:

If you are wanting to help reduce bloat within your code (which I highly advise), you could use the SASS placeholder selector like so:

%parent-vertical-align {
-webkit-transform-style: preserve-3d;
-moz-transform-style: preserve-3d;
transform-style: preserve-3d;
}

%vertical-align {
position: relative;
top: 50%;
-webkit-transform: translateY(-50%);
-ms-transform: translateY(-50%);
transform: translateY(-50%);
}

.incredible-parent-element {
@extend %parent-vertical-align;
}

.awesome-child-element {
@extend %vertical-align;
}

And that’s it folks!

Demo:

Create. Stop. Be Creative.