rsynctool集群同步脚本

本帖主要分享利用rsynctool主备集群同步工具实现主备库、表级别同步shell脚本，供参考：

#!/bin/bash
basedir=$(cd `dirname $0`; pwd)
GCLUSTER_RSYNCTOOL_HOME="/opt/gcluster_rsynctool"
logdir=$basedir/logs
backdir=$basedir/bak
username=gbase
password=gbase20110531
dt=`date +%Y%m%d%H%M`
alias gcluster_rsynctool="python ${GCLUSTER_RSYNCTOOL_HOME}/gcluster_rsynctool.py"

#同步配置信息，用户建议具备dba权限
syncUser='gbase'
masterIP='10.104.128.201'
masterPassord='gbase20110531'
slaveIP='10.104.128.203'
slavePassword='gbase20110531'
tblistfile="${basedir}/tblist.txt"
synclog=${basedir}/sync_$(date +'%Y%m%d%H%M').log

echo  "*************Start  execute sync script-$(date +'%Y%m%d%H%M')************" | tee ${synclog}
if [ ! -d ${logdir} ];then
  mkdir ${logdir}
fi
if [ ! -d ${backdir} ];then
  mkdir ${backdir}
fi

function maninfo(){
  echo "*****************************脚本使用说明************************************************"
  echo "本脚本主要用于gbase8a主备集群初始化和同步，脚本需三个参数:"
  echo "参数1：同步模式"
  echo "    A|a:表示全库同步"
  echo "    C|c:表示自定义同步表清单"
  echo "    -help:表示查看脚本使用说明"
  echo "参数2：	从库是否自动创建库"
  echo "    D|d : 脚本自动创建"
  echo "    N|n : 库已存在，无需创建"
  echo "    -help:表示查看脚本使用说明"
  echo "参数3：	从库是否自动创建表"
  echo "    T|t :脚本自动创建"
  echo "    N|n : 表已存在，无需创建"
  echo "    -help:表示查看脚本使用说明"
  echo "示例："
  echo "    sh  synctool4cluster.sh  A D T   全库同步，并自动创建库和表。Warning:如果库存在，会删库重建"
  echo "    sh  synctool4cluster.sh  A C T   全库同步，库已存在，自动创建表。Warning:如果表存在，会删表重建"
  echo "    sh  synctool4cluster.sh  A C N   全库同步，库和表结构都已存在，无需创建"
  echo "    sh  synctool4cluster.sh  P D T   自定义同步表清单，并自动创建库和表。Warning:如果库存在，会删库重建"
  echo "    sh  synctool4cluster.sh  P C T   自定义同步表清单，库已存在，自动创建表。Warning:如果表存在，会删表重建"
  echo "    sh  synctool4cluster.sh  P C N   自定义同步表清单，库和表结构都已存在，无需创建"
  echo "****************************************************************************************"
}

if [ "$1" = "-help" ] || [ $# -lt 3 ]|| [ $# -gt 3 ]; then
  #echo 'Script must assign three parameters !'
  maninfo | tee -a ${synclog}
  exit 1
fi

function createdb(){
  cat ${tblistfile} | cut -d. -f1 | uniq > ${basedir}/dblist.txt
  while read dbname
  do
    gccli -u${syncUser} -p${masterPassord} -h${masterIP} -Nse "show create database ${dbname}"| cut -f2 >>${basedir}/dbs_metadata.sql
  done <${basedir}/dblist.txt
  sed -i 's/CREATE DATABASE/CREATE DATABASE IF NOT EXISTS/g' ${basedir}/dbs_metadata.sql
  sed -i 's/$/& ;/g' ${basedir}/dbs_metadata.sql
  gccli -u${syncUser} -p${slavePassword} -h${slaveIP} -vvv -e "source ${basedir}/dbs_metadata.sql" 
  mv ${basedir}/dbs_metadata.sql ${backdir}/dbs_metadata.sql_bak$(date +'%Y%m%d%H%M')
  mv ${basedir}/dblist.txt ${basedir}/dblist.txt_bak$(date +'%Y%m%d%H%M') 
}

function createtb(){
  while read tbname
  do
    gccli -u${syncUser} -p${masterPassord} -h${masterIP} -Nse "show create table ${tbname}"| cut -f2  >${tbname%%.*}_${tbname##*.}.sql
    sed -i 's/CREATE TABLE/CREATE TABLE IF NOT EXISTS/g' ${tbname%%.*}_${tbname##*.}.sql
    gccli -u${syncUser} -p${slavePassword} -h${slaveIP} -D ${tbname%%.*} -vvv -e "source ${tbname%%.*}_${tbname##*.}.sql"
    mv ${tbname%%.*}_${tbname##*.}.sql ${backdir}/${tbname%%.*}_${tbname##*.}.sql_bak$(date +'%Y%m%d%H%M')
  done <${tblistfile}
}

case $1 in
a | A)
  gccli -u${syncUser} -p${masterPassord} -h${masterIP} -Nse "select table_schema||'.'||table_name from information_schema.tables where table_schema not in('information_schema','performance_schema','gbase','gclusterdb','gctmpdb') and table_type='BASE TABLE';" >${tblistfile}
  if [ ! -s ${tblistfile} ]; then
    echo "${tblistfile}为空，检查查询脚本80行IP/用户密码配置"  | tee -a ${synclog}
    exit 1
  fi
  ;;
p | P)
  if [ ! -s ${tblistfile} ]; then
    echo "同步列表文件${tblistfile}不能为空"  | tee -a ${synclog}
    exit 1
  fi
  ;;
*)
  echo 'Error! the first parameter value must be A|a|p|P' | tee -a ${synclog}
  exit 1
  ;;
  esac
  
  
case $2 in
d | D)
  echo "Slave database start create databases" | tee -a ${synclog}
  createdb | tee -a ${synclog}
  echo "Slave database finished create databases" | tee -a ${synclog}
  ;;
c | C)
  echo "No need create databases by script! database has created!" | tee -a ${synclog}
  ;;
*)
  echo 'Parameter value must be c|C|d|D' | tee -a ${synclog}
  exit 1
  ;;
  esac
case $3 in
t | T)
   echo "Slave database start create tables" | tee -a ${synclog}
   createtb | tee -a ${synclog}
   echo "Slave database finished create tables" | tee -a ${synclog}
  ;;
n | N)
  echo "No need create tables by script! tables has created!" | tee -a ${synclog}
  ;;
*)
  echo 'Parameter value must be d|D|N|n' | tee -a ${synclog}
  exit 1
  ;;
esac

echo  "Start sync data"  | tee -a ${synclog}

gcluster_rsynctool --master_mpp_ip=${masterIP} --slave_mpp_ip=${slaveIP} --table_list_file=${tblistfile} --database_user=${syncUser}  --master_mpp_gc_pw=${masterPassord}  --slave_mpp_gc_pw=${slavePassword}  --log_level=4 --error_table_list_file=${logdir}/error_tblist_$(date +'%Y%m%d%H%M').log  --log_name=${logdir}/rsynctool_$(date +'%Y%m%d%H%M').log  | tee -a ${synclog}
echo  "Sync data  end"  | tee -a ${synclog}

mv {tblistfile} {tblistfile}_bak$(date +'%Y%m%d%H%M')