mysqldump数据备份与恢复

备份

#!/bin/bash
 
# 数据库信息
DB_USER="xxxxxx"     #数据库用户名
DB_PASS="xxxxxx"     #数据库密码
#其它信息
BIN_DIR="/usr/bin"   #mysqldump命令执行路径
BACK_DIR="/data/mariaDBbak"    #备份文件存储目录
DATE=`date +%Y%m%d%H%M%S`       #显示备份时间

# 备份所有数据库
echo "--------------------- mysqldump start -------------------------"	
echo `date +%Y%m%d%H%M%S`  

for dbname in $(mysql -u$DB_USER -p$DB_PASS -e "show databases;" | grep -Evi "informa|perfo|database|mysql")
do
echo $dbname;
$BIN_DIR/mysqldump --opt -u$DB_USER -p$DB_PASS $dbname | gzip > $BACK_DIR/db_${dbname}_$DATE.sql.gz
done

echo "---------------------- mysqldump end --------------------------"	
echo `date +%Y%m%d%H%M%S`

# 删除5天之前的备份文件，但保留日期为1号的文件（用于手动删除）
find $BACK_DIR/* -regextype "posix-extended" -not -regex ".*[0-9]{6}01[0-9]{6}\.sql\.gz$" -mtime +5 -exec rm {} \;

生成的备份文件名为db_databasename_YYYYmmddHHMMSS.sql.gz

恢复

FILEDIR="/data/mariaDBbak"  #备份文件存储目录
for sqldata in `ls $FILEDIR`
do
strtmp=`echo ${sqldata#*db_}` #取db_之后的字符串
databasename=`echo ${strtmp%_*}` #取最后一个下划线之前的字符串
echo $sqldata #打印正在处理的备份文件名
echo $databasename #打印正在处理的数据库

#进入mysql处理
mysql -uxxx -pxxx <<QUERY_SQL
    DROP DATABASE IF EXISTS $databasename;
    create database $databasename;
    use $databasename;
    source $FILEDIR/$databasename;
QUIT
QUERY_SQL
done

问题

mysqldump备份报错

mysqldump: Got error: 1449: “The user specified as a definer
(‘xxxxxx’@’%’) does not exist” when using LOCK TABLES

原因：从其它库复制过来 带了原用户信息，与现用户不匹配，数据库执行查看DEFINER

SELECT * FROM information_schema.VIEWS;

解决：
数据库执行生

SELECT * FROM information_schema.VIEWS;
SELECT
	concat(
		"ALTER DEFINER='user'@'%' VIEW ",
		table_name,
		" AS ",
		view_definition,
		";"
	)
FROM
	information_schema.VIEWS
WHERE
	TABLE_SCHEMA = 'database';

更改 user和database 生成更新语句 并执行