11. Install the MariaDB Database Management System

by Cover Tower - Updated October 18, 2021

Install MariaDB from the Debian repositories:

apt-get install mariadb-server

By default, MariaDB is not hardened. You can secure MariaDB using the mysql_secure_installation script. You should follow the next steps carefully to set up the root password for MariaDB, remove anonymous users, disallow remote root login, and remove the test database and access. Therefore run:

mysql_secure_installation

Set a root password for MariaDB, and enter it again when you will be asked Change the root password? as shown below:

Enter current password for root (enter for none): strongpassword

Setting the root password ensures that nobody can log into the MariaDB

root user without the proper authorisation.

You already have a root password set, so you can safely answer ‘n’.

Change the root password? [Y/n] y

Enter new password: strongpassword

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

All done! If you’ve completed all of the above steps, your MariaDB

installation should now be secure.

Thanks for using MariaDB!

To log in to MariaDB, use the following command (note that it’s the same command you would use to log in to a MySQL database):

mysql -u root -p

Then enter the password.

To exit MariaDB write exit then press Enter.

Please note that by default, the MariaDB server cannot be accessed from a remote host, which is very good for security reasons. If, in special circumstances, you want to log in to the MariaDB server from a different host, you will have to make specific configuration changes.

11.1. Create a user with global privileges, to use instead of root

Next we need to create a MariaDB user with all the global privileges as the root user. We’ll use the new user to log in to phpMyAdmin instead of root and also to properly configure the /etc/mysql/debian.cnf file, as we’ll describe below.

Choose an easy to remember name, but one that is radically different from the easy to guess ‘admin’, ‘administrator’, ‘phpmyadmin’, ‘pma’, etc. Let’s say you choose walter. Run the following commands to create the new user and grant him all the rights over all the MariaDB databases on localhost:

mariadb -u root -p
Enter password:

MariaDB [(none)]> CREATE USER 'walter'@'localhost' IDENTIFIED BY 'strongpassword';
MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'walter'@'localhost' WITH GRANT OPTION;
MariaDB [(none)]> FLUSH PRIVILEGES;
MariaDB [(none)]> exit; Replace walter with your own user and strongpassword with your chosen password.

11.2. Optimize MariaDB

11.2.1. Increase max_allowed_packet size

The next step is to increase the max_allowed_packet size in order to increase the speed of execution of certain SQL intensive scripts. First make a copy of the /etc/mysql/my.cnf file:

cp /etc/mysql/my.cnf /etc/mysql/my.cnf_orig

Then edit the /etc/mysql/my.cnf file:

nano /etc/mysql/my.cnf

Right below [client-server] add the following line:

max_allowed_packet = 128M

11.2.2. Increase innodb_buffer_pool_size

The InnoDB engine uses a buffer pool for caching data and indexes in the RAM memory. By increasing its size, we ensure that the InnoDB engine will use the RAM memory rather than the hard drive for most read operations. Because a lot of processes will be using the system’s RAM, for a server with a total of 2GB of RAM we’ll set the innodb_buffer_pool_size to 200MB.

Add the following two lines below the max_allowed_packet = 128M line:

[mysqld]

innodb_buffer_pool_size = 200M

11.2.3. Increase the maximum number of concurrent connections

The max_connections parameter specifies how many concurrent connections are permitted. A connection is opened only for the time a SQL query is executed and afterwards it is closed and a new connection can take its place. The default setting for max_connections is 151. If the maximum number of concurrent connections is set too high, when reaching that number, the server can become unresponsive. We’ll set it to 800.

Add the following line below the innodb_buffer_pool_size = 200M line:

max_connections = 800

11.2.4. Disable reverse DNS lookups

By default MariaDB grants access to users based on IP address but also on hostnames. When there is a connection from a particular hostname, MariaDB does a reverse DNS lookup and compares the hostname and the IP address of the request to see if it finds a match. This process takes time and when all the connections come from the same machine, like in the setup presented in this guide, the reverse DNS lookup becomes unnecessary. To disable it add the following line below the max_connections = 800 line:

skip-name-resolve

11.2.5. Set the key_buffer_size, myisam_sort_buffer_size and query_cache_size

Below skip-name-resolve add the following three lines:

key_buffer_size = 1M

myisam_sort_buffer_size = 1M

query_cache_size = 2M

Restart MariaDB:

systemctl restart mariadb

11.2.6. Reduce system swappiness to prevent MariaDB (and other services) from using the swap memory instead of RAM

Debian has a swappiness setting which determines how much of the chunks of data stored in RAM will be moved to the preconfigured swap space located on the hard drive. Since reading data from the hard drive is slower than reading from the RAM, it’s recommended to set the swapping parameter to a lower value, so that the data will be stored and read mainly to/from the RAM. The default swappiness of Debian is 60. You can find it by running:

sysctl vm.swappiness

It’s recommended to set a swappiness no greater than 10. To do so, open the /etc/sysctl.d/99-sysctl.conf file:

nano /etc/sysctl.d/99-sysctl.conf

Add the following line at the bottom of this file:

vm.swappiness = 10

Restart the server:

reboot

11.3. Avoid errors related to MariaDB log rotation

To avoid errors while logrotate rotates MariaDB log files, which causes the logrotate service to enter failed state, you have to edit the /etc/mysql/debian.cnf file. First make a copy of it:

cp /etc/mysql/debian.cnf /etc/mysql/debian.cnf_orig

Then edit it, even if in the first line you are warned not to edit it:

nano /etc/mysql/debian.cnf

Make it look like this:

# Automatically generated for Debian scripts. DO NOT TOUCH!

[client]

host = localhost

user = walter

password = strongpassword

socket = /var/run/mysqld/mysqld.sock

[mysql_upgrade]

host = localhost

user = walter

password = strongpassword

socket = /var/run/mysqld/mysqld.sock

basedir = /usr

where walter is the username that you’ve set up in the previous step, and strongpassword is his password.

Make sure this file with very sensitive information has the right permissions by running:

cd /etc/mysql

chown root:root debian.cnf

chmod 600 debian.cnf

When you check ownership and permissions for /etc/mysql/debian.cnf by running:

ls -al /etc/mysql/debian.cnf

the result should be similar to this:

-rw——- 1 root root 311 Mar 3 09:48 /etc/mysql/debian.cnf

Also, when the logrotate service tries to rotate the MariaDB logs, it can’t find some log files mentioned by default and because of this, it will generate other errors. To avoid these errors, first open the /etc/logrotate.d/mysql-server file:

nano /etc/logrotate.d/mysql-server

Comment out the following lines:

mysqladmin –defaults-file=/etc/mysql/debian.cnf –local flush-error-log \

flush-engine-log flush-general-log flush-slow-log

to make them look like this:

#       mysqladmin --defaults-file=/etc/mysql/debian.cnf --local flush-error-log \
#         flush-engine-log flush-general-log flush-slow-log

Then add the following line right below these two lines:

mysqladmin --defaults-file=/etc/mysql/debian.cnf --local flush-logs

Restart the logrotate service:

systemctl restart logrotate