Disable remote access to MySQL database.

Posted on 21st February 2015

Allowing users, especially the root user, to connect remotely to a MySQL database possess many security risks. In most real world scenarios the access to MySQL database is required only by the applications running locally on the server. In such cases you can turn off remote access completely. The users can still connect to the database by connecting to the server first using SSH and then run a command to connect to the database.

This article illustrates how to disable remote access for either root users or for all users

Disable remote access for root user

Remote access to MySQL database is disabled by default for security reasons. However if you have changed this setting, then you can disable remote access to root by connecting to MySQL database and executing the following SQL statement

DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '', '::1');

The above SQL statement deletes all records in mysql.user table where user is root and host is either localhost, (IPv4 loopback address) or ::1 (IPv6 loopback address). After executing this statements make sure you reload the privileges using the statement -


Disable all remote connections

This can be done by simply preventing MySQL from listening for TCP/IP connections. In this case only local clients will be permitted to connect to the MySQL database.

To do this, edit MySQL options file my.ini or my.cnf depending on the platform it is installed.

In the [mysqld] section add the line skip-networking or uncomment this line(remove the leading #) if it already exists.


Post a comment


Anh Nhan | March 10, 2016 11:38 AM |

Regarding disabling networking, current versions of mysql-server prefer to listen to *only* There is no remote access.