Setting up external MySQL connections
When attempting to connect externally to your MySQL server through a database-management software, such as Navicat or MySQL GUI Tools, you will run into problems being able to connect. 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.
To edit your Access List, follow these steps:
- Log into the AccountCenter.
- From the Overview page or Domains tab, select your primary domain. See Figure 1.
- Click on the Manage Databases icon, see Figure 2.
- Select the Global Settings tab, see Figure 3.
- Scroll down the page and you will see a section titled Configure External DB Access, see Figure 4. You may add up to 10 IP addresses to the access list. For your convenience, we provide you with a "use my current IP" button to conveniently eliminate any guesswork.
- Click the blue ADD button.
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.
- 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:
- Alternative 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.
FIX: 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.
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.
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.
- 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.
1. Connect to your server via SSH.
2. Log into MySQL.
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';
- 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:
5. Next, quit MySQL by using this command:
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.