Let’s learn how to install and configure MariaDB, an open-source popular MySQL Relational Database Management System (RDBMS).
It is community developed and has the option of commercial support. MariaDB has been intended to remain highly compatible with MySQL though there are some deviations.
In this article, we’ll be covering the installation and configuration of MariaDB on Ubuntu 20.x and CentOS 7.x/8.x and at the end will review some best practices for securing and improving its performance.
MariaDB on Ubuntu
On Ubuntu 20.x, MariaDB is available directly from its default repositories. We’ll use apt for this task.
First update apt repositories data by:
$ sudo apt update
Once repository data is updated, run:
$ sudo apt install mariadb-server
This will install MariaDB and required packages. Confirm with ‘Y’ for any prompts you may get during the execution of the previous command.
Do you want to continue? [Y/n] Y
MariaDB on CentOS 7.x
For CentOS 7.x, the available MariaDB version from the default CentOS repository is 5.x. We’re going to install the latest available version of MariaDB.
For this, we need to first configure an additional yum repository. MariaDB provides an easy way to use the
mariadb_repo_setup script. To get the repository setup, run the following commands on your CentOS 7.x system:
$ sudo yum install wget $ wget https://downloads.mariadb.com/MariaDB/mariadb_repo_setup $ chmod +x mariadb_repo_setup $ sudo ./mariadb_repo_setup
This script will set up the required yum repository to install MariaDB’s latest version automatically. At the time of writing this article, it is 10.x.
Or in case you want to go the manual way, you can configure a manual yum repository by creating a new repo file as:
$ sudo vi /etc/yum.repos.d/MariaDB.repo
Then add the following details to the repo file and save it:
[mariadb] name = MariaDB baseurl = http://yum.mariadb.org/10.5/centos7-amd64 gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB gpgcheck=1
Now to install MariaDB, run the below command:
$ sudo yum install MariaDB-server
Confirm any prompts that appear during installation by entering ‘y’:
Is this ok [y/d/N]: y
This completes the installation of the MariaDB server and dependent packages.
MariaDB on CentOS 8.x
For CentOS 8.x, the available version from default repositories is 10.3 or newer. We can directly install MariaDB using the DNF command:
sudo dnf install mariadb-server
Else to get the latest available version, you can follow the manual way as given in the previous section for CentOS 7.x and that should get you working.
On an Ubuntu machine, you’ll have MariaDB service running right after installation while for CentOS, we’ve to manually enable and start relevant services.
In any case, for Ubuntu as well as CentOS, run the below commands to get MariaDB service started, enabled on boot as well as verify it:
$ sudo systemctl start mariadb.service $ sudo systemctl enable mariadb.service $ sudo systemctl status mariadb.service
$ sudo systemctl start mariadb.service $ sudo systemctl enable mariadb.service Created symlink from /etc/systemd/system/multi-user.target.wants/mariadb.service to /usr/lib/systemd/system/mariadb.service. $ sudo systemctl status mariadb.service ● mariadb.service - MariaDB 10.5.8 database server Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled) Drop-In: /etc/systemd/system/mariadb.service.d └─migrated-from-my.cnf-settings.conf Active: active (running) since Thu 2020-12-31 13:20:04 IST; 13s ago Docs: man:mariadbd(8) https://mariadb.com/kb/en/library/systemd/ Main PID: 13521 (mariadbd) Status: "Taking your SQL requests now..." CGroup: /system.slice/mariadb.service └─13521 /usr/sbin/mariadbd Dec 31 13:20:04 centos7vm mariadbd: 2020-12-31 13:20:04 0 [Note] InnoDB: 10.5.8 started; log sequence number 45118; transaction id 20 Dec 31 13:20:04 centos7vm mariadbd: 2020-12-31 13:20:04 0 [Note] Plugin 'FEEDBACK' is disabled. Dec 31 13:20:04 centos7vm mariadbd: 2020-12-31 13:20:04 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool Dec 31 13:20:04 centos7vm mariadbd: 2020-12-31 13:20:04 0 [Note] InnoDB: Buffer pool(s) load completed at 201231 13:20:04 Dec 31 13:20:04 centos7vm mariadbd: 2020-12-31 13:20:04 0 [Note] Server socket created on IP: '::'. Dec 31 13:20:04 centos7vm mariadbd: 2020-12-31 13:20:04 0 [Note] Reading of all Master_info entries succeeded Dec 31 13:20:04 centos7vm mariadbd: 2020-12-31 13:20:04 0 [Note] Added new Master_info '' to hash table Dec 31 13:20:04 centos7vm mariadbd: 2020-12-31 13:20:04 0 [Note] /usr/sbin/mariadbd: ready for connections. Dec 31 13:20:04 centos7vm mariadbd: Version: '10.5.8-MariaDB' socket: '/var/lib/mysql/mysql.sock' port: 3306 MariaDB Server Dec 31 13:20:04 centos7vm systemd: Started MariaDB 10.5.8 database server. $
As the first step after the installation of MariaDB, we should secure its deployment by setting a root password, disabling remote root login, removing the test database as well as anonymous users, and finally reload privileges.
Run the below command to harden MariaDB:
$ sudo mysql_secure_installation
You can follow the default prompts with suggested actions unless you have a specific requirement to deviate.
$ sudo mysql_secure_installation NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY! 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): 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] n ... skipping. 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! $
Here we’ll use the system’s authentication, so haven’t set up a separate root password for MariaDB, as it’s already secure. If you need to, you can always set up a separate root password as well.
To verify your MariaDB setup, run (specify the password you set up while running
mysql_secure_installation or, if skipped at that time, use your system’s root credentials):
$ sudo mysqladmin -u root -p version Enter password: mysqladmin Ver 9.1 Distrib 10.5.8-MariaDB, for Linux on x86_64 Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Server version 10.5.8-MariaDB Protocol version 10 Connection Localhost via UNIX socket UNIX socket /var/lib/mysql/mysql.sock Uptime: 53 min 17 sec Threads: 2 Questions: 77 Slow queries: 0 Opens: 20 Open tables: 14 Queries per second avg: 0.024 $
You can set up a new admin account in place of the root as shown below (change the value of
password with the one you intend to set for
$ sudo mariadb Welcome to the MariaDB monitor. Commands end with ; or g. Your MariaDB connection id is 44 Server version: 10.5.8-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. MariaDB [(none)]> GRANT ALL ON *.* TO 'admin'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION; Query OK, 0 rows affected (0.003 sec) MariaDB [(none)]> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.000 sec) MariaDB [(none)]> exit Bye $
Verify access with new
admin user as (enter the
password as set in the previous step):
$ mysqladmin -u admin -p version Enter password: mysqladmin Ver 9.1 Distrib 10.5.8-MariaDB, for Linux on x86_64 Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Server version 10.5.8-MariaDB Protocol version 10 Connection Localhost via UNIX socket UNIX socket /var/lib/mysql/mysql.sock Uptime: 56 min 59 sec Threads: 2 Questions: 83 Slow queries: 0 Opens: 20 Open tables: 14 Queries per second avg: 0.024 $
After installation and securing your MariaDB setup, you should take action for tuning your OS and database for optimal performance. This tuning will vary based on your system configuration, usage type, number of users, and a number of other factors.
From an OS perspective, some system parameters can be configured for MariaDB which we’ll discuss now.
Linux Kernel Settings – IO Scheduler
Recommended IO schedulers for MariaDB are
deadline. To check use
$ sudo cat /sys/block/sda/queue/scheduler [mq-deadline] kyber bfq none $
A temporary change can be done by issuing the following command and its effect, if any, will be immediate on system performance:
$ sudo echo noop > /sys/block/sda/queue/scheduler
To make it persistent, you’ll need to configure it in GRUB’s configuration file as shown below in
/etc/default/grub , rebuild GRUB, and reboot the system.
GRUB_CMDLINE_LINUX_DEFAULT="quiet splash elevator=noop"
Resource Limits – Open Files Limit
Linux usually limits the number of file descriptors each process can open. For an active DB system, this limit can easily exceed or may impact performance. On many Linux systems, this limit defaults to 1024. Further, there is an option of soft and hard limits.
To increase the limit, you can add the following lines in your
mysql soft nofile 65535 mysql hard nofile 65535
A system reboot will be needed post which
mysql user would be able to see and use the new limits. This can be checked as:
$ ulimit -Sn 65535 $ ulimit -Hn 65535
Resource Limits – Core File Size
Linux limits the size of core files as seen in the previous case. Again this has a soft and hard limit and by default soft limit is set as 0 which effectively disables core file generation. To allow core file generation (other configurations needed for core dump generation), we can increase this value in /etc/security/limits.conf as:
mysql soft core unlimited mysql hard core unlimited
After the system reboot,
mysql user would be able to see the new values using
ulimit command as:
$ ulimit -Sc unlimited $ ulimit -Hc unlimited
Swappiness value in Linux determines how likely the system is to swap a page from memory to swap space configured on the system. Usually, the default value is set as 60 which can be checked from:
Its value can range from 0 to 100, where a lower value means a lower likeliness of swapping. On a database server running only MariaDB, we would want to reduce this value to 0 to avoid using swap as much as possible. Do note here that setting swappiness value of 0 should be done with caution considering other system design factors, as in case of high memory usage or I/O load, there is a chance of Out Of Memory (OOM) process killing by the kernel.
Since a low swappiness setting is recommended for database workloads and as such for MariaDB databases, it is recommended to set swappiness to a value of 1. You can add below line in
/etc/sysctl.confto make this change persistent:
vm.swappiness = 1
The changes will take effect after system reboot though you can always do it in advance using
sysctl -w vm.swappiness=1
For MariaDB, the best Linux filesystems are generally regarded as
Btrfs which are all included in the mainline Linux kernel and are widely supported. These filesystems are available on most Linux distributions. Each filesystem has its unique properties and features and can be chosen based on requirements after proper review.
Further, it’s unlikely that you’ll need to record file access time on a database server. We can disable this to improve performance. You can mount the relevant filesystem with
noatime option or add it in the mount options in
/etc/fstab file to make it persistent.
There are several tunables internal to MariaDB functioning that can be customized based on one’s requirements and needs.
We’ll discuss a few of them here.
MariaDB is mostly configured by
On Ubuntu, you can find
While on CentOS its located at:
Detailed documentation on what variables are available for tuning in the configuration file can be referred from here.
A lot also depends on the type of engine used by MariaDB, namely,
InnoDB or XtraDB. Both have their own sets of pros and cons and choosing one depends upon database and application requirements.
We should set
innodb_buffer_pool_size to about 80% of your memory. This ensures that 80% of your working set is in memory.
Some of the other important tunable parameters are:
innodb_log_file_size innodb_flush_method innodb_thread_sleep_delay innodb_adaptive_max_sleep_delay innodb_buffer_pool_instances innodb_buffer_pool_size innodb_max_dirty_pages_pct_lwm innodb_read_ahead_threshold innodb_thread_concurrency
If you are interested, check out these resources to learn SQL and NoSQL.
MariaDB is one of the popular choices when it comes to relation DBMS. Being open-source with a diverse community adds further to it.
To learn further, refer to its documentation which includes topics like basic SQL, migration, MariaDB administration, high availability, performance tuning, storage engines, programming, and customization. You may also want to disable binary logging if not using a cluster.