Log in to MySQL via SSH

If you're using a shared hosting service (like Dreamhost) it can be a little tricky to figure out how to access your MySQL database from the command line. After a morning lost to trial and error figuring this out, I decided to share my steps in the hope that it would save you some time. Once you figure it out, it's really not so complicated.

First, sign in to SSH using your main server credentials (not your MySQL credentials). So, if your username was user and your domain was example.com, you'd enter this into Terminal, then press Return:

ssh user@example.com

That should prompt you for your password and then display a welcome message and command prompt.

From there, log into your MySQL server. If your MySQL hostname was mysql.example.com, your MySQL username was dbuser, and your MySQL database name was mydatabase, this is the command you'd use:

mysql -h mysql.example.com -p -u dbuser mydatabase

Again you'll be prompted for a password. This time it's your MySQL password that you want to enter. Once that's in, you should see a welcome message followed by a mysql> prompt. That's it, you're in!

Just to be sure that everything's working, you can ask for a list of the tables in your database:

show tables\g

If you're logged in and have permissions to see data, that should display a list of all the tables you have in your database. Note: The \g there tells MySQL to actually execute the command; just hitting Return isn't enough.

Hope that helps save you some time.



This post was published on October 17th, 2015 by Robert James Reese in the following categories: Linux and MySQL Before using any of the code or other content in this post, you must read and agree to our terms of use.