Connecting to your MySQL database with MySQL Workbench

MySQL Workbench is a program you install on your own computer to work on a database the way you would work in a spreadsheet: browse tables, run queries, export, import. It is far more comfortable than phpMyAdmin if you spend hours in there, and it is the tool a developer will ask you for.

The part that usually goes wrong is not the program. It is the route to the server. That is what this article is about, and it starts by saving you an afternoon.

A direct connection to the database port does not normally open from outside. The MySQL port is not exposed to the internet by default, and that is a security decision rather than a fault. If you typed the server name into Workbench and it sits there thinking until it gives up, your password is not the problem. The route that works is an SSH tunnel, described below.

The three things you need

Detail Where to get it
The database In cPanel, under MySQL Databases. The full name carries your account prefix in front of it, and the full name is what you type.
The database user Also under MySQL Databases. It is not your cPanel user. It is a separate account made for the database, and it has to be attached to that database with all privileges.
An SSH login That is your cPanel user and password. It only works if your plan has SSH access switched on. If it does not, ask us.

If you have not created the database and the user yet, the walkthrough is in creating databases and using phpMyAdmin.

The SSH tunnel connection, field by field

In Workbench, instead of a plain connection, pick the type Standard TCP/IP over SSH. You now get two blocks of fields: the SSH ones, which are the route to the machine, and the MySQL ones, which are what happens once you are inside it.

Field What to put in it
SSH Hostname The server name, then a colon, then 2299. The SSH port here is not 22, and forgetting that is the number one cause of «it will not connect».
SSH Username Your cPanel user.
SSH Password Your cPanel password, or a key file if you prefer keys.
MySQL Hostname 127.0.0.1. This trips everybody up: by this point you are already inside the server, so the address is the server as seen from within itself.
MySQL Server Port 3306.
Username The DATABASE user, prefix included.
Password That user’s password. If you do not have it, set it again in cPanel: it is never shown again after it is created.
Test before you save. Workbench has a Test Connection button that runs the whole chain and tells you which step failed. Fail at the SSH stage and it is the login or the port; fail at the MySQL stage and it is the database user. Two different problems, two different cures.

What about a direct connection, with no tunnel?

It exists, and there are cases for it: a desktop application that always connects from the same office, for instance. But two things have to be true at once, and one is not enough.

1 Your IP address has to be allowed, in cPanel, under Remote MySQL. Without that the server refuses the connection even with the right password.
2 The port has to be open to the outside, and by default it is not. That part is not in cPanel: talk to us, and we will tell you whether it fits your case and what it means.
3 Your IP has to be fixed. If it changes from one day to the next, as most home connections do, you will spend your life adding it back to the list. To find yours, see how to find your public IP address.

Side by side, the tunnel wins nearly every time: it works from any network, it exposes the database to nobody, and it needs no permission from us.

What usually goes wrong

What you see What it is
It hangs and gives up Almost certainly a direct connection instead of a tunnel, or port 22 typed into the SSH box instead of 2299.
Access denied for user The database user is right but was never attached to the database, or was attached without full privileges. Go back to Add User To Database in cPanel.
Unknown database The account prefix is missing from the name. The full name is the one listed in cPanel, not the one you typed when you created it.
A warning about the server version The server runs MariaDB, which Workbench talks to happily but does not always recognise by number. The warning is harmless: carry on.
It connected yesterday, not today If you were on a direct connection, your IP changed. If you were on a tunnel and it stopped, the firewall may have blocked your IP after failed attempts: see why the firewall blocks your IP.
Do not edit real data without a copy to hand. Workbench runs what you tell it, with no second question. An UPDATE with no WHERE wipes the whole column in under a second. Take the copy first: making and keeping your own backup.

Not sure whether your account has SSH access switched on? Ask us and we will check, and switch it on.

Open a support ticket

SEE ALSO

Hosting plans and what each one includes

Support Policy: how far our help goes

RECOMMENDED PRODUCT

Web hosting with cPanel

Domain and SSL included, daily backups and the panel you already know. from R180.00/mo

See plans
  • 0 Users Found This Useful
Was this answer helpful?

Related Articles

How to access cPanel Control Panel? cPanel is the control panel where you manage everything in your hosting account - files, e-mail accounts, databases, bac...
How to create an e-mail account in cPanel? Creating a professional e-mail address on your own domain - such as info@yourdomain.com - takes less than a minute in cP...
How to change your cPanel password There are two ways to change your cPanel password, and the difference is simple: one needs the current one, the other do...
What can you do in cPanel? cPanel is the control panel included with your hosting plan. Everything about your site - files, e-mail, databases, doma...
Your cPanel access details To log in to cPanel you need three things: the address, your username and your password. This page explains what each on...
When the site says it cannot connect to the database It is one of the more frightening messages a site can show, because there is no page at all behind it: just a line of wh...