-
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.
-
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.
-
Crafting an Extension Framework for MySQL: a talk from MySQL BR Conf 2026
I opened my MySQL BR Conf 2026 talk creating a new data type that stock MySQL doesn’t have: ROT13. … OK, admittedly, it’s a silly type. With a ROT13 column, you insert plain strings and read back plain strings, but the table stores scrambled text. … OK, it’s very *very* silly. But the point of this example isn’t to convince you extensions are useful; it’s to show a tiny extension that allows exploring design choices we made in designing the VEF (VillageSQL Extension Framework).What the talk coversSimpler functions and aggregates. VDF’s (VEF-Defined Functions) resemble MySQL UDF’s (User-Defined Functions), but are easier to write. I explore how we made it both more ergonomic in C++ and also how we make it harder to mess up.Types that take parameters. Some types take parameters; e.g., vectors need a length. To show how to handle this in our existing silly example, I generalize ROT13 into ROT(K).The escape path. I show how functions that don’t have tidy, compile-time signatures like printf fit into the VEF. I also describe our design pattern for cases like these. In the talk, I link to a blog post on this blog, which I will also now link here in an example of Linkception: “Happy Path, Escape Hatch, and the Space Between”.Porting a PostgreSQL extension. The talk ends with what happened when VillageSQL's Claude Code skill ported PostgreSQL's hstore extension, and what the port cost.About Daniel BentleyDan is a founding engineer at VillageSQL. He has worked at Google and Twitter, and he did a startup called Tilt that focused on Kubernetes developer tools. He is on a quest to make databases delightful to use by translating complex systems into elegant simplicity.Get started at villagesql.com, read the docs, or talk to us on Discord.
|
