RDBMS with MariaDB

Chapter 19 closes its discussion through a fork: Widenius copied the MySQL source code in 2009 and developed it independently into MariaDB after Oracle acquired Sun Microsystems. Chapter 20 continues the discussion by practicing directly with the forked RDBMS, which is currently the default choice and prioritized by Ubuntu over MySQL in several of its official toolings. We will install MariaDB 11.8, get to know its native command line toolchain that has abandoned the mysql* naming, manage users and privileges through role features not possessed by MySQL, and understand one important breaking change in mariadb-dump that can cause the database restore process to fail completely if not anticipated.

20.1 Installing MariaDB 11.8 on Ubuntu 26.04

Ubuntu 26.04 LTS provides MariaDB 11.8 directly from its official repository, without needing to add third-party repositories belonging to the MariaDB Foundation. MariaDB 11.8 is a Long Term Support (LTS) release with security support until June 2028, replacing MariaDB 11.4 as the previous LTS version. This release brings many new features compared to version 11.4, ranging from the VECTOR data type for AI-based similarity search requirements, the default utf8mb4 character set for full Unicode support including emojis, to a new authentication plugin named PARSEC.

20.1.1 Package Installation from the Ubuntu Repository

Hands-on Steps

1. Update the package list, then install the MariaDB server.

   sudo apt update
   sudo apt install mariadb-server

   The mariadb-server package downloads mariadb-server-core and mariadb-client as dependencies, following the same meta-package pattern as mysql-server in Section 19.2.1.
2. The installation process automatically creates the data directory at /var/lib/mysql, which is the exact same path as MySQL because both share the same data structure legacy since before the fork occurred. Confirm that the service is running through the mariadb.service unit.

   sudo systemctl status mariadb

3. Confirm the version that is actually installed.

   mariadb --version

   The output that appears formatted roughly as mariadb from 11.8.x-MariaDB, with the minor number varying depending on the last time the Ubuntu repository was synchronized.

Verification and Troubleshooting

• A normal systemctl status output displays an active (running) status. If it fails to start, check the logs via the command sudo journalctl -u mariadb -n 50.
• Crucial field note: MariaDB and MySQL share the /var/lib/mysql data directory as well as the exact same 3306 default port. These two services must not run simultaneously on the same server. If the lab machine being used still has a residual MySQL installation from Chapter 19, stop and purge that package first before installing MariaDB, or use separate VMs/containers for each database chapter in this Part V.

  sudo systemctl stop mysql
  sudo apt purge mysql-server mysql-client mysql-common

• Unlike MySQL which names its daemon binary mysqld across all versions, MariaDB names its daemon mariadbd, with mysqld still provided as a symlink for compatibility, following the same pattern as the command line tools symlinks discussed in Section 20.2.

20.1.2 Securing the Installation with mariadb-secure-installation

Since MariaDB 10.4, the unix_socket authentication plugin has been active by default for the root@localhost account, just like auth_socket in MySQL that we practiced in Section 19.2.2. Consequently, MariaDB's default installation security script no longer needs to ask about migrating to unix_socket as in older versions, because that condition is automatically met as soon as the installation process completes.

Hands-on Steps

1. First, prove how unix_socket works by logging in as root without using a password.

   sudo mariadb

   Type exit to leave after the MariaDB [(none)]> prompt appears.
2. Run the installation security script.

   sudo mariadb-secure-installation

3. The script will display a series of Y/n questions sequentially. Answer according to the following table for a safe lab environment to practice in.

   Enter current password for root (enter for none): [press Enter directly]
   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

   Answering n to the root password change question is the right choice here, because root has been locked via unix_socket and does not require a password to be used locally by Sysadmins.

Verification and Troubleshooting

• The closing message Thanks for using MariaDB! indicates that all steps have been successfully applied.
• The command mysql_secure_installation without the mariadb- prefix can still be used and produces identical output, because both are currently just two names for the same binary, as discussed in more detail in Section 20.2.
• Same as the note in Section 19.2.2, accounts with passwords must still be prepared separately from root for web-based tool requirements like phpMyAdmin in Chapter 22, because unix_socket only works for local connections via Unix socket, not via TCP.

20.2 Native Toolchain and the Phase-out of mysql* Symlinks

Sysadmins accustomed to typing mysql, mysqldump, or mysqladmin commands on a MySQL server will find something different when typing the same commands on a modern MariaDB server. Since MariaDB 10.5, the majority of command line tools have been renamed to mariadb-* so that the project's separate identity becomes clearer from MySQL, as part of the MariaDB Foundation's effort to build its own ecosystem after more than a decade of maintaining full compatibility with the old names.

20.2.1 Command Line Tools Name Changes Since MariaDB 10.5

The following table summarizes the mapping of old names to new names that Sysadmins need to remember when writing new scripts or documentation for MariaDB servers.

Ubuntu still installs all of those old names as symlinks pointing to the new binaries, so that legacy scripts, third-party tools, or typing habits of Sysadmins that have spanned years do not immediately break after the MariaDB installation finishes.

20.2.2 Proving Symlinks and Deprecation Warnings

Hands-on Steps

1. Directly inspect the target of the mysql symlink inside the binary directory.

   ls -la /usr/bin/mysql /usr/bin/mariadb

   The output shows that /usr/bin/mysql is a symlink pointing to /usr/bin/mariadb, not a separate binary.
2. Run the old command to see the deprecation warning that appears.

   mysql --version

   mysql: Deprecated program name. It will be removed in a future release, use '/usr/bin/mariadb' instead
   mariadb from 11.8.x-MariaDB

Verification and Troubleshooting

• The warning is printed to stderr and does not halt command execution, so legacy automation scripts that still call mysql or mysqldump continue to function normally for now, it's just that their output becomes a bit noisy due to the additional warning line.
• Field note: because this warning explicitly mentions a planned removal in a future release, habits when writing new backup, monitoring, or CI/CD scripts should immediately use the mariadb-* name from the start, rather than delaying migration until those symlinks are actually removed from official repositories.
• If a third-party tool (such as a hosting panel or database migration tool) fails to parse output due to this extra warning line, the safest option is to call the mariadb-* binary directly in that tool's configuration, rather than disabling the warning.

20.3 User, Privilege, and Role Management

The scenario in this section carries forward the same Developer requirements as in Chapters 18 and 19: an application database named webapp_db, this time on a MariaDB instance, along with a separate account from root. MariaDB inherits MySQL's 'user'@'host' account model discussed in Section 19.3.1 in its entirety, but adds one feature that MySQL Community Edition lacks: full roles for reusable privilege grouping.

20.3.1 Creating Application Database and User

Hands-on Steps

1. Log in to MariaDB as root via socket.

   sudo mariadb

2. Create a new database for the application.

   CREATE DATABASE webapp_db;

3. Create a new account dedicated to local connections. Replace this example password value with your own strong password before practicing on a real server.

   CREATE USER 'webapp_user'@'localhost' IDENTIFIED BY 'M4riaAmanSekali!2026';
   GRANT ALL PRIVILEGES ON webapp_db.* TO 'webapp_user'@'localhost';

4. Exit the root session, then test logging in using the new account.

   exit
   mariadb -u webapp_user -p webapp_db

Verification and Troubleshooting

• A successful login is marked by the prompt changing to MariaDB [webapp_db]>. Run the command SELECT DATABASE(), CURRENT_USER(); to ensure the session is truly connected as webapp_user@localhost.
• New accounts created via the CREATE USER ... IDENTIFIED BY command use the mysql_native_password plugin by default, unlike root which uses unix_socket. This behavior is consistent with the explanation in Section 20.1.2: unix_socket is only automatically used for the default installation's root@localhost, not for accounts created manually afterwards.
• Error messages that appear follow an identical format to MySQL in Section 19.3.2, because MariaDB inherited all error codes from the MySQL codebase prior to the fork.

20.3.2 Role-Based Access Control with CREATE ROLE

MySQL Community only gained role features in version 8.0, whereas MariaDB has had them since version 10.0.5. Roles separate the definition of a set of privileges from the accounts that use them, allowing Sysadmins to simply change privileges in one place (the role) rather than repeating the same GRANT commands across multiple accounts individually. This pattern is very helpful when managing Developer teams with a growing number of members.

Hands-on Steps

1. As root, create a new role for read-only access needs.

   sudo mariadb

   CREATE ROLE 'app_readonly';
   GRANT SELECT ON webapp_db.* TO 'app_readonly';

2. Create a new account for reporting team members, then grant that role to it.

   CREATE USER 'webapp_readonly'@'localhost' IDENTIFIED BY 'B4caSajaMaria!2026';
   GRANT 'app_readonly' TO 'webapp_readonly'@'localhost';

3. Make this role automatically active every time webapp_readonly logs in, without needing to manually type SET ROLE in every session.

   SET DEFAULT ROLE 'app_readonly' FOR 'webapp_readonly'@'localhost';

Verification and Troubleshooting

• Log in as webapp_readonly, then inspect the currently active role in the running session.

  mariadb -u webapp_readonly -p webapp_db -e "SELECT CURRENT_ROLE();"

  The result must display app_readonly, not NULL, proving that the default role works without needing to manually execute SET ROLE.
• Privileges granted to a role do not automatically take effect for the user holding it unless that role is active in the session, either through a default role as above or via the SET ROLE 'app_readonly'; command run manually at the start of the session. A common mistake for Sysadmins transitioning from MySQL is forgetting to activate the default role, then wondering why GRANT to a role seems to have no effect on the user holding it.
• Adding new privileges to an existing role, for example GRANT SELECT ON another_db.* TO 'app_readonly';, automatically applies to all users holding that role without needing to repeat the GRANT one by one to each account. This is the primary advantage of roles over assigning privileges directly to users as in Section 20.3.1.

20.3.3 Auditing Privileges with SHOW GRANTS

The audit process regarding user access rights becomes far more important as the number of users and roles grows, especially leading up to compliance or security audit processes discussed in more detail in Chapter 34.

SHOW GRANTS FOR 'webapp_readonly'@'localhost';
SHOW GRANTS FOR 'app_readonly';

Verification and Troubleshooting

• The first command displays direct privileges belonging to webapp_readonly along with the roles it holds, while the second command displays the privilege contents of the app_readonly role itself, which is a quick way to separate direct privileges from privileges acquired through roles.
• Logging in as webapp_readonly and then attempting an INSERT command should fail with the message ERROR 1142 (42000): INSERT command denied to user. This proves that read-only privileges are truly enforced via the role, not merely appearing in SHOW GRANTS without actual effect.

20.4 Breaking Change in mariadb-dump Format Since Version 11.0

The name mysqldump was officially removed entirely in MariaDB 11.0 and replaced by mariadb-dump as the sole native name, consistent with the rename pattern discussed in Section 20.2.1. The name change itself is actually harmless because compatibility symlinks remain available; however, there is another change that is far more risky, quietly causing database restore processes to fail if Sysadmins do not realize it beforehand.

20.4.1 Sandbox Mode: Security Vulnerability Mitigation in Restore Processes

Starting from MariaDB 10.5.25, 10.6.18, 10.11.8, 11.0.6, 11.1.5, 11.2.4, 11.4.2, and all subsequent releases including version 11.8 used in this chapter, mariadb-dump automatically inserts a specific directive line on the first line of every generated dump file.

/*!999999\- enable the sandbox mode */

This directive is an official mitigation tracked by MariaDB via internal ticket MDEV-21778, closing a security vulnerability in dump clients that theoretically allows crafted dump files from malicious parties to execute dangerous shell commands when restored through command line clients. Several third-party security reports associate this vulnerability with CVE-2024-21096 belonging to Oracle MySQL's mysqldump client, considering that mariadb-dump inherited the same codebase prior to the fork, although MariaDB itself does not explicitly state that CVE number in its official announcement. When this directive is read by a mariadb client version that supports it, the restore session will immediately enter sandbox mode, which blocks all client commands like \! or system that could touch the shell, ensuring that dump files embedded with dangerous commands can never execute them.

Hands-on Steps

1. Create a dump of the webapp_db database created in Section 20.3.1. Include the --single-transaction option so that InnoDB table dumps are taken from a single consistent transaction snapshot, without locking tables and temporarily halting write access for webapp_user.

   mariadb-dump -u root --single-transaction webapp_db > webapp_db.sql

   Field note: omitting this option on production servers where tables are actively receiving transactions is a mistake often overlooked by new Sysadmins, because the dump still succeeds without explicitly locking tables, but data consistency is not guaranteed if transactions run concurrently.
2. Inspect the first line of the resulting dump file to prove that the sandbox mode directive is indeed present.

   head -n 3 webapp_db.sql

Verification and Troubleshooting

• The first line of the file must read exactly /*!999999\- enable the sandbox mode */. This /*!999999 ... */ comment format is actually an old MySQL/MariaDB trick to hide commands from server versions too old to recognize them, used here for security purposes rather than just feature compatibility as usual.
• The problem arises when this dump file is restored using a client older than the versions mentioned above, or using the default mysql/mysqldump client from MySQL. Both types of clients do not recognize the \- syntax inside that directive and will immediately throw an error, halting the entire restore process just because of the failed first line parsing.

  ERROR 1064 (42000) at line 1: You have an error in your SQL syntax

• This scenario most commonly traps Sysadmins moving dumps from a new MariaDB server to an older MySQL server, an unpatched legacy MariaDB server, or third-party migration tools whose internals still call the classic mysql client. The combination of automated backup scripts and infrequently tested restore processes, as cautioned later in Chapter 23, means this issue is often only detected when an emergency restore is urgently needed.

20.4.2 Handling Incompatibilities During Restore

The safest way to avoid this problem is to ensure the restore process uses a mariadb client whose version supports sandbox mode, ideally the same or newer version than the server that generated the dump file. If the restore target must be MySQL or an older MariaDB version that does not support this directive, that first line can be removed before running the restore process.

Hands-on Steps

1. Remove the first line of the dump file prior to restoring by using the tail command to skip it.

   tail -n +2 webapp_db.sql | mariadb -u root webapp_db

2. The same pattern applies if directive removal is done directly during the dump process rather than during restore. This pattern is useful for backup pipelines specifically targeted for legacy system compatibility.

   mariadb-dump -u root webapp_db | tail -n +2 > webapp_db_compat.sql

Verification and Troubleshooting

• After the restore process succeeds, confirm that all tables and data are fully intact by comparing row counts on important tables, rather than relying solely on the absence of error messages.
• Removing the sandbox mode directive means waiving the security mitigation layer described in Section 20.4.1. This step should only be taken for dump files whose source is fully trusted, such as scheduled backups from your own server, not dump files received from external parties or unverified sources.
• A better long-term solution than continuously trimming the first line is to standardize client versions across the entire backup and restore pipeline, including staging servers and third-party migration tools, so that the sandbox mode directive is consistently recognized everywhere.

At this point, the server is running MariaDB 11.8 complete with the webapp_db database managed through a combination of accounts and roles, neatly separated from root which is locked via unix_socket, along with a backup process safe from the latest mariadb-dump format issues. Chapter 21 continues Part V with a different approach: MongoDB as a document-based NoSQL database, no longer relying on tables and rows like the three relational RDBMSs we studied in PostgreSQL, MySQL, and MariaDB.