Databases: Part 2 — MySQL

MySQL — a relational DBMS that has remained one of the most widespread in web development since the late 1990s. WordPress, Joomla, Drupal, many online stores and forums run on it, and it is supported by virtually every hosting provider. Since 2010 MySQL has been owned by Oracle: the free Community Server is distributed under the GPLv2 license, the commercial Enterprise Edition is available by subscription.

Versions and support

Since 2023 Oracle releases MySQL in two streams:

• LTS (Long-Term Support) — stable branches that receive fixes and security updates for many years without changing functionality. Currently these are 8.4 LTS (supported until April 2032 including extended) and 9.7 LTS, released in spring 2026;
• Innovation — quarterly releases with new features, each supported only until the next one is released.

For production systems you should choose LTS. The 8.0 branch is no longer supported: extended support ended in April 2026, and servers on it need to be migrated to 8.4 or 9.7.

In September 2025 Oracle cut about 70 developers from the MySQL team; industry reports say the team was moved into the HeatWave cloud service unit. Some of the community perceived this as a sign that the free version will be developed more slowly. This is a strong reason to look at compatible alternatives.

Alternatives: MariaDB and Percona Server

• MariaDB — a fork of MySQL created in 2009 by one of MySQL’s authors, Michael Widenius. For a long time it was a direct replacement for MySQL, but over the years the branches have diverged: migration between modern versions is possible but now requires testing. Current MariaDB LTS branches are 10.11, 11.4, 11.8 and 12.3. In many Linux distributions the “mysql” package installs MariaDB by default.
• Percona Server for MySQL — a MySQL build from Percona, fully compatible with the original, with additional diagnostic tools and features that Oracle provides only in the Enterprise edition.

Where MySQL fits

• Sites on CMSs and frameworks. WordPress officially supports MySQL and MariaDB; for it this is the natural choice.
• Online stores and web services with read-heavy workloads: MySQL handles a large number of simple queries well.
• Small corporate systems — CRM, ticketing, internal services — if the application is designed for MySQL.

If a project is starting from scratch and there are no strict DBMS requirements, compare MySQL with PostgreSQL: PostgreSQL has a richer set of data types and extensions, and it is not dependent on a single company.

How MySQL works

Storage engines

MySQL stores tables using pluggable engines. By default InnoDB is used: it supports transactions, foreign keys, row-level locking and crash recovery. The older MyISAM engine does not support transactions, locks the whole table on write and may require table repairs after a crash. It should not be used for new tables, and old ones should sensibly be converted to InnoDB:

ALTER TABLE table_name ENGINE=InnoDB;

Replication

Replication copies changes from a primary server to one or more replicas. Starting with version 8.0.22 MySQL uses the terms source and replica instead of the deprecated master and slave; commands were also renamed: START REPLICA, SHOW REPLICA STATUS, CHANGE REPLICATION SOURCE TO.

Replicas are used for:

• distributing read load;
• a standby server in case the primary fails;
• taking backups without loading the primary server.

For automatic failover and writing to multiple nodes there is Group Replication and InnoDB Cluster built on it.

Basic configuration

Separate user for each application

An application should not connect to the database as root. Create a separate database and user for each application with rights only to it:

CREATE DATABASE shop CHARACTER SET utf8mb4;
CREATE USER 'shop'@'localhost' IDENTIFIED BY 'secure_password';
GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'shop'@'localhost';

If the application creates and alters tables during upgrades, add CREATE, ALTER, INDEX and DROP to the privileges. The @'localhost' part means connections are allowed only from the same server. Do not open access from any address ('%') unless necessary.

It’s better to set the character set to utf8mb4: the old utf8 in MySQL does not store all Unicode characters, for example it does not preserve emoji.

Backups

mysqldump creates a logical copy — a set of SQL commands from which the database is restored. For InnoDB tables the --single-transaction flag makes a consistent snapshot without blocking the application:

mysqldump --single-transaction --routines --triggers --events shop > shop.sql

Restore:

mysql shop < shop.sql

A logical copy is convenient for small databases and for migrating between versions, but for tens-of-gigabytes databases its creation and especially restoration can take hours. For large databases physical copying is used:

• Percona XtraBackup copies data files without stopping the server. The XtraBackup version must match the server branch: XtraBackup 8.4 works only with MySQL 8.4;
• MySQL Shell with utilities util.dumpInstance() and util.loadDump() creates and restores dumps using multiple threads.

A backup that has never been restored does not give confidence in data safety: restoration should be tested regularly on a separate server.

Administration tools

• mysql — the console client, included with the server.
• MySQL Shell — a modern client with SQL, JavaScript and Python modes, backup utilities and a server upgrade readiness check (util.checkForServerUpgrade()).
• phpMyAdmin and Adminer — web interfaces familiar to hosting users. Do not publish them on the Internet without additional protection (IP restrictions, separate authentication): bots constantly scan for them.
• MySQL Workbench and DBeaver — desktop programs for schema and query work.

Common mistakes

• Deprecated authentication method. The mysql_native_password plugin is disabled by default in MySQL 8.4 and removed in 9.0; caching_sha2_password is used by default. Old clients and drivers may stop connecting after a server upgrade — update them in advance.
• Default settings on a production server. The main InnoDB performance parameter is innodb_buffer_pool_size, the amount of memory for the data cache. On a dedicated DB server it is usually set to 50–75% of RAM.
• Port 3306 open to the Internet. The database server should be accessible only to the application: listen on 127.0.0.1 or an internal network, or be blocked by a firewall.
• Lack of indexes. Find slow queries via the slow query log (slow_query_log) and analyze them with EXPLAIN.
• Upgrading without testing. Moving between branches (8.0 → 8.4 → 9.7) should be done after checking compatibility on a copy of the database: default parameters have changed and some deprecated features have been removed.

Summary

MySQL is a mature DBMS with a huge ecosystem and a natural choice for sites on popular CMSs. For production systems use an LTS branch, set up backups with restoration testing, and monitor how Oracle develops the free version. If dependence on a single company is critical, compatible MariaDB and Percona Server allow changing the vendor without rewriting the application.