mysql数据导入默认目录,MySQL使用dbdump实现传输数据，并更换默认存储目录

#!/usr/bin/env bash

### file name:dbdump.sh,save to ~/bin,and add $HOME/bin PATH to ~/.bash_profile or ~/.profile

### usage: dbdump.sh all or 1:with data,dbdump.sh none or 2:without data

####################################################################

## add these environment parameters to ~/.bash_profile or ~/.profile

# export ENV=数据库

# export db_host=数据库地址

# export db_port=数据库端口

# export db_user=数据库用户名

# export db_schema=数据库用户名

# export BACKUP_PATH=/data/docker/container/mysql/backup

# export PATH=$HOME/bin:$PATH

####################################################################

date=`date "+%Y%m%d"`

mkdir -p $BACKUP_PATH

cd $BACKUP_PATH

exec_dump() {

echo

echo "-- ${ENV} `date +%F\ %T` Database ${db_schema} dump to ${dump_file} start ..."

if [ -f ${dump_file} ]; then

rm -f ${dump_file}

fi

if [ "v${no_data}" == "v" ]; then

echo "mysqldump --add-drop-table --set-gtid-purged=OFF -h ${db_host} -u ${db_user} -P ${db_port} -p -R ${db_schema} 2> ${error_log} | gzip > ${dump_file}"

mysqldump --add-drop-table --set-gtid-purged=OFF -h ${db_host} -u ${db_user} -P ${db_port} -p -R ${db_schema} 2> ${error_log} | gzip > ${dump_file}

else

echo "mysqldump ${no_data} --add-drop-table --set-gtid-purged=OFF -h ${db_host} -u ${db_user} -P ${db_port} -p -R ${db_schema} 2> ${error_log} | sed 's/AUTO_INCREMENT=[0-9]*\s*//g' | gzip > ${dump_file}"

mysqldump ${no_data} --add-drop-table --set-gtid-purged=OFF -h ${db_host} -u ${db_user} -P ${db_port} -p -R ${db_schema} 2> ${error_log} | sed 's/AUTO_INCREMENT=[0-9]*\s*//g' | gzip > ${dump_file}

fi

if [ -s ${error_log} ] ; then

rm -f ${dump_file}

cat ${error_log}

echo

echo "-- ${ENV} `date +%F\ %T` Database ${db_schema} dump error."

exit 1

else

rm -f ${link_file}

ln -s ${dump_file} ${link_file}

echo

echo "-- ${ENV} `date +%F\ %T` Database ${db_schema} dump to ${dump_file} finished ."

fi

}

exec_dump_without_data() {

no_data='--no-data'

exec_dump

}

if [ x$1 != x ]; then

dump_type=$1

else

read -p "

Which dump type do you want?

1> all (with data)

2> none structure only (no data)

Input dump type: [1|2] > " dump_type

fi

case ${dump_type} in

1|all)

link_file=${db_schema}_dump_with_data.sql.gz

dump_file=${date}_${link_file}

exec_dump

;;

2|none)

dump_file=${date}_${link_file}

exec_dump_without_data

;;

*)

echo "dump type error, nothing going to do."

exit 1

;;

esac