Upgrading MariaDB 10.5 to MariaDB 11.2 on Debian 12 Bookworm: Pre-flight Checks and Schema Migration Playbook

Pre-Upgrade Assessment: MariaDB 10.5 on Debian 11 Bullseye

Before embarking on the upgrade path from MariaDB 10.5 to 11.2, a thorough assessment of the current production environment is paramount. This involves understanding the existing schema, identifying potential compatibility issues, and ensuring the system is in a stable, backed-up state. Debian 11 “Bullseye” is the current OS, and we’ll be targeting Debian 12 “Bookworm” for the MariaDB 11.2 deployment.

1. Inventory and Baseline Snapshot

The first step is to document the current MariaDB setup. This includes version, configuration parameters, and a full schema dump. This serves as a rollback point and a reference for comparison.

1.1. Current MariaDB Version and Configuration

Connect to your current MariaDB instance and execute the following to capture the version and key configuration variables. Pay close attention to `innodb_file_per_table`, `character_set_server`, and any custom `sql_mode` settings.

SELECT VERSION();
SHOW VARIABLES LIKE 'innodb_file_per_table';
SHOW VARIABLES LIKE 'character_set_server';
SHOW VARIABLES LIKE 'collation_server';
SHOW VARIABLES LIKE 'sql_mode';
SHOW GLOBAL VARIABLES;
SHOW GLOBAL STATUS;

Additionally, locate and back up the MariaDB configuration file, typically found at /etc/mysql/my.cnf or within /etc/mysql/mariadb.conf.d/.

sudo cp /etc/mysql/my.cnf /etc/mysql/my.cnf.bak-$(date +%Y%m%d_%H%M%S)
sudo cp -r /etc/mysql/mariadb.conf.d/ /etc/mysql/mariadb.conf.d.bak-$(date +%Y%m%d_%H%M%S)

1.2. Full Schema and Data Dump

A comprehensive dump of all databases is critical. This dump will be used to restore the data on the new MariaDB 11.2 instance. Ensure you have sufficient disk space for this operation.

sudo mysqldump --all-databases --routines --triggers --events --single-transaction --flush-logs --master-data=2 -u root -p > /path/to/backups/mariadb_full_backup_$(date +%Y%m%d_%H%M%S).sql

The --master-data=2 option is useful for replication setups, as it includes the binary log file name and position in the dump. --single-transaction ensures a consistent snapshot for InnoDB tables without locking them for extended periods. Adjust the -u root -p flags if you use a different administrative user or authentication method.

2. Compatibility Checks: MariaDB 10.5 vs. 11.2

MariaDB 11.2 introduces significant changes and deprecations. Reviewing the official release notes and performing targeted checks on your schema is essential to avoid runtime errors post-migration.

2.1. Deprecated Features and Syntax

Key areas to scrutinize include:

Deprecated Storage Engines: While InnoDB and Aria are standard, older or less common engines might have been removed or deprecated.
SQL Mode Changes: The default `sql_mode` can evolve. Ensure your applications are not relying on behaviors that are now disabled by default.
Removed Features: Certain functions, system variables, or syntax might have been removed.
JSON Functions: MariaDB 11.x has enhanced JSON support. If you heavily use JSON, review the new functions and potential syntax changes.

Consult the official MariaDB 11.2 release notes for a definitive list of changes. For a programmatic check, you can analyze your schema dump for patterns that might indicate deprecated usage. For instance, searching for specific storage engine declarations or known problematic functions.

grep -i 'ENGINE=' /path/to/backups/mariadb_full_backup_*.sql | grep -v -i 'InnoDB\|Aria'
grep -i 'SET SQL_MODE=' /path/to/backups/mariadb_full_backup_*.sql

2.2. Character Set and Collation Consistency

Ensure your `character_set_server` and `collation_server` settings are consistent with your application’s requirements and that your schema uses compatible character sets (e.g., `utf8mb4`). Mismatches can lead to data corruption or unexpected sorting behavior.

3. Preparing the Target Environment: Debian 12 Bookworm

The new environment will be a clean Debian 12 “Bookworm” installation. This ensures a stable and supported base for MariaDB 11.2.

3.1. System Updates and Dependencies

Start by updating the system and installing necessary packages. MariaDB 11.2 has specific library dependencies.

sudo apt update && sudo apt upgrade -y
sudo apt install -y software-properties-common dirmngr apt-transport-https ca-certificates gnupg2

3.2. Adding the MariaDB Repository

To install MariaDB 11.2, we need to add the official MariaDB repository for Debian 12. This ensures we get the correct version and subsequent updates.

# Import the GPG key
sudo apt-key adv --fetch-keys 'https://mariadb.org/mariadb/repositories/pubkey.gpg'
# Add the repository
echo "deb [arch=amd64,arm64,ppc64el] https://dlm.mariadb.com/repo/mariadb-server/11.2/repo/debian bookworm main" | sudo tee /etc/apt/sources.list.d/mariadb.list

# Update package list again
sudo apt update

Note: The `apt-key` command is deprecated. For newer Debian/Ubuntu versions, it’s recommended to store the key in /etc/apt/trusted.gpg.d/. The above command still works for compatibility but consider the modern approach for new deployments.

# Modern approach for GPG key (if apt-key fails or for best practice)
curl -fsSL https://dlm.mariadb.com/repo/mariadb-server/11.2/repo/pubkey.gpg | sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/mariadb.gpg
# Then add the repo as above

3.3. Installing MariaDB Server

Now, install the MariaDB server package. This will install MariaDB 11.2 and its dependencies.

sudo apt install mariadb-server mariadb-client -y

4. Schema and Data Migration Playbook

This section details the steps to migrate your schema and data from the old MariaDB 10.5 instance to the new MariaDB 11.2 instance.

4.1. Initial MariaDB 11.2 Configuration

Before importing data, configure MariaDB 11.2. Copy relevant settings from your old configuration, but be mindful of deprecated or changed parameters. The main configuration file is typically /etc/mysql/mariadb.conf.d/50-server.cnf.

# Example: Adjusting character set and collation
[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

# Ensure innodb_file_per_table is enabled for better manageability
innodb_file_per_table = 1

# Review and adjust sql_mode based on your application's needs and MariaDB 11.2 defaults
# Example: sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"
# Consult MariaDB 11.2 documentation for recommended modes.

After modifying the configuration, restart the MariaDB service.

sudo systemctl restart mariadb

4.2. Importing the Schema and Data

Use the previously created full backup to populate the new MariaDB 11.2 instance. This process can be time-consuming depending on the database size.

sudo mysql -u root -p < /path/to/backups/mariadb_full_backup_YYYYMMDD_HHMMSS.sql

If you encounter errors during import, they will be logged. Examine the output carefully. Common issues include syntax errors in the dump file due to version incompatibilities or missing privileges.

4.3. Post-Import Schema Upgrade (if necessary)

MariaDB often performs automatic schema upgrades when it starts with a new data directory or detects an older format. However, for major version jumps, it’s good practice to explicitly run the upgrade process if prompted or if you suspect issues.

sudo mariadb-upgrade --user=root --password

This command checks all tables for incompatibilities and upgrades them if necessary. It’s crucial to run this after the data import and before extensive testing.

5. Verification and Testing

Thorough verification is the final, critical step before switching production traffic.

5.1. Schema and Data Integrity Checks

Verify that all databases, tables, routines, and triggers have been imported correctly. Compare row counts for critical tables between the old and new systems.

-- On the OLD MariaDB 10.5 instance:
SELECT COUNT(*) FROM your_database.your_table;

-- On the NEW MariaDB 11.2 instance:
SELECT COUNT(*) FROM your_database.your_table;

Also, check for any errors or warnings in the MariaDB error log (typically /var/log/mysql/error.log or /var/log/mariadb/mariadb.log) on the new server.

5.2. Application-Level Testing

This is the most important phase. Point a staging or development version of your application to the new MariaDB 11.2 instance and run through all critical application workflows. This includes:

User authentication and authorization.
Data creation, read, update, and delete (CRUD) operations.
Complex queries and reporting.
Any features relying on specific SQL functions or behaviors.

Monitor application logs for any database-related errors. If possible, use a load testing tool to simulate production traffic against the new database to uncover performance bottlenecks or concurrency issues.

5.3. Performance Tuning

After successful migration and initial testing, monitor the performance of MariaDB 11.2 under load. Review the `SHOW GLOBAL STATUS` output and compare it to baseline metrics from the old system. Adjust configuration parameters (e.g., buffer pool sizes, query cache settings if applicable, thread caches) as needed. MariaDB 11.2 may have new performance-related variables or defaults that warrant investigation.

6. Production Cutover Strategy

Plan the cutover carefully to minimize downtime.

6.1. Downtime Window and Communication

Schedule a maintenance window. Communicate this to all stakeholders well in advance. Ensure all necessary personnel are available during the cutover.

6.2. Final Synchronization and Switchover

For minimal downtime, consider setting up replication from the old 10.5 master to the new 11.2 instance *before* the cutover. Once the new server is fully tested and ready:

Stop writes to the old database.
Wait for replication to catch up on the new server.
Perform final verification checks on the new server.
Update application connection strings/DNS to point to the new MariaDB 11.2 server.
Resume application operations.

If replication is not feasible, the process involves stopping applications, performing the final dump/restore (or a delta sync if possible), and then switching over. This will incur more downtime.

6.3. Rollback Plan

Always have a documented rollback plan. This typically involves reverting application connection strings back to the original MariaDB 10.5 instance. Ensure the old instance remains available and functional until the new system is proven stable in production.