#!/bin/bash
#FileName:select_into_bak.sh
#Desc:Use select into outfile to backup db or tables
#Created By:fedoracle
#Date:2012/04/24
DB=mysql
USER=test
PASSWD=test
HOST=192.168.164.129
BAK_DIR=/data/mysql/backup/$DB
DATE=`date "+%Y-%m-%d %H-%M-%S"`
[ -d "$BAK_DIR" ] || /bin/mkdir -p $BAK_DIR && /bin/chown mysql:mysql $BAK_DIR
/usr/local/mysql/bin/mysql -h$HOST -u$USER -p$PASSWD -e "show tables from $DB" | grep -v "Tables_in" > $BAK_DIR/tables.txt
for table in `cat $BAK_DIR/tables.txt`
do
/usr/local/mysql/bin/mysql -h$HOST -u$USER -p$PASSWD -e "select * from $DB.$table into outfile '"$BAK_DIR/$table".txt' character set utf8;"
done
cd $BAK_DIR
/bin/tar -czf "$DB-$DATE".tar.gz *.txt
/bin/rm -f *.txt
exit 0
