mysql 表备份_mysql备份每个库下面每个表

备份mysql每个库里的每张表

#!/bin/bash

MYUSER="root"

MYPASS="123456"

HOST="localhost"

BACKUPDIR="/backup_mysql"

DATE=`date -I`

RETENTION=20

#MYSQLDATA="/var/lib/mysql"

[ ! -d $BACKUPDIR/$DATE ] && mkdir -p $BACKUPDIR/$DATE

DBS=`mysql -u$MYUSER -p$MYPASS -Bse "show databases"|grep -v "information_schema" |grep -v "test"`

for db_name in $DBS

do

mkdir -p $BACKUPDIR/$DATE/$db_name

tables=`mysql -u$MYUSER -p$MYPASS -Bse "use $db_name;show tables"`

for table_name in $tables

do

mysqldump -u$MYUSER -p$MYPASS -h$HOST --single-transaction --master-data=2 $db_name $table_name >$BACKUPDIR/$DATE/$db_name/$table_name.sql

done

done

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30345407/viewspace-2135572/，如需转载，请注明出处，否则将追究法律责任。