#!/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