Setting up External MySQL Connections

• Applies to: Grid
  • Difficulty: Easy
  • Time Needed: 10
  • Tools Required: AccountCenter access, SSH
• Applies to: VPS Hosting
  • Difficulty: Easy
  • Time Needed: 10
  • Tools Required: SSH
• Applies to: All DV
  • Difficulty: Easy
  • Time Needed: 10
  • Tools Required: SSH

Introduction

When attempting to connect externally to your MySQL server through a database-management software, such as Navicat or MySQL GUI Tools, you will may run into connection problems. Typical error messages include "Lost connection to MySQL server" or "Access Denied", or some other text describing a difficulty in establishing the connection.

The reason this happens is because we secure your database access to limit external connections only to IP addresses you've explicitly declared on your External Database IP Access List.

Add access

To edit your Access List, follow these steps:

1. Log into your Media Temple account.
2. Select the blue ADMIN button associated to your Grid server.
3. Select Manage Databases from the Databases menu.

    

4.   On the Manage Databases page, click on the Users & Settings button to continue.

    

5. Scroll down the page and you will see a section titled External Databases. Add the IP address you wish to allow external database access using the Add IP button. You may add up to 10 IP addresses to the access list. For your convenience, we provide you with a Use Current IP button to eliminate any guesswork.

    

6. Select ADD IP.

After saving the changes, you will be able to connect to your database using the server name external-db.s00000.gridserver.com. (Be sure to replace 00000 with your site number. You can find this in your Server Guide.)

Supplemental links

• Navicat has instructions on their site in the Connection Settings section (based on your operating system, of course): http://www.navicat.com/en/support/documentation/online_manual.html
• MySQL GUI tools download:
  http://MySQL.org/downloads/gui-tools/5.0.html
• You may wish to use an alternate method of connecting externally to MySQL using a method called "tunnelling:" Tunnel local MySQL server through SSH.

Problems and fixes

Q: Why can't I connect to MySQL from an external connection even though my IP address is listed?
A: This usually happens when your database has been moved to / from a MySQL Container. Simply remove your IP address from your AccountCenter and add it again. This will reset the permissions and allow you to connect to your database(s) again.

NOTE:

When making an external MySQL connection from an external webserver, you must enter the server IP into the DB access tool and not the IP address of the website. When the server is making the external call, it will use the default IP address which is the server IP, not necessarily the IP address with which the website was provisioned.

Overview

READ ME FIRST
This article is provided as a courtesy. Installing, configuring, and troubleshooting third-party applications is outside the scope of support provided by (mt) Media Temple. Please take a moment to review the Statement of Support.

This walkthrough will show how to enable non-localhost connections to databases on your DV server.

NOTE:

• Make sure to create a database and user via your Plesk administration panel BEFORE completing the steps in this tutorial.
• To enable connections to your MySQL database from a location other than your server, you need to enable non-localhost connections.
• This may pose a security threat since opening up your MySQL database to connections from the Internet can be construed as a vulnerability. Do not attempt this unless you know what you are doing. If possible, only add staticly assigned IPs to minimize potential threats.

SSH

1. Connect to your server via SSH.

2. Log into MySQL.

my

Alternatively, you can log into MySQL as the admin user:

mysql -u admin -p`cat /etc/psa/.psa.shadow`

3. If you are attempting to grant non-localhost access to a user, you should use this line:

GRANT ALL PRIVILEGES ON dbname.* TO username@'IP' IDENTIFIED BY 'password';

Where:

• dbname is replaced by the database you'd like to open up (a * here will open up all databases)
• username is replaced by the user to be allowed access
• IP is replaced by the actual IP to connect from (a % here will open up to all IPs -- NOT RECOMMENDED).
• password is replaced by the desired password.
• A blank field here will result in no password (NOT RECOMMENDED).
• Changing the password for that user listed in Plesk will set it as well.

4. Apply these changes by using the MySQL command:

FLUSH PRIVILEGES;

5. Next, quit MySQL by using this command:

quit

6. You may need to allow the source IP from which you are connecting to connect to port 3306 after granting the privileges inside MySQL. Connect to your server as "root" and issue the following command:

iptables -I INPUT -s <SOURCE IP ADDRESS> -p tcp --dport 3306 -j ACCEPT

Be sure to replace <SOURCE IP ADDRESS> with your IP address.

Plesk

This will apply to DV/VPS server running Plesk 12.5 and Onyx.

1. Log into your Plesk control panel
2. Look for the Server Management section. Then click on Tools & Settings:

3. Once in Tools & Settings, look for Applications & Databases. Then click on Database Servers.

4. Once in Database Servers, click on Settings.



5. The section in question will be "Default settings for remove access of database users." By default Plesk will have this as "Allow remove connections from any host." If you want to make this specific to IP addresses, then you can select Allow remote connections from, then insert your desired IP Addresses.

Make sure to select OK or Apply to save your changes.

cPanel

 Your cPanel version may have a different interface than the one depicted here, but you should still be able to use these instructions to enable a remote connection.

1. External MySQL connections are disabled by default in cPanel. You will not be able to connect to your cPanel databases from an external host until the connection has been enabled. This must be done for each host. This also applies to connecting from cPanel to external databases. In order to enable connections, log into WHM and navigate to SQL Services >> Additional MySQL Access Hosts.

2. You will need to enter the host IP for each external MySQL database that you'd like to connect to.

3. You now have the option of enabling the external connection for all cPanel user accounts. If you do not use this option, you will need to manually enable the connections.

Enabling external connections for cPanel users

If you chose not to enable the external connection for all users, you will need to log into the cPanel control panel for each relevant account and add the remote host. Once you've logged in, look for the Databases section and select Remote MySQL. Type in the host name or IP and select Add Host.

You should now be able to connect to external databases. If you have any questions or concerns, please feel free to contact our award winning support team. 

Resources

• MySQL 5.1 Reference Manual: Causes of Access-Denied Errors
• MySQL 5.1 Reference Manual