Created
May 13, 2012 17:19
-
-
Save jcooklin/2689358 to your computer and use it in GitHub Desktop.
mysql stuff
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
[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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
This script is leveraged through puppet to initialize slaves in master-slave replication and master-master.