Moving on… to Percona’s XtraDB Cluster

Moving on from our MongoDB tour of duty, this post describes our recent experience in setting up Master/Slave and transitioning to Master/Master using Percona’s MySQL dbms, using their XtraDB Cluster, and XtraBackup.

BACKGROUND

We gather impression and click data hourly storing tens of millions of impressions per day in Riak.  On an hourly basis we run jobs that map/reduce the data and store the results of those jobs in Percona’s MySQL database.  Because we don’t want to have to re-run the jobs if the MySQL server goes down we wanted to use the Master/Master facility developed by the Percona people in the XtraDB Cluster, and on a write failure simply try to write the data to a different node in the cluster.  So we started with Master/Slave because we initially wanted to diminish the risk of lost data, and then moved to Master/Master in order to provide a solid failover solution.

MOVING TO MASTER/SLAVE

We went from a single server instance to using Master/Slave pretty easily.  We followed the instructions in the Percona post How to setup a slave for replication in 6 simple steps with Xtrabackup adding just a few tweaks.

One important note to know before starting:  On the slaves it’s important to have the skip-slave-start line in your my.cnf file prior to starting them the first time.  This enables you to pick a good starting point for replication when you start the slaves.

When setting up our first slave the only note pertains to step 2 “Copy backed up data to slave.”  When running the rsync, be sure to double check the owners and groups for the data installed on the slave are mysql:mysql, in addition to the data directory itself.  In our case the data directory is /var/lib/mysql.

When setting up the second slave in our three node configuration we had another small tweak.  Because the rsync from Master to Slave took several hours we brought the second slave up the next day — after jobs had restarted so more data was in the db both on master and slave.  We followed the steps used in the section entitled “Adding more slaves to the Master” with one notable exception.  In our case we used the data from xtraback_slave_info for the MASTER_LOG_FILE and MASTER_LOG_POS parameters when we ran the CHANGE MASTER TO command on the new slave.  This prevented a number of duplicate writes to the New Slave when replication started after doing the START SLAVE.

FROM MASTER/SLAVE TO MASTER/MASTER

We used several different sources to figure this out.  Once done Master/Master works well, but XtraDB Cluster is a pretty new project, and the documentation isn’t particularly strong.

We started by installing XtraDB Cluster.

First got and installed the apt-get key:

[cc]
gpg –keyserver hkp://keys.gnupg.net –recv-keys 1C4CBDCDCD2EFD2A1
gpg -a –export CD2EFD2A | sudo apt-key add –
[/cc]
Next we updated our apt sources list (in our case we use ubuntu lucid), and added the following lines to /etc/apt/sources.list:
[cc]
deb http://repo.percona.com/apt lucid main
deb-src http://repo.percona.com/apt lucid main
[/cc]
updating the local cache:
[cc]
# apt-get update
[/cc]
and then did the install:
[cc]
# sudo apt-get install percona-xtradb-cluster-client-5.5 percona-xtradb-cluster-server-5.5 percona-xtrabackup
[/cc]

Now to get from Master/Slave to Master/Master we looked to a presentation made by one of the Percona consultants

We started by copying our master’s my.cnf to my_cluster.cnf and editing it

We added the lines in the example configuration under the [mysqld_safe] section:
[cc]
wsrep_urls=gcomm://:4567,gcomm://:4567,gcomm://:4567,gcomm://
[/cc]

and then in the [mysqld] section:
[cc]
skip-slave-start

binlog_format=ROW
wsrep_provider  = /usr/lib/libgalera_smm.so # find your libgalera_smm.so and use that here

wsrep_slave_threads = 2
wsrep_cluster_name = tldr
wsrep_node_name = db1node
wsrep_node_address = #this node’s internal ip

# on the master we can start with xtrabackup as sst method, on the slaves we start with ‘skip’
# ‘skip’ means we won’t re-copy all the db tables over when we join the cluster the first time
# also note there’s an error in the slides, wsrep_sst_mode should be wsrep_sst_method
wsrep_sst_method = xtrabackup
wsrep_sst_auth =:

innodb_locks_unsafe_for_binlog = 1
innodb_autoinc_lock_mode = 2
[/cc]

We also deleted the line specifying server-id.

On the slaves we similarly copied my.cnf to my_cluster.cnf with similar adds and deletes:

We added the lines in the example configuration under the [mysqld_safe] section:
[cc]
wsrep_urls=gcomm://:4567,gcomm://:4567,gcomm://:4567 # NOTE: no gcomm://
[/cc]

and then in the [mysqld] section:
[cc]
skip-slave-start

binlog_format=ROW
wsrep_provider = /usr/lib/libgalera_smm.so # find your libgalera_smm.so and use that here

wsrep_slave_threads = 2
wsrep_cluster_name = tldr
wsrep_node_name = db2node # and db3node on the other slave
wsrep_node_address = #this node’s internal ip

# on the master we can start with xtrabackup as sst method, on the slaves we start with ‘skip’
# ‘skip’ means we won’t re-copy all the db tables over when we join the cluster the first time
# also note there’s an error in the slides, wsrep_sst_mode should be wsrep_sst_method
wsrep_sst_method = skip #skip set first and then change to xtrabackup
#wsrep_sst_method = xtrabackup
#wsrep_sst_auth =:

innodb_locks_unsafe_for_binlog = 1
innodb_autoinc_lock_mode = 2
[/cc]

We also deleted the line specifying server-id.

Finally we got to the part where we upgrade (with small variations on the slide entitled method 2– Blanket Changeover)

First we stopped our jobs from running.  Due to the way the jobs run on our platform we can stop things that read and write to the db and catch up later.

Second go into mysql on each of the slaves.  We made sure that the slaves were caught up with the master using

[cc]
mysql > SHOW SLAVE STATUS /G
[/cc]

then stopped and reset the slaves:
[cc]
mysql > STOP SLAVE;

mysql > RESET SLAVE;
[/cc]

Third we stopped all nodes.

Fourth on the master moved my.cnf to my_master.cnf and my_cluster.cnf to my.cnf
and on the slaves moved my.cnf to my_slave.cnf and my_cluster.cnf to my.cnf

Finally, we started the master node first (so it found the gcomm:// in the wsrep_urls) using
[cc]
mysqld_safe &
[/cc]
And started the slave nodes, which will join the cluster
[cc]
mysqld_safe &
[/cc]

Cleanup

We then went into my.cnf on the (former) master and changed
[cc]
wsrep_urls=gcomm://:4567,gcomm://:4567,gcomm://:4567,gcomm://
[/cc]
to
[cc]
wsrep_urls=gcomm://:4567,gcomm://:4567,gcomm://:4567
[/cc]

and on each of the former slaves my.cnf changed:
[cc]
wsrep_sst_method = skip #skip set first and then change to xtrabackup
#wsrep_sst_method = xtrabackup
#wsrep_sst_auth = : #un and pw with read/write permissions to *.*
[/cc]
to:
[cc]
#wsrep_sst_method = skip #skip set first and then change to xtrabackup
wsrep_sst_method = xtrabackup
wsrep_sst_auth = : [/cc]
And that wrapped it all up!

LINKS WE FOUND HELPFUL

wsrep_sst_method vs wsrep_set_mode

galera configuration options