Last updated on August 28th, 2020 at 05:15 pm

In this post, I will explain to you how we can allow remote access to our MySQL server using simple steps. To perform these steps you must have a server with root access so can easily modify MySQL server setting.

Connect to a MySQL database remotely

In these steps, I can explain to you how we can allow any IP to connect to your MySQL server remotely.

Step 1. Login to your server  root using Putty or Terminal.

Step 2. Now you need to run below command to modify your mysqld.cnf.

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Step 3. When you run above command mysqld.cnf the file will open where you can find bind-address as shown in the below image.

Connect to a MySQL database remotely

By default, this value is set to 127.0.0.1, mean that the server will only look for the local connection request. You can change this to specific IP for connection which you want to allow for the remote connection. We can set this to 0.0.0.0 allow for any remote connection.

After you make a change to bind-address you can press ctrl+o after that ctrl+x.

Step 4. You also need to allow 3306 port for the connection requests.

sudo ufw allow 3306

Step 5. Restart the MySQL server.

sudo systemctl restart mysql

Allow Specific IP to connect MySQL server Remotely

If you want a particular IP to access your MySQL server from remote, you can grant that IP exclusive permission to connect to the database remotely with the following command.

sudo ufw allow from REMOTE_IP_ADDRESS to any port 3306

Once this command runs you can restart the server

sudo systemctl restart mysql

Create MySQL User with remote access permission

Now in the above step, we can allow our MySQL server to allow connection from the remote machine but to connect and perform an operation in the database we need users also have permission to access your database from remote. Now we can create MySQL user who has permission to access the database from remote.

The easiest and simple way to do that using PHPMyAdmin. Login to your PHPAdmin and go to your database which you want to allow remote access after that click on privileges at top of the menu bar and after that click on Add user account

Allow MySQL to connect remotely using PHP

Now when you click on Add user account you need to select % for the host so any host can connect to this database.

Once you fill all the required detail create this user.

PHP Code to connect to remote MySQL Server

Here is the simple PHP code to connect to your MySQL server.

<?php 
try{ 
   $conn = new PDO('mysql:host=YOUR_IP_ADDREDD;dbname=database;port=3306','username','password'); 
   echo "Connection successfully";
 } catch (PDOException $e) {
    print "Error!: " . $e-><span data-mce-type="bookmark" style="display: inline-block; width: 0px; overflow: hidden; line-height: 0;" class="mce_SELRES_end"></span>getMessage() . "<br?-->";
    die();
}
?>

Hope this post helps you to connect your MySQL remotely. If this post helps you don’t forget to subscribe to my youtube channel (Subscribe from above link) and share this post.