mysqldump导出table,view,trigger,routines,events

最新推荐文章于 2024-08-19 04:06:27 发布

lwei_998

最新推荐文章于 2024-08-19 04:06:27 发布

阅读量1.1w

点赞数

分类专栏： MySQL

本文链接：https://blog.csdn.net/lwei_998/article/details/43020617

版权

MySQL 专栏收录该内容

123 篇文章 1 订阅

订阅专栏

实现功能：可以按库名导出一个mysql实例中的表，视图，存储过程/函数，触发器，事件

用法：

1. 将脚本保存为dump.sh

2. sh dump.sh 备份路径用户密码 IP 端口

例：sh dump.sh /backup/bjdb root root 127.0.0.1 3306

#!/bin/bash

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

done