This tutorial shows you how to install MariaDB on Debian 11.
Table of contents:
- 1. Install MariaDB
- 2. Verify MariaDB
- 3. Secure the MariaDB
- 4. Controlling MariaDB
- 5. Create root with password authentication(Optional)
- References
Summary steps
# update system
$ sudo apt update
# debian built-in mariadb
$ sudo apt install mariadb-server
# secure the mariadb for production
# follow prompts and answer y/n
$ sudo mariadb-secure-installation
# optional, create an admin with password authentication
> GRANT ALL ON *.* TO 'username'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION;
P.S Tested with Debian 11 and MariaDB 10.5.19.
1. Install MariaDB
When writing this, the Debian 11 software repositories have the built-in version 10.5.x.
$ sudo apt install mariadb-server
Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
......
2. Verify MariaDB
In the terminal, we can type mariadb -V to verify if we install the MariaDB successfully.
$ mariadb -V
mariadb Ver 15.1 Distrib 10.5.19-MariaDB, for debian-linux-gnu (x86_64) using EditLine wrapper
The above output shows that MariaDB version 10.5 is installed.
3. Secure the MariaDB
In the terminal, we can type sudo mariadb-secure-installation to secure the MariaDB server properly.
Follow the prompts, read CAREFULLY, and answer Y/n:
- Enter current password for root (enter for none): (enter)
- Switch to unix_socket authentication [Y/n] n
- Change the root password? [Y/n] n
- Remove anonymous users? [Y/n] y
- Disallow root login remotely? [Y/n] y
- Remove test database and access to it? [Y/n] y
- Reload privilege tables now? [Y/n] y
3.1 Enter current password for root (enter for none): enter
Read each step CAREFULLY, follows the RECOMMENDED action, and press enter for none; we don’t want to change the current password for the DB root user. This action turned off the password authentication for the DB root user. It enabled the unix socket authentication for the DB root user, which means the local root user (operating system root account) can connect to the MariaDB without knowing the db root user password.
# local root user can connect to the mariadb without password
$ sudo mysql -u root
$ 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...
3.2 Switch to unix_socket authentication [Y/n] n
The above step (3.1) already set the DB root user to use unix socket authentication; why ask again, confused? Anyway, answer No.
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
3.3 Remove anonymous users? [Y/n] y
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
3.4 Disallow root login remotely? [Y/n] y
If you answer No, you are a true hero.
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
3.5 Remove test database and access to it? [Y/n] y
Unless you have a reason to keep the test database.
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!
3.6 Reload privilege tables now? [Y/n] y
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!
4. Controlling MariaDB
We can use the command systemctl to control the MariaDB service.
$ sudo systemctl status mariadb
$ sudo systemctl start mariadb
$ sudo systemctl stop mariadb
$ sudo systemctl restart mariadb
$ sudo systemctl status mariadb
● mariadb.service - MariaDB 10.5.19 database server
Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
Active: active (running) since Mon 2023-08-07 16:24:05 +08; 13min ago
Docs: man:mariadbd(8)
https://mariadb.com/kb/en/library/systemd/
Process: 2604105 ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var/run/mysqld (code=exited, status=0/SUCCESS)
Process: 2604106 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
Process: 2604108 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= || VAR=`cd /usr/bin/..; /usr/bin/galera_recovery`; [ $? -eq 0 ] && systemctl set->
Process: 2604173 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
Process: 2604175 ExecStartPost=/etc/mysql/debian-start (code=exited, status=0/SUCCESS)
Main PID: 2604155 (mariadbd)
Status: "Taking your SQL requests now..."
Tasks: 9 (limit: 4675)
Memory: 73.6M
CPU: 556ms
CGroup: /system.slice/mariadb.service
└─2604155 /usr/sbin/mariadbd
Aug 07 16:24:05 server-tools systemd[1]: Started MariaDB 10.5.19 database server.
Aug 07 16:24:05 server-tools /etc/mysql/debian-start[2604177]: Upgrading MySQL tables if necessary.
Aug 07 16:24:05 server-tools /etc/mysql/debian-start[2604180]: Looking for 'mariadb' as: /usr/bin/mariadb
Aug 07 16:24:05 server-tools /etc/mysql/debian-start[2604180]: Looking for 'mariadb-check' as: /usr/bin/mariadb-check
Aug 07 16:24:05 server-tools /etc/mysql/debian-start[2604180]: This installation of MariaDB is already upgraded to 10.5.19-MariaDB.
Aug 07 16:24:05 server-tools /etc/mysql/debian-start[2604180]: There is no need to run mysql_upgrade again for 10.5.19-MariaDB.
Aug 07 16:24:05 server-tools /etc/mysql/debian-start[2604180]: You can use --force if you still want to run mysql_upgrade
Aug 07 16:24:05 server-tools /etc/mysql/debian-start[2604191]: Checking for insecure root accounts.
...
5. Create root with password authentication(Optional)
If we prefer a root user with password authentication, we can create an extra user with root privileges.
The below command creates a new user with root privileges and uses password authentication. Please changes the username and password to our preferences.
$ sudo mariadb
MariaDB [(none)]> GRANT ALL ON *.* TO 'username'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION;
For example, the below command will create a new user named admin with root privileges and be able to connect to db via password 123456.
GRANT ALL ON *.* TO 'admin'@'localhost' IDENTIFIED BY '123456' WITH GRANT OPTION;
Reflects the changes in the current session.
MariaDB [(none)]> FLUSH PRIVILEGES;
Bye, Done.
MariaDB [(none)]> EXIT;
References
About Author
