-
Deploying Cross-Site Replication in Percona Operator for MySQL (PXC)
Having a separate DR cluster for production databases is a modern day requirement or necessity for tech and other related businesses that rely heavily on their database systems. Setting up such a [DC -> DR] topology for Percona XtraDB Cluster (PXC), which is a virtually- synchronous cluster, can be a bit challenging in a complex Kubernetes environment.
Here, Percona Operator for MySQL comes in handy, with a minimal number of steps to configure such a topology, which ensures a remote side backup or a disaster recovery solution.
So without taking much time, let’s see how the overall setup and configurations look from a practical standpoint.
PXC Cross-Site/Disaster Recovery
DC Configuration
1) Here we have a three-node PXC cluster running on the DC side.shell> kubectl get pods -n pxc
NAME READY STATUS RESTARTS AGE
cluster1-haproxy-0 2/2 Running 0 23h
cluster1-haproxy-1 2/2 Running 0 23h
cluster1-haproxy-2 2/2 Running 0 23h
cluster1-pxc-0 3/3 Running 0 23h
cluster1-pxc-1 3/3 Running 0 7h37m
cluster1-pxc-2 3/3 Running 0 7h18m
percona-xtradb-cluster-operator-6756dbf588-vxjxt 1/1 Running 0 24h
xb-backup1-hlz2p 0/1 Completed 0 21h
xb-cron-cluster1-fs-pvc-2026480026-372f8-2gfhr 0/1 Completed 0 13h2) There are some configuration options which have to be enabled in a custom resource file[cr.yaml] to allow cross-site replication.
Expose all source PXC nodes so they can be communicated from outside or DR cluster.
expose:
enabled: true
Type: LoadBalancer
Define a dedicated replication channel and enable the source option.
replicationChannels:
- name: pxc1_to_pxc2
isSource: true
Finally, applying the custom resource changes.
shell> kubectl apply -f cr.yaml3) Now we will notice some “EXTERNAL IP” details for each PXC node. This is the endpoint that DR node [cluster1-pxc-0] will use to connect to DC.shell> kubectl get svc
NAME TYPE CLUSTER-IP EXTERNAL-IP PORT(S) AGE
cluster1-haproxy ClusterIP 34.118.227.249 <none> 3306/TCP,3309/TCP,33062/TCP,33060/TCP,8404/TCP 4h1m
cluster1-haproxy-replicas ClusterIP 34.118.225.41 <none> 3306/TCP 4h1m
cluster1-pxc ClusterIP None <none> 3306/TCP,33062/TCP,33060/TCP 4h1m
cluster1-pxc-0 LoadBalancer 34.118.234.140 34.29.145.138 3306:30425/TCP 4h1m
cluster1-pxc-1 LoadBalancer 34.118.239.132 34.30.233.0 3306:31340/TCP 4h1m
cluster1-pxc-2 LoadBalancer 34.118.236.64 35.225.0.19 3306:30642/TCP 4h1m
cluster1-pxc-unready ClusterIP None <none> 3306/TCP,33062/TCP,33060/TCP 4h1m
percona-xtradb-cluster-operator ClusterIP 34.118.235.168 <none> 443/TCP 4h11mAt this point, we are done with the DC setup. Next, we will take a backup from Source which we later used to build the DR.
Backup
Defining access key/secrets to connect to the GCP/S3 bucket.
cat backup-secret-s3.yamlapiVersion: v1
kind: Secret
metadata:
name: my-cluster-name-backup-s3
type: Opaque
data:
AWS_ACCESS_KEY_ID: <KEY>
AWS_SECRET_ACCESS_KEY: <SECRET>
In the custom resource file [cr.yaml] , we also need to define the bucket , secret file and endpoint/region details.
backup:
storages:
s3-us-west:
type: s3
verifyTLS: true
s3:
bucket: <bucket>
credentialsSecret: my-cluster-name-backup-s3
region: us-west-2
endpointUrl: https://storage.googleapis.com…shell> kubectl apply -f cr.yaml
Finally, we can take the backup by creating a [backup.yaml] file with below details.
apiVersion: pxc.percona.com/v1
kind: PerconaXtraDBClusterBackup
metadata:
# finalizers:
# - percona.com/delete-backup
name: backup1
spec:
pxcCluster: cluster1
storageName: s3-us-west…shell> kubectl apply -f cr.yaml
We can verify the successful backup as follows.
kubectl get pxc-backup
NAME CLUSTER STORAGE DESTINATION STATUS COMPLETED AGE
backup1 cluster1 s3-us-west s3://<bucket>/cluster1-2026-04-07-15:55:46-full Succeeded 125m 127mAs the backup is also ready, we can now move to the DR setup part.
DR Configuration
Below we have a similar PXC setup as having in DC in a separate Node/ K8s Cluster.kubectl get pods -n pxc-dr
NAME READY STATUS RESTARTS AGE
cluster1-haproxy-0 2/2 Running 0 35h
cluster1-haproxy-1 2/2 Running 0 35h
cluster1-haproxy-2 2/2 Running 0 35h
cluster1-pxc-0 3/3 Running 0 35h
cluster1-pxc-1 3/3 Running 0 35h
cluster1-pxc-2 3/3 Running 0 35h
percona-xtradb-cluster-operator-6756dbf588-2wc5m 1/1 Running 0 38h
prepare-job-restore1-cluster1-8h4vn 0/1 Completed 0 35h
restore-job-restore1-cluster1-trfg6 0/1 Completed 0 35h
xb-cron-cluster1-fs-pvc-2026480025-372f8-wv6bt 0/1 Completed 0 28h
xb-cron-cluster1-fs-pvc-2026490025-372f8-gxd59 0/1 Completed 0 4h48mFirst, we need to restore the backup on the DR server.
Data Restoration
Here we will create the [backup-secret-s3.yaml] file which contains the GCP/S3 credentials.
apiVersion: v1
kind: Secret
metadata:
name: my-cluster-name-backup-s3
type: Opaque
data:
AWS_ACCESS_KEY_ID: <KEY>
AWS_SECRET_ACCESS_KEY: <SECRET>…shell> kubectl apply -f backup-secret-s3.yaml
Next, we will create a [restore.yaml] file while mentioning the backup source and other useful information.
apiVersion: pxc.percona.com/v1
kind: PerconaXtraDBClusterRestore
metadata:
name: restore1
# annotations:
# percona.com/headless-service: "true"
spec:
pxcCluster: cluster1
backupSource:
# verifyTLS: true
destination: s3://<bucket>/cluster1-2026-04-07-15:55:46-full
s3:
bucket: <bucket>
credentialsSecret: my-cluster-name-backup-s3
endpointUrl: https://storage.googleapis.com/…shell> kubectl apply -f restore.yaml
Once the restoration is finished successfully, we will see the status below.
shell> kubectl get pxc-restore
NAME CLUSTER STATUS COMPLETED AGE
restore1 cluster1 Succeeded 27mNow we can do the remaining DR changes in the custom resource file [cr.yaml]. Basically, we need to add the replication channel and all source EXTERNAL-IPs. This cross-DC replication supports Automatic Asynchronous Replication Connection Failover feature, so in case any of the DC node is down, the Replica can connect and resume from other available DC nodes.replicationChannels:
- name: pxc1_to_pxc2
isSource: false
sourcesList:
- host: 34.29.145.138
port: 3306
weight: 100
- host: 34.30.233.0
port: 3306
weight: 100
- host: 35.225.0.19
port: 3306
weight: 100…shell> kubectl apply -f cr.yamlFor backup and restoration on the PXC operator, the manuals below can be referenced further.
https://docs.percona.com/percona-operator-for-mysql/pxc/backups-ondemand.html
https://docs.percona.com/percona-operator-for-mysql/pxc/backups-restore-to-new-cluster.html
Replication
Initially, when we check the replication status, we can notice the following error. This is because with [caching_sha2_password] authentication, it should be a secure SSL/TLS communication, or else we can use SOURCE_PUBLIC_KEY_PATH/GET_SOURCE_PUBLIC_KEY which basicaly enables the RSA key pair-based password exchange by requesting the public key from the source. shell> kubectl exec -it cluster1-pxc-0 -- sh
shell> mysql -uroot -pmysql> show replica status\G;
*************************** 1. row ***************************
Replica_IO_State: Connecting to source
Source_Host: 35.225.0.19
Source_User: replication
Source_Port: 3306
Connect_Retry: 60
Source_Log_File:
Read_Source_Log_Pos: 4
Relay_Log_File: cluster1-pxc-0-relay-bin-pxc1_to_pxc2.000001
Relay_Log_Pos: 4
Relay_Source_Log_File:
Replica_IO_Running: Connecting
Replica_SQL_Running: Yes
...
Error:
Last_IO_Error: Error connecting to source 'replication@35.225.0.19:3306'. This was attempt 2/3, with a delay of 60 seconds between attempts. Message: Access denied for user 'replication'@'35.225.0.19.' (using password: YES)Once we passed “GET_SOURCE_PUBLIC_KEY” in the “CHANGE REPLICATION” command the error is resolved and DR successfully able to communicate with the DC.mysql> STOP REPLICA;
mysql> STOP REPLICA IO_THREAD FOR CHANNEL 'pxc1_to_pxc2';
mysql> CHANGE REPLICATION SOURCE TO SOURCE_USER='replication', SOURCE_PASSWORD='password', GET_SOURCE_PUBLIC_KEY=1 FOR CHANNEL 'pxc1_to_pxc2';
mysql> START REPLICA;
Note – The Replication user will be auto-created on the DC node. So, with the help of below command we can get the decoded password for “replication” user.
shell> kubectl get secret cluster1-secrets -o jsonpath="{.data.replication}" | base64 --decodemysql> show replica status\G;
*************************** 1. row ***************************
Replica_IO_State: Waiting for source to send event
Source_Host: 35.225.0.19
Source_User: replication
Source_Port: 3306
Connect_Retry: 60
Source_Log_File: binlog.000006
Read_Source_Log_Pos: 3047027
Relay_Log_File: cluster1-pxc-0-relay-bin-pxc1_to_pxc2.000001
Relay_Log_Pos: 150132
Relay_Source_Log_File: binlog.000006
Replica_IO_Running: Yes
Replica_SQL_Running: Yes
...The other PXC DR nodes will sync as usual with the Galera Synchronous replication process.
Source Failover
The asynchronous connection failover is already enabled on the DR as we defined initially in the custom resource file. The “External IPs” shows different here because they changed in this testing scenario.mysql> select * from performance_schema.replication_asynchronous_connection_failover;
+--------------+---------------+------+-------------------+--------+--------------+
| CHANNEL_NAME | HOST | PORT | NETWORK_NAMESPACE | WEIGHT | MANAGED_NAME |
+--------------+---------------+------+-------------------+--------+--------------+
| pxc1_to_pxc2 | 34.29.145.138 | 3306 | | 100 | |
| pxc1_to_pxc2 | 34.45.151.96 | 3306 | | 100 | |
| pxc1_to_pxc2 | 34.71.57.38 | 3306 | | 100 | |
+--------------+---------------+------+-------------------+--------+--------------+
3 rows in set (0.00 sec)Now, in case the existing Source DC[cluster1-pxc-2] is down, the DR will connect to one of the other available DC nodes based on the “Weight” and chronological order [pxc-2, pxc-1, pxc-0 etc].
Here, we temporarily take down the Source DC[cluster1-pxc-2] node.
kubectl get pods -n pxc
NAME READY STATUS RESTARTS AGE
cluster1-haproxy-0 2/2 Running 0 2d3h
cluster1-haproxy-1 2/2 Running 0 2d3h
cluster1-haproxy-2 2/2 Running 0 2d3h
cluster1-pxc-0 3/3 Running 0 2d3h
cluster1-pxc-1 3/3 Running 0 35h
cluster1-pxc-2 2/3 Running 1 (6s ago) 34h
percona-xtradb-cluster-operator-6756dbf588-vxjxt 1/1 Running 0 2d3h
xb-backup1-hlz2p 0/1 Completed 0 2d1h
xb-cron-cluster1-fs-pvc-2026480026-372f8-2gfhr 0/1 Completed 0 41h
xb-cron-cluster1-fs-pvc-2026490026-372f8-mgfpv 0/1 Completed 0 17h
The DR replication breaks as it can’t reach the DC [cluster1-pxc-2].
mysql> show replica status\G;
*************************** 1. row ***************************
Replica_IO_State: Reconnecting after a failed source event read
Source_Host: 34.71.57.38
Source_User: replication
Source_Port: 3306
Connect_Retry: 60
Source_Log_File: binlog.000012
Read_Source_Log_Pos: 198
Relay_Log_File: cluster1-pxc-0-relay-bin-pxc1_to_pxc2.000002
Relay_Log_Pos: 369
Relay_Source_Log_File: binlog.000012
Replica_IO_Running: Connecting
Replica_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Source_Log_Pos: 198
Relay_Log_Space: 602
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Source_SSL_Allowed: No
Source_SSL_CA_File:
Source_SSL_CA_Path:
Source_SSL_Cert:
Source_SSL_Cipher:
Source_SSL_Key:
Seconds_Behind_Source: NULL
Source_SSL_Verify_Server_Cert: Yes
Last_IO_Errno: 2003
Last_IO_Error: Error reconnecting to source 'replication@34.71.57.38:3306'. This was attempt 2/3, with a delay of 60 seconds between attempts. Message: Can't connect to MySQL server on '34.71.57.38:3306' (111)
Once it reaches the “source_retry_count” and “source_connect_retry”, the Replica connects to another Source DC[cluster1-pxc-1].
mysql> show replica status\G;
*************************** 1. row ***************************
Replica_IO_State: Waiting for source to send event
Source_Host: 34.45.151.96
Source_User: replication
Source_Port: 3306
Connect_Retry: 60
Source_Log_File: binlog.000007
Read_Source_Log_Pos: 198
Relay_Log_File: cluster1-pxc-0-relay-bin-pxc1_to_pxc2.000003
Relay_Log_Pos: 369
Relay_Source_Log_File: binlog.000007
Replica_IO_Running: Yes
Replica_SQL_Running: Yes
...
Quick Summary
In this blog post, we walk through the steps to configure Cross-Site Replication in the Percona PXC operator. Although we have used the operator native Xtrabackup to feed the data to the DR via the restore process, we can also use logical backup options like (mysqldump, mydumper, etc.) to accomplish the same goals.
Using an “Asynchronous Replication” process to sync DR could lead to delays or replication lag due to its flow, or, more importantly, when working across data centres, where network latency is a big factor. However, adding a DR(PXC) cluster to DC(PXC) directly via synchronous replication could be more impactful or lead to flow control issues if any of the DR nodes struggle or experience performance/saturation issues. So, it’s equally important to consider all aspects or challenges before deploying in production.
The post Deploying Cross-Site Replication in Percona Operator for MySQL (PXC) appeared first on Percona.
-
MySQL MCP Server v1.7.0 is out
April 19, 2026
It took three release candidates and more CI tweaks than I’d like to admit, but v1.7.0 is finally tagged GA. Here’s what actually changed and why it matters.
The thing I kept getting asked about: add_connection
Almost every multi-database user hits the same wall: you configure your connections at startup, and that’s it. Want to point Claude at a different instance mid-session? Restart the server. Not great.
add_connection fixes that. Enable it with MYSQL_MCP_EXTENDED=1 and MYSQL_MCP_ENABLE_ADD_CONNECTION=1, and Claude can register a new named connection on the fly — DSN validation, duplicate-name rejection, and a hard block on the root MySQL user all happen before the connection is accepted. Once it’s in, use_connection it works as usual.
It’s intentionally opt-in behind two flags. Allowing an AI client to register arbitrary database connections at runtime warrants an explicit “yes, I want this” from the operator.
Finding stuff across a big schema: search_schema and schema_diff
Two tools I personally felt the absence of every time I was debugging a large schema.
search_schema does what it sounds like — pattern-match against table and column names across all accessible databases. Before this, you’d either write the query yourself or ask Claude to guess where a column lived. Now you just ask.
schema_diff is the one I’m more excited about. Point it at two databases, and it tells you what’s structurally different. Columns that exist in staging but not prod, type mismatches, missing indexes — all surface immediately. We’ve already caught more than a few “oh, that migration never ran” moments with it.
Pagination, retries, and the unglamorous stuff
run_query now supports an offset parameter for SELECT and UNION queries, returning has_more and next_offset in the response. Big result sets no longer mean hitting row caps and wondering what you missed.
Retries got a proper implementation too. Transient errors — bad pooled connections, deadlocks, lock wait timeouts — now trigger exponential backoff instead of just failing. After a driver.ErrBadConn the pool is re-pinged, which cuts recovery time noticeably after a MySQL restart.
Neither of these is flashy, but they’re the kind of thing that makes the tool feel solid rather than fragile.
Column masking
Set MYSQL_MCP_MASK_COLUMNS=email,password,ssn and those columns are redacted in every run_query response. Nothing leaves the server. No query rewrites, no application changes. It’s a small feature that a few teams have been asking for since before v1.6.
One breaking change worth knowing about: SSH host key verification
This one could bite you on upgrade if you’re using SSH tunnels. Host key verification is now on by default. The tunnel checks ~/.ssh/known_hosts (or MYSQL_SSH_KNOWN_HOSTS, or a pinned MYSQL_SSH_HOST_KEY_FINGERPRINT) before allowing the connection.
If you were running without strict host key checking, your tunnel will fail after upgrading until you either add the host key to known_hosts or explicitly opt out with MYSQL_SSH_STRICT_HOST_KEY_CHECKING=false. The opt-out exists, but it’s a MITM risk — the default is the right behavior.
Upgrading
# Homebrewbrew update && brew upgrade mysql-mcp-server# Dockerdocker pull ghcr.io/askdba/mysql-mcp-server:latest
Full changelog: github.com/askdba/mysql-mcp-server/releases/tag/v1.7.0
Questions and issues are welcome on GitHub.
-
Monitoring MySQL data locks, or the tip of the iceberg
Monitoring MySQL data locks, or the tip of the iceberg This story is about recent (*) performance improvements implemented in MySQL, related to monitoring of data locks. (*) Originally written in Feb 2025. Refresher What is a data lock? When a user session connects to the MySQL database, it executes SQL queries. The query runs; […]
-
Symlinks are Unsafe since MySQL 8.0.39 (and maybe even before)
You read this right, symbolic links (symlinks) are unsafe in MySQL since at least 8.0.39. As always, it is a little more complicated than that, but if you are using symbolic links and in certain conditions, you risk a crash. I think it is important to raise awareness on this, hence this post.
My attention was brought to this via the now private Bug #120156: MySQL 8.0.39/8.0.42
-
Join the Public MySQL Community Discussion Webinar (Edition #3)
Following the strong participation in the first two editions of our Public MySQL Community Discussion webinar series, we’re excited to invite you to Edition #3. These sessions are part of our ongoing commitment to increase transparency, strengthen collaboration, and make it easier for the community to provide input that helps shape the evolution of MySQL’s. […]
|
