MariaDB is an open-source database management system, commonly used as an alternative for the MySQL portion of the popular LAMP (Linux, Apache, MySQL, PHP/Python/Perl) stack. It is intended to be a drop-in replacement for MySQL.
The short version of this installation guide consists of these three steps:
apt
mariadb-server
package using apt
. The package also pulls in related tools to interact with MariaDBmysql_secure_installation
security script to restrict access to the server- sudo apt update
- sudo apt install mariadb-server
- sudo mysql_secure_installation
This tutorial will explain how to install MariaDB on an Ubuntu 18.04 server, and verify that it is running and has a safe initial configuration.
To follow this tutorial, you will need:
sudo
privileges and a firewall.On Ubuntu 18.04, MariaDB version 10.1 is included in the APT package repositories by default.
To install it, update the package index on your server with apt
:
- sudo apt update
Then install the package:
- sudo apt install mariadb-server
Ensure that MariaDB is running with the systemctl start
command:
- sudo systemctl start mariadb.service
These commands will install and start MariaDB, but will not prompt you to set a password or make any other configuration changes. Because the default configuration leaves your installation of MariaDB insecure, we will use a script that the mariadb-server
package provides to restrict access to the server and remove unused accounts.
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
. On Ubuntu, 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 implements the changes you have made.
On Ubuntu systems running MariaDB 10.1, 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:
- sudo mysql
Now, we will create a new user with root privileges and password-based access. Change the username and password to match your preferences:
- GRANT ALL ON *.* TO 'admin'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION;
Flush the privileges to ensure that they are saved and available in the current session:
- FLUSH PRIVILEGES;
Following this, exit the MariaDB shell:
- exit
Finally, let’s test the MariaDB installation.
When installed from the default repositories, MariaDB should start running automatically. To test this, check its status.
- sudo systemctl status mariadb
You’ll receive output that is similar to the following:
Output● mariadb.service - MariaDB 10.1.44 database server
Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
Active: active (running) since Wed 2020-03-25 16:51:16 UTC; 8min ago
Docs: man:mysqld(8)
https://mariadb.com/kb/en/library/systemd/
Main PID: 22559 (mysqld)
Status: "Taking your SQL requests now..."
Tasks: 27 (limit: 1152)
CGroup: /system.slice/mariadb.service
└─22559 /usr/sbin/mysqld
Mar 25 16:51:17 ubuntu-mariadb /etc/mysql/debian-start[22596]: mysql
Mar 25 16:51:17 ubuntu-mariadb /etc/mysql/debian-start[22596]: performance_schema
Mar 25 16:51:17 ubuntu-mariadb /etc/mysql/debian-start[22596]: Phase 6/7: Checking and upgrading tables
Mar 25 16:51:17 ubuntu-mariadb /etc/mysql/debian-start[22596]: Processing databases
Mar 25 16:51:17 ubuntu-mariadb /etc/mysql/debian-start[22596]: information_schema
Mar 25 16:51:17 ubuntu-mariadb /etc/mysql/debian-start[22596]: performance_schema
Mar 25 16:51:17 ubuntu-mariadb /etc/mysql/debian-start[22596]: Phase 7/7: Running 'FLUSH PRIVILEGES'
Mar 25 16:51:17 ubuntu-mariadb /etc/mysql/debian-start[22596]: OK
Mar 25 16:51:17 ubuntu-mariadb /etc/mysql/debian-start[22658]: Checking for insecure root accounts.
Mar 25 16:51:17 ubuntu-mariadb /etc/mysql/debian-start[22663]: Triggering myisam-recover for all MyISAM tables and aria-recover for all Aria tables
If MariaDB isn’t running, you can start it with the command sudo systemctl start mariadb
.
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:
Outputmysqladmin Ver 9.1 Distrib 10.1.44-MariaDB, for debian-linux-gnu on x86_64
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Server version 10.1.44-MariaDB-0ubuntu0.18.04.1
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/run/mysqld/mysqld.sock
Uptime: 10 min 9 sec
Threads: 1 Questions: 445 Slow queries: 0 Opens: 167 Flush tables: 1 Open tables: 30 Queries per second avg: 0.730
If 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.
In this guide you installed MariaDB to act as an SQL server. During the installation process you also secured the server. Optionally, you also created a separate password-authenticated administrative user.
Now that you have a running and secure MariaDB server, here some examples of next steps that you can take to work with the server:
You can also incorporate MariaDB into a larger application stack:
Thanks for learning with the DigitalOcean Community. Check out our offerings for compute, storage, networking, and managed databases.
This textbox defaults to using Markdown to format your answer.
You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!