Sunday, November 1, 2026

Upgrading a MariaDB Master/Slave Pair in Production

I'm an Oracle DBA by background, and most of my years have gone into Oracle EBS and Exadata. Lately I've been looking after MariaDB too. Recently I took a production MariaDB master/slave pair from 11.4.4 to 11.4.13. The data was about 640 GB, running on RHEL 8 in AWS, and the plan was a full outage with no application traffic.

It was my first MariaDB upgrade on a replicated production pair. It also took two attempts, because the first window was postponed. Along the way DEV and SIT taught me several lessons the hard way.

This post covers the lessons, the runbook, and the health check script I used. Hostnames in this article are generic placeholders: "db-primary" is the master and "db-replica" is the slave.

The setup

  • MariaDB 11.4.x on RHEL 8, installed from MariaDB's own yum repository
  • One master and one slave, using classic file and position replication (not GTID)
  • SSL replication, with the slave replicating one filtered database
  • About 640 GB of data
  • Backups: AWS AMI snapshots, taken with the instances stopped
  • DEV and SIT first (standalone servers, no replication), then production

The upgrade itself is simple: stop MariaDB, run dnf upgrade on the MariaDB packages, start MariaDB, and run mariadb-upgrade. The complicated part is everything around it.

Lesson 1: Check your repo file before anything else

On DEV, my first dnf upgrade said "Nothing to do". Then a partial upgrade updated only the compat packages, and the server stayed on the old version.

The cause was the MariaDB repo file. The [mariadb-main] section had its baseurl pinned to the exact version I was already running:

baseurl = https://dlm.mariadb.com/repo/mariadb-server/11.4.4/yum/rhel/8/x86_64

So dnf could never see anything newer. I first suspected a conflict with the RHEL AppStream repository, which was wrong. The thing that showed the truth was:

dnf repoquery --qf '%{name}-%{version} : %{repoid}' MariaDB-server MariaDB-client MariaDB-common MariaDB-shared

It listed only versions up to 11.4.4 from the mariadb-main repo. The fix is to point the baseurl at the 11.4 series instead of the exact version:

cp /etc/yum.repos.d/mariadb.repo /etc/yum.repos.d/mariadb.repo.bak_$(date +%Y%m%d)
sed -i 's|mariadb-server/11.4.4/yum/rhel/8/x86_64|mariadb-server/11.4/yum/rhel/8/x86_64|' /etc/yum.repos.d/mariadb.repo
dnf clean all && dnf makecache --refresh

dnf clean all and makecache only refresh the list of available packages. They are safe on a running database. After that, repoquery showed 11.4.13.

Takeaway: before any upgrade window, run repoquery and confirm the target version is visible on every node.

Lesson 2: A hostname change can split your binary logs

After an OS patching cycle, one of my servers came back with a different hostname. MariaDB names its binary logs after the hostname by default, so the binlog series split into two: one set named after the old hostname and one after the new.

Two things were behind it:

  1. cloud-init was allowed to reset the hostname on boot (preserve_hostname: false).
  2. The binlog basename was not set explicitly, so it followed the hostname.

On DEV and SIT I fixed it in two parts. First, I locked the hostname:

echo "preserve_hostname: true" | sudo tee /etc/cloud/cloud.cfg.d/99_preserve_hostname.cfg

Second, I set the binlog name explicitly in the server config so it no longer depends on the hostname:

log_bin = /database/mysql/mysql-bin

On production I made a different, deliberate choice. Replication there is file and position based, and the slave knows the master's binlog file names. I applied the hostname lock but did not change the binlog naming, so the logs keep their existing names and the slave simply resumes after the restart. Changing the binlog name on a live master is a separate planned change, not something to slip into an upgrade window.

Takeaway: lock the hostname on cloud instances, and know how your binlogs are named before any change that could touch the hostname.

Lesson 3: "Already upgraded" is normal within 11.4

After a successful package upgrade on SIT, running mariadb-upgrade printed:

This installation of MariaDB is already upgraded to 11.4.3.
There is no need to run mariadb-upgrade again for 11.4.13, because they're both 11.4.

That looks like a failure, but it isn't. Within the same release series (11.4.x), there is nothing for mariadb-upgrade to change in the system tables. You can still run it as a habit, but don't use --force just to see output.

Another normal message: the package step warns that server.cnf.rpmnew was created. That means your custom server.cnf was kept and the new default was saved next to it. Your configuration stays in place.

Lesson 4: The physical backup that never finished

My first plan for a backup was mariadb-backup, a hot physical backup, taken on the slave while it was running. I let it run for about 3 hours. It copied close to 600 GB, and then failed with a message about the redo log:

Was only able to copy log ... try increasing innodb_log_file_size

During a 3-hour copy, the InnoDB redo log wrapped around faster than the backup could read it. The redo log was only 512 MB (innodb_log_file_size = 536870912). Raising it needs a restart, so it is a separate planned change and could not be done in that window. Compression also saved much less than I'd estimated, around 7 to 10 percent, not the 40 to 60 percent I'd guessed.

I stopped chasing it and used AMI snapshots, taken with the instances stopped, as the only restore point.

Takeaway: on a large, busy database, check innodb_log_file_size before you rely on mariadb-backup, and always have a second restore method.

Lesson 5: No verified backup means no upgrade

The first production window was postponed because I did not have a verified restore point. That was the right decision, even though it was uncomfortable.

On the rescheduled night, the same rule was tested. In the first attempt, the slave's AMI did not complete, so we stopped before changing anything. Nothing was upgraded, and I brought the slave back to normal and confirmed replication. In the second attempt, both AMIs completed before I touched a single package.

Rule I now follow: no confirmed AMI on both nodes means no upgrade. The upgrade never starts until the SA team says, in words, that both snapshots are complete.

Lesson 6: Replication does not restart itself

Two weeks before the window, during pre-flight checks, I found the slave with both replication threads stopped and no errors recorded:

Slave_IO_Running: No
Slave_SQL_Running: No
Last_Errno: 0
Seconds_Behind_Master: NULL

I hadn't stopped it. After a MariaDB restart, the slave's replication threads did not come back on their own. On the upgrade night it showed the same behavior after the restart. So the runbook has an explicit START SLAVE and a checkpoint right after it. Never assume replication resumes by itself.

Lesson 7: Verify the filesystem before you start the database

After the AMI snapshots, on the night of the upgrade, the /database mount did not come up automatically on the restarted instances, and the SA team attached it manually. If MariaDB had started against an unmounted data directory, it could have created or used the wrong location.

Before starting anything, I checked:

df -h /database
mount | grep /database
du -sh /database
ls -ld /database/mysql

I looked for the right device, the expected size, the real database files and mysql:mysql ownership. Only then did I start MariaDB and check its journal for errors. I also asked the SA team to fix the /etc/fstab entry so a future reboot doesn't repeat it.

Takeaway: after any instance restart, confirm that storage is mounted and correct before the database starts.

The runbook

This is the order I used for a master/slave pair with a full outage. Slave first, always.

Pre-flight (the day before and again before the window)

On both nodes:

grep -E "^\[|baseurl" /etc/yum.repos.d/mariadb.repo
dnf clean all && dnf makecache --refresh
dnf repoquery --qf '%{name}-%{version} : %{repoid}' MariaDB-server MariaDB-client MariaDB-common MariaDB-shared | grep mariadb-main
rpm -qa | grep -i MariaDB | sort
cat /etc/cloud/cloud.cfg.d/99_preserve_hostname.cfg
hostname
df -h /database

On the slave:

mariadb -e "SHOW SLAVE STATUS\G" | grep -E "Slave_IO_Running|Slave_SQL_Running|Seconds_Behind_Master|Last_Errno"

Checks: both nodes see the same target version, the rpm database has no errors, the hostname lock exists, there is enough disk space, and replication shows Yes, Yes, lag 0.

Also check which packages are actually installed on each node. In my case, MariaDB-backup was installed on the slave but not on the master, so the two upgrade commands differed.

Phase 1: stop everything and take the AMIs

  1. The application team confirms in writing that traffic is stopped.
  2. On the slave, confirm lag 0, then: 
    mariadb -e "STOP SLAVE;"
systemctl stop mariadb
  3. On the master: 
    systemctl stop mariadb
  4. Confirm both are stopped (systemctl status mariadb, and no mariadbd process in ps), then shut down both instances.
  5. The SA team takes AMI snapshots of both. Wait for an explicit confirmation that both completed.
  6. Power on, then check hostnames, mounts and data directories.

Phase 2: upgrade the slave first

systemctl start mariadb
mariadb -e "SELECT VERSION();"
mariadb -e "STOP SLAVE;"
systemctl stop mariadb
dnf upgrade MariaDB-server MariaDB-client MariaDB-backup MariaDB-common MariaDB-shared MariaDB-client-compat MariaDB-server-compat -y
rpm -qa | grep -i MariaDB | sort
systemctl start mariadb
mariadb-upgrade -u root
mariadb -e "SELECT VERSION();"
mariadb -e "START SLAVE;"
sleep 20
mariadb -e "SHOW SLAVE STATUS\G" | grep -E "Slave_IO_Running|Slave_SQL_Running|Seconds_Behind_Master|Last_Errno|Last_IO_Error|Last_SQL_Error"

Checkpoint 1: both threads Yes, lag 0, no errors. If it is not clean, stop. Do not touch the master. Troubleshoot or roll back.

Phase 3: upgrade the master

Start the master first at the old version so the slave has something to connect to. After Checkpoint 1 passes:

systemctl stop mariadb
dnf upgrade MariaDB-server MariaDB-client MariaDB-common MariaDB-shared MariaDB-client-compat MariaDB-server-compat -y
rpm -qa | grep -i MariaDB | sort
systemctl start mariadb
mariadb-upgrade -u root
mariadb -e "SELECT VERSION();"
mariadb -e "SHOW MASTER STATUS;"

Phase 4: validate

On both nodes: hostname, SHOW VARIABLES LIKE 'server_id'; and the health check script below. On the slave, run the replication check again.

Checkpoint 2: both threads Yes, lag 0, no errors, same version on both nodes, hostnames and server IDs unchanged.

Then the application team runs their smoke test and confirms before traffic resumes.

Rollback

Decide the trigger before you start, not during. Mine was a fixed time in the window, set so that there was still enough time to restore from the AMIs, restart, and validate. The primary rollback is restoring the AMI snapshots with the SA team. Package downgrade is a fallback only. If the slave is the only affected node, the master stays untouched.

The health check script

This is a one-go script I run before and after every change. It checks the service, connectivity, version, connections, InnoDB, query counters, replication, disk and the error log. It connects as root over the local socket, because root on my servers uses unix_socket authentication with no password, so no credentials are stored in the script.

#!/bin/bash
# MariaDB Health Check - run as root (socket auth)

LOGFILE="/tmp/mariadb_health_$(date +%Y%m%d_%H%M%S).log"
MYSQL_CMD="mariadb"
command -v mariadb &>/dev/null || MYSQL_CMD="mysql"

if [ "$EUID" -ne 0 ]; then
    echo "Run as root: sudo $0"
    exit 1
fi

log() { tee -a "$LOGFILE"; }

echo "MariaDB Health Check - $(date)" | log

echo -e "\n--- 1. Service Status ---" | log
if systemctl is-active --quiet mariadb; then
    echo "MariaDB service: RUNNING" | log
else
    echo "MariaDB service: NOT RUNNING" | log
    exit 1
fi

echo -e "\n--- 2. Connectivity ---" | log
if $MYSQL_CMD -e "SELECT 1;" &>/dev/null; then
    echo "Connection: OK" | log
else
    echo "Connection: FAILED" | log
    exit 1
fi

echo -e "\n--- 3. Version and Uptime ---" | log
$MYSQL_CMD -e "SELECT VERSION() AS version;" 2>/dev/null | log
$MYSQL_CMD -e "SHOW GLOBAL STATUS LIKE 'Uptime';" 2>/dev/null | log

echo -e "\n--- 4. Connections and Threads ---" | log
$MYSQL_CMD -e "SHOW GLOBAL STATUS WHERE Variable_name IN
  ('Threads_connected','Threads_running','Max_used_connections','Connections','Aborted_connects');" 2>/dev/null | log
$MYSQL_CMD -e "SHOW VARIABLES LIKE 'max_connections';" 2>/dev/null | log

echo -e "\n--- 5. InnoDB Buffer Pool and Locks ---" | log
$MYSQL_CMD -e "SHOW GLOBAL STATUS WHERE Variable_name IN
  ('Innodb_buffer_pool_pages_free','Innodb_buffer_pool_pages_total',
   'Innodb_row_lock_current_waits','Innodb_row_lock_time_avg');" 2>/dev/null | log

echo -e "\n--- 6. Query Performance ---" | log
$MYSQL_CMD -e "SHOW GLOBAL STATUS WHERE Variable_name IN
  ('Slow_queries','Questions','Com_select','Com_insert','Com_update','Com_delete');" 2>/dev/null | log

echo -e "\n--- 7. Replication Status ---" | log
REPL=$($MYSQL_CMD -e "SHOW SLAVE STATUS\G" 2>/dev/null)
if [ -n "$REPL" ]; then
    echo "$REPL" | grep -E "Slave_IO_Running|Slave_SQL_Running|Seconds_Behind_Master|Last_Error" | log
else
    echo "No replication configured (or not a replica)." | log
fi

echo -e "\n--- 8. Disk Space (Data Directory) ---" | log
DATADIR=$($MYSQL_CMD -N -e "SHOW VARIABLES LIKE 'datadir';" 2>/dev/null | awk '{print $2}')
[ -n "$DATADIR" ] && df -h "$DATADIR" | log

echo -e "\n--- 9. Recent Errors ---" | log
ERRLOG=$($MYSQL_CMD -N -e "SHOW VARIABLES LIKE 'log_error';" 2>/dev/null | awk '{print $2}')
if [ -n "$ERRLOG" ] && [ -f "$ERRLOG" ]; then
    tail -n 20 "$ERRLOG" | log
else
    # log_error is empty on some systems: errors go to the journal
    journalctl -u mariadb --no-pager -n 20 2>/dev/null | log
fi

echo -e "\n--- 10. Storage Engine Summary ---" | log
$MYSQL_CMD -e "SELECT ENGINE, COUNT(*) AS table_count
  FROM information_schema.tables
  WHERE table_schema NOT IN ('information_schema','mysql','performance_schema','sys')
  GROUP BY ENGINE;" 2>/dev/null | log

echo -e "\nReport saved to: $LOGFILE" | log

One lesson from this script: on my servers log_error was empty, so the original version said "error log not found". The journal fallback in section 9 fixes that. If an error log "can't be found", check where your logging really goes before you assume it's fine.

What I'd tell anyone doing this for the first time

  1. Do DEV and SIT first, and write down every surprise. Mine paid for production.
  2. Verify the repo, the target version and the rpm database on every node before the window.
  3. Have two checkpoints and respect them. Slave first, and never touch the master until the slave is clean.
  4. No confirmed restore point, no upgrade. Wait for a clear confirmation from the team that takes the snapshots.
  5. Decide your rollback trigger before you start.
  6. After any restart, check mounts, hostnames and replication before you trust anything.
  7. Get the expectations right with other teams: who stops traffic, who confirms, who takes the AMIs, and who tests afterwards.

The upgrade itself took very little time. Everything that mattered happened before it and around it. Next on my list, in separate windows, are OS patching and raising innodb_log_file_size so that physical backups can work on a database this size.

If you run MariaDB in production on AWS and you've hit the same issues, or found a better way to handle any of these, I'd like to hear about it in the comments.

Posted by at
Labels: ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)