Mirror all MySQL DBs to the local machine

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.