Advanced MySQL security tips (a complete guide)

Guard Duty

Disabling Dangerous Functions

Another mandatory security measure to avoid local file SQL injection is to disable functions offered through FILE privilege to low-level users. This grant enables users with global commands like load_file, outfile, and dumpfile to read or make changes to the filesystem accessed through the server.

However, if an attacker does get access to the database through an application layer vulnerability (e.g., SQL injection), disabling the function will prevent the attacker read/write privileges on local files on the system.


The load_file function lets a user load all the data from a file accessed through the server. For instance, a user with the FILE privilege can load all the file content with the command,

mysql> select load_file('/etc/passwd');

whereas a user without a FILE grant will receive the output:

mysql> select load_file('/etc/passwd');
| load_file('/etc/passwd') |
| NULL                     |
1 row in set (0.000 sec)


The outfile function allows the user to overwrite all the files accessed through the server:

mysql> select 'Hello2' into outfile '/tmp/hello.txt';
$ cat /tmp/hello.txt

However, the following error is received by non-privileged users:

ERROR 1045 (28000): Access denied for user 'user'@'localhost' (using password: YES)


The dumpfile function is a select clause that writes to the file without separators in an unformatted row. However, the resulting output does not return to the client.

$ cat /tmp/world
Hello world!
mysql> select 'Hello world!' into dumpfile '/tmp/world';
Query OK, 1 row affected (0.001 sec)

To read/write to the system files, disable these functions by revoking the client FILE privileges:

mysql> revoke FILE on *.* from 'user'@'localhost';

Once revoked, the function's command will generate an error or NULL output.

No Root Privileges

MySQL must never be run as a root user. This precaution isn't related to the MySQL root user. Running MySQL as a root user enables any account with the FILE privilege to modify and create server files as root.

Accessing MySQL as a root user generally returns an error. However, this restriction can be overridden by starting MySQL with the -user=root option. The ideal practice is to access MySQL as a separate Unix user by editing the MySQL configuration file:

$ vim /etc/mysql/my.cnf
$ sudo service mysql restart

Disable Remote Login, Particularly Root (Optional)

Remote root logins can expose MySQL databases to high risk. To disable remote root access, sign in to the server and run the commands:

mysql> delete from mysql.user where user='root' and host not in ('localhost', '', '::1');
mysql> flush privileges;

Similarly, disallow all remote logins if not required by adding a skip-networking system variable to MySQL configuration files:

sudo service mysql restart

Enable Explicit Deny (Optional)

MySQL identifies any client connection by username, host value, and password. A host value allows the wildcard character % in the hostname or IP address, such that hostname % can enable connection from any client with a similar username over the Internet.

Similarly, the IP wildcard value 192.168.100.% allows connection from anyone on the subnet that can be easily exploited by naming the host . Hence, it is recommended to specify the host value as an IP address with a netmask that identifies bits to use for the network address:

mysql> create user 'user'@'';

This host value enables the user to connect from any IP address within a user_ip ranging from to, such that the following condition holds:

user_ip && netmask=host_ip

Change Default Port (Optional)

By default, the MySQL service runs on TCP port 3306. To check that port in the system, enter:

$ netstat -tanp | grep 3306

Attackers and IoT search engines normally scan the default port ranges and index them in their database. As a security precaution, open the MySQL configuration file located in the /etc/mysql folder to change the default port:

$ vim /etc/mysql/mysql.conf.d/mysql.cnf
#change port
#restart service
$ sudo service mysql restart


In this article, I discussed advanced security tips for MySQL server protection. You can refer to the official MySQL documentation [4] for more information.

Buy this article as PDF

Express-Checkout as PDF
Price $2.95
(incl. VAT)

Buy ADMIN Magazine

Get it on Google Play

US / Canada

Get it on Google Play

UK / Australia

Related content

comments powered by Disqus
Subscribe to our ADMIN Newsletters
Subscribe to our Linux Newsletters
Find Linux and Open Source Jobs

Support Our Work

ADMIN content is made possible with support from readers like you. Please consider contributing when you've found an article to be beneficial.

Learn More”>


		<div class=