实现功能: 可以按库名导出一个mysql实例中的表,视图,存储过程/函数,触发器,事件
用法:
1. 将脚本保存为dump.sh
2. sh dump.sh 备份路径 用户 密码 IP 端口
例:sh dump.sh /backup/bjdb root root 127.0.0.1 3306
############################################## # Function: DUMP MYSQL TABLE, PROCEDURE,VIEW,TRIGGER,EVENT # Date: 2015-01-21 # Author: Liang Wei # Mail: liangweione@gmail.com # Version: 1.0 ########################################################### #DEFINE VARIABLES MYSQL=`which mysql` MYSQLDUMP=`which mysqldump` GZIP=`which gzip` BACKUP_DIRECTORY=$1 V_USERNAME=$2 V_PASSWORD=$3 V_HOST=$4 V_PORT=$5 MYCMD="$MYSQL -u$V_USERNAME -p$V_PASSWORD -h$V_HOST -P$V_PORT " DUMP_TB="$MYSQLDUMP -u$V_USERNAME -p$V_PASSWORD -h$V_HOST -P$V_PORT --compact -d" DUMP_TRIGGER="$MYSQLDUMP -u$V_USERNAME -p$V_PASSWORD -h$V_HOST -P$V_PORT --triggers \ --no-create-info --no-data --no-create-db --skip-opt" DUMP_PROC="$MYSQLDUMP -u$V_USERNAME -p$V_PASSWORD -h$V_HOST -P$V_PORT --routines \ --no-create-info --no-data --no-create-db --skip-opt --skip-triggers" DUMP_EVENTS="$MYSQLDUMP -u$V_USERNAME -p$V_PASSWORD -h$V_HOST -P$V_PORT --events \ --no-create-info --no-data --no-create-db --skip-opt --skip-triggers" #echo MYSQL=$MYSQL #echo MYSQLDUMP=$MYSQLDUMP #echo GZIP=$GZIP # VALIDATE INPUT ARGUMENTS if [ "$#" -ne 5 ]; then echo "Usage: $0 <BACKUP_DIRECTORY> <V_USERNAME> <V_PASSWORD> <V_HOST> <V_PORT>" exit 1; fi # VALIDATE BACKUP DIR BACKUP_DIR="$1" if [ ! -d $BACKUP_DIR ]; then echo "$BACKUP_DIR does not exist" exit 1; fi DUMP_DIR="$BACKUP_DIR/`date +%F`" if [ ! -d $DUMP_DIR ]; then mkdir -p $DUMP_DIR if [ ! -d $DUMP_DIR ]; then echo "Failed to create backup dir $DUMP_DIR" exit 1; fi fi for DB in `$MYCMD -N -e "show databases" | grep -v 'Database\|performance_schema\|information_schema'` # for DB in sakila do mkdir -p $DUMP_DIR/$DB/{table,view,trigger,proc,event} # Dump table define for TB in `$MYCMD -N -e "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES \ WHERE TABLE_SCHEMA IN('${DB}') AND ENGINE IS NOT NULL AND ENGINE NOT IN('CSV');" ` do BK_FILE=$DUMP_DIR/$DB/table/${TB}.sql $DUMP_TB $DB $TB >$BK_FILE done #Dump view for VW in `$MYCMD -N -e "SELECT table_name from information_schema.views where TABLE_SCHEMA in('${DB}')" ` do $DUMP_TB $DB $VW > $DUMP_DIR/$DB/view/${VW}.sql done #Dump triggers $DUMP_TRIGGER $DB >$DUMP_DIR/$DB/trigger/${DB}_trigger.sql #Dump routines $DUMP_PROC $DB >$DUMP_DIR/$DB/proc/${DB}_proc_function.sql #Dump events $DUMP_EVENTS $DB >$DUMP_DIR/$DB/event/${DB}_event.sql
转载于:https://blog.51cto.com/fccwcom/1657629