RDBMS with MySQL

Chapter 18 already equipped us with a fully running RDBMS on the server, complete with a database and dedicated application role for webapp_db. PostgreSQL is not the only player in the open source RDBMS world, and Developers coming from popular framework backgrounds like Laravel, WordPress, or various legacy PHP and Node.js stacks have almost certainly crossed paths with the name MySQL. This chapter continues Part V with MySQL as the second RDBMS we master, starting from its brief history branching into MariaDB, initial installation and securing via mysql_secure_installation, user and privilege management with an account model quite different from PostgreSQL, opening secure remote access, and understanding key changes brought by the migration from MySQL 8.0 to 8.4.

19.1 Brief History: MySQL vs MariaDB

MySQL was first released in 1995 by a Swedish company named MySQL AB, developed by three people including Michael "Monty" Widenius. The name MySQL itself originates from the name of one of the co-founder's children, My, a small piece of trivia often forgotten despite being directly related to the story of the fork we will discuss shortly. The ownership journey of MySQL changed drastically within a short period: Sun Microsystems acquired MySQL AB in 2008, then Oracle Corporation acquired Sun in 2010, placing MySQL under the control of one of the world's largest proprietary RDBMS vendors.

This transfer of ownership to Oracle triggered concerns among parts of the open source community, particularly regarding long-term development direction and the consistency of the GPL license that served as MySQL's foundation. Widenius responded to these concerns by executing a fork, copying the entire MySQL source code in 2009 to be developed independently under a new project named MariaDB, a name once again taken from his other child, Maria. MariaDB was designed to remain broadly compatible with MySQL across connection protocols, client drivers, and basic SQL syntax, allowing applications written for MySQL to generally run on MariaDB without code changes.

That compatibility does not last perfectly forever. Both projects continued to evolve separately for over a decade, each adding unique features, changing default settings, and even replacing parts of their command line tooling, as we will see when discussing MariaDB in Chapter 20. Ubuntu includes both RDBMSs in its official repositories as separate packages, but over recent releases, MariaDB has become the preferred default choice across several Ubuntu toolings, while MySQL remains fully available for Sysadmins requiring strict compatibility with the official Oracle ecosystem, such as enterprise applications explicitly requiring MySQL.

19.2 Installing MySQL 8.4 and mysql_secure_installation

Ubuntu 26.04 LTS provides MySQL 8.4 directly from its official repository, similar to PostgreSQL 18 in Chapter 18, without needing to add third-party Oracle repositories. MySQL 8.4 itself is the first Long Term Support (LTS) release under MySQL's new release model, a topic explored deeper in Section 19.5.

19.2.1 Package Installation from Ubuntu Repository

Practical Steps

1. Update the package list and install the MySQL server.

   sudo apt update
   sudo apt install mysql-server

   The mysql-server package is a meta-package that automatically pulls mysql-server-8.0 or its available derivative versions from the repository while pulling mysql-client as a dependency, resembling the postgresql meta-package pattern we already know.
2. The installation automatically creates a data directory at /var/lib/mysql, runs the service, and enables it on boot. Unlike PostgreSQL, which names its service according to the major version, the Ubuntu package for MySQL always uses the unit name mysql.service regardless of version, meaning status checking commands do not need to change with every MySQL upgrade.

   sudo systemctl status mysql

3. Confirm the exact installed version of MySQL.

   mysql --version

Verification and Troubleshooting

• A healthy systemctl status output shows active (running). If it fails to start, inspect the logs via sudo journalctl -u mysql -n 50 to locate the cause, typically a port 3306 conflict with another running MySQL/MariaDB instance.
• Unlike PostgreSQL's structure in Chapter 18 that separates versioned data and configurations per cluster, MySQL on Ubuntu keeps all data inside a flat directory /var/lib/mysql, while configurations are spread across /etc/mysql/mysql.conf.d/mysqld.cnf included via the main file /etc/mysql/my.cnf. Remembering this structural difference is important as it affects how we locate the right files during troubleshooting.
• The minor version number displayed in mysql --version may differ depending on when the Ubuntu repository was last synchronized, the same note as the PostgreSQL version in Section 18.1.2.

19.2.2 Securing Installation with mysql_secure_installation

The mysql-server package on Ubuntu configures the root account using the auth_socket authentication method, a plugin matching the logged-in OS username with the target MySQL account name, functioning similarly to PostgreSQL's peer authentication in Section 18.2.1. Consequently, the MySQL root account has no password from the start of installation, and the only way to log in as root is through the Unix socket using sudo. The mysql_secure_installation script helps Sysadmins close remaining default security vulnerabilities following installation.

Practical Steps

1. Log in as root using the socket first, proving how auth_socket functions before running the security script.

   sudo mysql

   Type exit to leave after the mysql> prompt appears without asking for a password.
2. Run the installation security script.

   sudo mysql_secure_installation

   This script first offers the validate_password component, an optional plugin enforcing password complexity rules (minimum length, uppercase/lowercase combination, numbers, and special characters) for every newly created account thereafter. Answering no to this prompt is safe for lab environments since root uses no password at all, but production servers hosting multiple accounts should enable it and choose level MEDIUM or STRONG. Afterwards, the script detects root using auth_socket and directly skips setting a password for it, marked by a message reading as follows:

   Securing the MySQL server deployment.
   Skipping password set for root as authentication with auth_socket is used by default.

3. The script then interactively asks a series of Y/n questions. Answer Y to all of the following questions to close unneeded exposure points on a production server.

   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

Verification and Troubleshooting

• The closing message Success. All done! indicates all steps were applied successfully.
• Answering Y to Disallow root login remotely has no technical impact here, as root is already locked strictly to auth_socket, which naturally cannot be used over external networks. This question is more relevant for servers where the root account was assigned a password via mysql_native_password or caching_sha2_password.
• Field note: web-based management tools like phpMyAdmin (which we will set up in Chapter 22) cannot use auth_socket at all because their connections always go over TCP rather than a direct Unix socket from the PHP process. A dedicated account with a password, such as the webapp_user created in Section 19.3, remains mandatory for such needs.

19.3 User and Privilege Management

The scenario in this section continues the same Developer requirements from Chapter 18: an application database named webapp_db, this time in MySQL, along with an application account named webapp_user separate from root. The way MySQL models users differs slightly from PostgreSQL, a distinction crucial to understand before running the first GRANT.

19.3.1 The user@host Account Model in MySQL

PostgreSQL combines user and group concepts into a single role, whereas MySQL uses a model called an account, a pair consisting of username and connection source host written as 'user'@'host'. Two accounts sharing the same username but different hosts, such as 'webapp_user'@'localhost' and 'webapp_user'@'192.168.1.20', are treated by MySQL as entirely separate accounts with independent passwords and privileges, not a single user logging in from two locations. Host values support the wildcard % representing any host, as well as CIDR notation like 192.168.1.0/24 since MySQL 8.0.23, similar to subnet notation in pg_hba.conf practiced in Section 18.4.1.

19.3.2 Creating Application Database and User

Practical Steps

1. Log into MySQL as root via socket.

   sudo mysql

2. Create a new database for the application.

   CREATE DATABASE webapp_db;

3. Create a new account specifically for local connections, complete with a password. Replace this sample password value with your own strong password before applying it on a real server.

   CREATE USER 'webapp_user'@'localhost' IDENTIFIED BY 'S4ngatRahasia!2026';

4. Grant full privileges on webapp_db to the account, then apply changes.

   GRANT ALL PRIVILEGES ON webapp_db.* TO 'webapp_user'@'localhost';
   FLUSH PRIVILEGES;

5. Exit the root session and test logging in with the new account.

   exit
   mysql -u webapp_user -p webapp_db

Verification and Troubleshooting

• A successful login is marked by the prompt changing to mysql> with database webapp_db automatically selected. Execute SELECT DATABASE(), CURRENT_USER(); to confirm the session is connected as webapp_user@localhost.
• Executing FLUSH PRIVILEGES is technically optional after GRANT or CREATE USER in MySQL 8, as both commands automatically update privilege tables directly. This command is strictly required only when privileges are modified through direct manipulation of the mysql.user system table, an older habit rarely used now but still common in tutorials as a traditional safety measure.
• The message ERROR 1045 (28000): Access denied for user 'webapp_user'@'localhost' usually implies a mistyped password, whereas ERROR 1049 (42000): Unknown database 'webapp_db' indicates the CREATE DATABASE command in the previous step was not executed.

19.3.3 Granular Privileges for Read-Only Roles

Just like the reporting tools or BI dashboard requirements discussed in Section 18.3.2, accounts restricted to reading data without modification permissions are common in MySQL environments, typically used for monitoring or business reporting managed by Developer teams separate from transaction processing teams.

CREATE USER 'webapp_readonly'@'localhost' IDENTIFIED BY 'B4caSaja!2026';
GRANT SELECT ON webapp_db.* TO 'webapp_readonly'@'localhost';

The SELECT privilege granted via the webapp_db.* pattern automatically covers all existing tables within that database, including tables created after the GRANT command runs, differing from PostgreSQL's ALL TABLES IN SCHEMA privilege which limits scope to existing tables. This difference in scope often trips up Sysadmins accustomed to PostgreSQL who assume identical behavior in MySQL.

Verification and Troubleshooting

• The command SHOW GRANTS FOR 'webapp_readonly'@'localhost'; displays all privileges assigned to the account, a quick way to audit access rights without re-reading previous GRANT history.
• Logging in as webapp_readonly and trying an INSERT or UPDATE statement must fail with ERROR 1142 (42000): INSERT command denied to user, verifying granular privileges are working as intended.

19.4 Secure Remote Connections

Like PostgreSQL in Section 18.4, MySQL listens by default only on loopback addresses, an intentional security choice keeping database servers unexposed to external networks upon installation completion. Opening remote access requires three steps following the same pattern: changing the address MySQL listens on, creating an account with matching host definitions, and allowing traffic through the firewall.

19.4.1 Enabling bind-address and Firewall

Practical Steps

1. Open MySQL's main configuration file and locate the bind-address directive, set to 127.0.0.1 by default.

   sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

   Modify the line so MySQL listens on all server network interfaces.

   bind-address = 0.0.0.0

2. The bind-address directive is evaluated only when the mysqld process boots up, making a full restart necessary rather than a simple configuration reload.

   sudo systemctl restart mysql

3. Add a UFW rule allowing traffic to port 3306 specifically from the lab network 192.168.1.0/24, matching the restriction pattern used for port 5432 in Section 18.4.2.

   sudo ufw allow from 192.168.1.0/24 to any port 3306 proto tcp

Verification and Troubleshooting

• Confirm MySQL is active across all network interfaces.

  ss -tlnp | grep 3306

  Correct output shows the address 0.0.0.0:3306 instead of 127.0.0.1:3306.
• The core principle from Section 18.4.1 applies here: opening bind-address to 0.0.0.0 and limiting access through firewalls and host-restricted accounts is far safer than exposing port 3306 directly to public networks without rules.

19.4.2 Creating an Account for Remote Hosts

Practical Steps

1. Log back in as root via socket, then create a new account dedicated to connections from the lab network, separate from the existing 'webapp_user'@'localhost'.

   sudo mysql

   CREATE USER 'webapp_user'@'192.168.1.0/24' IDENTIFIED BY 'S4ngatRahasia!2026';
   GRANT ALL PRIVILEGES ON webapp_db.* TO 'webapp_user'@'192.168.1.0/24';

Verification and Troubleshooting

• Executing SELECT user, host FROM mysql.user WHERE user = 'webapp_user'; should output two entries, localhost and 192.168.1.0/24, proving they exist as two separate accounts as explained in Section 19.3.1.

19.4.3 Testing Connections from Remote Network Clients

Practical Steps

1. From another machine within the same lab network, install only the MySQL client software without full server packages.

   sudo apt install mysql-client

2. Attempt connecting to the database server using IP 192.168.1.20 with the newly generated remote account.

   mysql -h 192.168.1.20 -u webapp_user -p webapp_db

Verification and Troubleshooting

• A successful connection prompts for a password and then displays mysql>, identical to localhost testing in Section 19.3.2, except traffic travels across the network.
• The error ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.1.20' indicates MySQL is not listening on network interfaces or the firewall blocks port 3306; re-check Section 19.4.1.
• The error ERROR 1130 (HY000): Host '...' is not allowed to connect to this MySQL server means client IP addresses do not match registered hosts in mysql.user, either due to subnet typos during CREATE USER or client location outside 192.168.1.0/24.
• Network connections across internal lab environments rely on raw TCP traffic. By default, modern MySQL clients attempt negotiating TLS encrypted connections first before falling back to unencrypted sessions if unsupported, behavior known as PREFERRED mode. Mandating full encryption without plaintext fallbacks requires explicitly enabling REQUIRE SSL on target accounts or setting the system variable require_secure_transport server-wide to block unencrypted TCP connections.

19.5 MySQL 8.0 to 8.4 Migration Notes

MySQL 8.0 debuted in 2018 and remained the primary major release for years through minor update series. Oracle overhauled its release strategy starting with MySQL 8.4: Innovation releases launch roughly every three months featuring new capabilities supported only until the subsequent Innovation release, while Long Term Support (LTS) releases like 8.4 drop less frequently but gain security support for multiple years, closely mirroring Ubuntu's LTS strategy. MySQL 8.4 serves as the inaugural LTS release under this model, matching the default package shipped in Ubuntu 26.04 installed in Section 19.2.

Sysadmins migrating legacy applications from MySQL 8.0 to 8.4 should account for key updates, including:

• The mysql_native_password plugin is no longer loaded by default. Older application drivers relying on this legacy authentication method may fail to connect post-migration unless explicitly re-enabled or migrated to caching_sha2_password, the default plugin configured during installation in Section 19.2.
• Multiple system variables have been removed, including innodb_log_file_size and innodb_log_files_in_group replaced by innodb_redo_log_capacity, along with all variables prefixed with slave_* updated to replica_* equivalents, aligning with Oracle's ongoing standard terminology adjustments across modern MySQL documentation.
• Legacy query cache system variables are completely removed. The underlying query cache feature was dropped in MySQL 8.0, but legacy parameters like query_cache_size and query_cache_type were temporarily retained as no-op variables to avoid breaking legacy configuration files. MySQL 8.4 fully removes these legacy parameters, meaning legacy configurations containing them must be cleaned from my.cnf prior to upgrading, as MySQL will fail to start when encountering unrecognized parameters.

Oracle's recommended upgrade path involves an in-place upgrade using normal APT package mechanisms on systems running the latest minor release of MySQL 8.0, rather than full dump-and-restore procedures. Sysadmins must review official release notes and perform staging tests prior to production deployments, maintaining established operational standards applied throughout this series.

Verifying active engine versions can be completed using the MySQL client, offering a straightforward verification method following upgrade execution.

mysql -u root -p -e "SELECT VERSION();"

At this stage, the server is running MySQL 8.4 equipped with webapp_db managed by dedicated application credentials, separated from socket-locked root accounts, and securely exposed across local subnets using host-based permissions paired with firewall policies. Chapter 20 continues Part V covering MariaDB, exploring the open-source fork mentioned earlier alongside its role as Ubuntu's default database option.