Today we will show you how to install MariaDB database server on your Ubuntu 20.04 LTS system. Our focus is a variation allowing you to install and maintain the latest version.
MariaDB is an open-source relational database management system. Developed by the original MySQL developers, it can be used as an alternative to MySQL as it maintains high compatibility with the MySQL server, APIs and commands and is, therefore, a drop-in replacement for MySQL.
Before you get started, you will need a server running Ubuntu 20.04 LTS with a non-root administrative user and a firewall configured with UFW.
Step 1 – Setup Repository
Like many of the default packages in Ubuntu, the repositories contains old versions of Mariadb server. Therefore, we will have to import the official MariaDB repository and use it to get the latest version of MariaDB.
Add the package signing key to your system by running the following commands:
$ sudo apt install software-properties-common
$ sudo apt-key adv --fetch-keys 'https://mariadb.org/mariadb_release_signing_key.asc'
Next, create the apt configuration file on your Ubuntu system by running the following command:
$ sudo nano /etc/apt/sources.list.d/mariadb.list
Add this content to the configuration file.
# MariaDB 10.4 Repository
deb [arch=amd64] http://nyc2.mirrors.digitalocean.com/mariadb/repo/10.4/ubuntu focal main
deb-src http://nyc2.mirrors.digitalocean.com/mariadb/repo/10.4/ubuntu focal main
Save and exit the file.
Step 2 – Install MariaDB on Ubuntu 20.04 LTS
Now that we have access to the official MariaDB repository, we are ready to install the latest version of MariaDB.
We can install Mariadb server, client tools and other required packages by running the following commands:
$ sudo apt update
$ sudo apt install mariadb-server
Step 3 – Post Installation Setup
We now have to use the command-line utility provided by MariaDB to complete the post-installation steps. This additional setup allows you to configure a secure password for the root user and removing insecure default installation settings.
Run the following command in your terminal.
$ sudo mysql_secure_installation
This will guide you through a series of prompts where you can make your desired changes to your MariaDB installation’s security options.
The script starts off by asking for the current database root password. Since you have not set one up yet, press ENTER to proceed.
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 you haven't set the root password yet, the password will be blank, so you should just press enter here. Enter current password for root (enter for none):
Next you will be asked whetheryou would like to set up a database root password. Ideally you will want to set up a password here. However, with Ubuntu the root account for MariaDB is tied closely to automated system maintenance like log rotation, starting and stopping the server and more.
This presents a potential problem in the future. This presents the possibility for a package update to break the database system by removing access to the administrative account, so we should not change the configured authentication methods for that account.
For this prompt, just type N and then press ENTER.
. . . OK, successfully used password, moving on... Setting the root password ensures that nobody can log into the MariaDB root user without the proper authorisation. Set root password? [Y/n] N
For the next series of prompts, the recommended options are to select yes (‘Y’).
These will basically remove anonymous users, the test database, disable remote root logins, and load these new rules so that MariaDB immediately implements the changes you have made.
Step 4 — (Optional) Create an Administrative User that Uses Password Authentication
On Ubuntu systems running MariaDB 10.3+, the root MariaDB user is set to authenticate using the unix_socket plugin by default rather than with a password.
This is well and okay from a security and usability standpoint in most cases, but it can also affect operations of an external program like phpMyAdmin to access administrative rights.
You can create a user that has the same capabilities as the root user but configured for password authentication. In our example, we will call this user admin.
Go to the terminal and run this command to open up the MariaDB prompt:
$ sudo mariadb
create a new user with root privileges and password-based access using your own user and password combination.
MariaDB [(none)]> GRANT ALL ON *.* TO 'admin'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION;
Next, Flush the privileges to save and have the changes available in the current session:
MariaDB [(none)]> FLUSH PRIVILEGES;
You may now exit the MariaDB shell.
MariaDB [(none)]> exit
Step 4 — Test the MariaDB Service
You can test that MariaDB is started by running the following command:
$ sudo systemctl status mariadb
You’ll should see some output similar to this:
● mariadb.service - MariaDB 10.4.13 database server Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled) Active: active (running) since Tue 2020-06-14 11:18:12 UTC; 4min 25s ago Docs: man:mysqld(8) https://mariadb.com/kb/en/library/systemd/ Main PID: 25914 (mysqld) Status: "Taking your SQL requests now..." Tasks: 31 (limit: 2345) Memory: 48.6M CGroup: /system.slice/mariadb.service └─25914 /usr/sbin/mysqld
Step 5 – Manage MariaDB Service
You can use Ubuntu’s systemd to manage the MariaDB service. These are the four commands you would tend to use at different scenarios:
$ sudo systemctl start mariadb # To start service $ sudo systemctl stop mariadb # To stop service $ sudo systemctl restart mariadb # To stop and then start service $ sudo systemctl status mariadb # To check status of service
If you have successfully come this far you now have a working database service which has been secured using the mysql_secure_installation script, and an administrative user to handle the root user tasks.
That is all there is to install MariaDB on Ubuntu 20.04 LTS. From here you will need to start creating your databases, additional users, and start managing your data.
Next, you can install phpMyAdmin to do all the above via a web-based interface. You can also invest in a tool like Webyog’s SQLYog which includes a free community edition or purchase a license here. You can also use it to manage your MariaDB database.