OpenDBWorks

MySQL

MySQL design · HA · tuning · upgrades

MySQL is the standard RDBMS for web, commerce and internal systems. We build InnoDB Cluster and ProxySQL high availability, binlog-based point-in-time recovery and slow-query tuning, and plan your move to 8.4 LTS now that 8.0 support has ended.

MySQL consultation8.4 LTS upgrade

Architecture

Recommended HA architecture

Choose between the official InnoDB Cluster (Group Replication + MySQL Router + MySQL Shell) and asynchronous replication with ProxySQL, depending on requirements.

InnoDB Cluster uses Group Replication to replicate transactions by consensus (quorum) and automatically elects a new primary on failure. MySQL Router routes application connections to the current primary and secondaries.

ProxySQL adds rule-based read/write splitting, connection multiplexing, query caching and blocking rules, and is common in high-traffic environments.

  • Group Replication: at least three (odd-numbered) members
  • Single-primary mode by default; multi-primary only after reviewing conflict behaviour
  • DR via InnoDB ClusterSet or asynchronous replication to a remote site
  • Managed services such as Aurora are designed within their feature set
Figure. MySQL InnoDB Cluster reference design (Router and ProxySQL used alone or together)

Scope

MySQL service scope

Install & design

LTS vs Innovation release choice, character set/collation standards, InnoDB file and log layout, account standards.

HA & replication

GTID async and semi-sync replication, Group Replication, InnoDB Cluster/ClusterSet, ProxySQL.

Backup & PITR

Percona XtraBackup physical backups, MySQL Shell dump utilities, binlog point-in-time recovery.

Tuning

Buffer pool, redo log and flush settings, slow queries and plans, indexing and partitioning.

Monitoring

PMM, Performance Schema and sys schema, replication lag and lock waits, Grafana alerts.

Upgrades

Pre-checks with MySQL Shell Upgrade Checker, rolling 8.0 → 8.4 LTS upgrades.

Security

caching_sha2_password, role-based privileges, TLS, audit logging, password policy.

Migration

Oracle, Tibero or other MySQL-family → MySQL; on-premises ↔ cloud (Aurora/RDS etc.).

Replication

HA options compared

MySQL replication and HA options
OptionWhat it isGood forWatch out for
Async replication (GTID)Replicas apply the source’s binlogRead scaling, DR, simple setupsPossible data loss on failure → consider semi-sync
Semi-sync replicationCommit acknowledged after at least one replica receives itBetter RPONetwork latency affects write response
Group Replication / InnoDB ClusterConsensus replication + automatic failover + RouterProduction HA standardReview large transactions and write conflicts
ProxySQLQuery routing, R/W split, connection multiplexingHigh traffic, read scalingRouting rules and health checks need design

Backup & PITR

Backup and point-in-time recovery

Restore quickly from a physical backup, then apply binlogs to reach the exact moment you need.

XtraBackup full backup and restore preparation
# Backup (non-blocking physical backup while online)
xtrabackup --backup --target-dir=/backup/full_20261020 \
           --user=bkuser --password='***'
# Prepare before restoring
xtrabackup --prepare --target-dir=/backup/full_20261020
# Copy back into an empty datadir and fix ownership
xtrabackup --copy-back --target-dir=/backup/full_20261020
chown -R mysql:mysql /var/lib/mysql
Apply binlogs up to the target time
# From the position in xtrabackup_binlog_info up to just before the incident
mysqlbinlog --start-position=157 \
            --stop-datetime="2026-10-20 13:55:00" \
            binlog.000123 binlog.000124 | mysql -u root -p
Use the XtraBackup release that matches your MySQL version (e.g. XtraBackup 8.4 for MySQL 8.4). File names and positions are examples.

Tuning

Where we tune

InnoDB & server

  • innodb_buffer_pool_size and instances versus the working set
  • Redo log capacity (innodb_redo_log_capacity) and flush method
  • max_connections and thread/connection pooling strategy
  • Avoid oversized temp-table and sort buffers
  • Parallel replica apply (replica_parallel_workers)

Queries & schema

  • Pick top queries with the slow log + pt-query-digest / PMM Query Analytics
  • Confirm real execution paths with EXPLAIN ANALYZE
  • Composite index column order and covering indexes
  • Partitioning and retention for large log-style tables
  • Lock-wait and deadlock analysis (performance_schema, sys schema)

Upgrade

MySQL 8.0 → 8.4 LTS upgrade

Oracle announced MySQL 8.0 end of life (moved to Sustaining Support) in April 2026. A planned move to 8.4 LTS (or 9.7 LTS) is recommended.

  1. Pre-checks

    MySQL Shell util.checkForServerUpgrade() and mysqlcheck --check-upgrade

  2. Fix compatibility

    mysql_native_password accounts, new reserved words, removed variables and options

  3. Validate on staging

    Upgrade a production copy; regression and performance comparison

  4. Rolling cutover

    Upgrade replicas first → switch over → upgrade the former primary

You cannot upgrade 8.0 directly to 9.x; go through 8.4 LTS. Downgrades are not supported, so a backup before upgrading is mandatory. (Sources: MySQL 8.4 Reference Manual and MySQL EOL notice — check the latest documentation before acting.)

Migration

Oracle · Tibero → MySQL

  • Map data types, character set (utf8mb4) and collation
  • Sequences → AUTO_INCREMENT or a sequence-table design
  • PL/SQL packages → stored procedures or application logic
  • Decide on full load plus change sync
  • Validate counts, checksums and business-query results
  • Cutover rehearsal and rollback plan
For cloud migrations we also review what the target service’s migration tooling (e.g. AWS DMS) supports.

FAQ

Frequently asked questions

Can we keep running MySQL 8.0?

Since April 2026, 8.0 is in Sustaining Support with no new patches. To keep receiving security and bug fixes, plan a move to a supported version such as 8.4 LTS.

InnoDB Cluster or ProxySQL?

If automatic failover is the priority, InnoDB Cluster (+Router) is the standard. If you need fine-grained query routing or multiplexing, use ProxySQL alongside it or on its own.

Do you support Aurora MySQL?

Yes. We tune, monitor and migrate with the managed service’s characteristics (parameter groups, storage architecture, available features) in mind.

How long does slow-query tuning take?

It depends on the number of queries and what may change (indexes only, SQL rewrites, schema changes). After a diagnosis we propose priorities and an expected scope.

Shall we start by reviewing your cloud and databases as they are today?

Tell us your clouds, database versions, topology and concerns, and we will propose an assessment scope and approach.

Get in touchkhy061473@gmail.com