MariaDB Server is one of the most popular open source relational databases. It’s made by the original developers of MySQL and guaranteed to stay open source. It is part of most cloud offerings and the default in most Linux distributions. It is built upon the values of performance, stability, and openness, compatibility features with Oracle Database and Temporal Data Tables, allowing one to query the data as it stood at any point in the past.

Following is the supported flavors of (Ubuntu) by MariaDB

Ubuntu 18.04 LTS (bionic), 20.04 LTS (focal), and 22.04 LTS (jammy)

Install MariaDB on Ubuntu

Firstly, run the following command to update system package list:

sudo apt update

To install MariaDB, you need to configure the APT package repository on the system by the following commands.

wget https://downloads.mariadb.com/MariaDB/mariadb_repo_setup

echo "733cf126b03f73050e242102592658913d10829a5bf056ab77e7f864b3f8de1f  mariadb_repo_setup" \
       | sha256sum -c -

chmod +x mariadb_repo_setup

sudo ./mariadb_repo_setup --os-type=ubuntu --os-version=focal

You can pass --mariadb-server-version flag to choose a specific version, or leave it for the default. In our case 10.9 is the default version. To know and learn more about the flag options check MariaDB documentation.

Now, install MariaDB:

sudo apt install mariadb-server mariadb-backup

To check the status of the new installed DB, run the following command:

sudo systemctl status mariadb

To check its version, use this command:

mariadb -V

Secure MariaDB

Secure database installation by setting root password, disable remote root login and remove test databases:

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] y
Enabled successfully!
Reloading privilege tables..
... Success!

You already have your root account protected, so you can safely answer 'n'.

Change the root password? [Y/n] y

New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
... Success!

For other questions you can type Y and press Enter to accept default sittings

Now, let’s make sure that it’s only working by providing password, which is what we’re seeking for! Run the following command:

mysql -u root <ENTER>

You should get this:

ERROR 1698 (28000): Access denied for user 'root'@'localhost'

Do the same but with password:

$ mysql -u root -p

Enter password:

The output will look like this:

Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 14
Server version: 10.5.4-MariaDB-1:10.5.4+maria~xenial mariadb.org binary distribution
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)]> SELECT version();
+----------------------------------------+
| version()                              |
+----------------------------------------+
| 10.5.4-MariaDB-1:10.5.4+maria~xenial   |
+----------------------------------------+
1 row in set (0.001 sec)

Let’s create a test first database, by writing CREATE DATABASE test_db;

MariaDB [(none)]> CREATE DATABASE test_db;

The output will look like this:

Query OK, 1 row affected (0.001 sec)

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test_db            |
+--------------------+
4 rows in set (0.002 sec)

MariaDB [(none)]> DROP DATABASE test_db;

The output will look like this:

Query OK, 0 rows affected, 2 warnings (0.002 sec)

Let's show it by writing ``SHOW DATABASES;``:

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.001 sec)

To exit sql command:

MariaDB [(none)]> \q

Enable logging

To enable the general query log, set the general_log system variable to 1. It can be changed dynamically with SET GLOBAL. For example:

SET GLOBAL general_log=1;

Configuring the General Query Log Filename

One way to configure the general query log filename is to set the general_log_file system variable. It can be changed dynamically with SET GLOBAL. For example:

SET GLOBAL general_log_file='mariadb.log';

Choosing the General Query Log Output Destination

Writing the General Query Log to a File

The general query log is output to a file by default. However, it can be explicitly chosen by setting the log_output system variable to FILE. It can be changed dynamically with SET GLOBAL. For example:

SET GLOBAL log_output='FILE';

Writing the General Query Log to a Table

The general query log can either be written to the general_log table in the mysql database by setting the log_output system variable to TABLE. It can be changed dynamically with SET GLOBAL. For example:

SET GLOBAL log_output='TABLE';

Disabling the General Query Log for a Session

A user with the SUPER privilege can disable logging to the general query log for a connection by setting the SQL_LOG_OFF system variable to 1. For example:

SET SESSION SQL_LOG_OFF=1;

Optimize MariaDB Performance on a Server

Database optimization is the strategy of reducing database system response time.

Optimize MariaDB Queries

Part of the process of tuning MariaDB for better performance is checking for slow or inefficient queries. One poorly-structured MariaDB query can slow down the entire database. MariaDB can be configured to log any query which takes longer than the specified number of seconds. This will let you track any slow queries, and correct them as needed.

To enable slow query logging, if you’re not logged in, log in to MariaDB:

mysql -u root -p

Enter the following command to enable logging:

SET GLOBAL slow_query_log = 'ON';

The default threshold is 10 seconds. Use the following command to enable logging for any query that takes longer than 1 second:

SET GLOBAL long_query_time = 1;

Tune InnoDB Values

There are three InnoDB settings which can be tuned to improve the performance of MariaDB. More information about each of these settings can be found in the official MySQL documentation.

innodb_flush_method

To change the InnoDB settings, open the my.cnf file for editing:

sudo nano /etc/mysql/my.cnf

Add the following content to the top of the file. Be sure to put [mysqld] as the first line.

[mysqld]
innodb_buffer_pool_size = 500M
innodb_flush_log_at_trx_commit = 0
innodb_flush_method = O_DIRECT

Save and exit the file. Restart MariaDB for the changes to take effect:

sudo systemctl restart mysql