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.