How can I take remote access to MySQL database on Ubuntu machine??
MySQL database is an open-source database. Let us suppose, MySQL database server is at Ubuntu Server and we want to take its remote from our local Linux server. So here we need to allow remote access of MySQL database on the ubuntu server. In this article, we are using MySQL 5.7 version.
Read More: Install MySQL server on Ubuntu
Login to MySQL database server with root user or sudo user.
By default, MySQL only allows localhost (127.0.0.1) to connect with the database server. So we will edit the file
/etc/mysql/mysql.conf.d/mysqld.cnf and change the 127.0.0.1 to 0.0.0.0 (any host).
# sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf
After changing the bind-address to 0.0.0.0, save the file and restart the MySQL service.
# sudo service mysql restart
Next step is to create the MySQL database user to login remotely. For example, you are managing the database name ‘testing’ & user is ‘remoteuser’. You can create the same username for a different host. Here we will create user name ‘remoteuser’ for host 192.168.150.132 from which we will connect the database remotely.
Go to MySQL server, login to MySQL and run the following command:
# CREATE USER 'remoteuser'@'192.168.150.132' IDENTIFIED BY 'Arunchandel@123';
GRANT ALL PRIVILEGES ON testing. * TO 'remoteuser'@'192.168.150.132';
# flush privileges;
Where ‘testing’ = Database name
flush privileges = Reload the changes done
The final step is to take the remote access of MySQL database.
# mysql -u remoteuser -h 192.168.150.134 -p
where 192.168.150.134 = MySQL database server IP
Congratulations !!! You learned how to allow remote access to MySQL database on ubuntu server. For any feedback, write in the comment box.