Welcome to our guide of the day that will cover the steps needed to install MariaDB 10.6 on Ubuntu 22.04 LTS (Jammy Jellyfish). MariaDB is an open source relational database management system used by many small and large companies to power their applications. MariaDB database is focused on stability, reliability, data security and and performance. One of the many reasons why MariaDB is widely used is its robustness and scalability. MariaDB is the default database in most Linux distributions – Ubuntu, Debian, RHEL, CentOS, Rocky Linux, AlmaLinux among many other distributions.
In this guide, we are looking at the complete installation of MariaDB 10.6 on Ubuntu 22.04 from MariaDB APT repository. On a fresh installation of Ubuntu 22.04, the default OS APT repositories contain MariaDB 10.5 packages. MariaDB 10.6 is the current stable release of MariaDB and has some notable cool features:
- Ignored Indexes – These are indexes that are visible and maintained but not used by the optimizer
- JSON_TABLE() – can create a JSON table that can be used as a subquery from a JSON document.
- sys schema supported- This is a “system” database containing views and procedures for investigating performance problems.
- Oracle compatibility – There are ongoing works in making MariaDB compatible with OracleDB with some Oracle Syntaxes and functions already added.
- SKIP LOCKED – Locked tables are skipped from being updated or selected.
- OFFSET…FETCH…[WITH TIES] – WITH TIES is an optional clause that adds extra functionality. Example as used
Step 1 – System update / upgrade
Run Ubuntu system upgrades to ensure the OS is up-to-date:
sudo apt update && sudo apt upgrade -yAfter successful upgrade perform a reboot to use new set of packages on the system
sudo systemctl rebootStep 2 – Add MariaDB 10.6 APT Repository
Install software-properties-common package which provides add-apt-repository command used to add repositories to Debian based systems.
sudo apt install software-properties-common -yAs of this article update, MariaDB 10.6 APT repository for Ubuntu 22.04 is not available. We will use Ubuntu 20.04 (Focal Fossa) repository.
Run the commands below to configure MariaDB APT repository and add MariaDB signing key:
curl -LsS -O https://downloads.mariadb.com/MariaDB/mariadb_repo_setup
sudo bash mariadb_repo_setup --os-type=ubuntu --os-version=focal --mariadb-server-version=10.6The commands will add the repository and import GPG repository keys:
[info] Skipping OS detection and using OS type 'ubuntu' and version 'focal' given on the command line
[info] Checking for script prerequisites.
[info] Repository file successfully written to /etc/apt/sources.list.d/mariadb.list
[info] Adding trusted package signing keys...
[info] Running apt-get update...
[info] Done adding trusted package signing keysMariaDB APT repository file is located inside the /etc/apt/sources.list.d directory:
$ cat /etc/apt/sources.list.d/mariadb.list
# MariaDB Server
# To use a different major version of the server, or to pin to a specific minor version, change URI below.
deb [arch=amd64,arm64] https://dlm.mariadb.com/repo/mariadb-server/10.6/repo/ubuntu focal main
deb [arch=amd64,arm64] https://dlm.mariadb.com/repo/mariadb-server/10.6/repo/ubuntu focal main/debug
# MariaDB MaxScale
# To use the latest stable release of MaxScale, use "latest" as the version
# To use the latest beta (or stable if no current beta) release of MaxScale, use "beta" as the version
deb [arch=amd64] https://dlm.mariadb.com/repo/maxscale/latest/apt focal main
# MariaDB Tools
deb [arch=amd64] http://downloads.mariadb.com/Tools/ubuntu focal main
Step 3 – Install MariaDB 10.6 on Ubuntu 22.04
While trying to install mariadb-client package I encountered an error message:
mariadb-client-core-10.6 : Depends: libreadline5 (>= 5.2) but it is not installableThe work around was manually downloading and installing libreadline5 package for Debian Buster
wget http://ftp.us.debian.org/debian/pool/main/r/readline5/libreadline5_5.2+dfsg-3+b13_amd64.deb
sudo dpkg -i libreadline5_5.2+dfsg-3+b13_amd64.debThe remaining step is installation of MariaDB 10.6 Server and Client packages on Ubuntu 22.04:
sudo apt update
sudo apt install mariadb-server mariadb-clientAgree to continue with the installation of MariaDB 10.6 onUbuntu 22.04:
The following NEW packages will be installed:
galera-4 gawk libaio1 libcgi-fast-perl libcgi-pm-perl libdaxctl1 libdbd-mariadb-perl libdbi-perl libfcgi-bin libfcgi-perl libfcgi0ldbl libhtml-template-perl libmariadb3 libmysqlclient21
libndctl6 libpmem1 libsigsegv2 libterm-readkey-perl mariadb-client mariadb-client-10.6 mariadb-client-core-10.6 mariadb-common mariadb-server mariadb-server-10.6 mariadb-server-core-10.6
mysql-common socat
0 upgraded, 27 newly installed, 0 to remove and 0 not upgraded.
Need to get 29.5 MB of archives.
After this operation, 230 MB of additional disk space will be used.
Do you want to continue? [Y/n] yEnsure the service is set to start at system boot up
sudo systemctl start mariadb
sudo systemctl enable mariadb
Checking mariadb service status:
$ systemctl status mariadb
● mariadb.service - MariaDB 10.6.5 database server
Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
Drop-In: /etc/systemd/system/mariadb.service.d
└─migrated-from-my.cnf-settings.conf
Active: active (running) since Thu 2021-11-11 16:42:12 EAT; 3min 28s ago
Docs: man:mariadbd(8)
https://mariadb.com/kb/en/library/systemd/
Process: 8195 ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var/run/mysqld (code=exited, status=0/SUCCESS)
Process: 8196 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
Process: 8198 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_PO>
Process: 8261 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
Process: 8263 ExecStartPost=/etc/mysql/debian-start (code=exited, status=0/SUCCESS)
Main PID: 8245 (mariadbd)
Status: "Taking your SQL requests now..."
Tasks: 9 (limit: 9482)
Memory: 72.2M
CPU: 1.152s
CGroup: /system.slice/mariadb.service
└─8245 /usr/sbin/mariadbd
....
Step 4 – Secure MariaDB Database Server
Run mariadb-secure-installation to enable extra security for your MariaDB server:
$ sudo mariadb-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] Y
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!
Connect to MariaDB shell by authenticating with the root password set earlier:
$ mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 32
Server version: 10.6.5-MariaDB-1:10.6.5+maria~focal mariadb.org binary distribution
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)]>Run the following command to check MariaDB version
MariaDB [(none)]> SELECT VERSION();
+--------------------------------------+
| VERSION() |
+--------------------------------------+
| 10.6.5-MariaDB-1:10.6.5+maria~focal |
+--------------------------------------+
1 row in set (0.000 sec)
MariaDB [(none)]> QUIT
ByeStep 5 – Basic MariaDB Usage Examples
Here we will cover few examples on how to interact with MariaDB Database server from the command line.
Creating a database:
# Create a database named db1
MariaDB [(none)]> CREATE DATABASE db1;
Query OK, 1 row affected (0.000 sec)
MariaDB [(none)]> SHOW DATABASES;Create a database user and grant privileges;
#Create database user called myuser
MariaDB [(none)]> CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'mypassword';
#Grant all privileges to the user
MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'localhost' IDENTIFIED BY 'mypassword';
#Grant privileges to a specific database
MariaDB [(none)]> GRANT ALL PRIVILEGES ON 'DB1'.* TO 'user1'@'localhost';
#Remember to refresh the privileges
MariaDB [(none)]> FLUSH privileges;
#To check user grants in MariaDB
MariaDB [(none)]> SHOW GRANTS FOR 'myuser'@'localhost';Create a Table and Add Data MariaDB
Once you have created a database, you can create table and add data into it
MariaDB [(none)]> CREATE TABLE friends (id INT, name VARCHAR(20), email VARCHAR(20));
MariaDB [(none)]> INSERT INTO friends (id,name,email) VALUES(01,"John Kirui","[email protected]"This is the end of our guide on the installation of MariaDB 10.6 on Ubuntu 22.04. In our future guide we shall cover Master/Master, Master/Slave and Galera cluster setups of MariaDB on Ubuntu 22.04.
