syncdb.sh

#!/bin/bash -e

# ================================================
# Description: mysql local - remote sync tool
# Last Modified: Tue Aug 05, 2014 11:52PM
# Author: Allex Wang ([email protected])
# GistID: 7d9df0743deb78278761
# GistURL: https://gist.github.com/allex/7d9df0743deb78278761 
# ================================================

cmd=`basename $0`
dir=$(cd `dirname $0` && pwd)

db_name=wp_blog
db_file=$dir/${db_name}.sql

# pull | push
act=$1

NOW=`date "+%Y-%m-%d"`
MYSQL_CMD='/usr/bin/mysql'

R_MYSQL_SERVER=iallex.com
R_MYSQL_PWD="$2"

L_MYSQL_ARGS="-honebox -uwp_blog_usr -p123qwe"

if [ "${act}x" = "x" ]; then
    echo "$cmd [push|pull]"
    exit 1;
fi

echo "[${NOW}] Start to sync ${R_MYSQL_SERVER} database..."

if [ "${R_MYSQL_PWD}x" = "x" ]; then
    read -s -p "Enter your ${R_MYSQL_SERVER} mysql root password: " R_MYSQL_PWD
fi

if [ "${R_MYSQL_PWD}x" = "x" ]; then
    echo "password cannnot empty"
    exit 1;
fi

if [ "$act" = "pull" ]; then
    # backup
    ssh ${R_MYSQL_SERVER} "mysqldump -hlocalhost -uroot -p${R_MYSQL_PWD} ${db_name}" >$db_file
    $MYSQL_CMD ${L_MYSQL_ARGS} ${db_name} <$db_file
    mv "${db_file}" "${db_file}.${NOW}"
    gzip -f "${db_file}.${NOW}"
else
    # restore
    if [ "$act" = "push" ]; then
        mysqldump ${L_MYSQL_ARGS} ${db_name} >$db_file
        scp "${db_file}" ${R_MYSQL_SERVER}:~/${db_name}.sql
        ssh ${R_MYSQL_SERVER} "mysql -hlocalhost -uroot -p${R_MYSQL_PWD} ${db_name} <~/${db_name}.sql"
    else
        exit 1;
    fi 
fi