A script that attempts to create and configure a percona-xtradb-cluster setup

pxc_setup.sh

#!/bin/bash
# ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
# Sveesible - PXC-SS - Percona-Xtradb-Cluster-Simple-Setup-Example
# ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
# [Brad Svee] - [07-17-2014] - [PXC-SS] - [1.0]
# ---------------------------------
# [A setup script to help configure a Percona XtraDB MySQL Cluster and Galera]
# ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

install_percona=true
prompt=0
mysql_data_dir="/var/lib/mysql"
local_node=`hostname`
public_ip=`ifconfig | grep 'inet ' | grep -v '127.0.0.1' | head -n1 | awk '{print $2}' | cut -d ':' -f2`
first_node=""
second_node=""
galera_node=""
galera_cache_size="4097152000"
galera_cache_dir=""
is_galera_aribitrator_node=0
install_shared=0
debug=0
install_backup_script=0
verbose=0
enable_backups=0
cluster_name="default_cluster_name"
wsrep_threads=16
sst_pw=""
buffer_pool_size="256M"
mysql_server_id="${RANDOM}"
default_mycnf_file="H4sIAHof0FMAA5VUyY7jNhC98ysE9DXqtt22Ywygy6RzmIPTwCBADoMBURLLEmEuGi7uVr4+Raply4aDSS5e6r0qvlq/6cH/UI8e3QnddxbpR5VNrAaPQrrqKXrHBATIf07gnpSsn0ZOL0V5kApv7OOneCSYsW/jH+7hgN+ZH7yyLVkbJdEEMtjmiOFugMeEnQP8b3FB99kq8PTkO/2fxNbQHDkJrDaLBaPvEp2zU2Tbzj0y8piS8UfZlwY0lg69VSdkDwpci2UPLXrWWxeq5+fF9ue5anjnjTUGmyCt8VkF1euN/4johixseWPgs5TOAhOlTBSJPkt8cKhgKFOAm9elaayWpuUO+6kW5ciupck18ApOWMae6oy+OoDyyPC9lw6TAM8FDCSVjTNUSlGt1qvVkh1x4HU8HNBxL//GarXZ7plDENwZcYVsyGG3Y3VURy4NxQlX8JL89CA9aO7tv2OpeBlPFfkIvFjs2UPxQnitsMhFKxpoOvylMDYUNFGHqIpoBLqik21XKAuCjcXNPB6GHqvFlSmH3q73V0YltQzVbs9Cl3O8oubGgqKuoOA95ClIAWhEeUjSMrGoiufVPnM7hDvIbfZkXm1Xy/WaUZJfjLEvn4seHE1iQOcZ9S8PiHUaQvX19S8miSNq6lpz9DyatJIJ5iOTZkvgAaIK3AfraHg5mlYarMbYkzvEYGlschiuraDWTlAufU/ysvYr64eMz+AcNFHABNoeTWZ4SufX7W6yp63mndXI7+z2nDO+CaEjf1kn0/LTcrXbf0o68T2gEXPlo4XmrHGo6QSR12Z5TuCjur21aja1k7sQMi0mKK5RX0ipD5v9pbotb52N/c/UT9t7ibG4CnLd6fUZO6jou8yAwINLJ0PT8BGHrsVAjTm3c9Zv/gaS2FKjjYn6TKflA54m1pomOoemGQhfbm9leqrZmNis31kKzVtnBTm98pcvX3//7c8Jlpb2gOZdhhRyc3nyzcmACR7f9tXluaxlhuzYvd6ISKtD6fsuBmHfTPX6xz1aWudMC+ACCScWnqjp3NN2ikhHvBLjcRBsPHMGQzmvEmP/AMAsLGojBwAA"

Validate () {
  echo "Starting Validation"
  if [[ $EUID -ne 0 ]]; then
    echo "This script must be run as root" 1>&2
    exit 1
  fi
  
  if ! yum list installed | grep -q perl ; then
  echo "Perl is not installed, please install with yum install perl" 1>&2
    exit 1
  fi  
  
  if [ -z $second_node ] || [  ping -c1 $second_node &> /dev/null == 0 ]; then
   echo "Secondary Node ${second_node} is blank or unable to Ping Secondary Node: see -t parameter " 1>&2
    exit 1
  fi

  if [ $is_galera_aribitrator_node == 0 ]; then
    if [ -z $galera_node ] || [ ! ping -c1 $galera_node &> /dev/null ]; then
      echo "MySQL Galera Server is blank or not able to Ping it see -g parameter " 1>&2
      exit 1
    fi
  else
    if [ -z $first_node ] || [  ping -c1 $first_node &> /dev/null == 0 ]; then
      echo "Primary Node ${first_node} is blank or unable to Ping Primary Node: see -f parameter " 1>&2
      exit 1
    fi
  fi

  #SELINUX Disabled
  if [ -f /etc/selinux/config ]; then 
    if ! grep -iq 'SELINUX=disabled' /etc/selinux/config ||  ! getenforce | grep -q 'Disabled'; then 
      echo "SELINUX appears to be enabled on your system.  Please disable it prior to running this script " 1>&2
      exit 1
    fi
  fi

  echo ' Please check that Ports 3306, 4444, 4567 and 4568 are open '
  iptables -L -vn
    
}

CreateDir(){
  read -p "Create the directory ${1}? " ans
    
    while true
    do
      case $ans in
        [yY]* ) 
                echo "Okay, creating directory ${1}"; mkdir -p $1
                break;;
        [nN]* ) break;;

            * )     echo "Dude, just enter Y or N, please.";  ;;
      esac
    done  
}


InstallPercona () {
  Validate
  CreateLinuxUsers
  InstallPerconaPreqs
  if [ $is_galera_aribitrator_node == 1 ]; then
    InstallPerconaArbitratorNode  
  else
    InstallPerconaServer
    ConfigureMyCnf
    ConfigureMySQLPaths
    ConfigureGalera
    StartMySQL
    SetupMySQLUsers
    HardenMySQL
    ConfigureBackups
  fi

}

InstallPerconaArbitratorNode () {
 
  yum install Percona-XtraDB-Cluster-garbd-3.x86_64
  echo "#! /bin/bash
garbd -o \"wsrep_urls=;gmcast.listen_addr=tcp://${public_ip}:4567;ist.recv_addr=${public_ip}:4568;gcache.dir=${galera_cache_dir};gcache.size=${galera_cache_size}\" -a gcomm://${public_ip},${first_node},${second_node} -g ${cluster_name} -l /var/log/garbd.log -d " > /usr/bin/startGarbd.sh
  chmod 700 /usr/bin/startGarbd.sh  

}

InstallPerconaPreqs () {
  
  #todo: test for existing installation?

  echo "install EPEL first in order to install socat"
  rpm -Uh --quiet http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm

  echo "installing socat first which is a requirement for PXC to do cluster SST"
  yum -y install socat

  echo "installing Qpress compression library to use with Xbstream backups and SST"
  wget http://www.quicklz.com/qpress-11-linux-x64.tar 
  tar -xf qpress-11-linux-x64.tar -C /usr/bin/

  if ! yum list installed | grep -iq percona ; then
    echo "removing mysql-libs from centos which conflict with PXC"
    yum -y remove mysql-libs
  fi

  echo "Adding Percona Yum Repo"
  rpm -Uh --quiet http://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm

}

InstallPerconaServer () {

  echo "Installing Percona Packages"
  yum -y install Percona-XtraDB-Cluster-56 percona-xtradb-cluster-client-5.6 percona-xtrabackup percona-toolkit

  if [ $install_shared == 1 ]; then
    echo "installing Percona shared library"
    yum -y install Percona-XtraDB-Cluster-shared
  fi  

}


SetupMySQLUsers () {

  read -p "Enter A password for mysql root user? " rootpw
  mysqladmin -uroot password $rootpw
  echo "[client]
  password=${rootpw}" > /root/.my.cnf
  chmod 400 /root/.my.cnf
  
  
  sst_statement="GRANT ALL PRIVILEGES ON *.* TO 'sst'@'%' IDENTIFIED BY '${sst_pw}'"
  mysql -e "$sst_statement"

}

CreateLinuxUsers () {

  echo "Creating user accounts"
  groupadd mysql ; useradd -g mysql mysql

}

ConfigureMySQLPaths () {

  echo "Configuring mySQL Paths"
  if [ -z $mysql_data_dir ]; then
    read -p "Enter a directory for the mysql data files, enter for default /var/lib/mysql ? " mysql_data_dir_input
  
    if [ -z $mysql_data_dir_input ]; then
      mysql_data_dir="/var/lib/mysql"
    else
      mysql_data_dir=$mysql_data_dir_input
    fi
  fi
  
  if [ ! -d $mysql_data_dir ]; then
    CreateDir $mysql_data_dir
  fi
  if [ ! -d /var/log/mysql ]; then
    CreateDir "/var/log/mysql"
    chown -R mysql:mysql /var/log/mysql
  fi

  if [ ! -d $mysql_data_dir ]; then
    echo "MySQL Data Dir is not a valid path ${mysql_data_dir}" 1>&2
    exit 1
  fi
  chown -R mysql:mysql $mysql_data_dir
  #make sure mysql knows all about the new data dir
  
  /usr/bin/mysql_install_db --datadir=$mysql_data_dir --user=mysql
  chown -R mysql:mysql $mysql_data_dir
  # send this to my.cnf file datadir=$mysql_data_dir
  echo "fixing up /dev/shm for temp path"
  chmod 1777 /dev/shm
  
}

HardenMySQL () {

  echo "run MySQL hardening scripts mysql_secure_installation REMEMBER you already changed the Root pw"; sleep 4
  mysql_secure_installation
}

ConfigureMyCnf () {
  echo "pump the required values into the my.cnf file, this is missing some of the newer settings like [SST]"
  if [ ! -f /etc/my.cnf ]; then
    echo "no default my.cnf file found creating one for you, you should check the settings match your needs"
    echo $default_mycnf_file | base64 -d | gunzip > /etc/my.cnf
    
  fi
  echo "backup of my.cnf is /etc/my.cnf.bck"
  cp /etc/my.cnf /etc/my.cnf.bck
  
  myconfsettings=( "innodb_file_format=Barracuda" "default_storage_engine=InnoDB" 
                               'user=mysql' 
                               'tmpdir=/dev/shm' 
                               'binlog_format=ROW' 
                               'innodb_file_per_table' 
                               "datadir=${mysql_data_dir}"
                               "pid-file=${mysql_data_dir}/mysqld.pid"
                               "innodb_data_home_dir=${mysql_data_dir}"
                               "innodb_buffer_pool_size=${buffer_pool_size}" 
                               "innodb_log_group_home_dir=${mysql_data_dir}" 
                               "innodb_write_io_threads=16" 
                               "innodb_read_io_threads=8" 
                               "innodb_autoinc_lock_mode=2" 
                               "innodb_log_files_in_group=2" 
                               "server-id=${mysql_server_id}" 
							)

  for setting in "${myconfsettings[@]}"
  do
    setting_name=`echo ${setting} | cut -d'=' -f1`
    #echo $setting_name
    if   grep -q $setting_name "/etc/my.cnf" ; then
      sed -ie "s|.*${setting_name}.*|${setting}|" /etc/my.cnf
  else
    prl_str='print $_; print "'${setting}'\n" if(/\[mysqld\]/);'
    perl -ni -e "$prl_str" /etc/my.cnf
  fi
  done

  # use the format !include /etc/my.wsrep.cnf to include settings from the wsrep file
  if ! grep -q "/etc/my.wsrep.cnf" "/etc/my.cnf" ; then
    echo 'adding wsrep file'
    perl -ni -e 'print $_; print "!include /etc/my.wsrep.cnf\n" if(/\[mysqld\]/);' /etc/my.cnf
  fi


}

ConfigureGalera () {
  
  echo "Configuring Galera Cache Dir. This is used to store transaction data for incremental state transfers."
  if [ -z $galera_cache_dir ]; then
    read -p "Enter a directory for the mysql galera cache file, press enter to use the default /var/lib/mysql/gcache ? " galera_cache_dir_input

    if [[ -z $galera_cache_dir_input || $galera_cache_dir_input -eq 'y' ]]; then
      echo 'galera 1'
      galera_cache_dir="/var/lib/mysql/gcache"
    else
      echo "Setting Galera cache directory to ${galera_cache_dir_input}"
      galera_cache_dir="${galera_cache_dir_input}"
    fi
  fi

  if [ ! -d $galera_cache_dir ]; then
    CreateDir $galera_cache_dir
  fi
  if [ ! -d $galera_cache_dir ]; then
    echo "MySQL Galera Cache Dir is not a valid path ${galera_cache_dir}" 1>&2
    exit 1
  fi
  echo "Setting owner of galera cache dir ${galera_cache_dir} to mysql user"
  chown -R mysql:mysql $galera_cache_dir

  read -p "Enter A password for mysql cluster SST user? " sstpw  
  sst_pw=$sstpw

  galera_options="gmcast.listen_addr=tcp://${public_ip}:4567;ist.recv_addr=${public_ip}:4568;gcache.dir=${galera_cache_dir}; gcache.size=${galera_cache_size}"
  echo "[mysqld]
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://${public_ip},${second_node},${galera_node}
wsrep_slave_threads=${wsrep_threads}
wsrep_cluster_name=${cluster_name}
wsrep_provider_options=\"${galera_options}\"
wsrep_replicate_myisam=1
wsrep_sst_method=xtrabackup
wsrep_node_name=${local_node}
wsrep_node_address=${public_ip}
wsrep_sst_auth=sst:${sst_pw}
wsrep_sst_receive_address=${public_ip}
wsrep_node_incoming_address=${public_ip}

[sst]
streamfmt=xbstream

[xtrabackup]
compress
compact
parallel=2
compress-threads=2
rebuild-threads=2" > /etc/my.wsrep.cnf
  
  chmod 600 /etc/my.wsrep.cnf

}

EnableClusterCheck () {
   #TODO: setup the pyclusterCheck script
  #grant process on *.* to 'clustercheckuser'@'localhost' identified by 'clustercheckpassword!'
  #python pyClusterCheck
  echo "enabling clustercheck"
}

StartMySQL () {

  boostrap_val=""
  #bootstrap if first node
  read -p "Is this the first node in the cluster y or n ? " first_node

  while true
  do
    case $first_node in
        [yY]* ) 
                echo "Okay, we're going to bootstrap it."
                boostrap_val="bootstrap"
            break;;
        [nN]* ) 
			boostrap_val="start"
			break;;

        * ) echo "Dude, just enter Y or N, please.";  ;;
    esac
  done
 
  service mysql $boostrap_val

}

ConfigureBackups () {
	
  echo "Download and install a wrapper backup-script for mysql xtrabackup from somewhere like: https://github.com/gregster85/autoxtrabackup"
  read -p "Enter A password for mysql backups user? " backup_pw
  mysql -e "CREATE USER 'backup'@'localhost' IDENTIFIED BY '$backup_pw'; "
  mysql -e "GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'backup'@'localhost';"
  mysql -e 'FLUSH PRIVILEGES;'
  echo "TODO: create a cron tab that runs the full backup daily and incremental backups every 20 minutes"
  
}


Reset () {
	rm -rf "${mysql_data_dir}"
	rm -f /etc/my.cnf
	rm -rf /var/log/mysql
	
	rm -f /var/lock/subsys/mysql
}



Usage () {
  USAGE="This script will help you setup a 3 node cluster of XtraDB-Cluster on CentOS/RHEL with one of them as a Galera Arbitrator Node.
 Usage: -p prompt 
  -n [cluster name (pick a good one)]
  -f [cluster node ONE's ip address for arbitrator]
  -t [cluster node TWO's ip address] 
  -i [alternate ip to use] 
  -g [IP of the Galera Arbitrator Node] 
  -a marks this node as the Arbitrator Node  
  -c enable Pyclustercheck when behind a load balancer
  -s install Percona Shared Libraries
  -b enable backups and prompt for settings
  -v install a specific version of Percona default 5.6
  -r reset the installation clears out everything 
  -h usage
  "
  echo $USAGE
  exit 1

}



while getopts ":pn:f:t:i:g:acsbvrh" OPTION; do
    case "${OPTION}" in
      p)
        prompt=0
        ;;
      n)
        cluster_name=$OPTARG
        ;;
      f)
        first_node=$OPTARG
        ;;
      t)
        second_node=$OPTARG
        ;;
      i)
        public_ip=$OPTARG
        ;;
      g)
        galera_node=$OPTARG
        ;;
      a)
        is_galera_aribitrator_node=1
        ;;
      c)
        enable_cluster_check=1
        ;;
      s)  
        install_shared=1
        ;;
      b)
        enable_backups=1
        ;;
      r)
         echo 'resetting'
         Reset; exit
         ;;
      v)
        percona_version=$OPTARG
        ;;
      h) Usage; exit;;
     \?) echo "Unknown option: -$OPTARG" >&2; Usage; exit 1;;
      :) echo "Unknown option:"   >&2; Usage; exit 1;;
      *) echo "Unimplemented option: -$OPTARG" >&2; Usage; exit 1;;
  esac
done



InstallPercona