MariaDB is an open source relational database management system that was originally forked from MySQL project. MariaDB is focused on reliability, stability, security, and performance. Among the reasons for choosing MariaDB as database server is its robustness and scalability. In fact, MariaDB is the default database server in most Linux distributions. If you need Mariadb HA setup, have a look at the MariaDB Galera Cluster project. In this guide, we shall cover complete steps to install MariaDB 10.7 on Ubuntu 20.04|18.04 from an APT repository.
New features of MariaDB 10.7
MariaDB 10.7 comes with a number of new features discussed below:
- InnoDB – A number of improvements in InnoDB storage engine
- UUID Data Type – New UUID data type is intended for the storage of 128-bit UUID (Universally Unique Identifier) data.
- Provider Plugins – This release of MariaDB contains five provider plugins (bzip2, lzma, lz4, lzo, snappy) which provides compression capabilities to the server and storage engines
- SFORMAT function – It takes an input string and a formatting specification and returns the string formatted using the rules the user passed in the specification.
- Natural Sort – The NATURAL_SORT_KEY function added in 10.7.0 release is used for sorting that is closer to natural sorting
- Password Reuse – The password_reuse_check plugin is a new password validation plugin that prevents the new password from being the same as the one being used during the configurable retention period.
- InnoDB Bulk Insert – In bulk insert, pre-sort and build indexes one page at a time
- Diagnostics Property – Used to identify the affected row
- See the MariaDB Foundation release announcement for more details on MariaDB 10.7.x
Step 1 – System Upgrade
Upgrading your system before performing installations is a proof nothing will break. Run the following commands to update all packages and OS.
sudo apt update && sudo apt upgrade -yWait for all upgrades to complete then reboot your Ubuntu Desktop or Server system
sudo systemctl rebootStep 2 – Install Dependency packages
Before we install MariaDB 10.7 on Ubuntu 20.04|18.04, we install packages required to add MariaDB repository and perform other installations. To accomplish this, execute the commands provided in your terminal as root or user with sudo permissions.
sudo apt update
sudo apt install curl apt-transport-https software-properties-common lsb-release ca-certificates gnupg2
Step 3 – Add MariaDB APT Repository to Ubuntu system
We need to import repository GPG key and add MariaDB repository. Run the commands below to add MariaDB signing key and add MariaDB APT repository:
curl -LsS -O https://downloads.mariadb.com/MariaDB/mariadb_repo_setup
sudo bash mariadb_repo_setup --mariadb-server-version=10.7Command execution output:
[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 keysUpdate packages list on configured sources to confirm they work
$ sudo apt update
Get:1 http://security.ubuntu.com/ubuntu focal-security InRelease [114 kB]
Hit:2 https://downloads.mariadb.com/Tools/ubuntu focal InRelease
Hit:3 http://nova.clouds.archive.ubuntu.com/ubuntu focal InRelease
Get:4 http://nova.clouds.archive.ubuntu.com/ubuntu focal-updates InRelease [114 kB]
Get:5 https://dlm.mariadb.com/repo/mariadb-server/10.7/repo/ubuntu focal InRelease [7707 B]
Get:7 http://nova.clouds.archive.ubuntu.com/ubuntu focal-backports InRelease [101 kB]
Hit:6 https://downloads.mariadb.com/MaxScale/6.1.4/apt focal InRelease
Fetched 336 kB in 1s (385 kB/s)
Reading package lists... Done
Building dependency tree
Reading state information... Done
All packages are up to date.Step 4 – Install MariaDB 10.7 on Ubuntu 20.04|18.04
After adding configuration of MariaDB APT repositories on Ubuntu, check availability ofMariaDB 10.7 on Ubuntu 20.04|18.04:
$ apt policy mariadb-server
mariadb-server:
Installed: (none)
Candidate: 1:10.7.1+maria~focal
Version table:
1:10.7.1+maria~focal 500
500 https://dlm.mariadb.com/repo/mariadb-server/10.7/repo/ubuntu focal/main amd64 Packages
500 https://dlm.mariadb.com/repo/mariadb-server/10.7/repo/ubuntu focal/main arm64 Packages
1:10.3.31-0ubuntu0.20.04.1 500
500 http://nova.clouds.archive.ubuntu.com/ubuntu focal-updates/universe amd64 Packages
500 http://security.ubuntu.com/ubuntu focal-security/universe amd64 Packages
1:10.3.22-1ubuntu1 500
500 http://nova.clouds.archive.ubuntu.com/ubuntu focal/universe amd64 Packages
We can confirm MariaDB 10.7 packages are listed in the repository added. We can proceed with the installation of MariaDB 10.7 on Ubuntu 20.04|18.04 using the commands:
sudo apt install mariadb-server mariadb-clientAccept all installation prompts by pressing y key
The following additional packages will be installed:
galera-4 libcgi-fast-perl libcgi-pm-perl libconfig-inifiles-perl libdbd-mariadb-perl libdbi-perl libencode-locale-perl libfcgi-perl libhtml-parser-perl libhtml-tagset-perl libhtml-template-perl
libhttp-date-perl libhttp-message-perl libio-html-perl liblwp-mediatypes-perl libmariadb3 libmysqlclient21 libpmem1 libterm-readkey-perl libtimedate-perl liburi-perl mariadb-client-10.7
mariadb-client-core-10.7 mariadb-common mariadb-server-10.7 mariadb-server-core-10.7 mysql-common socat
Suggested packages:
libclone-perl libmldbm-perl libnet-daemon-perl libsql-statement-perl libdata-dump-perl libipc-sharedcache-perl libwww-perl mailx mariadb-test
The following NEW packages will be installed:
galera-4 libcgi-fast-perl libcgi-pm-perl libconfig-inifiles-perl libdbd-mariadb-perl libdbi-perl libencode-locale-perl libfcgi-perl libhtml-parser-perl libhtml-tagset-perl libhtml-template-perl
libhttp-date-perl libhttp-message-perl libio-html-perl liblwp-mediatypes-perl libmariadb3 libmysqlclient21 libpmem1 libterm-readkey-perl libtimedate-perl liburi-perl mariadb-client
mariadb-client-10.7 mariadb-client-core-10.7 mariadb-common mariadb-server mariadb-server-10.7 mariadb-server-core-10.7 mysql-common socat
0 upgraded, 30 newly installed, 0 to remove and 0 not upgraded.
Need to get 29.4 MB of archives.
After this operation, 230 MB of additional disk space will be used.
Do you want to continue? [Y/n] y
Step 5 – Secure MariaDB 10.7 installation on Ubuntu
The mariadb service is started automatically as can be confirmed with the following command:
$ systemctl status mariadb
● mariadb.service - MariaDB 10.7.1 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 Mon 2021-11-22 21:41:36 UTC; 54s ago
Docs: man:mariadbd(8)
https://mariadb.com/kb/en/library/systemd/
Main PID: 50856 (mariadbd)
Status: "Taking your SQL requests now..."
Tasks: 15 (limit: 2344)
Memory: 74.4M
CGroup: /system.slice/mariadb.service
└─50856 /usr/sbin/mariadbd
Nov 22 21:41:37 frank-ubuntu-01 /etc/mysql/debian-start[50877]: sys
Nov 22 21:41:37 frank-ubuntu-01 /etc/mysql/debian-start[50877]: Phase 6/7: Checking and upgrading tables
Nov 22 21:41:37 frank-ubuntu-01 /etc/mysql/debian-start[50877]: Processing databases
Nov 22 21:41:37 frank-ubuntu-01 /etc/mysql/debian-start[50877]: information_schema
Nov 22 21:41:37 frank-ubuntu-01 /etc/mysql/debian-start[50877]: performance_schema
Nov 22 21:41:37 frank-ubuntu-01 /etc/mysql/debian-start[50877]: sys
Nov 22 21:41:37 frank-ubuntu-01 /etc/mysql/debian-start[50877]: sys.sys_config OK
Nov 22 21:41:37 frank-ubuntu-01 /etc/mysql/debian-start[50877]: Phase 7/7: Running 'FLUSH PRIVILEGES'
Nov 22 21:41:37 frank-ubuntu-01 /etc/mysql/debian-start[50877]: OK
Nov 22 21:41:37 frank-ubuntu-01 /etc/mysql/debian-start[51390]: Checking for insecure root accounts.Run the below command to enable MariaDB to automatically start when server is rebooted
sudo systemctl enable mariadbOnce MariaDB is installed, run security script to secure your database server – set root password, disable root user remote login, and remove test database.
$ 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!After securing process, you should only access MariaDB shell as root user by providing password:
$ mysql -u root
ERROR 1698 (28000): Access denied for user 'root'@'localhost'
$ mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 56
Server version: 10.7.1-MariaDB-1:10.7.1+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.Check MariaDB version and exit the shell.
MariaDB [(none)]> SELECT VERSION();
+-------------------------------------+
| VERSION() |
+-------------------------------------+
| 10.7.1-MariaDB-1:10.7.1+maria~focal |
+-------------------------------------+
1 row in set (0.000 sec)
MariaDB [(none)]> EXIT
ByeStep 6 – Create test database and user
We’ll create test database called mydb;
MariaDB [(none)]> CREATE DATABASE mydb;
Query OK, 1 row affected (0.000 sec)
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.000 sec)To create a user and grant privileges tomydb database;
# Create user called mydbuser
MariaDB [(none)]> CREATE USER 'mydbuser'@'localhost' IDENTIFIED BY 'StrongDBPassw0rd';
#Grant privileges database created
MariaDB [(none)]> GRANT ALL PRIVILEGES ON mydb.* TO 'mydbuser'@'localhost';
MariaDB [(none)]> FLUSH privileges;
#Check grants for a user
MariaDB [(none)]> SHOW GRANTS FOR 'mydbuser'@'localhost';
+-----------------------------------------------------------------------------------------------------------------+
| Grants for mydbuser@localhost |
+-----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `mydbuser`@`localhost` IDENTIFIED BY PASSWORD '*14F91886CA4A917D5B2DA4B12F5045F40ABA3171' |
| GRANT ALL PRIVILEGES ON `mydb`.* TO `mydbuser`@`localhost` |
+-----------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)
MariaDB [(none)]> EXIT
ByeTest connection to database as user mydbuser:
$ mysql -u mydbuser -p'StrongDBPassw0rd'
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 58
Server version: 10.7.1-MariaDB-1:10.7.1+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)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb |
+--------------------+
2 rows in set (0.000 sec)
MariaDB [(none)]> QUIT
ByeYou can create table and input test data into the database created. When done do cleanup;
$ mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 59
Server version: 10.7.1-MariaDB-1:10.7.1+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)]> DROP DATABASE mydb;
Query OK, 0 rows affected (0.002 sec)
MariaDB [(none)]> DROP USER `mydbuser`@`localhost`;
Query OK, 0 rows affected (0.001 sec)
MariaDB [(none)]> QUIT
ByeUninstalling MariaDB 10.7 on Ubuntu 20.04|18.04
If you want to completely uninstall MariaDB server and remove its data, you can use the following commands:
sudo apt purge mariadb-server
sudo rm -rf /var/lib/mysql/We have been able to install MariaDB 10.7 on Ubuntu 20.04|18.04. It is a short and easy installation and we hope it was helpful to you.
Udemy Video Courses to Learn MySQL / MariaDB Databases:
- The Ultimate MySQL Bootcamp: Go from SQL Beginner to Expert
- MySQL, SQL and Stored Procedures from Beginner to Advanced
- The Complete MySQL Developer Course
- SQL – MySQL for Data Analytics and Business Intelligence
- MySQL Database Administration: Beginner SQL Database Design
- SQL for Beginners: Learn SQL using MySQL and Database Design
- Learn Database Design with MySQL
