备份数据库

#!/bin/bash

MYSQL_HOST="127.0.0.1"
MYSQL_PORT="3306"
MYSQL_USER="root"
MYSQL_PASSWORD="123456"

BACKUP=/home/.backupsql

DATETIME=$(date +%Y%m%d%H%M)

DATABASES=`/usr/local/mysql/bin/mysql -h${MYSQL_HOST} -P${MYSQL_PORT} -u${MYSQL_USER} -p${MYSQL_PASSWORD} -e "SHOW DATABASES;"`

for db in $DATABASES; do
	# 忽略备份的数据库
    if [[ "$db" != "Database" ]] && [[ "$db" != "information_schema" ]] && [[ "$db" != "performance_schema" ]]  && [[ "$db" != "mysql" ]] ; then
        echo "正在备份数据库: $db"
        rm -rf "${BACKUP}/$db"
        mkdir -p "${BACKUP}/$db"
        /usr/local/mysql/bin/mysqldump -h${MYSQL_HOST} -P${MYSQL_PORT} -u${MYSQL_USER} -p${MYSQL_PASSWORD} --databases $db > ${BACKUP}/$db/${db}.sql
        echo "数据库【$db】已备份到：${BACKUP}/${DATETIME}/$db.sql 下"
    fi
done


#删除180天前的备份数据
#find ${BACKUP} -mtime +180 -name "*.sql" -exec rm -rf {} \;

恢复数据库

#!/bin/bash

DB_USER="root"   # MySQL用户名
DB_PASSWORD="123456"    # MySQL密码

# 需要恢复的数据库列表
dbName="db_test1","db_test2"
DATABASES=(`echo $dbName | tr ',' ' '` )

BACKUP_DIR="/home/.backupsql/"      # 存放备份文件的目录路径

for db in ${DATABASES[@]}; do
    echo "Restoring database $db..."
    /usr/local/mysql/bin/mysql -u$DB_USER -p$DB_PASSWORD $db < "$BACKUP_DIR/${db}/${db}.sql"
done

来源链接：https://www.cnblogs.com/osinn/p/18807677