Hi Scott
You may use the script below to reload replication if you can ensure that
the master db doesn't change during the dump operation. Otherwise you may
set a lock on the master manually.
Regards, Thomas
#!/bin/bash
#
# replicate-reload
#
# This is free software. There is no warranty at all.
# The program may melt your computer and kill your cat.
# Use at your own risk.
#
# restart new replication of DBASE on localhost; dump from MASTER
#
# Note: No changes to DBASE may take place on the master during
# the dump operation. See comments below.
#
# Set your values here:
DBASE=adbtoreplicate
MASTER=host.domain.tld
MYUSER=useronlocalhost
MYPWD=thisisagoodpassword
# Set replication user and password
REPLUSER=replicationuser
REPLPWD=replicationuserpassword
# End of user configuration
SPACE=' '
TAB=$(echo -ne "\t")
MASTER_ALIAS=$(echo $MASTER | sed -e "s/\\..*//")
MASTER_POS=$(echo "FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;" | mysql -u $MYUSER -h $MASTER -p$MYPWD $DBASE \
| sed -e "/^${MASTER_ALIAS}-bin/ !d")
#################################################################
# Beware: From this point on no changes on the master may be made
# until the dump has finished. If this can't be enforced you
# have to place a lock manually on the master and release it
# once the dump is complete.
#################################################################
MASTER_FILE=$(echo "$MASTER_POS" | cut -s -d "$TAB" -f 1)
MASTER_LOGPOS=$(echo "$MASTER_POS" | cut -s -d "$TAB" -f 2)
#echo MASTER_POS="$MASTER_POS"
echo MASTER_FILE=$MASTER_FILE
echo MASTER_LOGPOS=$MASTER_LOGPOS
# Get the dump
echo "Dumping '$DBASE' from $MASTER"
#
# User: set your own dump options here as needed
mysqldump -u $MYUSER -h $MASTER -p$MYPWD \
--skip-opt \
--add-drop-table \
--max_allowed_packet=1M \
--character-sets-dir=/usr/share/mysql/charsets \
--skip-set-charset \
--extended-insert --lock-all-tables --quick \
--quote-names --master-data=2 $DBASE \
| sed -e "/^SET / d" > ${DBASE}.sql
#################################################################
# Note: Changes on the master are allowed from here on
#################################################################
echo -e "\nCHANGE MASTER TO MASTER_HOST='$MASTER', \
MASTER_USER='$REPLUSER', MASTER_PASSWORD='$REPLPWD', \
MASTER_LOG_FILE='$MASTER_FILE', MASTER_LOG_POS=${MASTER_LOGPOS};" \
> ${DBASE}.sync.sql
echo "STOP SLAVE;" | mysql -u $MYUSER -h localhost -p$MYPWD $DBASE
# reload dumped database
echo "Reloading '${DBASE}' on localhost"
cat ${DBASE}.sql ${DBASE}.sync.sql | mysql \
-u $MYUSER -h localhost -p$MYPWD $DBASE
echo "Starting slave $(hostname)"
echo "START SLAVE;" | mysql \
-u $MYUSER -h localhost -p$MYPWD -E $DBASE
sleep 2
echo "SHOW SLAVE STATUS;" | mysql \
-u $MYUSER -h localhost -p$MYPWD -E $DBASE
rm -f ${DBASE}.sql ${DBASE}.sync.sql
exit 0
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
