-
Multi-Node Unused Index Verification
Every MySQL schema that’s been around for a few years collects indexes nobody remembers adding. Some were for a report that was retired. Some came from an ORM migration. A few were added “just in case” and never used. They all cost something: extra writes on every INSERT and UPDATE, increased buffer pool pressure, larger backups, and slower schema changes.
MySQL makes it look easy to find them. Since 5.7, the sys schema ships a view called sys.schema_unused_indexes, and one query gives you a list of indexes with zero reads since the server started. It’s tempting to turn that list straight into DROP INDEX statements.
In an earlier post, How to Find Duplicate, Unused, and Invisible Indexes in MySQL, I showed how to use that view on a single server. This post covers what that approach misses once replicas are involved.
I’ve seen that go wrong, and it’s why I wrote check_unused_indexes. It’s a small Python script that asks the same question on every server in a replication topology and only reports an index when the answer is “unused” everywhere. This post covers why that matters, how the tool works, and how I’d use it on a production system.
Why one server’s answer isn’t enough
sys.schema_unused_indexes is built on Performance Schema’s per-index I/O counters. Those counters are local to the server you query. They know nothing about what your replicas are doing.
That’s the trap. In most setups, the primary takes writes and the light OLTP reads, while the heavy reads go elsewhere:
A reporting replica runs month-end queries that lean on an index the application never touches.
An Aurora reader serves the dashboard traffic, so the writer never sees those lookups.
In a Galera or Percona XtraDB Cluster, the load balancer may send one kind of query to one node more than the others.
So an index can show zero reads on the primary and still be the only thing keeping a replica’s reporting query under a second. Drop it on the primary, the DDL replicates, and the replica falls over into a full table scan.
There’s a second catch. The counters reset when MySQL restarts. A server patched on Tuesday has seen only a few days of traffic. A weekly batch job or a quarterly report hasn’t run yet, so its indexes look unused when they aren’t.
How check_unused_indexes works
You give the script a list of servers and tell it which one is the primary. It determines which of those servers belong to that primary’s topology, collects the unused-index lists from each one, and keeps only the indexes that appear on every list. It runs in three steps.
check_unused_indexes · 3 steps, 2 abort points
If the script can’t get a complete answer from every node, it stops instead of printing a list it can’t stand behind.
Step 1: discover the topology. The script connects to every server in your inventory and reads a little metadata from each:
SHOW REPLICA STATUS (or SHOW SLAVE STATUS on older versions) for the source host and port
@@wsrep_cluster_name, to spot Galera and PXC cluster members
information_schema.replica_host_status, which only exists on Aurora
the Uptime status variable
Then it walks downstream from the primary. A replica whose source matches the primary’s address is pulled in, then that replica’s own replicas, and so on. Chained and circular replication are handled the same way. If a node is in a Galera cluster, all its cluster peers are included as well. Aurora readers are matched by server ID, or by an alias naming convention I’ll come back to later.
Servers in the inventory that don’t belong to this primary are left out. That means you can keep a single inventory file for the entire fleet and point the script to different primaries.
Step 2: ask every node. For each node in the resolved topology, the script queries sys.schema_unused_indexes. Two filters apply on every node:
System schemas (sys, mysql, performance_schema, information_schema) are always skipped.
Any index whose first column is part of a foreign key is excluded. MySQL needs an index on FK columns, and it won’t let you drop one that a constraint depends on. Even if it did, you wouldn’t want to.
You can narrow things further with –include-schema and –include-table, or skip more schemas with –ignore-schema.
Step 3: intersect. The script starts with the primary’s list and removes anything that isn’t also on every other node’s list. What’s left is the set of indexes with zero reads everywhere the script looked. It prints that as a table, followed by the DDL to run on the primary.
Getting started
The script is a single file. It needs Python 3.6 or newer and one library, pymysql.git clone https://github.com/Percona-Lab/check_unused_indexes.git
cd check_unused_indexes
pip3 install --user pymysqlCredentials come from a MySQL option file. By default that’s ~/.my.cnf, and you can point at another one with -c:[client]
user=index_audit
password=change_meThe account needs to read sys.schema_unused_indexes and information_schema, and to run SHOW REPLICA STATUS. Use the same user on every node. The script reads only and never changes anything on the servers.
There are two ways to tell the script about your servers. For a quick check, you can list them on the command line:python3 check_unused_indexes.py \
--primary "db-primary=10.0.0.10:3306" \
--replicas "db-replica-1=10.0.0.11:3306,db-replica-2=10.0.0.12:3306"For anything bigger, I’d use an inventory file. It’s one alias=host:port per line. Blank lines and lines starting with # are ignored, and the port defaults to 3306 if you leave it off.# inventory.txt
db-primary=10.0.0.10:3306
db-replica-1=10.0.0.11:3306
db-replica-2=10.0.0.12:3306
db-analytics=10.0.0.13:3306Then pass the primary’s alias:python3 check_unused_indexes.py --inventory inventory.txt --primary db-primaryOne thing to check before your first run: the topology walk matches each replica’s Source_Host against the addresses in your inventory, as plain strings. If your replicas were set up with CHANGE REPLICATION SOURCE TO SOURCE_HOST=’db-primary.internal’, put that same hostname in the inventory, not the IP. If the topology comes back with fewer nodes than you expect, this is almost always why.
A run, step by step
Here’s what a run looks like against the inventory above, with the primary and two replicas reachable. The db-analytics server in the file replicates from a different primary, so the topology walk excludes it. This is the example output from the project README, with the colours stripped.=== Step 1: Discovering Topology ===
Mapping topology downstream from Primary: db-primary...
Resolved Topology (3 nodes): db-primary, db-replica-1, db-replica-2
=== Step 2: Checking Unused Indexes on Nodes ===
Checking [PRIMARY] db-primary (10.0.0.10:3306) - Uptime: 42.1 days
└─ Found 12 unused index candidates on this node.
Checking [REPLICA/CLUSTER] db-replica-1 (10.0.0.11:3306) - Uptime: 42.1 days
└─ Found 8 unused index candidates on this node.
Checking [REPLICA/CLUSTER] db-replica-2 (10.0.0.12:3306) - Uptime: 14.0 days
└─ Found 5 unused index candidates on this node.
=== Step 3: Intersecting Unused Indexes Across Topology ===
Safe Target Indexes (Unused across ALL 3 checked nodes):
================================================================================
SCHEMA | TABLE | INDEX NAME
--------------------------------------------------------------------------------
app_db | orders | idx_legacy_status
app_db | users | idx_created_at
================================================================================
Total Candidate Indexes: 2
Generated DDL Statements (Execute on Primary):
--------------------------------------------------------------------------------
ALTER TABLE `app_db`.`orders` ALTER INDEX `idx_legacy_status` INVISIBLE;
ALTER TABLE `app_db`.`users` ALTER INDEX `idx_created_at` INVISIBLE;
--------------------------------------------------------------------------------Look at the numbers in Step 2. Run the sys query on the primary alone and you get 12 indexes to drop. Only 2 of those 12 are unused on both replicas. The other 10 are carrying read traffic somewhere, and dropping them would have hurt.
That gap between 12 and 2 is the whole point of the tool. Expect a gap like this on most real systems. Replicas exist precisely to take the load the primary doesn’t serve.
If you only want the DDL, for a ticket or a change request, add -qs (–quiet-summary) to skip the table.
Safety checks: what happens when a node is down
The intersection is only as good as its inputs. If a replica can’t be reached and the script quietly skips it, the result looks complete but isn’t. Any index that replica depends on could end up on the drop list. The latest version therefore refuses to guess.
Situation
What the script does
Primary can’t be reached in Step 1
Stops with a fatal error. Without the primary there’s no topology to walk.
Some inventory nodes can’t be reached in Step 1
Prints a connection warning naming each one, with a note that they weren’t evaluated.
Primary drops out during Step 2
Stops at once. There’s nothing to intersect against.
A replica or cluster peer drops out during Step 2
Stops with a fatal error and lists the nodes, unless you pass –force.
Same, with –force
Carries on with the nodes that answered, prints a warning, and labels the result “checked nodes”.
A node’s uptime is under the threshold
Finishes, then warns that its statistics may be incomplete. The default is 7 days; change it with -u, or set 0 to disable it.
The Step 1 warning needs an explanation. If a replica is already down when you start, the script can’t read its replication status. It has no way of knowing that the node belongs to this topology, so it can’t treat it as missing. All it can do is tell you which inventory entries it couldn’t reach. Read that warning before you trust the result.
–force is there for the case where you know a node is gone for good, such as a replica being decommissioned. Don’t use it to get past a network blip.
The last option is –pause-seconds, which waits between Step 1 and Step 2. It’s meant for testing these checks. Start a run with –pause-seconds 60, stop MySQL on one replica while it waits, and check that the script aborts:
python3 check_unused_indexes.py -i inventory.txt -p db-primary –pause-seconds 60; echo “exit=$?”
You should see FATAL ERROR: Failed to inspect index state on resolved replica/peer node(s) and an exit code of 1. Run it once on a test setup before you rely on it in production.
From candidate list to dropped index
The script prints DDL and stops there. It never runs anything for you, and that’s on purpose. The list is a set of candidates to review, not a set of orders.
By default, the DDL makes the index invisible rather than dropping it:
ALTER TABLE app_db.users ALTER INDEX idx_created_at INVISIBLE;
Invisible indexes arrived in MySQL 8.0. The optimizer stops using the index, but MySQL continues to maintain it on every write. Making it invisible is a metadata change, and so is undoing it, so rolling back takes seconds. A real DROP INDEX followed by a re-create means rebuilding the index, which on a large table can take hours.
This is the rollout I’d follow:
Run the script and read the candidate list yourself. Make sure every name makes sense to you.
Run the INVISIBLE statements on the primary. They replicate, so every node in the topology changes.
Wait through at least one full business cycle, including the month-end jobs and any weekly batch. Watch the slow query log and your query metrics for regressions.
If something slows down, run ALTER INDEX … VISIBLE on that index, and you’re back where you started.
Once you’re confident, drop the indexes. –action drop prints the DROP INDEX statements, and –action both prints both kinds.
One more thing before step 5. On MySQL 8.0 you can set use_invisible_indexes=on in optimizer_switch for a single session. That lets you check whether a query you’re unsure about would still pick the index, without making it visible to the application.
For a longer walkthrough of invisible indexes, including testing a single query with SET_VAR(optimizer_switch = ‘use_invisible_indexes=on’), see my earlier post.
Options at a glance
Option
What it does
Default
-p, –primary
Primary alias from the inventory, or alias=host:port / host:port
required
-r, –replicas
Comma-separated replica specs on the command line
none
-i, –inventory
Inventory file, one alias=host:port per line
none
-c, –config
MySQL option file with credentials
~/.my.cnf
-u, –min-uptime
Warn when a node has been up fewer days than this
7
–action
DDL to print: invisible, drop or both
invisible
-s, –include-schema
Only check these schemas
all
-t, –include-table
Only check these tables
all
–ignore-schema
Extra schemas to skip, on top of the system ones
none
-qs, –quiet-summary
Print only the DDL
off
–force
Continue if a replica fails in Step 2 (unsafe)
off
–pause-seconds
Wait between Step 1 and Step 2, for testing
0
–color
auto, always or never
auto
Limits worth knowing
No tool can see traffic that hasn’t happened yet, and this one is no exception. Keep these in mind:
Only servers in the inventory are checked. A replica you forgot to list is invisible to the script, and so is everything it serves.
Unique indexes can still show up. sys.schema_unused_indexes excludes primary keys, but not UNIQUE indexes. A unique index can have zero reads and still be enforcing a business rule. Check every candidate with SHOW CREATE TABLE before you drop it.
Statistics start at the last restart. The uptime warning helps, but you decide what “long enough” means for your workload.
Performance Schema must be on. It’s on by default. If someone turned it off, the sys view has nothing to report.
Aurora needs an alias convention. Readers are matched by server ID or by alias prefix. Name the writer aurora and the readers aurora-reader-1, aurora-reader-2 and so on, and they’ll be grouped correctly.
Hosts are matched as strings. As covered above, use the same host name or IP in the inventory as the replicas use in Source_Host.
It finds unused indexes, not duplicate ones. An index can be redundant even if another index covers the same columns. For those, run pt-duplicate-key-checker from Percona Toolkit, as described in the earlier post.
Try it and tell me what breaks
The project is on GitHub at Percona-Lab/check_unused_indexes under the Apache 2.0 license. It’s one Python file, so it’s easy to read before you run it against anything that matters, and I’d encourage you to do so.
If it misreads your topology, or you run something it doesn’t handle yet (group replication, multi-source replicas), please open an issue or send a pull request. Real topologies are messier than test labs, and those reports are how the tool gets better.
The post Multi-Node Unused Index Verification appeared first on Percona.
-
Village News: MySQL News + Events (29 October 2026)
Welcome back to Village News, our curated roundup of MySQL and database news. This week, plans for FOSDEM 2027 took shape, with a new M-Ecosystem devroom and MySQL Belgian Days opening their calls for papers.
If you want to get these updates, just subscribe to the blog.
Enjoy!
MySQL News
Note: Aggregated MySQL news can be found at Planet for MySQL Community and Planet MySQL (Oracle curated)
Percona Operator for MySQL 1.3.0: autopilot tuning, zero-data-loss async failover, encrypted PITR
Slava Sarzhan, Percona Blog
TL;DR - The operator now tunes MySQL from pod resources, fails over async clusters without losing data, and supports PITR with encrypted binlogs. Read the upgrade notes first: async clusters now require backups.
MySQL Workbench 26.10: Advanced Authentication Support
Oracle MySQL Blog
TL;DR - The second release of the new-generation Workbench, following 26.7, focuses on support for advanced authentication methods.
How to Extract Data from InnoDB, MyISAM, and MyRocks Files Without a Running Server
Przemysław Malkowski, Percona Blog
TL;DR - Introduces innodump, a new open-source tool that decodes schema and rows straight from MySQL/MariaDB data files. It can skip corrupted pages and writes SQL or MySQL Shell-compatible dumps.
Getting Started With the MySQL REST Service Component
Oracle MySQL Blog
TL;DR - A walkthrough of the MRS component that shipped in MySQL 26.10. It builds a customer-orders REST API that returns products, orders and line items as nested JSON.
One Room for the Whole "M": The M-Ecosystem Devroom Is Coming to FOSDEM 2027
Maria Nesterova, OurSQL Foundation Blog
TL;DR - The MariaDB, MySQL & Friends devroom becomes "The M-Ecosystem" on Sunday, January 31, 2027. MySQL, MariaDB, Percona, VillageSQL and OurSQL Foundation organize it together, and the CFP is open.
MySQL Belgian Days 2027
Oracle MySQL Blog
TL;DR - Belgian Days returns to Brussels on January 28–29, 2027, just before FOSDEM. Registration and the call for papers are now open.
How to change MySQL account passwords with no downtime
Ronald Bradford
TL;DR - In MySQL 8.0 and later, ALTER USER ... RETAIN CURRENT PASSWORD and DISCARD OLD PASSWORD let an account accept two passwords while clients roll over. That means no outage and no duplicate account.
Crafting an Extension Framework for MySQL: a talk from MySQL BR Conf 2026
Dan Bentley, VillageSQL
TL;DR - A write-up of the VEF design talk. It uses a toy ROT13 type to cover functions, parameterized types and escape hatches, and ends with a port of PostgreSQL's hstore.
MySQL 8.4 Data Paths
yunushaikh.github.io
TL;DR - An interactive diagram that follows a transaction from client to disk. Change durability and flushing settings and watch which files get a write(), which get an fsync(), and what a crash would cost.
MySQL MCP Server v1.8.0 is out
Alkin Tezuysal, AskDba
TL;DR - Fixes 37 reachable vulnerabilities in Go and its SSH and MCP SDK dependencies, and adds MySQL 26.7 and MariaDB 12.3 to the CI matrix. govulncheck now blocks releases.
What's New in MySQL NDB Cluster 9.x – Better Visibility and Control in MySQL NDB Cluster
Oracle MySQL Blog
TL;DR - A tour of the NDB 9.x series: cluster-wide observability, hardened failure paths, more user control and faster recovery.
Benchmarking vector indexes in MySQL with vector-dev-bench
Adam Levin, VillageSQL
TL;DR - An open, laptop-scale benchmark built with Percona. It drives VillageSQL, MariaDB, pgvector and MySQL built with Google's ScaNN patch, so teams building MySQL vector indexes can compare results the same way.
MySQL Change Stream Applier (CSA): Fixing Replication Lag
Vinoth Kanna, Mydbops Blog
TL;DR - MySQL 26.7's new replica applier (APPLIER_VERSION = 2) uses per-channel worker pools and splits apply from commit. A small contention test shows how it avoids the worker starvation of the multi-threaded applier.
Database News
Benchmarking Vector search in six database engines
Evgeniy Patlan, Percona Blog
TL;DR - 990K OpenAI embeddings across MariaDB 11.8/12.3, AliSQL, pgvector, Valkey and Percona Search for MongoDB. Index builds ranged from 1.9 minutes to 6.7 hours. Under 32 clients, pgvector scaled 21.7× while the InnoDB-based engines stayed near 1×.
Multigres private alpha: automatic failover for Supabase Postgres
Aditya Maruvada and Manan Gupta, Supabase Blog
TL;DR - Multigres, from the team behind Vitess, now runs paid-plan Supabase projects as three-node Postgres clusters across availability zones, with automatic failover and pooling.
Designing a planned switchover for minimum disruption
Shlomi Noach, PlanetScale Blog
TL;DR - How Neki promotes a Postgres replica with preflight checks, a final WAL position, synchronous replication and query buffering, so that nothing acknowledged is lost.
OrioleDB public beta: a Postgres storage engine for update-heavy tables
Aditya Maruvada, Daniel Mitterdorfer, Alexander Korotkov and Artur Zakirov, Supabase Blog
TL;DR - OrioleDB, an alternative to the heap that targets bloat and VACUUM pain, is now selectable on every Supabase plan. It is billed like standard Postgres but is not yet production-ready.
How MariaDB 11.8 became about 3× faster on some in-memory workloads, fixing a regression dating back to 10.5
Alessandro Vetere, MariaDB Foundation Blog
TL;DR - InnoDB's LRU promotion has held buf_pool.mutex too eagerly since 10.5. The fix takes 11.8 from 20K to 57K QPS on one of Mark Callaghan's tests, and it now beats 10.4 by 7–9%.
The Benchmarking Crisis
Thomas Neumann, Database Architects
TL;DR - AI-tuned ClickBench entries are gaming the benchmark with hash-only GROUP BY, cached predicates and LIMIT shortcuts. Neumann argues it needs TPC-H-style ad-hoc rules.
Announcing ProxySQL 2.7.4: Security and Severe Bug Fixes Only
ProxySQL Blog
TL;DR - A maintenance release for the 2.7 LTS series that backports fixes for 15 security advisories, some reachable before authentication, plus crash and transaction-state bugs. Users are urged to upgrade.
HPTS 2026 (Day 2 and Day 3)
Murat Demirbas
TL;DR - Session notes from the second half of HPTS, covering GPU-native analytics (Sirius on DuckDB), object storage, failures, consensus and lakehouses.
Replication Manager 3.1.43 to 3.1.45: logical backups get closer to physical backup speed, welcome MCP in our API, welcome PostgreSQL, more dynamic resources on Cloud18, improved security
Signal18 Blog
TL;DR - The MariaDB/MySQL HA manager now handles PostgreSQL switchover and failover, with an 8-second failover and pg_rewind rejoin. It also adds backup archive plugins (restic, plakar, Proxmox) and an MCP API.
Upcoming Database Events
PG Down Under 2026
October 30, 2026
Surry Hills
Sydney, Australia
OurSQL Foundation Webinar: Beyond the Proxy
November 3, 2026, 10:00 AM ET / 16:00 CET
Online
MySQL Contributor Summit
November 4-5, 2026
Virtual
KubeCon + CloudNativeCon North America (VillageSQL is a sponsor)
November 9-12, 2026
Salt Lake City, Utah
PASS Data Community Summit
November 9-11, 2026
Hyatt Regency Seattle
Seattle, WA
pgDay México 2026
November 13, 2026
Puerta Polanco
Mexico City, Mexico
DuckDB × MySQL Day
November 17, 2026
Online
PGConf.Asia 2026
November 17-18, 2026
Hong Kong
PgConf.Nepal 2026
November 18-21, 2026
Pulchowk Campus, Tribhuvan University
Kathmandu, Nepal
PGConf.PL 2026
November 24, 2026
ARCHE Dwór Uphagena
Gdańsk, Poland
PostgreSQL Conference Japan 2026
November 27, 2026
AP Tokyo Yaesu
Tokyo, Japan
AWS re:Invent 2026
November 30 – December 4, 2026
Las Vegas, NV
Open Source Summit Japan
December 7-9, 2026
Tokyo, Japan
CIDR 2027
January 24-27, 2027
Mövenpick Hotel Amsterdam City Centre
Amsterdam, The Netherlands
MySQL Belgian Days 2027
January 28-29, 2027
ICAB Incubator
Brussels, Belgium
FOSDEM PGDay 2027
January 28-29, 2027
Brussels Marriott Hotel Grand Place
Brussels, Belgium
FOSDEM 2027
January 30-31, 2027
ULB Solbosch Campus
Brussels, Belgium
CERN PGDay 2027
February 12, 2027
CERN Council Chamber
Geneva, Switzerland
PGConf India 2027
March 2-5, 2027
Sheraton Grand Hotel at Brigade Gateway
Bengaluru, India
FOSSASIA PGDay 2027
March 9, 2027
Bangkok, Thailand
KubeCon + CloudNativeCon Europe 2027
March 15-18, 2027
Barcelona, Spain
Nordic PGDay 2027
March 16, 2027
Courtyard Kungsholmen
Stockholm, Sweden
SCaLE 24x
April 1-4, 2027
Pasadena Convention Center
Pasadena, CA
PgDay Boston 2027
April 10, 2027
Tufts University Joyce Cummings Center
Boston, MA
Postgres Conference 2027
April 21-23, 2027
San Jose Marriott
San Jose, CA
PGConf.dev 2027
May 11-14, 2027
Plaza Centre-Ville
Montréal, QC, Canada
-
Percona Operator for MySQL 1.3.0: autopilot tuning, zero-data-loss async failover, encrypted PITR
Percona Operator for MySQL 1.3.0 is out. This release moves three things forward for teams running MySQL on Kubernetes: the operator now tunes MySQL for you based on the pod resources, async clusters can fail over without losing data in flight, and point-in-time recovery works on clusters where binary logs are encrypted at rest.
The headline features in this post are automatic MySQL configuration tuning, zero data loss failover for asynchronous clusters, and encrypted binary logs for point-in-time recovery. Each of them removes a step that teams were doing by hand, or a case they had to accept as a known gap.
As with every operator release, most of what shipped came from what the community asked for on forums.percona.com and in JIRA. The operator is open source and runs on any CNCF-conformant Kubernetes.
In this post, you’ll learn about:
Important upgrade notes you should read before you touch 1.3.0
Automatic MySQL configuration tuning (and how it hot-applies without a restart)
Zero data loss failover for asynchronous clusters
Encrypted binary logs for point-in-time recovery
Other improvements in the release
Before you upgrade
Two defaults changed in 1.3.0. Review both before upgrading.
Leader election is on by default
The operator now runs with leader election enabled out of the box. In every realistic deployment this is what you want. If you have a single-replica controller and want the old behavior for a specific reason (local development, debugging), set the PSO_LEADER_ELECTION_ENABLED environment variable on the operator Deployment to false.
Asynchronous clusters now require backups
This is the one that will trip an upgrade. In 1.3.0, async clusters must have backups enabled, because the zero data loss failover feature (below) relies on reading binary logs from the failed primary. If backups are not configured on an async cluster, the operator refuses to reconcile.
You have two options:
Enable backups on the cluster (the recommended path). See the backups documentation.
Explicitly opt out of zero data loss failover by setting the escape hatch:
spec:
unsafeFlags:
asyncFailoverWithPossibleDataLoss: true
This restores the pre-1.3.0 behavior: fast failover, but transactions committed on the old primary that had not yet reached the chosen replica are lost.
Group Replication clusters are unaffected by this change.
Automatic MySQL configuration tuning
Why it matters
Tuning MySQL by hand for a given pod shape is work most teams do once and then forget. When the pod resource limits change, the hand-tuned values rarely follow. 1.3.0 adds a first-class autoConfig block to the CR so the operator computes the MySQL configuration from the pod’s CPU and memory, and the workload shape you declare.
How it works
The relevant fields live under spec.mysql.autoConfig. enabled turns the feature on, loadType tells the operator the workload shape, and version tells it which MySQL version to tune for. The accepted loadType values are mostlyReads, someWrites, equalReadsWrites, and heavyWrites. The version string follows the X.Y.Z MySQL version format.
The CRD enforces that mysql.resources sets both CPU and memory when autoConfig.enabled is true. Without those limits, the operator has no basis for its calculation.
Dynamic configuration changes
Where a MySQL variable allows it, the operator applies configuration changes with SET GLOBAL instead of a restart. Static variables still require a restart, which the operator rolls through pods one at a time.
Wiring it up
The following block comes from the sample CR in the operator repository:
apiVersion: ps.percona.com/v1
kind: PerconaServerMySQL
metadata:
name: cluster1
spec:
mysql:
size: 3
resources:
limits:
memory: 8Gi
cpu: 4
autoConfig:
enabled: true
loadType: someWrites
version: 8.4.0
With that block, the operator sizes the buffer pool and friends from the 8 GiB / 4 CPU limits, biases the configuration toward a mixed workload with some writes, and tunes for MySQL 8.4.0. Change the limits or the load type and the operator recomputes.
Zero data loss failover for asynchronous clusters
Why it matters
Async MySQL replication, by definition, acknowledges a transaction to the client before any replica has it. If the primary dies with transactions in its binary log that no replica has read yet, those transactions are gone, even with a well-configured HA controller.
How it works
The operator extends the xtrabackup sidecar on every MySQL pod with a POST /failover/stream endpoint on port 6450 that serves binary logs as a tar stream. When the primary fails, the failover tool runs on the chosen replica.
The salvage sequence
Probe the failed primary and stand down if it has come back.
Stop replication and flush relay logs.
Read Source_Log_File and Read_Source_Log_Pos to find the gap.
Fetch the missing binary logs from the failed primary’s sidecar.
Trim the fetched binlogs back to their last complete transaction.
Splice them into the latest closed relay log.
Start the SQL thread and wait until every missing transaction is applied.
Only then does the orchestrator promote the chosen replica.
Timeouts and the escape hatch
The failover has a budget. By default, the failover tool waits up to one hour for the source pod to become reachable and apply the missing transactions. When the budget expires, the on-timeout policy decides what happens: Abort holds the cluster, or ForceWithPossibleDataLoss promotes anyway. The budget is counted from the first attempt, not per retry.
Because the mechanism requires the failed primary’s pod to be reachable (its mysqld can be down, but its sidecar must answer), the CRD now validates that backups are enabled on async clusters. To opt out of this gate and restore the pre-1.3.0 behavior, set spec.unsafeFlags.asyncFailoverWithPossibleDataLoss: true.
Encrypted binary logs for point-in-time recovery
Why it matters
The operator’s binlog server ships binary logs to object storage for point-in-time recovery. In 1.3.0 those shipped files can be encrypted at rest, with the key material held in a Kubernetes Secret instead of being carried on each MySQL pod.
How it works
The binlog server uses envelope encryption. A per-file data encryption key (DEK) encrypts each binlog file. That DEK is itself wrapped under a key encryption key (KEK) that the operator reads from a keyring Secret. The CR surfaces three fields for this:
spec.backup.pitr.binlogServer.storage.encryption.cipher picks the symmetric cipher (AES-256-CTR by default).
spec.backup.pitr.binlogServer.storage.encryption.kekId names the KEK entry inside the keyring.
spec.backup.pitr.binlogServer.keyringSecret points at the Kubernetes Secret that holds the keyring file (default key keyring.json).
On the restore side, the PITR tool reads the keyring, finds the KEK named in the file’s metadata, unwraps the file key, and streams the decrypted events through mysqlbinlog into the restored MySQL. If the keyring is missing or the named KEK is not in it, the restore fails fast with an explicit error rather than producing partially restored data.
Wiring it up
Enable PITR with an encrypted binlog server. The relevant block in the CR is spec.backup.pitr.binlogServer, which carries its own encryption sub-block and a reference to the keyring secret:
apiVersion: ps.percona.com/v1
kind: PerconaServerMySQL
metadata:
name: cluster1
spec:
backup:
enabled: true
pitr:
enabled: true
binlogServer:
storage:
encryption:
cipher: AES-256-CTR
kekId: alpha
s3:
bucket: S3-BACKUP-BUCKET-NAME-HERE
credentialsSecret: ps-cluster1-s3-credentials
endpointUrl: https://s3.amazonaws.com
region: us-west-2
keyringSecret:
key: keyring.json
name: ps-cluster1-binlog-server-keyring
The encryption.cipher and encryption.kekId fields are what tell the binlog server to encrypt shipped binary logs at rest. The keyringSecret provides the master key material.
Other improvements
The release notes have the full list with ticket IDs. The highlights:
Application users managed in the CR: declare application users in the top-level spec.users list, each with name, optional passwordSecretRef, dbs, hosts, grants, and withGrantOption.
Custom CA for S3-compatible storage: the backup path now accepts a custom CA bundle, which lets you point at private MinIO or S3-compatible endpoints behind a corporate certificate authority.
cert-manager ClusterIssuer support: the TLS block can reference a cluster-scoped issuer instead of a namespaced one.
TLS rotation without a restart: the operator merges old and new CA, hot-reloads leaves, and drops the old root once propagation is confirmed.
Backup deadlines: a backup that exceeds its deadline fails fast with a clear status instead of hanging.
Configurable leader election: tune the leader-election parameters via PSO_LEADER_ELECTION_* environment variables on the operator Deployment.
Persistent operator logging: operator logs survive controller restarts.
DigitalOcean Kubernetes (DOKS) support as an officially supported platform.
MySQL 9.7 added to the supported-version matrix.
Replication type switch (tech preview), ClusterSet rejoin, replication lag reporting, even-sized async clusters, and async bootstrap from backup round out the async and multi-cluster story.
Conclusion
Percona Operator for MySQL 1.3.0 tightens the parts of a MySQL platform that reviews and on-call rotations care about most: autoconfig gives each pod the configuration its CPU and memory actually warrant, zero data loss failover salvages the transactions stranded on a failed primary before promoting a replica, and encrypted binlog shipping lets point-in-time recovery stay on, under keys you control, for clusters where encryption at rest is a hard requirement. Application users in the CR, custom CAs for private S3 endpoints, and in-place TLS rotation move more day-two work out of runbooks and into the spec. If there is a workflow you still script around the operator, tell us on the forum, since that is where releases like this one come from.
Try It Out
Release notes: Percona Operator for MySQL 1.3.0 Release Notes
GitHub: percona/percona-server-mysql-operator
Documentation: docs.percona.com/percona-operator-for-mysql/ps
Community Forum: forums.percona.com
The post Percona Operator for MySQL 1.3.0: autopilot tuning, zero-data-loss async failover, encrypted PITR appeared first on Percona.
-
How to Extract Data from InnoDB, MyISAM, and MyRocks Files Without a Running Server
Probably everyone responsible for managing database servers at some point realizes that it would be helpful to have a tool capable of extracting data directly from raw database files. Something completely offline, without the need to have a running database server, and that would work with data directories, files, or binary backups (storage volume snapshots, XtraBackup, etc).
There are a couple of use cases for that I could think of:
Crashed/corrupted data recovery – if the database service refuses to start or enters a crash-restart loop and there are no good backups (PITR!) available.
Backup (additional) verification and data files health check.
Extraction of individual databases or tables from a full backup.
Quick provisioning of staging/QA instances directly from live production.
Recovery of accidentally deleted rows.
Forensics (i.e., quick seek for data across many old backups without the need to reload them).
Import/migrate data from very old/legacy database versions.
Backups!
Here, let me drop an obvious yet often neglected statement: nothing will replace good backups! Your database infrastructure should always be planned with the assumption that your data may get damaged or lost at any time. Visit here if you want to check if your current backup policy is up to date: https://www.percona.com/blog/mysql-backup-and-recovery-best-practices/
Now, would an offline tool with the ability to read SQL data from files be another backup method? NO! Modern ACID- and MVCC-compliant data engines are too complex in terms of data durability and consistency. So, reading directly from database files does not guarantee the dump would be consistent. Dedicated backup tools like XtraBackup engage additional steps to ensure consistency, like following the redo log and using backup locks.
Corruption and Crash Recovery
Typically, when MySQL / MariaDB server detects severely corrupted data, it may need to perform an emergency shutdown with the intention of preventing further damage. A lot depends on the scope of the damage. Some minor issues with secondary indexes could often be fixed by re-creating the index, but clustered key corruption often leads to an engine crash whenever the corrupted page is accessed. You may find yourself in a situation similar to these reports:
https://bugs.mysql.com/bug.php?id=111610
https://bugs.mysql.com/bug.php?id=113706
Again, with good backups, this should be no problem. But a bad surprise may force you to try to read all healthy rows in the hope of restoring as much as possible. The task will be, in most cases, very tedious – an attempt to read a row sitting in a corrupted page would crash the server, so you have to wait for a restart before you can try reading the next row.
This was one of the reasons for me to try to come up with a tool fully capable of extracting rows without needing to run MySQL. Otherwise, a challenging project was affordable using vibe-coding, where the agent could build data decoding by reading the relevant engine’s source code. So, let me introduce a new open-source project: innodump! Up to this point, I have implemented all three most popular MySQL and MariaDB engines: InnoDB, MyISAM, and MyRocks (RocksDB). The tool can read and dump the whole data directory, or specific tables, either from a common or general tablespace or from a per-table file. It decodes both the table schema and rows into SQL files or a TSV structure compatible with MySQL Shell dump utilities. The latter allows you to load the dump into another instance very efficiently.
There is an option relevant from the corruption point of view:
--skip-corrupted – in case a corrupted data page is encountered, it prints a comment and skips to the next page, effectively reading as much data as possible.
Example run:$ innodump --file innodump_work/instant_t.ibd --verbose --skip-corrupted
File: innodump_work/instant_t.ibd (130023424 bytes)
Space ID: 409
Pages: 7936
Page size: 16384 bytes
FSP flags: 0x00004021 (post_antelope=true atomic_blobs=true data_dir=false shared=false temporary=false encryption=false sdi=true)
SDI: present (written by MySQL 8.0.33, dd_version 80023, sdi_version 80019)
Tables in SDI: 1
Row format: DYNAMIC
Clustered index: "PRIMARY" (id 943), root page 4
Secondary indexes: 5
Physical fields: 31 (29 output)
=== innodump 0.8.1 ===
Table: db1.instant_t (row_format=DYNAMIC)
Columns: 29 output (31 total incl. system/hidden)
warning: corrupted page 100 (index "PRIMARY", id 943): LSN low bytes at the start and end of the page disagree
Schema file: sqldump_2026-10-21_16.42/db1.instant_t-schema.sql
Data file: sqldump_2026-10-21_16.42/db1.instant_t-data.sql (100680 row(s) written, 1 corrupted page(s) skipped - see warnings above)
Data Verification
--examine – this option makes the tool read every table and decode every row without storing them; it produces a report of how many rows can be found and how many of them are corrupted.
Note: the tool scans only the clustered (primary) index, so it will not find possible secondary index corruptions; it is expected to only ensure which rows can be restored.$ innodump --examine --source-dir /data/sandboxes/msb_11_6_2/data/
=== innodump 0.8.1 ===
Following /data/sandboxes/msb_11_6_2/data/ recursively — recognized as a single instance's datadir (found ibdata1), so every InnoDB table found inside (file-per-table .ibd files, plus any table found only in ibdata1's own shared tablespace) and every MyISAM table (.MYD files), plus every MyRocks table (read from #rocksdb) will be examined (nothing is written).
Source: /data/sandboxes/msb_11_6_2/data/ (6 .ibd file(s), 129 .frm-only table(s) to resolve via ibdata1, a MyRocks data directory (#rocksdb) found)
db1.t0 InnoDB 8 row(s) OK [row_format=COMPACT]
db1.t1 InnoDB 5 row(s) OK [row_format=COMPACT]
mysql.gtid_slave_pos InnoDB 0 row(s) OK [row_format=COMPACT]
mysql.innodb_index_stats InnoDB 13 row(s) OK [row_format=COMPACT]
mysql.innodb_table_stats InnoDB 3 row(s) OK [row_format=COMPACT]
mysql.transaction_registry InnoDB 0 row(s) OK [row_format=COMPACT]
db1.sbtest1 ROCKSDB 1000000 row(s) OK
Summary
InnoDB 6 table(s), 29 row(s)
ROCKSDB 1 table(s), 1000000 row(s)
total 7 table(s), 1000029 row(s)
Note: 129 .frm file(s) with no matching .ibd had no InnoDB entry in ibdata1's own dictionary either - likely non-InnoDB tables, VIEWs, or a general tablespace - not examined
Result: every table read cleanlyIf you are looking for a way to scan InnoDB files for potential corruption, you should always start with the official innochecksum. With innodump’s examination, the main goal is to show what’s actually readable.
Staging/dev/QA Data Provisioned the Easy and Unintrusive Way?
Quite often, a DBA may be tasked with setting up a non-production MySQL or MariaDB instance having a recent data snapshot from production. And typically, this kind of data copy for staging purposes does not have to be point-in-time consistent. And note that using this tool on a live instance or on an unprepared backup will mean various data consistency issues, like dirty pages may not be on disk yet, pages can be mid-write, it can produce rows that are missing, stale, or wrongly flagged, and even whole tables can be missing if created recently enough inside a common space.
Now, if the last regular available backup is up to date enough, it may still be too costly to restore the full backup while only a handful of databases are needed. Here, innodump can dump from specific database folders or files:$ innodump --source-dir /data/sandboxes/msb_9_7_0/data/db2/ --format tsv --compression zstd
=== innodump 0.8.1 ===
Following /data/sandboxes/msb_9_7_0/data/db2/ recursively — recognized as a single database directory (no subdirectories of its own), so every InnoDB table found inside (file-per-table .ibd files, plus any table found only in ibdata1's own shared tablespace) and every MyISAM table (.MYD files) will be dumped.
Source: /data/sandboxes/msb_9_7_0/data/db2/ (5 .ibd file(s) found)
db2.categories: 10000 row(s)
db2.cities: 100000 row(s)
db2.products: 100000 row(s)
db2.sales: 1049877 row(s)
db2.stores: 100000 row(s)
Files: 5/5 .ibd file(s) processed
Tables: 5 extracted
Rows: 1359877 written
Output: tsvdump_2026-10-21_16.58The exported folder can use the format compatible with fast MySQL Shell import:$ ls tsvdump_2026-10-21_16.58/
db2@categories.json db2@categories.tsv.zst db2@cities.sql db2.json db2@products.sql db2@sales.json db2@sales.tsv.zst db2@stores.json db2@stores.tsv.zst @.json
db2@categories.sql db2@cities.json db2@cities.tsv.zst db2@products.json db2@products.tsv.zst db2@sales.sql db2.sql db2@stores.sql @.done.jsonWhat if no recent backup is available but there is a need to fetch staging data quickly from production? Bearing in mind that reading from a live datadir has the caveats above, which is fine for a throwaway staging copy, the tool can do it gently, as it allows you to limit the IO throughput during exporting. Moreover, it can bypass the OS page cache via the O_DIRECT flag, so as not to trash the server’s cache with garbage and avoid causing additional memory pressure (the kernel might decide to swap out some least recently used user space memory to make room for page cache!). Therefore, to make the export from production with as little impact as possible, I recommend using --io-limit (RATE bytes per second) and --direct-io options, plus being gentle on the CPU side, running the tool with nice.
Example run results without these options used:$ time innodump --file /data/sandboxes/msb_9_7_0/data/tpcdh/orders.ibd --format tsv --compression zstd
=== innodump 0.8.1 ===
Table: tpcdh.orders (row_format=DYNAMIC)
Columns: 9 output (11 total incl. system/hidden)
Dump dir: tsvdump_2026-10-21_17.25 (MySQL Shell util.loadDump()-compatible, zstd level 1)
Rows: 1500000 written
real 0m1.726s
user 0m6.601s
sys 0m0.362s
$ fincore /data/sandboxes/msb_9_7_0/data/tpcdh/orders.ibd
RES PAGES SIZE FILE
192.1M 49168 228M /data/sandboxes/msb_9_7_0/data/tpcdh/orders.ibdAnd with these safety settings (time longer bc io limit, and page cache is skipped bc direct io):$ time innodump --file /data/sandboxes/msb_9_7_0/data/tpcdh/orders.ibd --format tsv --compression zstd --direct-io --io-limit 30M
=== innodump 0.8.1 ===
Table: tpcdh.orders (row_format=DYNAMIC)
Columns: 9 output (11 total incl. system/hidden)
Dump dir: tsvdump_2026-10-21_17.28 (MySQL Shell util.loadDump()-compatible, zstd level 1)
Rows: 1500000 written
real 0m9.241s
user 0m8.024s
sys 0m0.486s
$ fincore /data/sandboxes/msb_9_7_0/data/tpcdh/orders.ibd
RES PAGES SIZE FILE
0B 0 228M /data/sandboxes/msb_9_7_0/data/tpcdh/orders.ibd
Recover Deleted Rows?
InnoDB does not immediately purge or overwrite deleted rows in the table spaces. The record is first only delete-marked and stays in the page. If no new writes have actually reclaimed the space yet, such rows usually can be decoded. So, if done quickly, there is a good chance you can restore those rows, and innodump makes it easy via the --deleted-only option!$ innodump --file /data/sandboxes/msb_8_4_3/data/sbtest/sbtest1.ibd --deleted-only
=== innodump 0.8.1 ===
Table: sbtest.sbtest1 (row_format=DYNAMIC)
Columns: 4 output (6 total incl. system/hidden)
Schema file: sqldump_2026-10-21_17.46/sbtest.sbtest1-schema.sql
Data file: sqldump_2026-10-21_17.46/sbtest.sbtest1-data-deleted.sql (2100 delete-marked row(s) written)I hope you may find the tool handy for the cases mentioned above as well as possibly others, like extracting data from some ancient/legacy database versions, where the regular backup tools don’t even work.
Note: it is not an officially supported Percona software, but rather an experimental personal project.
The tool can be found on GitHub, and all feedback is welcome!
https://github.com/PrzemekMalkowski/innodump
The article was created by a human.
The post How to Extract Data from InnoDB, MyISAM, and MyRocks Files Without a Running Server appeared first on Percona.
-
How to change MySQL account passwords with no downtime
Changing the password of a MySQL account that an application uses was hard before MySQL 8.0. It is easy in MySQL 8.0 and later, thanks to two clauses of ALTER USER: RETAIN CURRENT PASSWORD and DISCARD OLD PASSWORD.
|
