How to install MariaDB on Debian 9

Introduction

MariaDB is the best-in-class database server that comes as a drop-in substitute for MySQL database. It’s an incredible option for database experts looking for a scalable, reliable, and robust SQL server.

This guide will help you install and configure the MariaDB database on Debian 9.

Ready? Let's get started!

Step 1 - Installing The Database Server (MariaDB)

By default, the latest version of MariaDB is a component of the Debian 9 APT package repository. This version is identified as a default variant of MySQL by Debian’s MariaDB/MySQL packaging teams.

The first thing when installing MariaDB is updating your package index:

$ sudo apt update

Next, run the command below to install MariaDB:

$ sudo apt install Mariadb-server

The command above automatically installs the database server and will not ask you to submit the password or implement any configuration changes. For this reason, your MariaDB server is left insecure.

Step 2 - Securing MariaDB Server

Since this is a new installation you can execute the included script to secure your database. Running the included script will partly modify the insecure options such as the sample users and remote root user logins.

Now, run the command below:

$ sudo mysql_secure_installation

The command above presents a number of prompts that require your authentication to make changes to the security options for MariaDB. Here are some of the prompts:

  • The first one will request you to provide the root password for your database. Since this is a new installation and no password has been configured yet, use the ENTER to continue.
  • Next, you will be asked whether you want to create a new root password for your database. Simply type N (No) and press ENTER to continue. We opted not to create a new password because MariaDB root account in Debian is closely linked to the automated system updates. Modifying the default verification methods for MariaDB account may prompt the system update to remove access to your admin account.
  • From there, type Y and press ENTER  for the subsequent prompts. By doing so, you will remove anonymous users, disable all logins for remote root, eliminate any test database, and load new rules to permit MariaDB to adopt the new changes.

Step 3 - Modifying User Privileges and Authentication

By default, when using MariaDB on a Debian system the root database user authenticates via a unix_socket instead of authenticating with the password. Although this is great in terms of usability and security options, it may be intricate when you intend to allow the administrative rights of external applications such as PhpMyAdmin.

For this reason, it always nice to adjust user privileges and authentication. To accomplish this we’ll not modify the root database account, but create a new administrative account and configure the password-authenticated access.

We’ll create an account titled admin which is similar to the root database account in terms of capabilities but is set up for password verification. First, log in to access your MariaDB prompt:

$ sudo mysql

Next, execute the command below to create a user with the required root permissions and well-defined password-authenticated access.

MariaDB [(none)]>

GRANT ALL ON *.* TO'admin'@'localhost'IDENTIFIEDBY'password'WITHGRANTOPTION;

Now, flush these privileges to save them and ensure they are available in your current session:

MariaDB [(none)]>

FLUSHPRIVILEGES;

Run the command below to exit MariaDB shell.

MariaDB [(none)]>

exit

Step 4 - Verifying The Installation

MariaDB should run automatically since we installed it from default Debian repository. To check if the database server is running, execute the command below:

$ sudo systemctl status mariadb

This delivers the output below:

● mariadb.service - MariaDB database server
   Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
   Active: active (running) since Tue 2018-09-29 16:22:47 UTC; 1h 45min ago
  Process: 15596 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSIT
  Process: 15594 ExecStartPost=/etc/mysql/debian-start (code=exited, status=0/SUCCESS)
  Process: 15478 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= ||   
  Process: 15474 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITI
  Process: 15471 ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var/run/mysql
 Main PID: 15567 (mysqld)
   Status: "Taking your SQL requests now..."
    Tasks: 27 (limit: 4915)
   CGroup: /system.slice/mariadb.service
           └─15567 /usr/sbin/mysqld

Sep 2916:22:45 deb-mysql1 systemd[1]: Starting MariaDB database server...
Sep 2916:22:46 deb-mysql1 mysqld[15567]: 2018-09-2916:22:46140183374869056 [Note] /usr/sbin/mysqld (mysqld 10.1.26-MariaDB-0+deb9u1) startingas process 15567 ...
Sep 2916:22:47 deb-mysql1 systemd[1]: Started MariaDB database server.

If the service is not running, execute the command below to start it

$ sudo systemctl start mariadb

Additionally, execute the command below to perform further checks:

$ sudo mysqladmin version

The command will give you an output indicating the version of MariaDB you installed:

mysqladmin  Ver 9.1 Distrib 10.1.26-MariaDB, for debian-linux-gnu on x86_64
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
Server version      10.1.26-MariaDB-0+deb9u1
Protocol version    10
Connection      Localhost via UNIX socket
UNIX socket     /var/run/mysqld/mysqld.sock
Uptime:         2 hours 44 min 46 sec

Threads: 1  Questions: 36  Slow queries: 0  Opens: 21  Flush tables: 1  Open tables: 15  Queries per second avg: 0.003

In the case where a new administrative user was configured with password verification. You can run the command below to confirm MariaDB version:

$ mysqladmin -u admin -p version

Conclusion

That is It! You have successfully installed MariaDB database server on your Debian 9 server.

Check out these top 3 Best web hosting services

Was this article helpful?