Chat with us, powered by LiveChat

This website uses cookies

Our website, platform and/or any sub domains use cookies to understand how you use our services, and to improve both your experience and our marketing relevance.

How to Enable MySQL Remote Connection

March 27, 2019

5 Min Read
mysql remote connection
Reading Time: 5 minutes

Imagine you are part of a big team of developers with multiple roles, working on large software products, complex database schema and connection. You need to work with SQL queries and create a connection remotely from different locations. To enable MySQL remote connection, you need to grant remote MySQL access on your hosting server and whitelist the IPs of your developers.

Although this is a simple process, it is not very secure.  A better approach comprises of adding developers as team members in your hosting solution, and then allowing them to connect to MySQL database remotely, as MySQL allow remote connections. This will allow you to create a secure environment, and let your remote developers work with the centralized database.

Connect to Remote MySQL Database From the Command Line

Enable MySQL Remote Connection
1. Add a Developer as a Team Member?
2. Whitelist IP of the developer
3. Apply Remote Access Configs
4. MYSQL Client Connection
5. Connect to Remote MySQL Database From the Command Line
6. Final Words

On Cloudways PHP hosting platform, you can easily enable MySQL remote access by following the few steps outlined in this article. By default, Cloudways does not allow MySQL access from remote hosts because of the inherent security risks to the servers and applications.

In this article, I’ll show you how to connect MySQL remote server for efficiently working remotely on your projects. I will cover the following topics:

  • Add a developer as a team member
  • Whitelist IPs
  • Apply remote access configs
  • MySQL Client Connection

Add a Developer as a Team Member

At Cloudways there are two ways you can give access to developers. The first is to give them SFTP access. The second is to add them as a team member so they are comfortable and can be more independent with their servers and applications. For allowing MySQL remote access, the best option is to add your developer as a team member first, before granting the permissions and whitelisting his public IP.

Note: Only account owners can create Team Members. If an email address is already associated with a Cloudways account (Trial or Full), it cannot be used as a team member account.

Simply log into the Cloudways Platform with your credentials, and then click on the ‘team’ subtitle in the top menu.

Now, click on the Add icon on the top menu bar and choose Add Member.

 

Provide the name and email address of your team member and select the Job Title from the drop-down menu. Once that is done, set his/her status to Active.

 

Whitelist IP of the developer

The next step is to find your public IP and add it to the targeted server. Then, move to the server access detail page and click on security tab. Add the IP address to the “Add IP to Whitelist” text area and click the “Add” button. You can use this method to add multiple IP addresses if you need to. After this, simply click the save button.  

You can also use sites like whatismyip to find your public IP.

whitelist ip

 

Apply Remote Access Configs

As you whitelist your IP, you can copy your database credentials from application access details page and add to your php connection code like this:

<?php

function getdb(){

$servername = "46.101.5.233"; // put your cloudways server IP here
$username = "qxxfumxxxbd";
$password = "xxxxbQxxmM";
$db = "qxxfumxxxbd";

try {

   $conn = mysqli_connect($servername, $username, $password, $db);

    //echo "Connected successfully";

   }

catch(exception $e)

   {

   echo "Connection failed: " . $e->getMessage();

   }

   return $conn;

}

As you can see above, I’ve added the Cloudways server IP address in host connection. That’s all you need to grant MySQL remote access. You are then good to go for creating queries and manipulating the database.

MYSQL Client Connection

In the explanation above, I explained how to set up a MySQL remote connection at Cloudways and then use it in your code. Sometimes, however, you need to use MySQL clients to work with databases. You can also connect clients like SQLYOG and Mysql workbench. In sqlyog you just need to pass on the Cloudways server IP and database credentials to create db connection.

connect to mysql host

You will now see your database is connected to sqlyog.

mysql database connected

You Might Also Like: MySQL Performance Tuning Tips To Optimize Database

Connect to Remote MySQL Database From the Command Line

To connect remote MySQL database through command line, just follow the below given steps.

Open the Cloudways SSH terminal and provide your application database name and password by using following command:

mysql -u USERNAME -p

At the Enter Password prompt, type your password. When you type the correct password, the mysql> prompt appears.

After enabling MySQL remote access, just type the following command to get the list of all databases:

show databases;

To allow remote access to MySQL database, type the following command at the mysql> prompt, replace DBNAME with the database which you want to access:

use DBNAME;

After you are granted the MySQL remote access, you can run SQL queries, list tables, and carry out your other tasks. To view a list of MySQL commands, type help at the mysql> prompt.

To exit the mysql program, type \q at the mysql> prompt.

Grant Remote Access to MySQL Database

In order to grant access to a user from a remote host, you must follow these steps. Log in to your MySQL server locally, as the root user, by using the following command:

mysql -u root -p

Use a GRANT command in the following format to enable access for the remote user. Ensure that you change 1.2.3.4 to the IP address that you had obtained earlier. You must also change my_password to the password for which you want to use for “demoemployee”.

mysql> GRANT ALL ON demoDatabase.* TO demoemployee@'1.5.6.4' IDENTIFIED BY 'my_password';

Final Words

Cloudways provides an easy way for developers and software business owners to work with peace of mind and focus on the project in front of them. Just like enabling mysql remote connection, you’re also able to complete many other actions in just a few clicks, like installing SSL certificates, CDN, DNS management, and so on. All you need to do to access all we have to offer is sign-up, where you will then be able to launch your servers and applications with little hassle, thanks to this straightforward guide!

Share your opinion in the comment section. COMMENT NOW

Share This Article

Launch PHP websites without the worry of Server Management.

Pre-Installed Optimized Stack with Git, Composer & SSH

Shahroze Nawaz

Shahroze is a PHP Community Manager at Cloudways - A Managed PHP Hosting Platform. Besides his work life, he loves movies and travelling. You can email him at shahroze.nawaz@cloudways.com

Get Our Newsletter
Be the first to get the latest updates and tutorials.

Do you like what you read?

Get the Latest Updates

Share Your Feedback

Please insert Content

Thank you for your feedback!