Hello, please sign in or register
You are here: Home

Mysqldump and restore slave from master using bash

Restore slave servers from the master.

This uses the mysqldump method and reads the extracted contents into a remote mysql server using the -h command to specify the location of the remote server.

The script copies only the databases which the slave is configured to synchronize with.

./restore_slave.sh -h slavehost -p slavepass -u slaveusr -U masteruser -P masterpass

I've tested on a 1.4G database. Master server AMD 1.4Ghz 512RAM, Slave  Intel 1.6Ghz 256RAM, 10MBitsSec network

READ: mysqldump in ~7mins
WRITE: 14mins. 

Hardly lightening speed, but saves hassle of logging into both boxes.

An alternative might be quicker using rsync of Tab or CSV data. E.g. using `mysqldump --tab ...` , or even rsync'ing raw files i.e

rsync ./table.MYD user@slave/data/

The latter method would change the engine type*. But both methods would be capable of using rsync's compression, thus preffered for remote servers or for massive datasets.

*It is common that the slave uses a read fast MyISAM table capable of FULLTEXT and the Master uses a transactional database such as InnoDB. We can omit the create tables in the example below, (presuming tables already exist and identical structures) using `--no-create-db --no-create-info`

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

## Define the function which will show a waiting icon

spinner(){
PROC=$1
while [ -d /proc/$PROC ];do
echo -n '-' ; sleep 0.5
echo -n '-' ; sleep 0.5
echo -n '' ; sleep 0.5
echo -n '|' ; sleep 0.5
done
return 0
}

#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"`

MASTERLOGPOS=${MASTERLOGPOS#*:}

echo "MASTER_LOG_POS: $MASTERLOGPOS"

MASTERLOGFILE=`mysql -h$MASTERHOST -u$MASTERUSER -p$MASTERPASS --vertical -e "SHOW MASTER STATUS" | grep "File"`
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 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=1 -h$MASTERHOST -u$MASTERUSER -p$MASTERPASS --quick --databases $SLAVE_DBS > $TMP_FILE &
spinner $(pidof mysqldump)

echo Updating slave ...
time mysql -h$SLAVEHOST -u$SLAVEUSER -p$SLAVEPASS < $TMP_FILE
mysql -h$SLAVEHOST -u$SLAVEUSER -p$SLAVEPASS -e "START SLAVE" 

echo Removing temporary file
rm $TMP_FILE

echo COMPLETED

Comments

clibin
Tyrion 5c cases watched her ray ban sunglasses read. His timber...
Created 18/04/16
chenyan
Bond grinned. pandora-bracciali.it "We bcbgmax.in.net only chaussure...
Created 15/06/16
Title*
Comment

Prove you are not a robot

To prove you are not a robot, please type in the six character code you see in the picture below
Security confirmation codeI can't see this!
Contact
Name*
Email never shown*
Home Page

Author

Andrew Dodson
Since:Feb 2007

Comment | flag

Categories

Bookmark and Share