IT Knowledge Base

Technical Repository

HowTo: Enable Remote Connections to MySQL

Open the the MySQL configuration file:

nano /etc/mysql/my.cnf

and comment out the following line, save the file and then restart the MySQL service.

#bind-address           = 127.0.0.1

Restart the MySQL service.

service mysql restart

OR you can run the following command to comment out the same line and then restart the MySQL service.

sed -i 's/^bind-address.*/#bind-address            = 127.0.0.1/'g /etc/mysql/my.cnf

Restart the MySQL service.

service mysql restart

Then we need to grant the correct permissions in the MySQL database. Log into MySQL and issue the following command.

mysql -u root -p

GRANT ALL PRIVILEGES ON *.* TO 'USERNAME'@'IPADDRESS_OF_REMOTE_MACHINE' IDENTIFIED BY 'PASSWORD';
FLUSH PRIVILEGES;
EXIT;

Example:
GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.1.10' IDENTIFIED BY 'MySecurePassword';
FLUSH PRIVILEGES;
EXIT;

Use the following command to enable remote connections from any machine.

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'MySecurePassword';
FLUSH PRIVILEGES;
EXIT;

Use the following command to enable remote connections from any machine on the 192.168.1.0 subnet.

GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.1.%' IDENTIFIED BY 'MySecurePassword';
FLUSH PRIVILEGES;
EXIT;



Leave a Reply