Quite a number of cheap web hosting companies ban their customers from accessing MySQL databases directly using software such as Sequel Pro, unless customers pay extra for business hosting accounts or move to dedicated servers. Chillidog Hosting has taken a more relaxed approach, and we welcome any customers (on any hosting plan) to access and administrate MySQL databases remotely.
Sometimes databases require administration or maintenance work doing to them. For example, software like Concrete5 might require you to edit a database to change ownership of a website. Wordpress sometimes needs settings changed in the database. Or you may find that you just want to periodically backup or export a database for safe-keeping.
Although you can already login and administrate databases through PHPmyAdmin (in your control panel), it is fair to say that this can be a bit tedious and the administration panel is not exactly 21st century! So some people prefer to use their own software.
Sequal Pro
Sequel Pro is a free (dontationware) application which you download and install on your computer. It works a bit like FTP software, and lets you connect remotely to a web server and access your MySQL databases. The connection to the database is made via SSL, so it is extremely secure. Sequel Pro includes all the basics for simple database administration tasks, and a large collection of sophisticated tools for expert power-users.
Sequel Pro
Connecting via Sequal Pro
Once you have setup your IP address in Remote MySQL, launch Sequal Pro on your computer. Click File > New Connection if the new connection window is not shown automatically. Click on the SSH tab and follow these instructions carefully and refer to the screenshot as an example:
Sequel Pro Conenction Settings
Name
This is the name of the bookmark to be created, storing your connection details. Typing something like ‘Chillidog’ will do fine.
MySQL Host
This is the host address. Type 127.0.0.1 in the box.
Username
This is the username you created, when you created the new MySQL database in CPanel. Typically it is your CPanel username, followed by an underscore character, and the username of the database. If you are not sure what username you gave your database, log into CPanel, scroll down to the Databases section and click on MySQL Databases. Here you will see a list of users assigned to each database.
Password
Similar to above, this is the password of the user your created, during creation of your MySQL database.
Database
This is optional and can normally be left blank. You have an opportunity shortly to choose a database and tables from a drop-down menu.
Port
The port number needs to be set to 3306.
SSH Host
The SHH Host is your primary / main domain name (the same one shown in the sidebar in CPanel). It is the same domain name you used when you signed-up to Chillidog Hosting.
SSH Username
This is the same username you use, when signing into CPanel.
SSH Password
This is the same password you use, when signing into CPanel.
SSH Port
This is the port number on the web server firewall, to which a tunnel will be created and the database can be connected to. The port number should be set to 23261.
Use the Test Connection button. If the settings appear to be correct, click on the Save Changes button, to save all of these settings as a bookmark. Click the Connect button to continue.
To summarise, the SSH settings are normally identical to those which you use to login to CPanel and which you see displayed in CPanel. These settings are all used to verify that you are the genuine owner of the database, which you are connecting to. The first group of connection settings relate to the database you are connecting to, so you use the same It’s the same username and password combination you used when you linked software (like Armadillo, Wordpress, Concrete5 or Piwik analytics) to your database.
Choosing a database and tables to administrate
Once you are logged in, click on the drop-down menu at the top left of the Sequel Pro window. From the menu, choose the database name. All available tables are shown in the lefthand sidebar. Click to select one of these and then start whatever tasks you want. Other database options like backup and restore can be found in the main Sequel Pro menu. You can find many help resources relating to Sequel Pro on the Sequel Pro website.
Fixing common connection problems
If you are unable to connect to a database:
-
Check that your internet connection is live and that a firewall or security software is not blocking your connections.
-
Log into CPanel and check that your IP address is white-listed in the Remote MySQL page. Dynamic IP’s may change as often as daily.
-
Scrutinise all of your connection settings and check for any errors.
-
If you suspect your IP address may have been blocked by the web server (if you can’t access any of your web pages or CPanel) follow the instructions for unblocking your IP.
Disclaimer
Software like Sequel Pro is extremely fast and efficient for performing just about any database task. It really is a dream to work with and is the same software used by many experts and high-end companies. With this power comes the risk that a simple mistake can break websites or other online services. So get in the habit of making database backups regularly, and take care when manually editing anything in a database or tables. If in doubt, leave it alone. Disconnect and quit Sequel Pro, when you are done with making any edits.
