Install MariaDB from the Debian repository:
apt-get install mariadb-serverBy 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_installationAnswer the questions as shown below in blue and red:
In order to log into MariaDB to secure it, we'll need the current
password for the root user. If you've just installed MariaDB, and
haven't set the root password yet, you should just press enter here.
Enter current password for root (enter for none): press Enter here
OK, successfully used password, moving on...
Setting the root password or using the unix_socket ensures that nobody
can log into the MariaDB root user without the proper authorisation.
You already have your root account protected, so you can safely answer 'n'.
Switch to unix_socket authentication [Y/n] n
... skipping.
You already have your root account protected, so you can safely answer 'n'.
Change the root password? [Y/n] y
New password: strongpassword
Re-enter new password: strongpassword
Password updated successfully!
Reloading privilege tables..
... Success!
By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them. This is intended only for testing, and to make the installation
go a bit smoother. You should remove them before moving into a
production environment.
Remove anonymous users? [Y/n] y
... Success!
Normally, root should only be allowed to connect from 'localhost'. This
ensures that someone cannot guess at the root password from the network.
Disallow root login remotely? [Y/n] y
... Success!
By default, MariaDB comes with a database named 'test' that anyone can
access. This is also intended only for testing, and should be removed
before moving into a production environment.
Remove test database and access to it? [Y/n] y
- Dropping test database...
... Success!
- Removing privileges on test database...
... Success!
Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.
Reload privilege tables now? [Y/n] y
... Success!
Cleaning up...
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:
mariadb -u root -pThen enter the MariaDB root password that you have just set up. 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 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 which we won’t discuss here.
11.1. Create a user with global privileges, to use instead of root
Next, create a MariaDB user with all the global privileges as the root user. You’ll use the new user to log in to phpMyAdmin instead of root.
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_origThen edit the /etc/mysql/my.cnf file:
nano /etc/mysql/my.cnfAbove the following lines:
# Import all .cnf files from configuration directory
!includedir /etc/mysql/conf.d/add these lines:
[mysqld]
max_allowed_packet = 128M11.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 innodb_buffer_pool_size to 200MB.
Add the following line right below the max_allowed_packet = 128M line:
innodb_buffer_pool_size = 200M11.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 = 80011.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-resolve11.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 = 2MRestart MariaDB:
systemctl restart mariadb11.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 swappiness 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.swappinessIt’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.confAdd the following line at the bottom of this file:
vm.swappiness = 10Restart the server:
reboot11.3. Upgrading MariaDB
Since MariaDB has been installed from the official Debian repository, to upgrade it, all you need to do is to run apt-get update && apt-get dist-upgrade with a specific frequency, as described in the Maintenance steps chapter. This command will upgrade MariaDB if there is a new version available. Also, during these upgrades, the configuration changes implemented as described above, will be preserved.
