Top 9 Tips to Achieve MySQL and MariaDB Security

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

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

This guide will provide the top 9 tips to secure MySQL and MariaDB databases.

Table Of Contents

• Top 9 Tips to Secure MySQL and MariaDB Databases.
• 1 Secure MySQL/MariaDB Installation
• 2 Change MySQL Default Port and Listening Address
• 3 Disable LOCAL INFILE
• 4 Enable MySQL Logging
• 5 Secure MySQL/MariaDB Connection with SSL/TLS
• 6 Remove the MySQL History File
• 7 Change MySQL Passwords Regularly
• 8 Update MySQL/MariaDB Package Regularly
• 9 Rename MySQL Root User
• Conclusion

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 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. It 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 login 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 sent to and from the database. In addition, it will add a layer of security to your MySQL server and ensure that all transferred data is safe and cannot be sniffed by an attacker.

6 Remove the MySQL History File

When you install MySQL and MariaDB, the MySQL history file .mysql_history is created automatically. This file contains all installation and configuration information and commands. It could be dangerous because it will record all commands, usernames, and passwords you have typed on the shell 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. It will prevent snoopers from 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.

Backup one server, database, or application for free forever.

Get started for free

No credit card required. Cancel anytime!