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 updateOnce repository data is updated, run:
$ sudo apt install mariadb-serverThis 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] YMariaDB 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_setupThis 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.repoThen 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=1Now to install MariaDB, run the below command:
$ sudo yum install MariaDB-serverConfirm any prompts that appear during installation by entering ‘y’:
Is this ok [y/d/N]: yThis 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-serverElse 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.
Starting MariaDB
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.serviceOutput:
$ 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[13521]: 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[13521]: 2020-12-31 13:20:04 0 [Note] Plugin 'FEEDBACK' is disabled.
Dec 31 13:20:04 centos7vm mariadbd[13521]: 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[13521]: 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[13521]: 2020-12-31 13:20:04 0 [Note] Server socket created on IP: '::'.
Dec 31 13:20:04 centos7vm mariadbd[13521]: 2020-12-31 13:20:04 0 [Note] Reading of all Master_info entries succeeded
Dec 31 13:20:04 centos7vm mariadbd[13521]: 2020-12-31 13:20:04 0 [Note] Added new Master_info '' to hash table
Dec 31 13:20:04 centos7vm mariadbd[13521]: 2020-12-31 13:20:04 0 [Note] /usr/sbin/mariadbd: ready for connections.
Dec 31 13:20:04 centos7vm mariadbd[13521]: Version: '10.5.8-MariaDB' socket: '/var/lib/mysql/mysql.sock' port: 3306 MariaDB Server
Dec 31 13:20:04 centos7vm systemd[1]: Started MariaDB 10.5.8 database server.
$Securing MariaDB
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_installationYou can follow the default prompts with suggested actions unless you have a specific requirement to deviate.
Output:
$ 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.
Setup Validation
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 admin account):
$ 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
$OS Optimization
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 noop and deadline. To check use cat /sys/block/${DEVICE}/queue/scheduler
$ 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/schedulerTo 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 /etc/security/limits.conf:
mysql soft nofile 65535
mysql hard nofile 65535A 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
65535Resource 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 unlimitedAfter the system reboot, mysql user would be able to see the new values using ulimit command as:
$ ulimit -Sc
unlimited
$ ulimit -Hc
unlimitedConfigure Swappiness
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:
sysctl vm.swappinessIts 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 = 1The changes will take effect after system reboot though you can always do it in advance using sysctl command:
sysctl -w vm.swappiness=1Filesystem Optimizations
For MariaDB, the best Linux filesystems are generally regarded as ext4, XFS and 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.
DB Optimization
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 my.cnf file.
On Ubuntu, you can find my.cnf at:
/etc/mysql/my.cnfWhile on CentOS its located at:
/etc/my.cnfDetailed 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, MyISAM and 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_concurrencyMore details about tuning InnoDB or XtraDB variables can be found here. Refer to this guide for all available tuning options for MariaDB.
If you are interested, check out these resources to learn SQL and NoSQL.
Conclusion
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.