
Advanced MySQL security tips (a complete guide)
Guard Duty
MySQL security configurations include a range of topics, along with their possible effects on MySQL servers and corresponding applications. In this article, I look at MySQL security encryption services, account-associated authorization systems, and other required security precautions to ensure protection against misuse and attacks. This security guide will help you protect sensitive data, even if the MySQL service is compromised at some point.
Most of the advanced MySQL security configurations require changes to the server's main configuration file my.cnf
. This file is generally located inside the /etc/mysql
directory or in the /opt/lampp/etc/
folder for LAMPP installations. However, you can locate the file with the find
or locate
command in Linux.
Encryption at Rest
By default, MySQL stores plaintext data inside InnoDB tables. Any non-admin user with access to these files can read tables and pose a security threat. Data at rest or in transit both require protection to avoid potential risk. MySQL transparent data encryption (TDE) enables encryption at rest to prevent information threats and privacy breaches, even if your system is compromised at some level.
MySQL provides TDE by enabling encryption at rest for physical files in the database. It encrypts data automatically on the go before writing to the storage and decrypts before reading. In this section, I will show you how to configure encryption at rest to ensure protection from physical data theft.
Getting Started
MySQL InnoDB tablespaces are stored in .ibd
file format and are generally located in the /var/lib/mysql
directory. You can also find them with the locate
command:
$ locate ibd | less /var/lib/mysql/testDB/testTB.ibd /var/lib/mysql/sys/sys_config.ibd ...
Once located, you can retrieve sensitive plaintext data with the cat
or strings
command:
$ cat /var/lib/mysql/testDB/testTB.ibd | head -n 20
The command output will generate plaintext information containing raw data stored in the corresponding database table.
Key Management
The centralized key management solution offers electronic code book (ECB) and cipher block chaining (CBC) block encryption for tablespace keys and data encryption in the MySQL server. Encryption at rest for the InnoDB search engine and tablespaces involve a two-tier key architecture that implements easy key management and master key rotation:
- Tablespace key: An encrypted key stored in the tablespace header.
- Master key: A key that decrypts the tablespace key.
MySQL implements InnoDB tablespace encryption with the use of tablespace keys. After tablespace encryption, the master key encrypts the tablespace key to place it inside the tablespace header.
When an authenticated user accesses the encrypted table, InnoDB uses the master key to decrypt the tablespace key. The decrypted tablespace key allows you to perform read/write operations on data.
Master Encryption Key Rotation
A decrypted tablespace key never changes; instead, you can only change the master key by key rotation, which is an instance-level operation that re-encrypts all the tablespace keys and saves them back to the tablespace header:
mysql> alter instance rotate InnoDB master key;
However, the process does not re-encrypt or decrypt the tablespace data.
MySQL Keyring Plugin
Data-at-rest encryption in MySQL supports keyring plugins that enable internal server components to retrieve sensitive content. Here, I use a keyring_file
plugin to store the keyring data inside local files in the server host. Configuration requires system variables in the mysql.cnf
file, which is located inside the /etc/mysql/conf.d
directory:
[mysqld] early-plugin-load=keyring_file.so keyring_file_data=/var/lib/mysql/keyring-data/keyring
The system variable keyring_file_data
defines the keyring_file
data location for data storage.
File-per-Table Tablespace Encryption
InnoDB data-at-rest encryption allows file-per-table tablespace encryption by providing the innodb_file_per_table
system variable in the mysql.cnf
file. Once enabled, you can provide encryption at rest for tables created in file-per-table tablespaces,
[mysqld] innodb_file_per_table=ON
and restart the mysql
service:
$ sudo service mysql restart
You can enable encryption for a new file-per-table tablespace by specifying an encryption='y'
clause along with the create table
statement:
mysql> create table testTB (c1 INT) encryption='y'; mysql> alter table testTB encryption='y'; Query OK, 1 row affected (0.33 sec) Records: 1 Duplicates: 0 Warnings: 0
The alter table
statement shows how the system variable also enables encryption for existing InnoDB file-per-table tablespaces.
Encryption-at-Rest Verification
Next, you should query the information_schema.tables column to identify newly created or altered encrypted file_per_table tablespaces; similarly, verify that the plugin status is set to ACTIVE to determine successful plugin configuration (Listing 1).
Listing 1: Encryption-at-Rest Verification
mysql> select table_schema, table_name, create_options from information_schema.tables where create_options like '%encryption%'; +--------------+------------+----------------+ | TABLE_SCHEMA | TABLE_NAME | CREATE_OPTIONS | +--------------+------------+----------------+ | test | testTB | ENCRYPTION="Y" | +--------------+------------+----------------+ ** mysql> select plugin_name, plugin_status from information_schema.plugins where plugin_name like 'keyring%'; +--------------+---------------+ | plugin_name | plugin_status | +--------------+---------------+ | keyring_file | ACTIVE | +--------------+---------------+ 1 row in set (0.00 sec)
Finally, confirm data encryption at rest with the strings
command to view the encrypted output:
$ strings /var/lib/mysql/testDB/testTB.ibd | head -n 20
In this section, you learned how to configure MySQL to provide encryption at rest to ensure physical data protection. Next up, I show you how to set up the encryption-in-transit configuration for a MySQL client and server to secure data on the network.
Encryption in Transit
Data in transit or in motion is exposed to potential risk if sniffed or intercepted by a man in the middle (MITM) attack. Encryption provides effective measures against MITM to secure unprotected, in-transit data. MySQL and MariaDB databases provide encrypted communication between client and server with the SSL/TLS protocol. In this section, I walk you through the MySQL configuration of SSL to ensure secure communication between the client and server.
MySQL version 5.7.28 and above provides the handy mysql_ssl_rsa_setup
tool that automatically creates required files to set up the default encrypted communication. To begin, you should check either the default SSL status connection on the MySQL server instance or the value of the Ssl_cipher
variable for the current session (Listing 2). The output indicates an unencrypted connection.
Listing 2: SSL Status
mysql> show global variables like '%ssl%'; +---------------+-----------------+ | Variable_name | Value | +---------------+-----------------+ | have_openssl | DISABLED | | have_ssl | DISABLED | | ssl_ca | | | ssl_capath | | | ssl_cert | | | ssl_cipher | | | ssl_crl | | | ssl_crlpath | | | ssl_key | | +---------------+-----------------+ 9 rows in set (0.53 sec) ** mysql> show session status like 'Ssl_cipher'; +---------------+-----------------+ | Variable_name | Value | +---------------+-----------------+ | Ssl_cipher | | +---------------+-----------------+ 1 row in set (0.50 sec)
Intercepting the Plaintext Table
To create a client that can use its credentials to log in remotely to the MySQL server and access tables, enter:
$ mysql -u user -p -h <SSLsrvr_IPaddress>
In the meantime, initiate a tshark
session at your server or client to sniff the plaintext data:
$ tshark -i any > mysql_plaintext.pcap
Hitting Ctrl+C stops the capture process and opens the mysql_plaintext.pcap
file in Wireshark to retrieve the plaintext (Figure 1).

Configuring the MySQL Server
To enable SSL support, you need to create the required SSL files and keys manually. MySQL requires private keys and X509 certificates signed by a certificate authority (CA) to ensure secure encrypted communication. Similarly, the MySQL server needs private keys and X509 certificates from every client that wants to connect to the server over SSL. Table 1 lists the files you need to prepare manually.
Tabelle 1: Keys and Certificates
File |
Function |
---|---|
|
The private key used to generate an X509 certificate for the certificate authority. |
|
The X509 certificate containing certificate details and public key. |
|
The server certificate signing request (CSR). |
|
The private server key. |
|
A self-signed X509 certificate that contains server certificate metadata and the public key. |
|
The client CSR. |
|
The client private key |
|
A self-signed X509 client certificate. |
Creating SSL Files with OpenSSL
The OpenSSL command-line tool will help you prepare and generate the required SSL files. This handy tool uses the OpenSSL library to perform various tasks, like generating X509 request certificates, providing private keys, verifying and signing X509 certificates, and so on.
Before generating SSL files, create a directory in which to place keys and certificates for encryption in transit:
$ mkdir /var/lib/mysql/new_certs && cd /var/lib/mysql/new_certs
Next, generate the RSA 2048-bit private key to create the CA X509 certificate that signs the server and client X509 certificates:
$ openssl genrsa 2048 > ca-key.pem $ openssl req -new -x509 -nodes -days 3500 -key ca-key.pem -out ca.pem
The openssl
command generates the server's private key and certificate signing request. Once obtained, you need to remove the passphrase and sign server-req.pem
with the CA key and certificate to obtain the final X509 certificate for the server:
$ openssl genrsa 2048 > server-key.pem $ openssl req -new -key server-key.pem -out server-req.pem $ openssl x509 -req -in server-req.pem -days 3600 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem
MySQL configuration for SSL only requires server-key.pem
, server-cert.pem
, and the CA certificate.
Similarly, you need to generate the private key (cert-key.pem
) and a self-signed X509 certificate for the MySQL client:
$ openssl genrsa 2048 > client-key.pem $ openssl req -new -key client-key.pem -out client-req.pem $ openssl x509 -req -in client-req.pem -days 3600 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem
The openssl verify
command lets you verify that OpenSSL has generated the correct certificates:
$ openssl verify -CAfile ca.pem server-cert.pem client-cert.pem server-cert.pem: OK client-cert.pem: OK
The OK value indicates that the X509 certificate was signed correctly.
Setting System Variables in MySQL Server
To enable MySQL for encrypted communication, MySQL requires the ssl_ca
, ssl_cert
, and ssl_key
system variables, which specify the paths to SSL files that permit clients to connect through an encrypted connection.
Next, you need to edit the mysqld.cnf
configuration file located in the /etc/mysql/mysql.conf.d
directory for the new certificates and enable mandatory encrypted connection requirements for the client with the require_secure_transport
[1] system variable:
$ vim /etc/mysql/mysql.conf.d/mysqld.cnf [mysqld] ssl_ca= /var/lib/mysql/new_certs/ca.pem ssl_cert=/var/lib/mysql/new_certs/ server-cert.pem ssl_key=/var/lib/mysql/new_certs/server-key.pem require_secure_transport=ON
Now, change the ownership and permissions to SSL files and restart the database service:
$ chown -R mysql:mysql /var/lib/mysql/new_certs/ $ chmod 600 client-key.pem server-key.pem ca-key.pem $ sudo service mysql restart
Once backed up, log in to the server and check the SSL status by typing \s
or by checking the value of the have_ssl
variable (Listing 3).
Listing 3: Checking have_ssl
mysql> show global variables like '%ssl%'; +---------------+--------------------------------------+ | Variable_name | Value | +---------------+--------------------------------------+ | have_openssl | YES | | have_ssl | YES | | ssl_ca | \etc\mysql\new_certs\ca.pem | | ssl_capath | | | ssl_cert | \etc\mysql\new_certs\server-cert.pem | | ssl_cipher | | | ssl_crl | | | ssl_crlpath | | | ssl_key | \etc\mysql\new_certs\server-key.pem | +---------------+--------------------------------------+ 9 rows in set (0.53 sec)
The configuration ensures an SSL connection and disables MySQL client access to the server with the --ssl-mode=DISABLED
string. The client will receive the error
$ mysql -u user -p -h <MySQL_IPaddress> ERROR 1045 (28000): Access denied for user 'user'@'%' (using password: YES)
if access to the server is attempted.
Client-Side Encryption in Transit
To secure this connection further, copy the CA and client files to the client and modify the user to require a trusted certificate. To begin, create a directory in which to save client files and use scp
or some other utility to transfer ca.pem
, client-key.pem
, and client-cert.pem
files to the client machine:
$ mkdir ~/certs $ scp user@[IP_Address]:/var/lib/mysql/new_certs/ca-cert.pem ~/certs/ $ scp user@[IP_Address]:/var/lib/mysql/new_certs/client-cert.pem ~/certs/ $ scp user@[IP_Address]:/var/lib/mysql/new_certs/client-key.pem ~/certs/
If the server is not configured with the require_secure_transport
system variable and the user account is created with no REQUIRE
clause or the account has no specific encryption requirements, as above, connection attempts fall to an unencrypted connection. To alter the user to add the REQUIRE X509
clause, enter:
mysql> alter user 'user'@'client_ip' require X509; mysql> flush privileges;
From now onward, every remote connection from client user will require that -ssl-key
and -ssl-cert
options be specified, whereas adding the --ssl-ca
variable is optional. These variables contain paths to the client's SSL files under the ~/certs
directory:
$ mysql -u user -p -h <SSLServer_IPAddress>-ssl-ca= ~/certs/ca.pem -ssl-cert=~/certs/client-cert.pem -ssl-key=under ~/certs/client-key.pem
After hitting Enter, the client will establish a secure SSL connection. In the meantime, start the tshark
sniffer to confirm encryption in transit. You will observe an encrypted communication.
Low-Privilege Users
MySQL offers account management statements to set up user accounts and control associated account privileges. The authorization system grants privileges that differ in context and are applied at varying levels of operations. However, it's a good practice not to assign unnecessary privileges to the account users and exercise caution by enabling limited resource access. In this section, I discuss security precautions for providing only enough access required for the job.
Least privileged user accounts reduce the risk of an attacker's access to critical systems. MySQL assigns administrative, database, and specific database object-relevant privileges to users.
The most common recommended privileges are SELECT
, UPDATE
, DELETE
, and INSERT
. However, if a user only needs to add information to the database, only the INSERT
privilege is required to add records into it. To assign insert permission to the user, enter:
mysql> grant insert on database.* to 'user'@'localhost';
Similarly, the lowest level privilege for access only allows the user to read, edit, or delete a column. For column-level reading privileges, enter:
mysql> grant select(coulmn_name) on database.Clients to 'user'@'localhost'; mysql> flush privileges;
Find out more about the MySQL account authorization system from the official documentation [2].
Privilege Guidelines
MySQL offers certain privilege statements, which, if assigned unnecessarily, can potentially risk subverting the privilege system or reading and writing files accessed by the server host. The following are some potentially risky user privileges that can significantly affect server security:
-
GRANT OPTION
: Revokes or grants certain privileges from other users that the user itself poses. However, users can use theWITH
clause to combine their assigned privileges. -
ALTER
: Allows non-administrative users to undermine the authorization system by renaming tables. -
SHUTDOWN
: Allows the use ofmysqladmin shutdown
to terminate the server and restrict server services to the users. -
SUPER
: Controls server behaviors and operations and lets a client kill other account threads and modify the server configuration. -
PROCESS
: Lets the user see process threads of other account users in plaintext and gives access to InnoDBINFORMATION_SCHEMA
FILES
tables. -
FILES
: Allows user to read, write, and create files on the server host and is a global privilege that allows writing to the server data directory files that implement privilege tables.
Most importantly, grants for MySQL systems that allow access to the authentication_string column of the mysql.user
table can enable changing the password and connecting to the server through that account.
You can find more privilege-granting guidelines for specific clauses from the official MySQL documentation [3].
Setting Resource Limits
Another way to set low-privilege user accounts that enhances MySQL security is to set up resource limits. MySQL offers a global system variable max_user_connections
that allows setting a limit of simultaneous connections by given accounts. However, it does not place any limits on what happens once the user connects. Hence, MySQL offers an individual account management system by setting per-hour resource limits in the mysql.user
table for:
-
max_queries_per_hour
to store queries made by the user in the max_questions column, -
max_updates_per_hour
to store updates issued by an account in the max_updates column, and -
max_connections_per_hour
to store number of user connects with the server in the max_connections column.
To establish these limits at account creation time, use create user
or check assigned resources for an already established account with select user
(Listing 4, which indicates that no limits have been set on resource access) before altering existing account limits with alter user
and setting a limit for per-hour queries generated by the user:
mysql> create user 'user'@'localhost' identified by 'password' - > with max_queries_per_hour 15 - > max_updates_per_hour 12 - > max_connections_per_hour 4 - > max_user_connections 3; mysql> alter user 'user'@'localhost' with max_queries_per_hour 25;
Listing 4: Checking Assigned Resources
mysql> select user, max_questions, max_updates, max_connections, max_user_connections from mysql.user where user='user_name'; +-----------+---------------+-------------+-----------------+---------------------+ | User | max_questions | max_updates | max_connections | max_user_connections| +-----------+---------------+-------------+-----------------+---------------------+ | user_name | 0 | 0 | 0 | 0 | +-----------+---------------+-------------+-----------------+---------------------+ 1 row in set (0.001 sec)
Now, recheck the mysql.user
table to confirm the settings.
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.
load_file
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)
outfile
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 Hello2
However, the following error is received by non-privileged users:
ERROR 1045 (28000): Access denied for user 'user'@'localhost' (using password: YES)
dumpfile
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 user=mysql $ 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', '127.0.0.1', '::1'); mysql> flush privileges;
Similarly, disallow all remote logins if not required by adding a skip-networking
system variable to MySQL configuration files:
[mysqld] port=XXXX skip-networking 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 198.168.100.example.com. 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'@'192.168.100.0/255.255.255.0';
This host value enables the user to connect from any IP address within a user_ip
ranging from 192.168.100.0 to 192.168.100.255, 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 port=XXXX #restart service $ sudo service mysql restart
Conclusion
In this article, I discussed advanced security tips for MySQL server protection. You can refer to the official MySQL documentation [4] for more information.