How to Enable Remote MySQL Connection

Hosting the database(s) and applications on the same web server is not unusual.

However, given the broadening scope of applications, many organizations are moving towards a more distributed environment. A separate database server can improve security and allows you to quickly scale resources. Understanding how to efficiently handle remote resources and connections in the distributed environment has become an essential requirement of many projects.

Imagine you are part of a big team of developers with multiple roles, working on large software products with complex database schema and connections.

You need to work with SQL queries and create a connection remotely from various locations. To enable MySQL remote connection, you need to grant remote MySQL access at your hosting server and whitelist the IPs of the incoming connection points.

Although this is a simple process, it is not very secure. A better approach is adding developers as team members at the level of the hosting solution and then allow them to utilize the MySQL support for remote connections to connect to the database remotely. This creates a secure environment where remote developers can work with a centralized database.

In this article, I’ll demonstrate two methods of connecting to connect a MySQL remote server for efficiently working remotely.

Method 1: Connect to Remote MySQL Database From the Command Line

Method 2: Enable Remote MySQL Connection Using Cloudways Platform

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 SSH terminal and provide your application database name and password by using the following command:

1. 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:

1. 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:

1. 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:

1. 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”.

1. 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:

1. <?php
3. function getdb(){
5. $servername = "46.101.5.233"; // put your cloudways server IP here
6. $username = "qxxfumxxxbd";
7. $password = "xxxxbQxxmM";
8. $db = "qxxfumxxxbd";
10. try {
12. $conn = mysqli_connect($servername, $username, $password, $db);
14. //echo "Connected successfully";
16. }
18. catch(exception $e)
20. {
22. echo "Connection failed: " . $e->getMessage();
24. }
26. return $conn;
28. }

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.

Q: How to Provide Remote Access to an Existing MySQL Database?

A: The following commands will grant remote access to a user to an existing database:

update db set Host=’136.157.33.109′ where Db=’yourDBname’;

update user set Host=’136.157.33.109′ where user=’user1′;

User1 is now able to access yourDBname from a remote location identified by the IP 136.157.33.109

Q: Why is MySQL database server’s remote access disabled by default?

A: The remote access to the MySQL database server is disabled due to security reasons.

Q: How can I Test MySQL Database Remote Connection?

A: Use the command:

mysql -h HOST -u USERNAME -p PASSWORD

Through MySQL shell, don’t forget to run show databases command to ensure if you have the right privileges at the remote machine.

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!