shell脚本实现mysql传输表空间

yum -y install sshpass

#源实例信息配置
shost=192.168.37.219  #源实例ip
sport=3306  #源实例端口
suser=root  #源实例用户名
spassword=xxxx@123edc #源实例密码
sdatadir=/data/mysql/mysql3306/data/  #源实例数据文件目录
smysql_path=/usr/local/mysql/bin/mysql #源实例mysql指令位置

#目标实例信息配置
dhost=192.168.169.40  #目标实例ip
dport=3306  #目标实例端口
duser=root  #目标实例用户
dpassword=xxxx@123edc #目标实例密码
ddatadir=/data/mysql/mysql3306/data/ #目标实例数据文件目录
dmysql_path=/usr/local/mysql/bin/mysql #目标实例mysql指令位置

#目标服务器配置sshpass拷贝文件
host='192.168.169.40' #目标服务器ip
sshport='22'    #ssh端口
hostuser='root' #服务器用户名
hostpassword='xxxx@www23@'  #服务器密码

vi tran_tab.sh
#!/bin/bash
#*************************************************************************
#  Author       :               wangwei
#  CreateDate   :               2020-09-27
#  Description  :               this script using the Transportable Tablespaces
#                               archive move data 
#
#*************************************************************************
#!/bin/bash

Usage() {
cat << EOF
tran_tab
OPTIONS:
   -t    table name
   -d    destination instance db
   -s    source instance db
For secrity: This scripts check the full need arguments
EOF
}
while getopts ":t:d:s:" opt; do
  case $opt in
    t)
      stab_name=${OPTARG}
      ;;
    d)
      ddbname=${OPTARG}
      ;;
    s)
      sdbname=${OPTARG}
      ;;
    \?)
      echo "Invalid option: -$OPTARG" >&2
      exit 1
      ;;
    :)
      echo "Option -$OPTARG requires an argument." >&2
      Usage
      exit 1
      ;;
  esac
done
if [ $# != 6 ] ; then
    Usage
    exit 1;
fi

#echo $stab_name >> 1.txt
#echo $ddbname >> 1.txt
#echo $sdbname >> 1.txt
 
#源实例信息配置
shost=192.168.37.219
sport=3306
suser=root
spassword=xxxx@123edc
sdatadir=/data/mysql/mysql3306/data/
smysql_path=/usr/local/mysql/bin/mysql

#目标实例信息配置
dhost=192.168.169.40
dport=3306
duser=root
dpassword=xxxx@123edc
ddatadir=/data/mysql/mysql3306/data/
dmysql_path=/usr/local/mysql/bin/mysql

#目标服务器配置sshpass拷贝文件
host='192.168.169.40'
sshport='22'
hostuser='root'
hostpassword='xxxx@www23@'

#获取表的创建脚本
$smysql_path -h$shost -P$sport -u$suser -p$spassword --skip-column-names $sdbname -e "show create table $stab_name \G" >./.$stab_name.sql
sed -i '1,2d' ./.$stab_name.sql
sed -i '$a ;' ./.$stab_name.sql
#在目标实例上创建表
strsql=`cat ./.$stab_name.sql`
echo $strsql
$dmysql_path -h$dhost -P$dport -u$duser -p$dpassword $ddbname -e "$strsql"

#On the destination instance, discard the tablespace
$dmysql_path -h$dhost -P$dport -u$duser -p$dpassword $ddbname -e "ALTER TABLE $stab_name DISCARD TABLESPACE;"

#On the source instance, run FLUSH TABLES ... FOR EXPORT 
$smysql_path -h$shost -P$sport -u$suser -p$spassword $sdbname -e "FLUSH TABLES $stab_name FOR EXPORT;select sleep(30)" &

#Copy the .ibd file and .cfg metadata file from the source instance to the destination instance
sshpass -p $hostpassword scp -P$sshport -o StrictHostKeyChecking=no $sdatadir/$sdbname/$stab_name.{cfg,ibd} $hostuser@$host:$ddatadir/$ddbname/

#修改文件权限
sshpass -p $hostpassword ssh -p$sshport $hostuser@$host "chown mysql:mysql -R $ddatadir/$ddbname/$stab_name.{ibd,cfg}"

#On the source instance, use UNLOCK TABLES to release the locks
$smysql_path -h$shost -P$sport -u$suser -p$spassword $sdbname -e "UNLOCK TABLES;"

#On the destination instance, import the tablespace:
$dmysql_path -h$dhost -P$dport -u$duser -p$dpassword $ddbname -e "ALTER TABLE $stab_name IMPORT TABLESPACE;"