Continuing on my quest to get MySQL replicating over ssh, I am using the following bash function to replicate all remote DBs locally:
function MirrorAllRemoteDBsToLocal {
for REMOTEDB in $(mysql -h 127.0.0.1 -P 7777 --batch --skip-column-names -e "SHOW DATABASES")
do
LOCALDBEXISTS=$(mysql --batch --skip-column-names -e "SHOW DATABASES LIKE '"$REMOTEDB"';" | grep "$REMOTEDB" > /dev/null; echo "$?")
if [ $LOCALDBEXISTS -ne 0 ];then
echo "adding $REMOTEDB to local MySQL"
mysql -e "create database $REMOTEDB;"
echo "getting a dump"
mysqldump -h 127.0.0.1 -P 7777 $REMOTEDB | mysql $REMOTEDB;
echo " adding $REMOTEDB to my.conf"
sed -i '/master-port/a\\treplicate-do-db='$REMOTEDB'' /etc/my.cnf
fi
done
}
Line 2 connects to the local AutoSSH tunnel and gets a list of all the remote DBs.
Then we loop through the DBs and if there is not a DB locally with that name, the script will create a database. Next the script gets (Line 9) a dump of the DB and copies it to the newly created DB.
And finally the script add the DB to the /etc/my.cnf (line 11).
All that should have to happen is to issue a slave stop and then slave start, and all DBs should be mirrored locally.
Comments are closed.