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

 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