You can buy managed database from Digital Ocean that have ready to serve database. On this you can easily add or remove user granting them remote access or access to specific server. However, it is costly. The cheapest on as of this writing cost 15 dollars per month. You can buy server with a same capacity for just 5 dollar and install the
mysql server and configure remote access.
So on this tutorial, we will show you how you can set up database and grant remote access to all or specific server from scratch.
$ ssh [email protected]
$ sudo passwd root
$ sudo apt-get update && sudo apt-get install mysql-server -y $ sudo mysql_secure_installation utility
$ sudo vim /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.0Note: If you want to provide access to specific server, then add the ip(s) of the server(s) instead of 0.0.0.0.
$ sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf //Add or edit sql_mode to empty, as below sql_mode =
3306port on firewall and restart:
$ sudo ufw enable && sudo ufw allow 3306 $ sudo systemctl restart mysql
$ sudo systemctl enable mysql
mysqlon the same server by running following command:
$ mysql -uroot -p
SELECT User, Host, authentication_string FROM mysql.user;
CREATE USER 'sam'@'%' IDENTIFIED BY 'your-password'; [ NOTE: '%' means db can be accessed by anyone (droplet, server, users) For more security, one or more ip address can be added to restrict access to database to certain IP (server) For e.g. CREATE USER 'sam'@'220.127.116.11' IDENTIFIED BY 'your-password'; ]
GRANT ALL PRIVILEGES ON *.* TO 'sam'@'%' WITH GRANT OPTION; [ NOTE: '%' means db can be accessed by anyone (droplet, server, users) For more security, one or more ip address can be added to restrict access to database to certain IP (server) For e.g. GRANT ALL PRIVILEGES ON *.* TO 'sambhu'@’18.104.22.168' WITH GRANT OPTION; ]
mysql -usam -p -h 22.214.171.124
mysql -usam -p -h 126.96.36.199 name-of-your-database < name-of-your-database.sql // With Progress viewer pv name-of-your-database.sql | mysql -usam -p -h 188.8.131.52 name-of-your-databaseSource: