Personal blog of Yzmir Ramirez

On December 1, 2025, MariaDB announced that MySQL Galera Cluster will reach end of life on September 30, 2026. After that date, the MySQL build of Galera stops receiving maintenance and binary releases, and all new clustering features land only in MariaDB Galera Cluster. MariaDB’s recommended path is an in-place migration onto their own server.

If you run MySQL Galera Cluster today, that gives you a real decision to make, and not much time to make it. The good news is that you have more than one option, and the one most teams overlook keeps you on MySQL.

You have two paths, not one

The deadline forces a move, but it does not force you onto MariaDB. There are two realistic destinations, and the difference between them is larger than it first appears, because one is a database engine change and the other is not.

The pattern holds across every row. MariaDB Galera Cluster moves you to a different database and asks you to rebuild around it, while PXC keeps the database you already have and changes what sits underneath it. That is what makes the move below a distribution change rather than a re-platforming.

“In-place” is still a database migration

MariaDB describes its path as near-zero downtime and in place. That is fair for the cluster mechanics, but it understates what is changing underneath. By MariaDB’s own migration documentation, moving to MariaDB Galera Cluster means a different system table structure, a fundamentally different data dictionary, and user accounts and privileges that are not mapped one-to-one and must be recreated by hand.

In other words, you are not upgrading MySQL Galera Cluster. You are moving to a different database that also happens to use Galera. For many teams that is a larger project than the “in-place” label suggests, with application testing, account re-creation, and a new server to operate and support afterward.

Why PXC is the natural landing spot

PXC treats this as continuity rather than conversion. It is MySQL, not a fork of it.

• It is built on Percona Server for MySQL, a drop-in compatible build of MySQL, so your schema, system tables, and user accounts carry over as they are.
• Its clustering uses the same Galera write-set replication model you already run, on Percona’s own open Galera fork, which we maintain and ship on our own schedule and on terms we control.
• It keeps strong binary compatibility with MySQL and Percona Server for MySQL, and integrates with Percona XtraBackup, Percona Monitoring and Management, and both Kubernetes and traditional deployments.

For a MySQL Galera Cluster user, that means the move is a server and distribution change inside the MySQL family, not a migration to a new database.

For a fuller version of this argument, see Marco Tusa’s personal take: The Galera Crossroads: Why PXC is the Lifeline for MariaDB Community Users.

Why PXC, not just the easier migration

Staying on MySQL is the practical argument. There is also a case for choosing PXC on the merits, independent of how much migration effort each path takes.

• It is genuinely MySQL, not a relative of it. PXC is Percona Server for MySQL, a drop-in compatible build of MySQL. MariaDB began as a MySQL fork but has diverged over the years and is no longer a drop-in replacement for MySQL. With PXC, your MySQL knowledge, queries, tooling, and application compatibility carry forward. With MariaDB, some of that has to be revisited.
• Open source, with nothing held back. Percona ships its software, including PXC and our Galera fork, as open source, with no enterprise-only tier gating the features you depend on. MariaDB operates as a commercial vendor, with proprietary and enterprise components alongside the community server. If freedom from lock-in is part of why you run open source databases, that difference matters.
• A steward with no competing database to sell you. This one is worth stating plainly. The company retiring the MySQL build of Galera is the same company recommending you move onto its own database. MariaDB owns Codership, the maintainer of Galera, and has set the end-of-life date while pointing those users to MariaDB Galera Cluster. Percona does not sell a competing database. Our interest is in keeping you successful on MySQL, which is the same interest you have.
• A long track record in the MySQL ecosystem. Percona has maintained MySQL-focused software for years, including Percona Server for MySQL, Percona XtraBackup, Percona Monitoring and Management, and Percona XtraDB Cluster, all under long-term support. Supporting MySQL users is not a new direction for us.

Taken together, the question is not only which migration is easier. It is which project is built around keeping MySQL open, compatible, and independent, and which one benefits from MySQL Galera coming to an end.

What the migration looks like

Because PXC stays within the MySQL ecosystem, the migration is a distribution change rather than a re-platforming exercise, so the work is mostly planning, testing, and a controlled cutover.

Before you start. Inventory your current cluster: the exact MySQL and Galera versions, node topology, wsrep settings, and any custom configuration. Confirm the PXC version that lines up with your MySQL version, so you are moving across a compatible boundary rather than changing major versions at the same time. Stand up a staging cluster that mirrors production, and capture a baseline backup with Percona XtraBackup before you touch anything.

The right approach depends mainly on how much downtime you can tolerate, ranging from a straightforward binary swap during a maintenance window to a near-online cutover for systems that must stay available. The full, step-by-step guide lives on docs.percona.com, where we keep it current as the tooling improves, and we will link it here once it is published.

Whichever path you take, the same disciplines apply: rehearse the whole thing on staging first, validate application behavior and query performance against PXC before production, and keep a tested rollback (a verified backup or an untouched source cluster) until you are confident. Plan any cutover for a low-traffic window and watch cluster and replication health closely for the first hours afterward.

Start before the deadline

September 30, 2026 is when maintenance and binary releases stop for MySQL Galera Cluster. Running an unmaintained cluster past that point means no security patches and no bug fixes, which is not where you want a mission-critical system to sit. The time it takes to test and validate a move now is worth far more than the risk of waiting.

If you are weighing your options, the short version is this: you do not have to leave MySQL to keep a supported, open source Galera cluster. PXC is here, it is maintained, and it is the closest thing to staying exactly where you are.

Talk to us

If you want help mapping out a migration path, sizing the work, or pressure-testing your high availability strategy, reach out to your Percona contact, post in the Percona community forums, or connect with our team directly. We are happy to walk through it with you.

 

---

Written by Dennis Kittrell. Reviewed by Michal Nosek and Marco Tusa.

MySQL, MariaDB, and Galera Cluster are trademarks of their respective owners. Percona is not affiliated with, sponsored by, or endorsed by these owners.

The post How to Migrate from MySQL Galera Cluster to Percona XtraDB Cluster appeared first on Percona.

In this blog, I am going to share a real-world debugging case study where a routine Percona XtraDB Cluster node restart led to an unexpected failure. I will walk through what we observed, what we checked, and how we ultimately identified the root cause.

Let’s see how the maintenance goes. It was supposed to be a simple restart. The kind you’ve done a hundred times. You SSH in, run the maintenance, bring the node back up, and go grab a coffee. Except this time, the coffee went cold on the desk… because MySQL refused to start.

The Problem

The error log of Percona XtraDB Cluster (8.0) had the following information:

2025-11-05T05:26:10.982984Z 0 [ERROR] [MY-000059]   [Server] SSL error: Unable to get certificate from '/var/lib/mysql/server-cert.pem'.
2025-11-05T05:26:10.983030Z 0 [Warning] [MY-013595] [Server] Failed to initialize TLS for channel: mysql_main. See below for the description of exact issue.
2025-11-05T05:26:10.983045Z 0 [Warning] [MY-010069] [Server] Failed to set up SSL because of the following SSL library error: Unable to get certificate
2025-11-05T05:26:10.983052Z 0 [Note] [MY-000000] [WSREP] New joining cluster node configured to use specified SSL artifacts
2025-11-05T05:26:10.983083Z 0 [Note] [MY-000000] [Galera] Loading provider /usr/lib64/galera4/libgalera_smm.so initial position: 07c67757-0d18-11ef-b5a9-ee5d87b39aa8:4147053897
2025-11-05T05:26:10.983098Z 0 [Note] [MY-000000] [Galera] wsrep_load(): loading provider library '/usr/lib64/galera4/libgalera_smm.so'
2025-11-05T05:26:10.983742Z 0 [Note] [MY-000000] [Galera] wsrep_load(): Galera 4.22(f6c0465) by Codership Oy <info@codership.com> (modified by Percona <https://percona.com/>) loaded successfully.
2025-11-05T05:26:10.983771Z 0 [Note] [MY-000000] [Galera] Resolved symbol 'wsrep_node_isolation_mode_set_v1'
2025-11-05T05:26:10.983784Z 0 [Note] [MY-000000] [Galera] Resolved symbol 'wsrep_certify_v1'
2025-11-05T05:26:10.983807Z 0 [Note] [MY-000000] [Galera] CRC-32C: using 64-bit x86 acceleration.
2025-11-05T05:26:10.983995Z 0 [Note] [MY-000000] [Galera] not using SSL compression
2025-11-05T05:26:10.984341Z 0 [ERROR] [MY-000000] [Galera] Bad value '/var/lib/mysql/server-cert.pem' for SSL parameter 'socket.ssl_cert': 336245135: 'error:140AB18F:SSL routines:SSL_CTX_use_certificate:ee key too small'
         at /mnt/jenkins/workspace/pxc80-autobuild-RELEASE/test/rpmbuild/BUILD/Percona-XtraDB-Cluster-8.0.42/percona-xtradb-cluster-galera/galerautils/src/gu_asio.cpp:ssl_prepare_context():471
2025-11-05T05:26:10.984401Z 0 [ERROR] [MY-000000] [Galera] Failed to create a new provider '/usr/lib64/galera4/libgalera_smm.so' with options 'gcache.size=1G;gcache.recover=yes;socket.ssl=yes;socket.ssl_ca=/data00/mysqldata/ca.pem;socket.ssl_cert=/data00/mysqldata/server-cert.pem;socket.ssl_key=/data00/mysqldata/server-key.pem;socket.ssl_key=/var/lib/mysql/server-key.pem;socket.ssl_ca=/var/lib/mysql/ca.pem;socket.ssl_cert=/var/lib/mysql/server-cert.pem': Failed to initialize wsrep provider
2025-11-05T05:26:10.984434Z 0 [ERROR] [MY-000000] [WSREP] Failed to load provider
2025-11-05T05:26:10.984448Z 0 [ERROR] [MY-010119] [Server] Aborting
2025-11-05T05:26:10.984602Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.42-33.1)  Percona XtraDB Cluster (GPL), Release rel33, Revision 6673f8e, WSREP version 26.1.4.3.
2025-11-05T05:26:10.985473Z 0 [ERROR] [MY-010065] [Server] Failed to shutdown components infrastructure.

 

MySQL was down, and the maintenance clock was running. The certificate file sitting at /var/lib/mysql/server-cert.pem was the same file that had been working perfectly fine before the restart!!
From past history, it was known that the following commands were executed correctly on the same cluster node

SET GLOBAL ssl_ca = '/var/lib/mysql/ca.pem';
  SET GLOBAL ssl_cert = '/var/lib/mysql/server-cert.pem';
  SET GLOBAL ssl_key = '/var/lib/mysql/server-key.pem';
  ALTER INSTANCE RELOAD TLS;

Clients connected over TLS. Galera nodes communicated securely. There were zero complaints from the error log.
In other words, the SSL reload at runtime inherited the process environment that existed when MySQL originally booted. Everything was smooth, but after a restart? MySQL complains and declines to start. So what has changed?

Checking Usual Suspects

File permissions

We checked the PEM files. 

Ownership: mysql:mysql.
Permissions: 644 for the cert, 600 for the key. 

We compared them against the other Galera nodes, and they were identical. This didn’t look like a permissions problem.

Is SELinux to blame here?

SELinux has ruined enough DBA time that it is one of the top spots on such checklists – but it was permissive.

$ getenforce
Permissive

That means it was logging any security issues, but not blocking. And there were no AVC denials related to MySQL or the PEM files in /var/log/audit/audit.log or dmesg!

File corruption

Did the files get corrupted/replaced during or before the MySQL restart?

$ openssl x509 -in /var/lib/mysql/server-cert.pem -noout -text
# Output looked perfectly valid when compared to the output from other nodes

$ openssl rsa -in /var/lib/mysql/server-key.pem -check
RSA key ok

The files were fine. They parsed cleanly. OpenSSL could read them. So why couldn’t MySQL?

More Logs review

We scanned /var/log/messages and journalctl for anything unusual around the time of the restart. No disk errors. No OOM kills. No kernel panics. Nothing that screamed “I am the Dhurandhar that’s destroyed your node.” At this point, most of the usual suspects were guilt-free, staring at us, asking, “Who did it?”

The Clue

It is good to communicate with stakeholders, and we did – “Was there any recent change on your side?” to the client, and then uttered the golden words “Last week the crypto-policy was updated on all of the DB servers to comply with PCI.”

PCI > Crypto-policy – Let’s go and check it !!

$ update-crypto-policies --show
FUTURE

The system was running RHEL’s FUTURE cryptographic policy.

For those unfamiliar (including me at the time), Red Hat Enterprise Linux (and its derivatives, such as Rocky, Alma, and Oracle Linux) ships with a system-wide cryptographic policy framework. It’s a centralized way to enforce minimum standards for TLS versions, cipher suites, key lengths, and signature algorithms across all applications on the system that include OpenSS and yes, anything that links against those libraries… like MySQL.

Here’s a table that shows information about the crypto-policy levels:

Policy	RSA Minimum	TLS Minimum	SHA-1 Signatures	Use Case
LEGACY	1024-bit	TLS 1.0	Allowed	Old systems compatibility
DEFAULT	2048-bit	TLS 1.2	Allowed	Standard operations
FUTURE	3072-bit	TLS 1.2	Blocked	Forward-looking hardening
FIPS	2048-bit	TLS 1.2	Blocked	FIPS 140 compliance

 

 

 

 

 

 

 

 

So FUTURE demands a 3072-bit RSA key; otherwise, it is blocked. What do we have?

$ openssl rsa -in server-key.pem -text -noout | head -1
RSA Private Key: (2048 bit, 2 primes)

2048 bits! C’mon! And now I recall the error log again… The hint was there:

error:140AB18F:SSL routines:SSL_CTX_use_certificate:ee key too small

Now we have our story straight.
On restart, our PXC cluster node started a new process linked against OpenSSL, which now enforced the FUTURE policy. OpenSSL looked at the 2048-bit RSA certificate and said: “Nope. Too small.”

Fixture

The quick fix here would be to adjust the policy to DEFAULT.

sudo update-crypto-policies --set DEFAULT

This will accept the current SSLs, and the node will join the cluster readily.

Alternatively, to remain compliant and adhere to the security policy strictness, the fixture will be to

• Generate new certificates
• Deploy the keys/certs to all Galera nodes
• Perform a rolling restart

 

Conclusion

This was a classic case of a problem hiding at the boundary between two domains, database administration and operating system security. The DBA saw valid certificates and correct MySQL configuration. The sysadmin saw a properly hardened system with a strong crypto policy. Neither was wrong. But the intersection of their two correct configurations produced a failure.

This incident reinforces the importance of cross-domain awareness, where resolving database issues sometimes requires understanding and challenging system-level security decisions.

 

 

 

 

The post Curious case of PXC node that refused to start due to SSL appeared first on Percona.

The replication  manager script can be particularly useful in complex PXC/Galera topologies that require Async/Multi-source replication. This will ease the auto source and replica failover to ensure all replication channels are healthy and in sync. If certain nodes shouldn’t  be part of a async/multi-source replication, we can disable the replication manager script there to tightly controlled the flow. Alternatively, node participation can be controlled by adjusting the weights in the percona.weight table, allowing replication behavior to be managed more precisely.

At Percona, we’ve always been dedicated to providing robust, open source solutions that meet our users’ evolving needs. Percona XtraDB Cluster (PXC) stands as a testament to this commitment, offering a highly available and scalable solution for your MySQL and Percona Server for MySQL deployments. We understand that database high availability is critical for your […]

At Percona, we’ve always been dedicated to providing robust, open source solutions that meet our users’ evolving needs. Percona XtraDB Cluster (PXC) stands as a testament to this commitment, offering a highly available and scalable solution for your MySQL and Percona Server for MySQL deployments. We understand that database high availability is critical for your […]

Keeping Your Data Safe: An Introduction to Data-at-Rest Encryption in Percona XtraDB Cluster. In the first part of this blog post, we learned how to enable GCache and Record-Set cache encryption in Percona XtraDB Cluster. This part will explore the details of the implementation to understand what happens behind the scenes. How does it work […]

Keeping Your Data Safe: An Introduction to Data-at-Rest Encryption in Percona XtraDB Cluster. Data breaches are a major concern today. Whether you’re a business owner or value your privacy, the thought of your sensitive information being exposed is unsettling. Fortunately, data-at-rest encryption can help. Think of it as a sophisticated lock that scrambles your data, […]

Recently, I was working on a very unfortunate case that revolved around diverging clusters, data loss, missing important log errors, and forcing commands on Percona XtraDB Cluster (PXC). Even though PXC tries its best to explain what happens in the error log, I can vouch that it can be missed or overlooked when you do not know what to expect.

This blog post is a warning tale, an invitation to try yourself and break stuff (not in production, right?).

TLDR:
Do you know right away what happened when seeing this log?

2023-06-22T08:23:29.003334Z 0 [ERROR] [MY-000000] [Galera] gcs/src/gcs_group.cpp:group_post_state_exchange():433: Reversing history: 171 -> 44, this member has applied 127 more events than the primary component.Data loss is possible. Must abort.

Demonstration

Using the great https://github.com/datacharmer/dbdeployer:

$ dbdeployer deploy replication --topology=pxc --sandbox-binary=~/opt/pxc  8.0.31

Let’s write some data

$ ./sandboxes/pxc_msb_8_0_31/sysbench oltp_read_write --tables=2 --table-size=1000 prepare

Then let’s suppose someone wants to restart node 1. For some reason, they read somewhere in your internal documentation that they should bootstrap in that situation. With dbdeployer, this will translate to:

$ ./sandboxes/pxc_msb_8_0_31/node1/stop
stop /home/yoann-lc/sandboxes/pxc_msb_8_0_31/node1

$ ./sandboxes/pxc_msb_8_0_31/node1/start --wsrep-new-cluster
......................................................................................................^C

It fails, as it should.

In reality, those bootstrap mistakes happen in homemade start scripts, puppet or ansible modules, or even internal procedures applied in the wrong situation.

Why did it fail? First error to notice:

2023-06-22T08:00:48.322148Z 0 [ERROR] [MY-000000] [Galera] It may not be safe to bootstrap the cluster from this node. It was not the last one to leave the cluster and may not contain all the updates. To force cluster bootstrap with this node, edit the grastate.dat file manually and set safe_to_bootstrap to 1 .

Reminder: Bootstrap should only be used when every node has been double-checked to be down; it’s a manual operation. It fails here because it was not forced and because this node was not the last to be stopped in the cluster.

Good reflex: Connecting to other mysql and check for ‘wsrep_cluster_size’ and ‘wsrep_cluster_status’ statuses before anything.

mysql> show global status where variable_name IN ('wsrep_local_state','wsrep_local_state_comment','wsrep_local_commits','wsrep_received','wsrep_cluster_size','wsrep_cluster_status','wsrep_connected');

Do not: Apply blindly what this log is telling you to do.

But we are here to “fix” around and find out, so let’s bootstrap.

$ sed -i 's/safe_to_bootstrap: 0/safe_to_bootstrap: 1/' ./sandboxes/pxc_msb_8_0_31/node1/data/grastate.dat
$ ./sandboxes/pxc_msb_8_0_31/node1/start --wsrep-new-cluster
.. sandbox server started

At this point, notice that from node1, you have:

$ ./sandboxes/pxc_msb_8_0_31/node1/use -e "show global status where variable_name in ('wsrep_cluster_status', 'wsrep_cluster_size')"
+----------------------+---------+
| Variable_name        | Value   |
+----------------------+---------+
| wsrep_cluster_size   | 1       |
| wsrep_cluster_status | Primary |
+----------------------+---------+

But from node2 and node3 you will have:

$ ./sandboxes/pxc_msb_8_0_31/node2/use -e "show global status where variable_name in ('wsrep_cluster_status', 'wsrep_cluster_size')"
+----------------------+---------+
| Variable_name        | Value   |
+----------------------+---------+
| wsrep_cluster_size   | 2       |
| wsrep_cluster_status | Primary |
+----------------------+---------+

Looks fishy. But does your monitoring really alert you to this?

Let’s write some more data, obviously on node1, because why not? It looks healthy.

$ ./sandboxes/pxc_msb_8_0_31/node1/sysbench oltp_delete --tables=2 --table-size=1000 --events=127  run

127 will be useful later on.

Nightmare ensues

We are a few days later. You are still writing to your node. Some new reason to restart node1 comes. Maybe you want to apply a parameter.

$ ./sandboxes/pxc_msb_8_0_31/node1/restart
.............................................................................................................................................................^C

It fails?

Reviewing logs, you would find:

$ less sandboxes/pxc_msb_8_0_31/node1/data/msandbox.err
...
2023-06-22T08:23:29.003334Z 0 [ERROR] [MY-000000] [Galera] gcs/src/gcs_group.cpp:group_post_state_exchange():433: Reversing history: 171 -> 44, this member has applied 127 more events than the primary component.Data loss is possible. Must abort.
...

Voila, We find our “127” again.

Good reflex: Depends. It would need a post of its own, but that’s a serious problem.

Do not: Force SST on this node. Because it will work, and every data inserted on node1 will be lost.

What does it mean?

When forcing bootstrap, a node will always start. It won’t ever try to connect to other nodes if they are healthy. The other nodes won’t try to connect to the third one either; from their point of view, it just never joined, so it’s not part of the cluster.

When restarting the previously bootstrapped node1 in non-bootstrapped mode, that’s the first time they all see each other in a while.

Each time a transaction is committed, it is replicated along with a sequence number (seqno). The seqno is an ever-growing number. It is used by nodes to determine if incremental state transfer is possible, or if a node state is coherent with others.

Now that node1 is no longer in bootstrap mode, node1 connects to the other members. node1 shares its state (last primary members, seqno). The other nodes correctly picked up that this seqno looks suspicious because it’s higher than their own, meaning the node joining could have applied more transactions. It could also mean it was from some other cluster.

Because nodes are in doubt, nothing will happen. Node1 is denied joining and will not do anything. It won’t try to resynchronize automatically, and it won’t touch its data. Node2 and node3 are not impacted; they will be kept as is too.

How to proceed from there will depend as there are no general guidelines. Ideally, a source of truth should be found. If both clusters applied writes, that’s the toughest situation to be in, and it’s a split brain.

Note: seqno are just numbers. Having equal seqno does not actually guarantee that the underlying transactions applied are identical, but it’s still useful as a simple sanity check. If we were to mess around even more and apply 127 transactions on node2, or even modify seqno manually in grastate.dat, we could have “interesting” results. Try it out (not in production, mind you)!

Note: If you are unaware of bootstrapping and how to properly recover, check out the documentation.

Conclusion

Bootstrap is a last resort procedure, don’t force it lightly. Do not force SST right away if a node does not want to join either. You should always check the error log first.

Fortunately, PXC does not blindly let any node join without some sanity checks.

Minimize unexpected downtime and data loss with a highly available, open source MySQL clustering solution.

Download Percona XtraDB Cluster today

In this blog post, I will try to cover a few dump and data-loading utilities offered by MySQL Shell.

What is MySQL Shell?

It is a robust client and code editor for MySQL. In addition to having APIs for dealing with MySQL, MySQL Shell offers scripting capabilities for JavaScript and Python. It provides an AdminAPI to manage MySQL servers and create InnoDB Cluster and ReplicaSet deployments. It supports an X DevAPI to work on both SQL and NoSQL interfaces via the X Protocol. Finally, it provides utilities to make working with MySQL in Python and Javascript mode easier.

Now, let’s jump into some practical stuff and see what these tools offer us.

Dump Utility

In the dump utility, there are three tools: util.dumpInstance(), util.dumpSchemas(), and util.dumpTables () so let’s discuss them one by one. 

1.  “util.dumpInstance()” – This was introduced in MySQL Shell 8.0.21 and supports the export of all databases excluding information_schema, MySQL, ndbinfo, performance_schema, and sys schema. The backups can be kept locally or could be taken remotely from the MySQL Shell utility.

Performing a complete instance dump with “util.dumpInstance()”:

a) Log in to the database via MySQL Shell.

shell> mysqlsh --uri root@localhost

b) Prior to doing the actual backup, we will first perform a dryRun procedure to check for any potential problems or compatibility issues.

mysql shell > util.dumpInstance("/home/backup/", {dryRun:”true”})

Output:

dumping.
Acquiring global read lock
Global read lock acquired
Initializing - done 
1 out of 5 schemas will be dumped and within them 16 tables, 0 views.
3 out of 5 users will be dumped.
Gathering information - done 
All transactions have been started
Global read lock has been released
Writing global DDL files
Writing users DDL
Writing DDL - done         
Starting data dump
0% (0 rows / ~1.60M rows), 0.00 rows/s, 0.00 B/s uncompressed, 0.00 B/s compressed

 So, here we didn’t get any issues or warnings so we are good to proceed with the actual backup process.

c) Running the full instance backup and saving in the target location.

mysql shell >util.dumpInstance("/home/backup/")

Output:

dumping.
Acquiring global read lock
Global read lock acquired
Initializing - done 
1 out of 5 schemas will be dumped and within them 16 tables, 0 views.
3 out of 5 users will be dumped.
Gathering information - done 
All transactions have been started
Global read lock has been released
Writing global DDL files
Writing users DDL
Running data dump using 4 threads.
NOTE: Progress information uses estimated values and may not be accurate.
Writing schema metadata - done       
Writing DDL - done         
Writing table metadata - done         
Starting data dump
101% (1.63M rows / ~1.60M rows), 114.56K rows/s, 22.13 MB/s uncompressed, 10.09 MB/s compressed                 
Dump duration: 00:00:15s                                                                       
Total duration: 00:00:15s                                                                      
Schemas dumped: 1                                                                              
Tables dumped: 16                                                                              
Uncompressed data size: 313.55 MB                                                              
Compressed data size: 142.87 MB                                                                
Compression ratio: 2.2                                                                         
Rows written: 1629368                                                                          
Bytes written: 142.87 MB                                                                       
Average uncompressed throughput: 20.01 MB/s                                                    
Average compressed throughput: 9.12 MB/s

We have successfully finished the backup here. By default, it employs “4 threads” and applies the zstd compression algorithm to the database backup.

d) Running the instance dump with more advanced options.

mysql shell >util.dumpInstance("/home/backup/", {threads:8,maxRate:"100M",consistent:true,chunking:true,bytesPerchunk:"64M",compression:"zstd"})

Output:

Acquiring global read lock
Global read lock acquired
Initializing - done 
1 out of 5 schemas will be dumped and within them 16 tables, 0 views.
3 out of 5 users will be dumped.
Gathering information - done 
All transactions have been started
Global read lock has been released
Writing global DDL files
Writing users DDL
Running data dump using 8 threads.
NOTE: Progress information uses estimated values and may not be accurate.
Writing schema metadata - done       
Writing DDL - done         
Writing table metadata - done         
Starting data dump
101% (1.63M rows / ~1.60M rows), 468.47K rows/s, 84.22 MB/s uncompressed, 38.41 MB/s compressed                  
Dump duration: 00:00:03s                                                                       
Total duration: 00:00:03s                                                                      
Schemas dumped: 1                                                                              
Tables dumped: 16                                                                              
Uncompressed data size: 313.55 MB                                                              
Compressed data size: 142.87 MB                                                                
Compression ratio: 2.2                                                                         
Rows written: 1629368                                                                          
Bytes written: 142.87 MB                                                                       
Average uncompressed throughput: 85.24 MB/s                                                    
Average compressed throughput: 38.84 MB/s

There are various options available to control and enhance the dump process.

“dryRun”: List out all of the compatibility issues before starting the dump.
“consistent”: In order to provide consistent backup, it automatically acquires a global read lock. By default, it is turned on.
“chunking”: splits each table's data into a number of files. It is enabled by default.
“bytesPerChunk”: specify the approximate number of bytes that should be written for each data block. 
“threads”: The number of parallel threads to employ when dumping data from the MySQL instance. "4" threads are used by default.
“maxRate”: Maximum data read throughput for each thread during the dump in bytes per second.

2) “util.dumpSchemas” –  This was introduced in MySQL Shell 8.0.21 and supports the export of selected databases either locally or remotely. 

Performing database dump with “util.dumpSchemas()”. Here we will take a specific database(“sbtest”) backup only.

mysql shell > util.dumpSchemas(["sbtest"],"/home/backup/sbtest/",{threads :2})

Output:

dumping.
Acquiring global read lock
Global read lock acquired
Initializing - done 
1 schemas will be dumped and within them 16 tables, 0 views.
Gathering information - done 
All transactions have been started
Global read lock has been released
Writing global DDL files
Running data dump using 2 threads.
NOTE: Progress information uses estimated values and may not be accurate.
Writing schema metadata - done       
Writing DDL - done         
Writing table metadata - done         
Starting data dump
104% (1.63M rows / ~1.56M rows), 424.56K rows/s, 81.96 MB/s uncompressed, 37.35 MB/s compressed                  
Dump duration: 00:00:03s                                                                       
Total duration: 00:00:03s                                                                      
Schemas dumped: 1                                                                              
Tables dumped: 16                                                                              
Uncompressed data size: 313.55 MB                                                              
Compressed data size: 142.87 MB                                                                
Compression ratio: 2.2                                                                         
Rows written: 1629368                                                                          
Bytes written: 142.87 MB                                                                       
Average uncompressed throughput: 81.82 MB/s                                                    
Average compressed throughput: 37.28 MB/s

3) “util.dumpTables” –  In MySQL Shell 8.0.22, the table dump utility “util.dumpTables()” was released.

 Performing selective tables dump. Here we take only table “sbtest2” dump from the “sbtest” database.

mysql shell> util.dumpTables("sbtest", [ "sbtest2"], "/home/backup/sbtest/",{threads: 4})

Output:

Writing schema metadata - done       
Writing DDL - done       
Writing table metadata - done       
Starting data dump
101% (101.89K rows / ~100.58K rows), 0.00 rows/s, 0.00 B/s uncompressed, 0.00 B/s compressed                
Dump duration: 00:00:00s                                                                    
Total duration: 00:00:00s                                                                   
Schemas dumped: 1                                                                           
Tables dumped: 1                                                                            
Uncompressed data size: 19.61 MB                                                            
Compressed data size: 8.93 MB                                                               
Compression ratio: 2.2                                                                      
Rows written: 101892                                                                        
Bytes written: 8.93 MB                                                                      
Average uncompressed throughput: 19.61 MB/s                                                 
Average compressed throughput: 8.93 MB/s

Data load utility

So far, we’ve seen how to use various methods to take a dump. We will now demonstrate how to restore the same using a single restoration command for all sorts of backups.

The utility “util.loadDump()” for loading dumps was introduced in MySQL Shell 8.0.21. It enabled the parallel loading of tables or table chunks during the data-loading process. 

Load the dump files using util.loadDump().

a) Running a dry run process to validate any compatibility issues or errors prior to the actual restoration process.

mysql shell > util.loadDump("/home/backup/", {dryRun :"true"})

Output:

Loading DDL and Data from '/home/backup/' using 4 threads.
Opening dump...
dryRun enabled, no changes will be made.
Target is MySQL 5.7.38-41-57-log. Dump was produced from MySQL 5.7.38-41-57-log
Scanning metadata - done       
Checking for pre-existing objects...
Executing common preamble SQL
Executing DDL - done       
Executing view DDL - done       
Starting data load
Executing common postamble SQL                        
0% (0 bytes / 313.55 MB), 0.00 B/s, 16 / 16 tables done
Recreating indexes - done 
No data loaded.                                        
0 warnings were reported during the load.

Note – no data loaded, it just prints us information about any warnings or errors during the restoration process.

b) Running the data restoration process.

mysql shell > util.loadDump("/home/backup/", {progressFile :"/home/backuplog.json",threads:4,backgroundThreads:4,maxBytesPerTransaction:"4096"})

Output:

Loading DDL and Data from '/home/backup/' using 4 threads.
Opening dump...
Target is MySQL 5.7.38-41-57-log. Dump was produced from MySQL 5.7.38-41-57-log
Scanning metadata - done       
Checking for pre-existing objects...
Executing common preamble SQL
Executing DDL - done       
Executing view DDL - done       
Starting data load
Executing common postamble SQL                                              
100% (313.55 MB / 313.55 MB), 3.03 MB/s, 16 / 16 tables done                
Recreating indexes - done       
16 chunks (1.63M rows, 313.55 MB) for 16 tables in 1 schemas were loaded in 2 min 43 sec (avg throughput 1.92 MB/s)
0 warnings were reported during the load.

Here, we have successfully completed the restoration process.

There are various options available to control and enhance the data loading process.

“threads”:The number of parallel threads to employ when dumping data from the MySQL instance. "4" is used by default.
“backgroundThreads”:the number of threads in the pool of background threads that were used to retrieve the file data.
“maxBytesPerTransaction”:The maximum number of bytes that can be loaded from a data file with a single LOAD DATA statement.
“loadIndexes”: Secondary indexes are not built during the import when this option is set to false; you must do so afterwards. This may come in handy if you're loading the DDL files and data files separately and want to modify the table structure after the DDL files have been loaded.
“deferTableIndexes”:Postpone creating secondary indexes until after loading the table's contents. This may speed up loading. off denotes that all indexes are built as the table is loaded.

Taking backups on cloud storage (GCP and AWS S3)

MySQL Shell also provides the capability to store dumps remotely over some S3-compatible cloud storage such as Amazon S3 and Google Cloud storage. Here, we’ll talk about how to do it with Google Cloud storage and an Amazon S3 environment.

Let’s start by configuring it for Google Cloud storage.

Prerequisites

• Storage Bucket should exist in Google Cloud
• Access keys and secrets defined in a file(“/home/credentials”) 

[gcb]
aws_access_key_id=xxxxxxxx
aws_secret_access_key=xxxxxxxx

•  GCP cloud storage endpoint – “https://storage.googleapis.com”

 

a) Running full backup and putting it in the GCP bucket “ajpxctest”.

mysql shell> util.dumpInstance("sbtest",{s3BucketName: "ajpxctest",s3EndpointOverride: "https://storage.googleapis.com", s3CredentialsFile:"/home/credentials", s3Profile: "gcb", threads: 4})

Output:

dumping.
Acquiring global read lock
Global read lock acquired
Initializing - done 
1 out of 5 schemas will be dumped and within them 16 tables, 0 views.
3 out of 5 users will be dumped.
Gathering information - done 
All transactions have been started
Global read lock has been released
Writing global DDL files
Writing users DDL
Running data dump using 4 threads.
NOTE: Progress information uses estimated values and may not be accurate.
Writing schema metadata - done       
Writing DDL - done         
Writing table metadata - done         
Starting data dump
104% (1.63M rows / ~1.56M rows), 172.12K rows/s, 34.60 MB/s uncompressed, 15.75 MB/s compressed                  
Dump duration: 00:00:12s                                                                       
Total duration: 00:00:14s                                                                      
Schemas dumped: 1                                                                              
Tables dumped: 16                                                                              
Uncompressed data size: 313.55 MB                                                              
Compressed data size: 142.87 MB                                                                
Compression ratio: 2.2                                                                         
Rows written: 1629368                                                                          
Bytes written: 142.87 MB                                                                       
Average uncompressed throughput: 25.21 MB/s                                                    
Average compressed throughput: 11.49 MB/s

b) Then, using these same details, we will restore the data from the S3 bucket into the local database.

mysql shell> util.loadDump("sbtest",{s3BucketName: "ajpxctest", s3EndpointOverride: "https://storage.googleapis.com",s3CredentialsFile:"/home/credentials", s3Profile: "gcb", threads: 4})

Output:

Loading DDL and Data from AWS S3 bucket=ajpxctest, prefix='sbtest' using 4 threads.
Opening dump...
Target is MySQL 5.7.38-41-57-log. Dump was produced from MySQL 5.7.38-41-57-log
Fetching dump data from remote location...
Listing files - done 
Scanning metadata - done         
Checking for pre-existing objects...
Executing common preamble SQL
Executing DDL - done         
Executing view DDL - done       
Starting data load
1 thds loading / 100% (313.55 MB / 313.55 MB), 6.23 MB/s, 14 / 16 tables done
Recreating indexes - done       
Executing common postamble SQL                                               
16 chunks (1.63M rows, 313.55 MB) for 16 tables in 1 schemas were loaded in 1 min 8 sec (avg throughput 7.41 MB/s)
0 warnings were reported during the load.      

S3 options:

“s3BucketName”: Name of the s3 bucket where the dump will be kept.
“s3EndpointOverride”: s3 endpoint or url.
“s3CredentialsFile”: Credential file containing key & secret details.
“s3Profile”: The name of the s3 profile to identify the credential details.

Let’s configure the same for Amazon S3 

Prerequisites

• AWS S3 bucket should exist
• Configure AWS CLI

###~/.aws/credentials### 
[default]
aws_access_key_id=xxxxxxx
aws_secret_access_key=xxxxxxx

###~/.aws/config### 
[default]
region = us-east-1

a) Now, let’s perform the instance backup process which keeps the dump file in “ajtestbkp” S3 bucket.

mysql shell> util.dumpInstance("sbtest",{s3bucketName: "ajtestbkp",threads: 4})

Output:

Acquiring global read lock
Global read lock acquired
Initializing - done 
1 out of 5 schemas will be dumped and within them 16 tables, 0 views.
3 out of 5 users will be dumped.
Gathering information - done 
All transactions have been started
Global read lock has been released
Writing global DDL files
Writing users DDL
Running data dump using 4 threads.
NOTE: Progress information uses estimated values and may not be accurate.
Writing schema metadata - done       
Writing DDL - done         
Writing table metadata - done         
Starting data dump
101% (1.63M rows / ~1.61M rows), 287.53K rows/s, 53.81 MB/s uncompressed, 24.51 MB/s compressed                  
Dump duration: 00:00:06s                                                                       
Total duration: 00:00:07s                                                                      
Schemas dumped: 1                                                                              
Tables dumped: 16                                                                              
Uncompressed data size: 313.55 MB                                                              
Compressed data size: 142.87 MB                                                                
Compression ratio: 2.2                                                                         
Rows written: 1629368                                                                          
Bytes written: 142.87 MB                                                                       
Average uncompressed throughput: 49.93 MB/s                                                    
Average compressed throughput: 22.75 MB/s

b) Now, let’s restore the dump from S3 into our local database.

mysql shell> util.loadDump("sbtest",{s3BucketName: "ajtestbkp", threads: 4})

Output:

Loading DDL and Data from AWS S3 bucket=ajtestbkp, prefix='sbtest' using 4 threads.
Opening dump...
Target is MySQL 5.7.38-41-57-log. Dump was produced from MySQL 5.7.38-41-57-log
Fetching dump data from remote location...
Listing files - done 
Scanning metadata - done        
Checking for pre-existing objects...
Executing common preamble SQL
Executing DDL - done         
Executing view DDL - done       
Starting data load
1 thds loading / 100% (313.55 MB / 313.55 MB), 8.80 MB/s, 15 / 16 tables done
Recreating indexes - done       
Executing common postamble SQL                                               
16 chunks (1.63M rows, 313.55 MB) for 16 tables in 1 schemas were loaded in 38 sec (avg throughput 9.14 MB/s)
0 warnings were reported during the load.

Summary

These utilities are a great addition to MySQL Shell and very useful in the scenario of backup/restoration of large tables by using different control options. Now, we can have better handling of the dump/load process by defining options such as threads, max rate, and maxBytesPerTransaction.  Dump utilities are supported in (MySQL 5.6,5.7, and 8.0), however, for data loading, the database should be either MySQL 5.7 or 8.0.

This article outlines the basic configurations for setting up and deploying Percona XtraDB Cluster 8.0 (PXC) on Amazon EC2, as well as what is new in the setup compared to Percona XtraDB Cluster 5.7.

What is Percona XtraDB Cluster an ideal fit for?

Percona XtraDB Cluster is a cost-effective, high-performance clustering solution for mission-critical data. It combines all the improvements, and functionality found in MySQL 8 with Percona Server for MySQL‘s Enterprise features and Percona’s upgraded Galera library.

A Percona XtraDB Cluster environment is an ideal fit for applications requiring 5-9s uptime with high read workloads; industries like financial or healthcare businesses that require in-house or externally dedicated database resources.

How is a three-node cluster configured in an EC2 environment?

In order to describe the setup procedures, I’ll be using Amazon EC2 instances to build the environment, and based on the business requirements, we may utilize alternative infrastructures to build the cluster environment.

Amazon EC2 settings are designed to provide uptime and high availability. When designing architecture in an EC2 environment, it is preferable to have one node situated in another Availability Zones to avoid the loss of an entire AZ and its data.

If a different region is planned for a node, we can prevent the loss of the entire region and its data. It is desirable that nodes and regions have appropriate network connectivity because network latency between the two regions affects synchronous replication write latency. Alternatively, an async replica in a different region is an option.

I’m not going into too much depth on Amazon EC2 to keep this blog brief and readable. 

To build the three-node Percona XtraDB 8.0 cluster environment we first spin up the following three nodes in EC2. I’m using Amazon Linux but you can also use Ubuntu or any of the Percona-supported operating systems.

It is advised that a cluster’s nodes all have the same configuration. 

PXCNode1   IP Address: 172.31.18.119

PXCNode2   IP Address: 172.31.86.114

PXCNode3  IP Address: 172.31.26.152

To install the Percona repository on all three nodes, use the following command.

$ sudo yum install -y https://repo.percona.com/yum/percona-release-latest.noarch.rpm

Enable the Percona Server for MySQL 8.0 repository in all three nodes by running the following command.

$ sudo percona-release setup pxc-80
* Disabling all Percona Repositories
* Enabling the Percona XtraDB Cluster 8.0 repository
* Enabling the Percona Tools repository
<*> All done!
$

Using the following command, install the Percona XtraDB Cluster packages and software on all three nodes.

$ sudo yum install percona-xtradb-cluster

Before starting the nodes, update the basic variables listed below for the nodes

The following default variables must be modified with the first installations. Those that came from PXC 5.7 might wonder why wsrep_sst_auth is missing. The wsrep_sst_auth variable was removed in PXC 8 since it causes security concerns, as the user and password are saved in the config file and are easily visible to OS users. 

In PXC 8, a temporary user is created when a new node joins the existing cluster. For additional details on this security enhancement, check this article.

$ vi /etc/my.cnf
..
######## wsrep ###############
wsrep_provider=/usr/lib64/galera4/libgalera_smm.so
wsrep_cluster_address=gcomm://172.31.18.119,172.31.86.114,172.31.26.152
wsrep_slave_threads=8
wsrep_log_conflicts
wsrep_node_address=172.31.18.119
wsrep_cluster_name=pxc-cluster
#If wsrep_node_name is not specified,  then system hostname will be used
wsrep_node_name=pxc-cluster-node-1
wsrep_sst_method=xtrabackup-v2
..

Percona XtraDB Cluster nodes utilize the following ports by default, so we must open them and ensure that the nodes can communicate with one another.

3306 is used for MySQL client connections and SST (State Snapshot Transfer) via mysqldump.

    4444 is used for SST via Percona XtraBackup.

    4567 is used for write-set replication traffic (over TCP) and multicast replication (over TCP and UDP).

    4568 is used for IST (Incremental State Transfer).

For example, to test access.

Node 1
#  socat - TCP-LISTEN:4444
hello

Node 2
# echo "hello" | socat - TCP:172.31.18.119:4444
#

How is the first node bootstrapped?

After configuring each PXC node, you must bootstrap the cluster starting with the first node. All of the data that you wish to replicate to additional nodes must be present on the first node. 

Run the command to bootstrap the first node.

# systemctl start mysql@bootstrap.service
#

Since this is a brand new install, a temporary password is generated for the ‘root’ MySQL user which we can find in the mysqld.log

# grep -i "A temporary password is generated " /var/log/mysqld.log
2022-09-13T06:52:37.700818Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: PmqQiGf*#6iy
#

Reset the temporary password using the following alter.

$ mysql -uroot -p
Enter password:
mysql> SET PASSWORD = 'GdKG*12#ULmE';
Query OK, 0 rows affected (0.03 sec)

How can the cluster’s remaining nodes be joined?

Before starting node2, you must copy the SSL certificates from node1 to node2 (and to node3). PXC 8 by default encrypts all replication communication, so this is a critical step that most users miss, causing cluster startup failures.

PXCNode1# scp /var/lib/mysql/*.pem 172.31.86.114:/var/lib/mysql/
PXCNode2# chown -R mysql.mysql /var/lib/mysql/*.pem

Start the node.

PXCNode2# systemctl start mysql
#

Verify the following SET and make sure they appear as below once the node has been added to the cluster.

PXCNode2$ mysql -uroot -p -e " show global status where variable_name IN ('wsrep_local_state','wsrep_local_state_comment','wsrep_cluster_size','wsrep_cluster_status','wsrep_connected','wsrep_ready');"
Enter password:
+---------------------------+---------+
| Variable_name             | Value   |
+---------------------------+---------+
| wsrep_cluster_size        | 2       |
| wsrep_cluster_status      | Primary |
| wsrep_connected           | ON      |
| wsrep_local_state         | 4       |
| wsrep_local_state_comment | Synced  |
| wsrep_ready               | ON      |
+---------------------------+---------+

The third node may be added to the cluster using the same procedures, and its status will then look as follows.

PXCNode3$ mysql -uroot -p -e " show global status where variable_name IN ('wsrep_local_state','wsrep_local_state_comment','wsrep_cluster_size','wsrep_cluster_status','wsrep_connected','wsrep_ready');"
Enter password:
+---------------------------+---------+
| Variable_name             | Value   |
+---------------------------+---------+
| wsrep_cluster_size        | 3       |
| wsrep_cluster_status      | Primary |
| wsrep_connected           | ON      |
| wsrep_local_state         | 4       |
| wsrep_local_state_comment | Synced  |
| wsrep_ready               | ON      |
+---------------------------+---------+

Additional supporting factors

Additionally, the use of failover technologies like ProxySQL, which assist in removing failing nodes from the active read pool in some cases and shifting the primary, is advised.

It is advised to have a backup in place, with the open-source tool Percona XtraBackup taking physical copies of the dataset that are significantly faster to recover. It is strongly advised to back up binary logs using mysqlbinlog in order to do point-in-time recovery. Backups should be encrypted, compressed, and transferred to S3 as soon as possible.

To copy binlogs to an s3 bucket the command looks like this.

aws s3 sync /backups/binlogs/ s3://backup-bucket/
upload: ../../../../backups/binlogs/binlog.000001.gz.gpg to 
s3://backup-bucket/binlog.000001.gpg

For query analytics and time-based database performance insights, the open-source tool Percona Monitoring and Management is highly recommended. Using the Amazon Marketplace, this needs to be deployed on a separate host. This monitors the operating system and MySQL metrics and provides sophisticated query analytics.