MariaDB is an open-source

MariaDB was developed as a software fork of MySQL in 2009 in response to Oracle’s acquisition of MySQL. MariaDB intends to remain free and open-source software under the GNU General Public License.

In this guide we will learn how to install and configure

Related Posts

• How to install Mysql 8 on Rocky Linux/Centos 8
• How to install and Configure Mariadb 10 in Ubuntu 20.04
• How to install and Configure Mariadb in Fedora 35
• How to Install and Set Up mysql 8 on Ubuntu 20.04

Prerequisites

To follow along, ensure you have:

• An up to date Debian 11 server
• Root access to the server or user with Sudo access
• Access to the internet from the server

Table of content

1. Update system
2. Installing Mariadb
3. Configuring Mariadb
4. Optional: Adjusting User Authentication and Privileges
5. Testing MariaDB

1. Update the system

Before proceeding let us ensure that the Debian server is up to date. First update the repos then do a system upgrade to ensure all the installed packages are up to date:

In your terminal, type these. The -y option in apt upgrade is to ensure that the system doesn’t pause for us to accept the upgrade.

$ sudo apt update
$ sudo apt upgrade -y

2. Installing mariadb

Mariadb is found in the default repos for debian. Use this command to install the database server:

sudo apt install -y mariadb-server

Mariadb will be started by default.

$ sudo systemctl status mariadb
● mariadb.service - MariaDB 10.5.12 database server
     Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
     Active: active (running) since Fri 2021-11-12 07:31:38 UTC; 32s ago
       Docs: man:mariadbd(8)
             https://mariadb.com/kb/en/library/systemd/
    Process: 32218 ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var/run/mysqld (code=exited, status=0/SUCCESS)
    Process: 32219 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
    Process: 32221 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= ||   VAR=`cd /usr/bin/..; /usr/bin/galera_recovery`; [ $? -eq 0 ]   && systemctl set-environment _WSREP_START_>
    Process: 32328 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
    Process: 32330 ExecStartPost=/etc/mysql/debian-start (code=exited, status=0/SUCCESS)
   Main PID: 32296 (mariadbd)
     Status: "Taking your SQL requests now..."
      Tasks: 14 (limit: 4626)
     Memory: 76.9M
        CPU: 703ms
     CGroup: /system.slice/mariadb.service
             └─32296 /usr/sbin/mariadbd

Nov 12 07:31:40 ip-10-2-40-39 /etc/mysql/debian-start[32335]: mysql
Nov 12 07:31:40 ip-10-2-40-39 /etc/mysql/debian-start[32335]: performance_schema
Nov 12 07:31:40 ip-10-2-40-39 /etc/mysql/debian-start[32335]: Phase 6/7: Checking and upgrading tables
Nov 12 07:31:40 ip-10-2-40-39 /etc/mysql/debian-start[32335]: Processing databases
Nov 12 07:31:40 ip-10-2-40-39 /etc/mysql/debian-start[32335]: information_schema
Nov 12 07:31:40 ip-10-2-40-39 /etc/mysql/debian-start[32335]: performance_schema
Nov 12 07:31:40 ip-10-2-40-39 /etc/mysql/debian-start[32335]: Phase 7/7: Running 'FLUSH PRIVILEGES'
Nov 12 07:31:40 ip-10-2-40-39 /etc/mysql/debian-start[32335]: OK
Nov 12 07:31:40 ip-10-2-40-39 /etc/mysql/debian-start[32945]: Checking for insecure root accounts.
Nov 12 07:31:40 ip-10-2-40-39 /etc/mysql/debian-start[32952]: Triggering myisam-recover for all MyISAM tables and aria-recover for all Aria tables

3. Configuring MariaDB

For new MariaDB installations, the next step is to run the included security script. This script changes some of the less secure default options. We will use it to block remote root logins and to remove unused database users.

Run the security script:

sudo mysql_secure_installation

This will take you through a series of prompts where you can make some changes to your MariaDB installation’s security options. The first prompt will ask you to enter the current database root password. Since we have not set one up yet, press ENTER to indicate “none”.

The next prompt asks you whether you’d like to set up a database root password. Type N and then press ENTER. In Debian, the root account for MariaDB is tied closely to automated system maintenance, so we should not change the configured authentication methods for that account. Doing so would make it possible for a package update to break the database system by removing access to the administrative account. Later, we will cover how to optionally set up an additional administrative account for password access if socket authentication is not appropriate for your use case.

From there, you can press Y and then ENTER to accept the defaults for all the subsequent questions. This will remove some anonymous users and the test database, disable remote root logins, and load these new rules so that MariaDB immediately respects the changes you have made.

This is my server’s 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]
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!


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!

4. Optional: Adjusting User Authentication and Privileges {#step-3-—-optional-adjusting-user-authentication-and-privileges.wp-block-heading}

In Debian systems running MariaDB 10, the root MariaDB user is set to authenticate using the unix_socket plugin by default rather than with a password. This allows for some greater security and usability in many cases, but it can also complicate things when you need to allow an external program (e.g., phpMyAdmin) administrative rights.

Because the server uses the root account for tasks like log rotation and starting and stopping the server, it is best not to change the root account’s authentication details. Changing credentials in the /etc/mysql/debian.cnf configuration file may work initially, but package updates could potentially overwrite those changes. Instead of modifying the root account, the package maintainers recommend creating a separate administrative account for password-based access.

To do so, we will create a new account called admin with the same capabilities as the root account, but configured for password authentication. To do this, open up the MariaDB prompt from your terminal then create a new user with root privileges and password-based access. Change the username and password to match your preferences:

$ mysql -h 127.0.0.1 -u root -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 56
Server version: 10.5.12-MariaDB-0+deb11u1 Debian 11

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 'T*88hn3@H^RDibAc%puz' WITH GRANT OPTION;
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> FLUSH PRIVILEGES;

5. Testing MariaDB

Now that mariadb is all set up and is running, we need to confirm that it can accept connections.

To test, connect to mariadb with root user – mysql -h 127.0.0.1 -u root -p

Output:

$ mysql -h 127.0.0.1 -u root -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 54
Server version: 10.5.12-MariaDB-0+deb11u1 Debian 11

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)]>

Check mariadb version:

MariaDB [(none)]> SELECT VERSION();
+---------------------------+
| VERSION()                 |
+---------------------------+
| 10.5.12-MariaDB-0+deb11u1 |
+---------------------------+
1 row in set (0.000 sec)

MariaDB [(none)]>

For an additional check, you can try connecting to the database using the mysqladmin tool, which is a client that lets you run administrative commands. For example, this command says to connect to MariaDB as root and return the version using the Unix socket:

sudo mysqladmin version

You should receive output similar to this:

$ sudo mysqladmin version
mysqladmin  Ver 9.1 Distrib 10.5.12-MariaDB, for debian-linux-gnu on x86_64
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Server version		10.5.12-MariaDB-0+deb11u1
Protocol version	10
Connection		Localhost via UNIX socket
UNIX socket		/run/mysqld/mysqld.sock
Uptime:			1 hour 38 min 27 sec

Threads: 1  Questions: 490  Slow queries: 0  Opens: 171  Open tables: 28  Queries per second avg: 0.082

Since you configured a separate administrative user with password authentication, you could perform the same operation by typing:

mysqladmin -u admin -p version

This means that MariaDB is up and running and that your user is able to authenticate successfully.

Conclusion

In this guide you installed MariaDB to act as an SQL server. During the installation process you also secured the server.