MySQL Master to Master Percona Galera Cluster in CentOS 6 2014

MySQL Master to Master Percona Galera Cluster in CentOS 6 2014



Installing a MySQL multi-master cluster on CentOS 6 requires quite a few steps that may not be obvious. This guide highlights some of the lessons we learned when installing our cluster. This was done in March of 2014, and as time goes by, this guide may become less useful.
To start, you will want to install the CentOS firewall gui so you can easily open the ports required by the cluster.



sudo yum install system-config-firewall
sudo system-config-firewall-tui



In the config tool, make sure ssh is selected, then also open ports 3306 4444 4567 4568 in the additional ports section.
Next, clean off any prior versions of MySQL, MariaDB, galera, percona, etc. by issuing these commands (warning this will delete any MysSQL database you currently have installed.) If you wish to save the data on this machine, do a backup first (by using mysqldump or some other means):

sudo yum erase -y mysql*
sudo yum erase -y mariadb*
sudo yum erase -y MariaDB*
sudo yum erase -y percona*
sudo yum erase -y Percona*
sudo yum erase -y galera*
sudo rm -fR /var/lib/mysql/*




We chose to use the percona package, so we added the package repository to yum as well as their required socat release from rpmforge.


sudo rpm -Uhv http://www.percona.com/downloads/per...0-1.x86_64.rpm
sudo wget http://apt.sw.be/redhat/el6/en/x86_6....rf.x86_64.rpm
sudo rpm -Uvh rpmforge-release*rpm
sudo yum install socat



Now we are ready to install the Percona Cluster packages.


sudo yum install -y Percona-XtraDB-Cluster-server Percona-XtraDB-Cluster-client \
Percona-XtraDB-Cluster-galera percona-xtrabackup Percona-XtraDB-Cluster-client \
percona-toolkit percona-xtrabackup rsync nc



In order to enable the communication between the cluster, we must edit the


sudo vi /etc/selinux/config



Change the line that says enforcing to permissive as shown here:


# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
# enforcing - SELinux security policy is enforced.
# permissive - SELinux prints warnings instead of enforcing.
# disabled - SELinux is fully disabled.
SELINUX=permissive
# SELINUXTYPE= type of policy in use. Possible values are:
# targeted - Only targeted network daemons are protected.
# strict - Full SELinux protection.
SELINUXTYPE=targeted



Set up your my.cnf file for the cluster. For this example we will use the IP addresses 10.1.1.1-4, for the first cluster node you will start with the following to initialize the cluster


sudo vi /etc/my.cnf




[mysqld]

datadir=/var/lib/mysql
user=mysql

# Path to Galera library
wsrep_provider=/usr/lib64/libgalera_smm.so

# Cluster connection URL contains the IPs of node#1, node#2 and node#3
#wsrep_cluster_address=gcomm://10.1.1.1,10.1.1.2,10.1.1.3,10.1.1.4
wsrep_cluster_address=gcomm://

# In order for Galera to work correctly binlog format should be ROW
binlog_format=ROW

# MyISAM storage engine has only experimental support
default_storage_engine=InnoDB

# This changes how InnoDB autoincrement locks are managed and is a requirement for Galera
innodb_autoinc_lock_mode=2

# Node #1 address
wsrep_node_address=10.1.1.1
wsrep_sst_receive_address=10.1.1.1

# SST method
wsrep_sst_method=xtrabackup

# Cluster name
wsrep_cluster_name=myclustername

# Authentication for SST method
wsrep_sst_auth="root:supersecretpassword"

# sync slave updates
server-id=5
log-bin=binlog
log-slave-updates=true



The section under the comment “sync slave updates” is important if you want the cluster to act as a slave to an existing mysql server. There are a few things to watch for:

• Make sure your tables are all InnoDB tables.
• Make sure binary logging is enabled and ROW level replication is set
• Make sure the log-slave-updates is ON, otherwise the other cluster nodes will not get the changes from the master
• Make sure all nodes in the cluster are set for the same server-id

Now that we are ready, we can initialize the database and start this node:

sudo mysql_install_db
sudo /etc/init.d/mysql start



Repeat this process for each of the other nodes, but switch the cluster address line when setting up the other nodes. Also, once the cluster is running, that line on the first node that was set up so it will rejoin the cluster if it is restarted. All the node my.cnf should have this config when done:

wsrep_cluster_address=gcomm://10.1.1.1,10.1.1.2,10.1.1.3,10.1.1.4
#wsrep_cluster_address=gcomm://



After you have set up the cluster as a slave to another MySQL database, you may find the cluster slave node gets stuck, you can have the slave node skip a statement with this SQL:

STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START SLAVE;
SHOW SLAVE STATUS\G;


http://corp.icoa.com/mysql-master-to...centos-6-2014/

Enjoy!

Quick Script to Add or Upgrade Oracle Java to CentOS ›

Posted in Uncategorized


Custom Live Chat and Call Center Solutions
ICOA develops customer communication including call center, social touch point, live web chat and integration into your enterprise back end.

RFP
If you have a custom project need for call center, mobile, web, Microsoft Office extension, VoIP/SIP or other application, contact us for a cost proposal. Submit your request to rfp@icoa.com.