How to enable remote access on MySQL server

1. Login to your machine where MySQL is installed.


2. Go to directory "/etc/mysql/" and list files inside it.



3. Go to directory "mysql.conf.d" and list files inside it.




4. Edit the file "mysqld.cnf" with nano editor using below command

sudo nano mysqld.cnf





5. Change the value of "bind-address" parameter to "0.0.0.0" and save the file using keys "Ctrl+o", "Enter" and "Ctrl+x"

 


6. Restart "mysql" service using below command

sudo service mysql restart


 

7. Login to mysql and change database to "mysql" using command "USE mysql;"



8. Execute below commands.

CREATE USER 'root'@'ip-address-of-your-mysql-database-server' IDENTIFIED BY 'password-of-your-mysql-database-server'; 


CREATE USER 'root'@'%' IDENTIFIED BY 'password-of-your-mysql-database-server';



GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password-of-your-mysql-database-server' WITH GRANT OPTION;


GRANT SELECT,INSERT,DELETE,UPDATE ON *.* TO 'root'@'%' IDENTIFIED BY 'password-of-your-mysql-database-server' WITH GRANT OPTION;

FLUSH PRIVILEGES;

exit; 



9. Verify using below command

SELECT user, host FROM user;

 


10. Restart "mysql" service using below command

sudo service mysql restart