linuxでmysqlデータベースをバックアップするshellスクリプト

#!/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