mysql stuff

my.cnf template

[mysqladmin]
user=opsmgr
password=monitoring

[mysqldump]
user=backup
password=back_password

[mysql]

[client]
socket=/mysql/data/mysql.sock
#override any other client config.
#user=guest
#password=guestpass
#host=localhost

[mysqld]
user=mysql
sql_mode=NO_AUTO_CREATE_USER
#INNODB tuning parameters
innodb_buffer_pool_size=1000M # 70% of memory
innodb_log_buffer_size=256M
innodb_flush_method=O_DIRECT
#Log Shipping configuration
#uncomment the follwing lines to enable binlogs
server-id=1
#expire_logs_days=5
innodb_flush_log_at_trx_commit=1
log_slave_updates
sync_binlog=1
innodb_file_format=barracuda
innodb_file_per_table
datadir=/mysql/data
socket=/mysql/data/mysql.sock
#General tuning parameters
long_query_time=15
max_connections=500
max_connect_errors=10000
#File Mappings
log-bin=/mysql/files/binlogs/binlogs_<%= hostname %>.log
slow_query_log_file=/mysql/files/errorlogs/slow_query_<%= hostname %>.log
log_error=/mysql/files/errorlogs/mysql_errorlog_<%= hostname %>.err
general_log_file=/mysql/files/errorlogs/mysql_<%= hostname %>.log

mysql_init_slave.sh

#!/bin/bash
#
# Restore the slave server, this script updates the contents of the slave.
# Parameters for this script are
# -h    Slave Host
# -p    Slave password
# -u    Slave Username
# -H    Master Host
# -P    Master password
# -U    Master Username
#

## SET DEFAULTS

MASTERHOST=localhost
TMP_FILE=tmp.sql

# GET PARAMETERS (OVERIDING DEFAULTS)

while getopts  "h:u:p:H:U:P:" flag
do
  case "$flag" in
        h) echo "SLAVE_HOST: $OPTARG"
           SLAVEHOST=$OPTARG;;
        p) SLAVEPASS=$OPTARG;;
        u) SLAVEUSER=$OPTARG;;
        H) echo "MASTER_HOST: $OPTARG"
           MASTERHOST=$OPTARG;;
        P) MASTERPASS=$OPTARG;;
        U) MASTERUSER=$OPTARG;;
        d) echo "Databases set to $OPTARG";;
  esac
done

echo "slave host: $SLAVEHOST";

## Define the function which will show a waiting icon

spinner(){
    SP_STRING=${2:-"'|/=\'"}
    while [ -d /proc/$1 ]
    do
        printf "$SP_COLOUR\e7  %${SP_WIDTH}s  \e8\e[0m" "$SP_STRING"
        sleep ${SP_DELAY:-.2}
        SP_STRING=${SP_STRING#"${SP_STRING%?}"}${SP_STRING%?}
    done
}

## Adjust to taste (or leave empty)
SP_COLOUR="\e[37;41m"
SP_WIDTH=1.1  ## Try: SP_WIDTH=5.5
SP_DELAY=.2

#sleep 3 &
#spinner "$!" '.o0Oo'

#CHECK CONNECTIONS TO BOTH SLAVE AND MASTER && GET THE MYSQL VERSIONS AT THE SAME TIME
SLAVEVERSION=`mysql -h$SLAVEHOST -u$SLAVEUSER -p$SLAVEPASS --vertical -e "SELECT VERSION()"`
SLAVEVERSION=${SLAVEVERSION#*:}

MASTERVERSION=`mysql -h$MASTERHOST -u$MASTERUSER -p$MASTERPASS --vertical -e "SELECT VERSION()"`
MASTERVERSION=${MASTERVERSION#*:}

if [ -z $MASTERVERSION ]; then
    echo Could not connect to Master
    exit 1
fi

if [ -z $SLAVEVERSION ]; then
    echo Could not connect to Slave
    exit 1
fi


## - GET INFORMATION FROM SLAVE


#SLAVEVERSION
echo "SLAVE_VERSION: $SLAVEVERSION"
#SLAVEDBS
SLAVE_DBS=`mysql --vertical -h$SLAVEHOST -u$SLAVEUSER -p$SLAVEPASS -e "SHOW SLAVE STATUS" | grep "Replicate_Do_DB"`
SLAVE_DBS=${SLAVE_DBS#*:}
echo "SLAVE_DBS: $SLAVE_DBS"

## - GET INFORMATION FROM MASTER

#MASTERVERSION
echo "MASTER_VERSION: $MASTERVERSION"

MASTERLOGPOS=`mysql -h$MASTERHOST -u$MASTERUSER -p$MASTERPASS --vertical -e "SHOW MASTER STATUS" | grep "Position" | tr -d ' '`
MASTERLOGPOS=${MASTERLOGPOS#*:}
echo "MASTER_LOG_POS: '$MASTERLOGPOS'"

MASTERLOGFILE=`mysql -h$MASTERHOST -u$MASTERUSER -p$MASTERPASS --vertical -e "SHOW MASTER STATUS" | grep "File" | tr -d ' '`
MASTERLOGFILE=${MASTERLOGFILE#*:}
echo "MASTER_LOG_FILE: '$MASTERLOGFILE'"

## - PREPARE
# Lock the tables on the master and slave servers.
# Turn off the slave's IO

echo Stopping slave server
mysql -h$SLAVEHOST -u$SLAVEUSER -p$SLAVEPASS -e "STOP SLAVE" 

echo Resetting slave server
mysql -h$SLAVEHOST -u$SLAVEUSER -p$SLAVEPASS -e "RESET SLAVE" 

echo Starting the restoration of $SLAVEHOST
SLAVE_DBS=${SLAVE_DBS//,/ }

echo Dumping SQL... to tmp.sql 
## Setting --master-data=1 uses FLUSH TABLES WITH READ LOCK, which prevents any modification of the Master Data, 
# Run the process in the background, and use spinner proc to occupy the user
#time mysqldump -C --master-data=2 -h$MASTERHOST -u$MASTERUSER -p$MASTERPASS --quick --all-databases $SLAVE_DBS > $TMP_FILE &
#time mysqldump -C --master-data=2 -h$MASTERHOST -u$MASTERUSER -p$MASTERPASS --quick --all-databases > $TMP_FILE &
time ( mysqldump -C --master-data=2 -h$MASTERHOST -u$MASTERUSER -p$MASTERPASS --quick --all-databases > $TMP_FILE || { echo "ERROR exporting data from master"; exit 1; }) &
spinner "$!" '.o0Oo'


echo Updating slave ...
time ( mysql -h$SLAVEHOST -u$SLAVEUSER -p$SLAVEPASS < $TMP_FILE || { echo "ERROR importing data from master"; exit 1; }) &
spinner "$!" '.o0Oo'
if [ $? == 1 ]  
then
        echo "ERROR starting slave- $error"
        exit 1;
fi

echo Updating the slaves MASTER_LOG_FILE and MASTER_LOG_POS
error=`mysql -h$SLAVEHOST -u$SLAVEUSER -p$SLAVEPASS -e "CHANGE MASTER TO MASTER_HOST='$MASTERHOST', MASTER_USER='$MASTERUSER', MASTER_PASSWORD='$MASTERPASS', MASTER_LOG_FILE='$MASTERLOGFILE', MASTER_LOG_POS=$MASTERLOGPOS;" 2>&1`
if [ $? == 1 ]  
then
        echo "ERROR updating slave - $error"
        exit 1;
fi

echo Starting Slave ...
error=`mysql -h$SLAVEHOST -u$SLAVEUSER -p$SLAVEPASS -e "START SLAVE" 2>&1` 
if [ $? == 1 ]  
then
        echo "ERROR starting slave- $error"
        exit 1;
fi

echo Removing temporary file
#rm $TMP_FILE

echo COMPLETED

This script is leveraged through puppet to initialize slaves in master-slave replication and master-master.