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', '127.0.0.1', '::1');
The above SQL statement deletes all records in mysql.user table where user is root and host is either localhost, 127.0.0.1 (IPv4 loopback address) or ::1 (IPv6 loopback address). After executing this statements make sure you reload the privileges using the statement -
FLUSH PRIVILEGES;
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.
Example:
[mysqld] port=3306 sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" skip-networking
