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: Once repository data is updated, run: This will install MariaDB and required packages. Confirm with ‘Y’ for any prompts you may get during the execution of the previous command.
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: 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: Then add the following details to the repo file and save it: Now to install MariaDB, run the below command: Confirm any prompts that appear during installation by entering ‘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: 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.
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: Output:
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: You can follow the default prompts with suggested actions unless you have a specific requirement to deviate. Output: 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): 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): Verify access with new admin user as (enter the password as set in the previous step):
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 A temporary change can be done by issuing the following command and its effect, if any, will be immediate on system performance: 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. 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: 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: 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: After the system reboot, mysql user would be able to see the new values using ulimit command as: Configure 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: 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: The changes will take effect after system reboot though you can always do it in advance using sysctl command: Filesystem 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: 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, 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: More details about tuning InnoDB or XtraDB variables can be found here. Refer to this guide for all available tuning options for MariaDB.
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.
