mysql 8.0数据库备份与导入

数据库备份 db_backup.sh

#!/bin/bash

#备份保存数据库及表结构数据信息
#需要备份的数据库将库名保存在databases.table文件中

#勿取消注释
## mysql -h <mysql_host> -u <mysql_user> -p<mysql_password> -Dxxx(数据库名)  < xxx.sql

set -x

current_path=`pwd`
DATE=`date "+%m-%d"`
mkdir $current_path/$DATE

if [ $# != 4 ];then
  echo 'bash databases_backup.sh <mysql_host> <mysql_user> <mysql_password> <databases.list path文件位置>'
  exit 1
fi

mysql_host=$1
mysql_user=$2
mysql_password=$3
mysql_databases=$4

database_storage_path=$current_path/$DATE
backup_log=$database_storage_path/backup.log

time_open=`date "+%Y-%m-%d %H:%M:%S"`
DATE_open=`date +%s -d "$time_open"`
for database in `cat ${mysql_databases}`
do
        time_1=`date "+%Y-%m-%d %H:%M:%S"`
        DATE_1=`date +%s -d "$time_1"`
        echo "${database}" >> $backup_log
        mysqldump -h ${mysql_host} -u ${mysql_user} -p${mysql_password} --databases ${database} --set-gtid-purged=off > $database_storage_path/${database}.sql
        time_2=`date "+%Y-%m-%d %H:%M:%S"`
        DATE_2=`date +%s -d "$time_2"`
        echo "$time_1 开始备份${database}" >> $backup_log
        echo "$time_2 备份完成${database}" >> $backup_log
        echo "用时`expr $DATE_2 - $DATE_1` s" >> $backup_log
done
time_close=`date "+%Y-%m-%d %H:%M:%S"`
DATE_close=`date +%s -d "$time_close"`
echo "总用时`expr $DATE_close - $DATE_open` s" >> $backup_log

数据库导入 db_import.sh

#!/bin/bash

set -x

if [ $# != 4 ];then
  echo 'bash databases_import.sh <mysql_host> <mysql_user> <mysql_password> <databases_sql_path目录>'
  exit 1
fi

mysql_host=$1
mysql_user=$2
mysql_password=$3
databases_path=$4

for sql in `ls ${databases_path} |grep .sql`
do
  echo "执行${sql}"
  mysql -h ${mysql_host} -u ${mysql_user} -p${mysql_password}  < ${databases_path}/${sql}
  sleep 2
done