70 Blog: [MySQL] 非標準的資料庫備份與還原

2012年11月21日星期三

[MySQL] 非標準的資料庫備份與還原

瀏覽數

最近使用mysqldump備份資料庫，有時候不會成功。改用 MySQL 的 INTO OUTFILE 指令轉出文字檔，再壓縮。此方式的備份速度出奇的快速，至於有沒有缺點，還不知道。

將底下程式碼儲存到 _dumptxt.sh

#!/bin/bash
week=`date +%d`
user='user'
pwd='1234'
dir=`pwd`

#echo $# #參數數量
[ $# != 1 ] && echo "請輸入資料庫名稱 database_name" && exit 0

path='/light/mysql/dump/'
tmppath='/tmp'
cd $tmppath

dbs=$1
databases="$(mysql -u$user -p$pwd -Bse 'show databases like "'$dbs'"')"
for db in $databases
do
  fname=$path$db'.sql.'$week'.tgz'
  workpath=$tmppath'/'$db'.sql.'$week
  test -d $workpath && rm -rf $workpath
  test ! -d $workpath && mkdir -p $workpath
  chmod 777 $workpath
  create=$workpath'/create.sql'
  echo 'DROP DATABASE IF EXISTS '$db';' > $create
  echo 'CREATE DATABASE '$db';' >> $create
  echo 'USE '$db';' >> $create
  mysqldump $db -u$user -p$pwd --opt --default-character-set=latin1 --no-data >> $create

  tables="$(mysql $db -u$user -p$pwd -Bse 'show tables')"
  for tname in $tables
  do
    txt=$workpath'/'$tname'.txt'
    mysql -u$user -p$pwd -Bse 'SELECT * FROM `'$db'`.`'$tname'` INTO OUTFILE "'$txt'"'
  done
  cd $tmppath
  tar -zcf $fname $db'.sql.'$week
  rm -rf $workpath
  echo $fname
done

cd $dir

將底下程式碼儲存到 _restore.sh

#!/bin/bash
user='user'
pwd='1234'
dir=`pwd`

#echo $# ＃參數數量
[ $# != 1 ] && echo "請輸入備份檔名 {dbname}.sql.{01-31}.tgz" && exit 0
test ! -f $1 && echo '檔案 '$1' 不存在!' && exit 0

dumpfile=$1
dumpdir=`echo $1 | sed 's/^.*\///g' | sed 's/.tgz//g'`
dbs=`echo $dumpdir | sed 's/\.sql.*$//g'`
#dbs=`echo $dumpdir | sed 's/\..*$//g'`
tmppath='/tmp/'
workpath=$tmppath$dumpdir

test -d $workpath && rm -rf $workpath
tar -zxf $dumpfile -C $tmppath
test ! -d $workpath && exit 0

cd $workpath
mysql -u$user -p$pwd < create.sql

databases="$(mysql -u$user -p$pwd -Bse 'show databases like "'$dbs'"')"
for db in $databases
do
  tables="$(mysql $db -u$user -p$pwd -Bse 'show tables')"
  for tname in $tables
  do
    txt=$workpath'/'$tname'.txt'
    mysql -u$user -p$pwd -Bse 'LOAD DATA INFILE "'$txt'" INTO TABLE `'$db'`.`'$tname'`'
  done
done
cd $tmppath
rm -rf $workpath
cd $dir