Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Sync one database to second using `pt-table-sync` + sync table structures as well
#!/bin/bash
# Fix for cron incorrect paths
HOME='/root'
# Sync database1 to database2
SRC_DB='database1'
DST_DB='database2'
EXCLUDE_TABLES=(table1 table2)
SRC_TABLES=`sudo -H mysql --defaults-extra-file=~/.my.cnf $SRC_DB -Bse 'show tables'`
for SRC_TABLE in ${SRC_TABLES[@]}
do
SYNC=1
for i in "${EXCLUDE_TABLES[@]}"
do
if [ "$i" == "$SRC_TABLE" ] ; then
echo "Skipping $SRC_TABLE"
SYNC=0
break
fi
done
if [ $SYNC -eq 1 ] ; then
echo "Syncing $SRC_TABLE"
sudo -H pt-table-sync --execute --verbose --chunk-size=32M --charset=utf8 h=localhost,D=$SRC_DB,t=$SRC_TABLE h=localhost,D=$DST_DB,t=$SRC_TABLE
if [ $? -eq 1 ] || [ $? -eq 3 ] ; then
echo "SYNC FAILED - Recreating Table on Destination"
sudo -H mysql --defaults-extra-file=/root/.my.cnf $DST_DB -e "drop table ${SRC_TABLE}"
sudo -H mysqldump --defaults-extra-file=/root/.my.cnf --no-data ${SRC_DB} ${SRC_TABLE} > /tmp/sync_table.sql
sudo -H mysql --defaults-extra-file=/root/.my.cnf $DST_DB < /tmp/sync_table.sql
sudo -H pt-table-sync --execute --verbose --chunk-size=32M --charset=utf8 h=localhost,D=$SRC_DB,t=$SRC_TABLE h=localhost,D=$DST_DB,t=$SRC_TABLE
fi
fi
done
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment