How to Install Mariadb on Debian 11 Server Tutorial (Step by Step)
How to Install Mariadb on Debian 11 Server. MariaDB is a replacement of MySQL with more features, new storage engines and better performance.
MySQL server is a popular database management system and also a large component of the LAMP stack. MySQL server has been replaced with MariaDB in Debian repositories, which is why MariaDB is a good alternative to MySQL.
What is MariaDB
MariaDB is one of the leading database servers across the globe. MariaDB aims to stay free and open source for users to benefit. Today, industry leaders such as Auto Europe, CCV, Samsung, NOKIA, Red Hat, Walgreens, Verizon, etc. leverage MariaDB to enhance their workloads and boost efficiencies.
MariaDB supports ACID style data processing and converts data into a structured form for an extensive range of applications. It ensures to retain data isolation, reliability, stability and atomicity for transactions.
MariaDB was originally developed in October 2009 as an improved, drop in fork for MySQL. It is scalable, fast and a highly robust solution comprising rich storage engines, powerful plugins and various other tools to suit users. It was designed with an objective to make sure the MySQL code base remains free forever.
MariaDB is an open source relational database management system (DBMS) that offers an SQL interface for users to access data. Its latest release has JSON/GIS features, Temporal Data Tables, Oracle Database compatibility features and advanced MariaDB clustering with Galera Cluster 4 as well. Some of the main highlights of MariaDB include parallel data replication, rich reams of storage engines including Cassandra, Aria, Spider, InnoDB, TokuDB, MyRocks and MariaDB ColumnStore.
Majority of the development of MariaDB aims to bridge the gap between features of MySQL and MariaDB. Most users can easily migrate to MariaDB by simply uninstalling MySQL to replace it with MariaDB installation. Most cloud storage providers include MariaDB and most Linux distributions also have it by default.
MariaDB Benefits
MariaDB embraces a versatile approach to keep up with the fluctuating database needs of today’s world. It has exclusively built, pluggable storage engines that easily work with processes without the need of a specialized database.
Enterprises can simply use one all inclusive, powerful database for their business specific needs, be it in the cloud or on prem. Deploy MariaDB within minutes to achieve unparalleled operational agility along with full SQL and ACID compliance. It is ideal for various hybrid cloud, analytical and transactional use cases.
Mariadb Pros
- Open source.
- MariaDB hosting is available at an affordable price, along with good documentation and online support portals.
- Works ideally for a CMS to publish large volumes of content without the need to set it up.
- Enables to write enquiries to the database direct or work in the source code of the website for seamless content publications.
- It runs under LGPL, BSD or GPL licenses.
- Supports a standard and prominent query language.
- Comprises advanced Galera Cluster technology.
- Supports PHP which is a popular web development language.
- Provides Backward Compatibility.
- Based on the MySQL Community Version.
- Powerful storage engines such as FederatedX, Maria, XtraDB, PBXT, InnoDB, TokuDB and so on that can integrate with other relational DBMS.
- Supports various SQL statements, rules, functions, structure and procedures, database backup, logs, server monitoring, server variables and SQL modes.
- Compatibility features with Oracle Database.
- Temporal Data Tables.
Follow this post to show you How to Install Mariadb on Debian 11 Server.
How to Install Mariadb on Debian 11 Server
Add MariaDB Repository
By default, the latest version of MariaDB is not included in the Debian 11 default repository. So it is recommended to add the MariaDB official repository to get the latest MariaDB version.
First, install all the required dependencies using the command given below:
apt-get install curl software-properties-common gnupg2 -y
Once all the packages are installed, download and MariaDB repository with the following command:
curl -LsS -O https://downloads.mariadb.com/MariaDB/mariadb_repo_setup
bash mariadb_repo_setup --os-type=debian --os-version=buster --mariadb-server-version=10.6
You will get the following output:
# [info] Skipping OS detection and using OS type 'debian' and version 'buster' as given on the command line
# [info] Checking for script prerequisites.
# [info] MariaDB Server version 10.6 is valid
# [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 keys
Next, download and install other dependencies using the following command:
wget http://ftp.us.debian.org/debian/pool/main/r/readline5/libreadline5_5.2+dfsg-3+b13_amd64.deb
dpkg -i libreadline5_5.2+dfsg-3+b13_amd64.deb
If you will get any dependency errors then resolve them with the following command:
apt-get update -y
Once the MariaDB repository is updated, you can proceed to install the MariaDB.
Install Mariadb on Debian 11
At this point, the MariaDB repository is up to date. You can now install the latest version of the MariaDB server and client package by running the following command:
apt-get install mariadb-server mariadb-client -y
Once both packages are installed, start the MariaDB service and enable it to start at system reboot:
systemctl start mariadb
systemctl enable mariadb
Next, you can verify the MariaDB installed version with the following command:
mysqladmin version
You should get the MariaDB version in the following output:
mysqladmin Ver 9.1 Distrib 10.6.7-MariaDB, for debian-linux-gnu on x86_64
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Server version 10.6.7-MariaDB-1:10.6.7+maria~buster
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /run/mysqld/mysqld.sock
Uptime: 15 sec
Threads: 1 Questions: 58 Slow queries: 0 Opens: 33 Open tables: 26 Queries per second avg: 3.866
You can also verify the MariaDB listening port with the following command:
ss -antpl | grep mariadb
You should see the MariaDB listening port in the following output:
LISTEN 0 80 127.0.0.1:3306 0.0.0.0:* users:(("mariadbd",pid=4595,fd=20))
Secure MariaDB Installation
By default, the Mariadb installation is not secured. So I would recommend to secure it using the mysql_secure_installation script:
mysql_secure_installation
You will be asked to provide your current MariaDB root password if you have set:
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):
If you have not set any password then just press Enter to continue. You will be asked to switch to the unix socket authentication method:
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] n
Type n and hit Enter to skip it. You will be asked to change the MariaDB root password:
... skipping.
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:
Set your MariaDB root password and press Enter. You will be asked to remove anonymous users.
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
Type Y and press Enter. You will be asked to disallow root login remotely:
... 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
Type Y and press Enter. You will be asked to remove the test database:
... 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
Type Y and press Enter. You will be asked reload the privilege tables:
... 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
Type Y and press Enter. Once the MariaDB is secured, you should get the following output:
... 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!
With this article about how to Install Mariadb on Debian 11 Server let’s move onto creating Maradb database and table.
Create a Database and Table in MariaDB
First, you will need to log in to the MariaDB console using the following command:
mysql -u root -p
You will be asked to provide your MariaDB root password:
Enter password:
Provide your MariaDB root password and press Enter. Once you are connected, you should get into the MariaDB shell as shown below:
MariaDB [(none)]>
Create MariaDB Database
To create Mariadb database called authors, run the following command:
MariaDB [(none)]> CREATE DATABASE authors;
To list all databases, run the following command:
MariaDB [(none)]> SHOW DATABASES;
You should see all databases in the following output:
+--------------------+
| Database |
+--------------------+
| authors |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
Now, switch the database to authors using the following command:
MariaDB [(none)]> USE authors;
Create MariaDB Table
Please create Mariadb table called names using the following command:
MariaDB [authors]> CREATE TABLE names (id int, name varchar(20), surname varchar(20));
Next, list your created table using the following command:
MariaDB [authors]> SHOW TABLES;
You should see the following output:
+-------------------+
| Tables_in_authors |
+-------------------+
| names |
+-------------------+
To display the table structure, run the following command:
MariaDB [authors]> DESCRIBE names;
You should see the following output:
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| surname | varchar(20) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
To insert some data in the first and second row, run the following command:
MariaDB [authors]> INSERT INTO names VALUES (1,"raj","shah");
MariaDB [authors]> INSERT INTO names VALUES (2,"vyom","singh");
If you want to display your table’s content, run the following command:
MariaDB [authors]> SELECT * FROM names;
You should get the following output:
+------+------+---------+
| id | name | surname |
+------+------+---------+
| 1 | raj | shah |
| 2 | vyom | singh |
+------+------+---------+
Backup and Restore MariaDB Database
MariaDB comes with a mysqldump command line utility that allows you to easily backup and restores your database and tables in the event of database corruption or system failure.
To back up a database named authors and create a backup file named authors.dump, run the following command:
mysqldump -u root -p authors > authors.dump
To back up all databases, run the following command:
mysqldump -u root -p --all-databases > alldb_back.dump
To restore a backup from the authors.dump backup file, run the following command:
mysql -u root -p authors < authors.dump
To restore all databases, run the following command:
mysql -u root -p < alldb_back.dump
Remove MariaDB Database
If you want to remove the MariaDB database from your server, run the following command:
apt-get remove mariadb-server --purge
After removing the MariaDB database, some other unwanted dependencies are still persist in your system. You can remove them by running the following command:
apt-get autoremove
Next, remove all package cache with the following command:
apt-get clean
How to Install Mariadb on Debian 11 Server Conclusion
In this post, you learned how to Install Mariadb on Debian 11 Server. I hope you can now easily create a database, tables, insert data into a table and use it with your web applications.
