How to Allow Remote Access to Database on Digital Ocean Server?

By Hardik Savani May 1, 2024 Category : Ubuntu

Hello Dev,

This definitive guide, we will show you allow remote access to mysql database ubuntu. let’s discuss about allow remote access to mysql database digitalocean. you can understand a concept of how to enable remote access to mysql server on ubuntu. step by step explain ubuntu mysql server allow remote access. Follow the below tutorial step of how to allow mysql remote access in ubuntu server.

Sometimes, we need to allow remote access to the MySQL database on the ubuntu server. I have a Digital Ocean Ubuntu server and I want to enable remote access to the MySQL server. I followed a few steps to allow remote access to MySQL database digital ocean server.

So, let's follow the below steps:

Step 1: Connect to SSH Server

Here, we will connect to my digital ocean server using the ssh command. right now i am adding my IP address like 111.111.111.23. let's run the below command:

ssh root@111.111.111.23

Step 2: Install MySQL Server

This is an optional step, if you haven't installed MySQL server then you need to run the following commands to install it.

sudo apt-get update && sudo apt-get install mysql-server -y

Step 3: Configure MySQL Server

Here, we will open mysqld.cnf config file and change value of bind-address to 0.0.0.0, so let's run below command and change value.

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

Now change bind-address to 0.0.0.0, so that it can be accessed by any remote server.

bind-address = 0.0.0.0

Step 4: Enable Firewall

Here, we will enable firewall port 3306 with restart mysql server.

sudo ufw enable && sudo ufw allow 3306

sudo systemctl restart mysql

sudo systemctl enable mysql

Step 5: Create MySQL User

Here, we will create mysql user demo with password. follow the below commands:

mysql -u root -p

SELECT User, Host, authentication_string FROM mysql.user;

CREATE USER 'demo'@'%' IDENTIFIED BY 'your-password';

GRANT ALL PRIVILEGES ON *.* TO 'demo'@'%' WITH GRANT OPTION;

FLUSH PRIVILEGES

Step 6: Connect Database Remotely

now, you are ready to connect remotely database. so let's check with following command:

mysql -udemo -p -h 111.111.111.23

You can also import database by following command:

mysql -udemo -p -h 111.111.111.23 name-of-your-database < name-of-your-database.sql

I hope it can help you...

Tags :
Shares