How To Configure Remote Access To MySQL in Linux


Linux tips

Linux tips – Photo by JPPI, 2015, MorgueFile.com

Suppose you installed MySQL server on your Linux box.

Suppose you need to let access to the server from applications outside the host.

In this case you need to configure remote access.

In the file /etc/my.cnf you need to comment skip-network parameter if present and you need to assign bind one as following:

bind=0.0.0.0

but if you have more network cards and you need to reduce access to a single network you need something more specific.

Suppose you need to have the 192.168.0.0/24 netwok enabled access to MySQL server.

Just specify your IP address box in the bind option:

bind=192.168.0.1

How to enable external hosts to access to your MySQL? The default configuration won’t let you do it.

You should create a MySQL user and give it the correct grants.

You can choose if let access any host in that network or if let access just one or more specific hosts.

In less restrictive access you can simply do in this way from your MySQL console:

CREATE USER 'mysqluser'@'%' IDENTIFIED BY 'aC0MPL3XPa33W0RD';
GRANT ALL PRIVILEGES ON *.* TO 'mysqluser'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

You created mysqluser with the password aC0MPL3XPa33W0RD. And you can connect to MySQL server from any host in the network, not only form 192.168.0.0/24.

If you want specify just one IP address to accept incoming connection do in this other way.

Open a MySQL console and add, for example, 192.168.0.12 host IP address, giving the command:

mysql -u root -p

The server will ask you for a password:

MySQL server will ask you a root password:
Enter password:

And then it will starts the console.

In the console give these commands:

CREATE USER 'mysqluser'@'192.168.0.12' IDENTIFIED BY 'aC0MPL3XPa33W0RD';
GRANT ALL ON *.* to mysqluser@'192.168.0.12' WITH GRANT OPTION;
FLUSH PRIVILEGES;
quit

Consider that the mysqluser user will have access to all databases. If you want give access just to one database you need something as could be:

CREATE DATABASE IF NOT EXISTS `mydatabase`;
CREATE USER 'mysqluser'@'%' IDENTIFIED BY 'aC0MPL3XPa33W0RD';
GRANT ALL ON `mydatabase`.* TO 'mysqluser'@'%';
FLUSH PRIVILEGES;
quit

That’s all.

Annunci

Rispondi

Inserisci i tuoi dati qui sotto o clicca su un'icona per effettuare l'accesso:

Logo WordPress.com

Stai commentando usando il tuo account WordPress.com. Chiudi sessione /  Modifica )

Google photo

Stai commentando usando il tuo account Google. Chiudi sessione /  Modifica )

Foto Twitter

Stai commentando usando il tuo account Twitter. Chiudi sessione /  Modifica )

Foto di Facebook

Stai commentando usando il tuo account Facebook. Chiudi sessione /  Modifica )

Connessione a %s...

Questo sito utilizza Akismet per ridurre lo spam. Scopri come vengono elaborati i dati derivati dai commenti.