Monday, July 11, 2016

How to allow MySQL remote connections

If you have a MySQL database running on your server, and you want it to accept remote connections, you have to follow these steps:

1) Allow MySQL to listen to all interfaces (default is the loopback interface only):

sudo vim /etc/mysql/my.cnf
You replace this statement:
bind-address            = 127.0.0.1
by this one:
bind-address            = 0.0.0.0

2) Create a new database use with all privileges granted (you shouldn't allow remote connections with the root user):

mysql -u root -p -h 0.0.0.0
mysql> CREATE USER 'yourusername'@'localhost' IDENTIFIED BY 'yourpassword';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'yourusername'@'localhost' WITH GRANT OPTION;
mysql> CREATE USER 'yourusername'@'%' IDENTIFIED BY 'yourpassword';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'yourusername'@'%' WITH GRANT OPTION;

3) Test the connection from a different network (make sure you don't have any proxy limitations):

mysql -u yourusername -p -h your.server.address

No comments:

Post a Comment