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.
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:
Run the command below to exit MariaDB shell.
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: Starting MariaDB database server... Sep 2916:22:46 deb-mysql1 mysqld: 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: 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
That is It! You have successfully installed MariaDB database server on your Debian 9 server.