MySQL 5.5 Replication – How to Replicate to Galera/Percona XtraDB Cluster

galeraMySQLperconareplicationxtradb-cluster

We are currently investigating moving our current MySQL 5.5 Master > 5 x Slaves (with Statement Based Replication, apx 250GB data and growing) to a HA clustering option. Mainly to provide greater resilience, write scaling, and easier to carry out admin stuff like Optimising tables without taking the master offline.

We have ruled out (for the moment) MySQL ndb Cluster as it would require too many changes to our current database structure (large number of BLOB fields amongst other problems).

So we are looking at either Galera Cluster or Percona Cluster as the alternatives, as the they seem to do what we want, without 'too' steep a learning curve.

We would want to start off by setting the Cluster up as a Slave to the main Master (for testing etc), before moving over to the Cluster as our Primary system in the future. Is this possible? Reading through the documentation doesn't seem to really talk about this side of things (unless I've missed it).

Best Answer

Yes, it is very possible.

As the Documentation says, you should have a minimum of three nodes in a Percona XtraDB Cluster. You only need one node out of the three nodes.

Make sure all nodes in the PXC have unique server_ids.
Make sure the Master you are replicating from has a unique server_id from the PXC nodes
Make sure the master and every PXC node is configured with the following
- binlog_format=ROW
- log-slave-updates
Make sure the master and every PXC node is using binlog_format=ROW

Pick one node and set it up as you would a DB Server designated as a Slave via CHANGE MASTER TO.

If you are going to set up any replication filters, restrict it to just the PXC node that is your Slave. Personally, I would stay away from using replication filters altogether in this setup since one of Percona's Galera Instructors does not feel good about it.

Please be aware that data takes time to be on all nodes at the same instant of time (See Investigating MySQL Replication Latency in Percona XtraDB Cluster)

By the way, you can use any PXC as a Master to StandAlone Slaves as well

Jun 07, 2013 : Percona XtraDB Cluster and MySQL Replication
Jan 29, 2014 : Native replication from Percona XtraDB Cluster to a standard stand-alone slave
Nov 24, 2014 : What is the Best way to set Disaster recovery setup for percona Xtra DB Cluster?

HEADS UP #1

Don't be surprised by replication lag in the PXC node acting as the MySQL Slave. After all, three nodes must write data and become eventually consistent before all committing changes together. See Follow these basics when migrating to Percona XtraDB Cluster for MySQL for more info.

HEADS UP #2

Since you are using MySQL 5.5, do not use PXC 5.6. Use PXC 5.5. Why ? Row-based binlogging in MySQL 5.5 can sometimes not be properly interpreted by a MySQL 5.6 mysqld process. This can break replication from MySQL 5.5 to MySQL 5.6. I wrote a post about this two months ago. The work around was to switch to binlog_format=STATEMENT, but you cannot do that for Percona XtraDB Cluster as it requires binlog_format=ROW.

CHOICE #1 : Percona XtraDB Cluster

I am currently evaluating it and I think it is brilliantly designed for MultiMaster writes. It can use mysqldump (default), rsync, and xtrabackup (preferred) for initializing new Cluster node. You have total freedom and power. This may be the greatest cliche of all time but WITH GREAT POWER, THEIR MUST ALSO ALWAYS BE GREAT RESPONSIBILITY (19:16 - 19:25 of the Video).

You ultimately become responsible for

sizing memory requirements and disk configuration for InnoDB
remembering that DDL/DML on MyISAM is not replicated in the Galera Write Set Replicator Libraries. Since GRANT commands is storage-engine neutral, MyISAM table in the mysql schema is handled with no problem. Any DML against mysql.user is not replicated.
adding provisioning new Cluster Nodes for Reads/Writes

CHOICE #2 : Amazon RDS

Amazon RDS makes MySQL Database Cloud Services a snap. You must spend some time deploying Servers with one of 7 server models. By default, all InnoDB log files are 128M. Here are the only options that are unique to each Server Model:

MODEL      max_connections innodb_buffer_pool_size
---------  --------------- -----------------------
t1.micro   34                326107136 (  311M)
m1-small   125              1179648000 ( 1125M,  1.097G)
m1-large   623              5882511360 ( 5610M,  5.479G)
m1-xlarge  1263            11922309120 (11370M, 11.103G)
m2-xlarge  1441            13605273600 (12975M, 12.671G)
m2-2xlarge 2900            27367833600 (26100M, 25.488G)
m2-4xlarge 5816            54892953600 (52350M, 51.123G)

You are not given SUPER privilege and there is no direct access to my.cnf. In light of this, in order to change my.cnf options for startup, you must first create a MySQL-based DB Parameter Option List and use the RDS CLI (Command Line Interface) to change the desired Options. Then, you must do this to import the new options:

Create a Custom DB Parameter Group (call it MySettings)
Download RDS CLI and setup a config file with your AWS Credentials
Execute the following : ./rds-modify-db-parameter-group MySettings --parameters "name=whateveroption,value=whatevervalue,method=immediate"
Modify using DB Parameter Option List MySettings
Restart the MySQL RDS Instance

As for scaling out to data centers, you have the option to create read replicas. Since the default storage engine is InnoDB, making a read replica becomes seamless because data can be sync'd to Slaves without interrupting the Master.

Higher Server Models means you can have more Memory, more IOPs. Don't forget the cliche I mentioned because when it comes to Amazon RDS, with GREAT POWER COMES GREAT MONEY.

Percona XtraDB Cluster vs MySQL Replication – Key Differences

it seems the binlog doesn't include the new inserts

I'm not sure whether you're saying the binlog actually doesn't include them, and you have confirmed this with mysqlbinlog, or that it "seems" like it doesn't, because they don't replicate.

PXC needs log_slave_updates turned on at the node serving as master to the asynchronous slave, otherwise, not everything will be written to the master's binary log. This is very different than an ordinary MySQL server as master, where log_slave_updates will do nothing at all (unless the master is actually a slave to another master).

If that's not it, remove replicate_do_db and binlog_do_db and all of their related options from your configuration and then remove them from your brain. They should never be added unless you know exactly how they work, in your sleep. The simplest and by far most reliable replication configuration is, and will always be, replicate everything, which is the default.

Forget about binlog_format on the slave. It makes absolutely no difference unless the slave, itself, has other, subtended slaves... and if the master is using ROW format, the slave will still log in ROW format if you do indeed have it configured with subtended slaves. Also, the slave's binlogs (not to be confused with the relay logs) will not log statements received from an upstream master unless log_slave_updates is enabled on the slave.

The same thing goes for innodb_flush_log_at_trx_commit. It does not impact actual replication. It's a setting the determines a tradeoff between ACID compliance and performance.

Best Answer

HEADS UP #1

HEADS UP #2

Related Solutions

Scaling Percona Datacenters – Setup and Replication Best Practices

CHOICE #1 : Percona XtraDB Cluster

CHOICE #2 : Amazon RDS

Percona XtraDB Cluster vs MySQL Replication – Key Differences

Related Question