MariaDB is an open-source database server created by the original developers of MySQL. It is used by top internet giants like Google, WordPress.com, and Wikipedia. It is adept in converting data into structured information for a wide array of applications, ranging from banking to websites.
If you are also looking to use this excellent database server, this article is going to help you install MariaDB and configure it for the best performance on Ubuntu OS through a detailed step-by-step guide.
So, without further ado, let’s get started.
Installing MariaDB on Ubuntu
Ubuntu 20.x has MariaDB available on its default repositories, which can be installed directly from there.
First of all, you will need to update apt repositories data because you will be using apt for the installation of MariaDB. You can run the following command on the terminal app to update apt repositories:
$ sudo apt update
After the repository data is updated, use the command mentioned below to install MariaDB and additional required packages:
$ sudo apt install mariadb-server
You will need to enter ‘Y’ in answer to the prompt that asks you to confirm the installation of MariaDB:
Do you want to continue? [Y/n] Y
Now, MariaDB will be successfully installed on your Ubuntu 20.x machine.
Running MariaDB on Ubuntu
After being installed successfully, MariaDB will automatically start running on an Ubuntu machine. However, you can use the following commands to ensure that MariaDB runs on your Ubuntu machine:
$ sudo systemctl start mariadb. service $ sudo systemctl enable mariadb.service $ sudo systemctl status mariadb.service
Running these commands will make the MariaDB service start automatically every time your machine is booted.
After entering the commands, you will get an output that will look something like this:
$ 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 xx.xx.xx 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
It is essential to secure the deployment of MariaDB, which can be done if you set up a root password, disable remote root login, remove the test database along with anonymous users, and reload privileges.
MariaDB can be hardened by running the following command:
$ sudo mysql_secure_installation
You might see some prompts after this, and you can take necessary actions for them unless you have a special requirement.
Here is the output you will get on the terminal after MariaDB has been hardened:
$ 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 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 authorization. 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 a user account. 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 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 take effect immediately.
Reload privilege tables now? [Y/n] Y ... Success! Cleaning up… Thanks for using MariaDB! $
All done! If you’ve completed all of the above steps, your MariaDB installation should now be secured.
The system’s authentication has been used here, and that is why we haven’t set up a separate root password for MariaDB as it has been already secured, but you can set up a separate root password at any time you want.
Setup Validation
- To verify your MariaDB setup, you need to run the command mentioned below and put in the password you set up while running mysql_secure_installation. If you skipped that step earlier and did not set up a password, you can enter your system’s root credentials.
$ sudo mysqladmin -u root -p version
Output:
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 $
- It is also possible to set up a new admin account and use it on MariaDB instead of the root, and you can do so by running the following command (change the password to the one you want to set for your admin account):
$ sudo MariaDB
Output:
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 $
- You will need to verify the access with the new admin user by entering this command:
$ mysqladmin -u admin -p version
Output:
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
Once you have successfully installed and secured your MariaDB setup, it is time to configure it for delivering optimal performance. Two ways can help to achieve the optimal performance of MariaDB: the first way is to tune your operating system, and the second way is tuning the MariaDB.
Firstly, we will take a look at how to tune your OS, and this optimization will vary based on your system configuration, usage type, number of users, and various other factors.
Linux Kernel Settings – IO Scheduler
The IO schedulers that are recommended for MariaDB are noop and deadline. For checking the scheduler, use the command mentioned here:
$ sudo cat /sys/block/sda/queue/scheduler [mq-deadline] kyber bfq none $
A temporary change can be initiated by issuing the following command and its effect can be observed immediately on the performance of the system:
$ sudo echo noop > /sys/block/sda/queue/scheduler
For continuing this, you will need to configure it in GRUB’s configuration file as shown below in /etc/default/grub. Then, you need to rebuild GRUB and initiate rebooting the system.
GRUB_CMDLINE_LINUX_DEFAULT="quiet splash elevator=noop"
Resource Limits – Open Files Limit
The number of file descriptors that can be opened by each process is limited by Linux. This limit can be easily exceeded for an active DB system. This limit is 1024 by default on many Linux systems, and if you want to increase this, you can add the below-mentioned lines in your /etc/security/limits.conf:
mysql soft nofile 65535 mysql hard nofile 65535
After adding these lines, you will be required to reboot the system, and then the new limits will be applied, and you can use them. You can check the increase in limits by running the following commands:
$ ulimit -Sn 65535 $ ulimit -Hn 65535
Resource Limits – Core File Size
Linux also puts limits on the size of core files, and it has a soft as well as a hard limit. By default, the soft limit is 0, which disables core file generation. You can enable the core file generation by increasing the value of core file size in /etc/security/limits.conf:
mysql soft core unlimited mysql hard core unlimited
You will be able to check the new core file size values after rebooting the system by using the ulimit command in the following way:
$ ulimit -Sc unlimited $ ulimit -Hc unlimited
Configure Swappiness
There is a swappiness value in Linux, which tells how likely is the system to swap a page from memory to swap space configured on the system, and this value is usually set to 60 by default. It can be checked by running the following command:
sysctl vm.swappiness
The value of this can fall anywhere from 0 to 100, and the higher is the value, the more likely is the system to swap. When a system is running only MariaDB, the value should be 0 because swapping should be avoided as much as possible. However, setting the value to 0 can be risky considering other design factors of the system, and if the memory usage or I/O load is high, the kernel can end up killing the Out of Memory (OOM) process.
Therefore, setting the swappiness value to 0 can pose high risks, and thus, it is advised to set it to 1 instead to avoid such risks. This can be done by adding the line mentioned below in /etc/sysctl.conf so that this change persists.
vm.swappiness = 1
In order to see the changes, you need to reboot the system; however, you can do it before as well by using this command:
sysctl -w vm.swappiness=1
MariaDB Optimization
On MariaDB, there are several tunables in its functioning that are customizable according to the needs of the user. We will be discussing some of them here.
You can configure MariaDB by using my.cnf file, which is available on Ubuntu at:
/etc/mysql/my.cnf
The details of the variables that you can use when customizing the configuration file can be found here.
The type of engine that MariaDB uses affects this tuning. These engines can be MyISAM and InnoDB or XtraDB. Both of these engines come with different advantages and disadvantages. When choosing one among them, you should consider your database and application requirements.
It can improve your performance to a high degree if you set innodb_buffer_pool_size to about 80% of your memory, as this ensures that 80% of your working set is in memory.
Here are a few other parameters that can be tuned to improve the performance of MariaDB on your Ubuntu machine:
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 want to know more about optimizing InnoDB or XtraDB variables, you can visit this link and refer to this guide to see all the available tuning options for MariaDB.
Conclusion
MariaDB is one of the most popular database servers, being open-source certainly adds to its popularity. Through this article, we shared with you an extensive account of how to install and run MariaDB on your Ubuntu machine along with the two popular ways to optimize MariaDB so that it can perform at its best.