How to Enable Remote MySQL Connection

On the 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.

How to Enable Remote MySQL Connection Using Command Line

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

Open the Cloudways SSH terminal and provide your application database name and password by using the 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;

enable MySQL remote access

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';

TIP: Revoke Remote Access to MySQL Database

If you have accidentally granted access to a user, you can revoke access very easily.

The following command will revoke all access options for USERNAME from all machines:

mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM ‘USERNAME’@’%’;

Following will revoke all options for USERNAME from a particular IP:

mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM ‘USERNAME’@’1.2.3.4’;

It’s better to check information_schema.user_privileges table after running the REVOKE command to confirm that the revoke action has updated the access rights.

How to Enable Remote MySQL Connection Using Cloudways Platform

• Step 1: Add a developer as a team member
• Step 2: Whitelist IPs
• Step 3: Apply remote access configuration
• Step 4: 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 the 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.

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 the 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.

You will now see your database is connected to SQLyog.

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!