SnapShooter Learning Center

Top 9 Tips to Achieve MySQL and MariaDB Security

Learn how to secure your MySQl or MariaDB from common issues

Simon Bennett]
Simon Bennett
Last Updated: Feb 28, 2021
Table of Contents

MySQL is a free, open-source and one of the most popular database system around the world. While MariaDB is a fork of MySQL and one of the fastest-growing open-source database system. Database security is a top priority of any system administrator. It is critical for today's digital businesses.

MySQL and MariaDB default installation is not secured. So you will need to tweak some configuration to secure MySQL and MariaDB from hackers.

In this guide, we will provide the top 9 tips to secure MySQL and MariaDB databases.


# 1 Secure MySQL/MariaDB Installation

By default, MySQL/MariaDB installation is not secure. By default, the test database and the anonymous user is available in the MySQL/MariaDB and can be accessed by all users. It is also recommended to disable the MySQL root user account from outside access.

You can run the mysql_secure_installation script to secure all the things:

mysql_secure_installation

You will be asked to set a root password, remove the test database and anonymous user and disable root login remotely as shown below:

Enter current password for root (enter for none): Provide your root user password
Switch to unix_socket authentication [Y/n] n
Change the root password? [Y/n] Y
New password: 
Re-enter new password: 
Remove anonymous users? [Y/n] Y
Disallow root login remotely? [Y/n] Y
Remove test database and access to it? [Y/n] Y
Reload privilege tables now? [Y/n] Y

# 2 Change MySQL Default Port and Listening Address

By default, MySQL and MariaDB listen on port 3306 on the loopback address. It is a good idea to change the MySQL default port for security purposes.

For MySQL you can change the MySQL default port and listening address by editing the file:

nano /etc/mysql/mysql.conf.d/mysqld.cnf

Change the following line:

bind-address = 127.0.0.1
Port = 9090

For MariaDB you can change the MariaDB default port and listening address by editing the file:

nano /etc/mysql/mariadb.conf.d/50-server.cnf

Change the following line:

bind-address = 127.0.0.1
Port = 9090

Save and close the file then restart the MySQL/MariaDB service with the following command:

systemctl restart mysql
systemctl restart mariadb

# 3 Disable LOCAL INFILE

It is also recommended to disable local_infile in MySQL. This will deny a client to load data from a local file to a remote MySQL server. You can disable it by editing the MySQL/MariaDB default configuration file:

nano /etc/mysql/mysql.conf.d/mysqld.cnf

Or

nano /etc/mysql/mariadb.conf.d/50-server.cnf

Change the following line:

local-infile=0

Save and close the file then restart MySQL/MariaDB service to apply the changes.


# 4 Enable MySQL Logging

By default, logging is disabled in both MySQL and MariaDB servers. You should enable it to find the potential cause of the issue and understand what happens on a server.

You can enable the loggin by editing the MySQL and MariaDB default configuration file:

nano /etc/mysql/mysql.conf.d/mysqld.cnf

Or

nano /etc/mysql/mariadb.conf.d/50-server.cnf

change the following line:

log_error = /var/log/mysql/error.log

Save and close the file then restart MySQL/MariaDB service to apply the changes.


# 5 Secure MySQL/MariaDB Connection with SSL/TLS

It is also recommended to secure your MySQL/MariaDB connection with SSL/TLS. Enabling SSL/TLS will encrypt the data being sent to and from the database. This will add an additional layer of security to your MySQL server and ensure that all data that is transferred is safe and cannot be sniffed by an attacker.


# 6 Remove the MySQL History File

When you install the MySQL and MariaDB, the MySQL history file .mysql_history is created automatically. This file contains all installation and configuration information and commands. This could be dangerous because all commands, usernames, passwords that you have typed on the shell will be recorded in the history file.

So it is a good idea to remove this file from your system.

rm -rf ~/.mysql_history

# 7 Change MySQL Passwords Regularly

It is also a good habit to change the MySQL passwords regularly. This will prevent any snoopers who are tracking your activity for a long time.

To change the MySQL user password, connect to the MySQL shell with the following command:

mysql -u root -p

Once connected, change the database to MySQL with the following command:

USE mysql;

Next, set the new root password with the following command:

ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'yourpassword';

Next, flush the privileges and exit from the MySQL shell with the following command:

FLUSH PRIVILEGES;
EXIT;

# 8 Update MySQL/MariaDB Package Regularly

You should also update and upgrade your MySQL/MariaDB package regularly to keep your server with security updates and bug fixes.

You can update the MySQL/MariaDB package with the following command:

apt-get upgrade mysql-server -y
apt-get upgrade mariadb-server -y

Once the package is updated, restart the MySQL/MariaDB service to apply the changes:

systemctl restart mysql
systemctl restart mariadb

# 9 Rename MySQL Root User

It is also recommended to rename the MySQL root username to prevent a direct attack on the root user.

First, connect to MySQL with the following command:

mysql -u root -p

Once login, rename the root user to admin with the following command:

rename user 'root'@'localhost' to 'admin'@'localhost';

Now, verify the changes with the following command:

select user,host from mysql.user;

Output:

+------------------+-----------+
| user             | host      |
+------------------+-----------+
| admin            | localhost |
| debian-sys-maint | localhost |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
+------------------+-----------+

Next, flush the privileges and exit from the MySQL shell with the following command:

FLUSH PRIVILEGES;
EXIT;

You can now log in with newly created user as shown below:

mysql -u admin -p

# Conclusion

In the above post, you learned how to secure MySQL and MariaDB database server. I hope this will help you to secure your database server.


Did you find this article helpful?