Remote Access to MySQL Database for WordPress

Here's a quick visual tutorial on how to connect to a Remote MySQL Database, using MySQL Administrator.

These instructions should be compatible with all hosts that allow remote connections to MySQL and have CPanel. Most hosts I've dealt with, offering C-Panel allow this type of connection, but YMMV.  Check their documentation, or just try my tutorial. If their documentation isn't clear, or the tutorial I wrote doesn't match what you see with your webhost. Let your webhost know, or you can ask for help in the comments.

Works in all 3 major platforms

You can use this for MySQL Administrator in Mac, Linux & Windows

The Goal here is to: Connect to your WordPress Database on a regular webhost such as [random:webhosts] using MySQL Administrator. You can probably configure other webhosts which offer C-Panel and MySQL with minor tweaks to the instructions, and really it doesn't have to be a WordPress Database.

It should take you about 5-10 minutes to complete

Get your external IP Address

If you're connecting to a remote webserver, you most likely will need your external IP Address.

[singlepic id=105 w=450 h= float=center]

It should be something like NNN.NNN.NNN.NNN where each "N" is a number from 1 to 254.  Down below you'll see the same number in a slightly different format: ipnnn-nnn-nnn-nnn.your.service.provider.com. That's it! That's your IP Address. Note it down, you'll need it shortly.

Add your IP Address to the Remote MySQL

Log into your CPanel and add Databases, then click on Remote MySQL. The next screen will let you enter the IP Address you got from ipchicken. Type in the address and click "Add Host." Make sure there are no spaces before or after the address.

[singlepic id=107 w=450 h= float=center]

Verify your IP Address was added

After you enter the address in there, you should see it listed under "Access Hosts." This tells the MySQL Server that your IP Address is allowed to connect remotely. You'll need to change this if your IP Changes periodically.

[singlepic id=110 w=450 h= float=center]

Please note that you might want to do the previous step only when you need to access your database remotely. As with any other service that is available on the internet, having a port open can potentially lead to someone trying to hack your server. Please Please Please note that I said POTENTIALLY. It isn't a trivial thing to do, but if you're paranoid you could just activate this for the time when you need to work on your database then remove the IP when you're finished.

Configure a new MySQL Administrator Connection

Assuming you've already installed MySQL Administrator, you now need to create a new connection. Start the program, and click on the "..." next to the Stored Connection data field. This will take you to the screen you see below and you can configure your connection information there.

[singlepic id=106 w=450 h= float=center]

Usually, the fields are the same as you have for CPanel. Username usually matches the one you use to login to your webhost's cPanel. Password is also generally the same.  Hostname could be different in different cases, your best bet and most defaults is the same as your domain, e.g. www.example.com. Schema is the database name.

Use the Stored Connections Dropdown to connect

[singlepic id=109 w=450 h=0 float=center]

Using the dropdown menu from the MySQL Administrator, you can select the configuration you just created and be on your way to MySQL Administration bliss. If you like using a program in your Desktop, and prefer graphical interfaces (GUIs) instead of text-only or web applications, this is what you need.

Go crazy in there looking around

[singlepic id=108 w=450 h= float=center]

Just don't start changing things you're not sure about. This is one of many ways of administering your MySQL database, and it works particularly well for WordPress Installations. A companion to MySQL Administrator is MySQL Query Browser which allows you to actually run queries against the databases so you may change many fields at once, or do special queries from your desktop. This works both on Windows and Mac, and if memory serves me well even Linux.

What can you actually do with this?

A few of the things you can get done with this are:

• Changing many posts at once with a MySQL query.
• Running a query to see how many plugins you have installed.
• Auditing your database to make sure you haven't been hacked.
• Making a Backup (use the Schedule Tab under Backup for automatic nightly backups)
• Restoring your database from a backup after a problem.
• Exporting registered users for an external report.
• Learn the WordPress Database structure.

Please share this post if you found it useful. You can use one of the buttons below to Tweet it, send it to FaceBook, or LinkedIn, or save it to other locations. Your comments and feedback is also welcome!