Enable remote access to MySQL database

Sometimes you might need to remotely access a MySQL database. As a security measure, by default that is prohibitted, but on your own responsibility we’ll show you how you can make it work.

There are two changes you should make on a clean MySQL installation – first enable the MySQL server to listen on all networks and second to create an user allowed to connect from any client.

To enable network connections change the ”bind-address” line in the /etc/mysql/mysql.conf.d/mysqld.cnf to:

bind-address         = 0.0.0.0

You can use this one liner to reaplace it aswell:

sed -i “s/bind-address.*/bind-address = 0.0.0.0/g” /etc/mysql/mysql.conf.d/mysqld.cnf

 

And restart the MySQL service:

service mysql restart

 

 

Second you need to create a user allowed to connect remotely. Most often users are being created with allowance to connect only locally from the DB server host.

To create a user with remote access:

CREATE USER ‘root’@’%’ IDENTIFIED BY ‘your_password’;

GRANT ALL ON *.* TO ‘root’@’%’;

FLUSH PRIVILEGES;

 

With that you are now able to connect remotely to your database server.

Keep in mind thath now your setup is very unsecure, so you should spen some time to either firewall it or limit the remote connection more strictly only to specific remote clients.

Leave a Reply

Your email address will not be published. Required fields are marked *