Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 2 down vote accepted

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).

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.