I'm replicating from one node of a three-member Percona XtraDB cluster to a separate slave, using regular MySQL replication. The node is set to write binlog in ROW mode, with log_slave_updates=true specifically for this. Replication seems to work fine. Still I wonder, given that XtraDB is an extension of InnoDB which the slave is using, is there a risk of some updates not replicating? Is it worth it to replicate into an XtraDB slave, and how would that need to be configured? I want the native async replication to the slave as it is an analytics DB and the goal is to offload the production Percona cluster from analytics queries, so it should not be just a regular cluster member.
Percona XtraDB Cluster – Native Replication to a Stand-Alone Slave
innodbMySQLperconareplicationxtradb
Related Question
- Mysql – Transaction speed benchmarks for theSQL v5.6 replication – seems very slow
- Mysql – Galera Cluster not syncing from a standalone Mysql Server
- Mysql – Replicating into a Percona XtraDB Cluster
- MySQL – Fix Slave Skipping Some Updates
- Mysql – Percona XtraDB cluster backup solution
- How to Make an Async Slave Track All Nodes in Percona XtraDB Cluster
- MySQL Solution to Sync 3 servers data
Best Answer
The binlog doesn't contain different structures when the storage engine is XtraDB vs InnoDB.
In the sense of interoperability, XtraDB is not an "extension" of InnoDB -- it's a fully-compatible replacement, that handles some internal operations differently but is still very much "InnoDB" wherever it is exposed outside the server core.
For what it's worth, I've taken "datadir" -- including ibdata1 and the ibd files from a stopped MySQL server, put them on a MariaDB server (which also uses XtraDB instead if InnoDB) and had zero compatibility issues.
The only caveat that comes to mind is the same as any MySQL async replication setup: the slave must be running the same or newer version of MySQL as its master (which, by the way, can be any one of thr PXC machines).
The reason for this "slave version the same or newer" rule is that the binlog format is forward-extensible only: when new capabilities are added to the binlog format, a newer master cam break an older slave, since the slave may not always understand what it is reading from the binlog, and replication will hit a hard stop (not a soft fail). A newer slave should never fail to understand what an older master writes to its binlog, since, officially, replication is supported across major releases (e.g., 5.1 to 5.5) but in practice, 5.1 to 5.6 also seems completely compatible.
The parallel minor version or newer, of MySQL Server, MariaDB, or -- seemingly the most foolproof -- Percona Server... should work fine as an async slave.
If you start out the slave with Identical data, and you replicate every table and schema, missing transactions should never happen. Don't even try to filter what replicates and try to replicate a subserlt of your schemata/tables unless and until you really understand the issues this can encounter. Replicating everything is the default behavior.
Yes... it is very much worth it to set up async slaves for reporting. The cluster members should already have the config they need for this to work (othet than the user account the slave will use to connect).