MySQL 备份脚本

发表于 | 分类于
字数统计: 712 | 阅读时长 ≈ 3

1. MySQL 备份脚本

最初脚本为在某个 QQ 群里看到然后又改的。

点击下载

1
#!/bin/sh
2
# MySQL 数据库备份 可以指定备份保留次数，指定不备份数据库
3
4
PATH=/bin:/sbin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/local/sbin:~/bin
5
export PATH
6
7
db_host="localhost"
8
db_user="tmper"
9
db_passwd="MT1IdhVds"
10
#保留备份数
11
savebakcount=8
12
#排除备份数据库
13
notbakdatabase=('performance_schema','information_schema','mysql','test','sys')
14
# 备份文件目录
15
backup_dir="/home/mysql_bak"
16
# 备份文件时间格式(yyyy-mm-dd_HH_MM)
17
time="$(date +"%Y-%m-%d_%H_%M")"
18
19
# 要备份的 MySQL 信息
20
MYSQL="/usr/bin/mysql"
21
MYSQLDUMP="/usr/bin/mysqldump"
22
MKDIR="mkdir"
23
RM="rm"
24
MV="mv"
25
GZIP="gzip"
26
# 检查目录是否可写
27
test ! -w $backup_dir && echo "Error: $backup_dir is un-writeable." && exit 0
28
# 测试是否创建了临时备份目录
29
test ! -d "$backup_dir/backup.0/" && $MKDIR "$backup_dir/backup.0/"
30
# 得到所有的数据库信息
31
all_db="$($MYSQL -u $db_user -h $db_host -p$db_passwd -Bse 'show databases')"
32
for db in $all_db;do
33
#数据库是否为排除备份数据库
34
echo "${notbakdatabase[@]}" | grep -wq "$db" &&  continue
35
echo "正在备份${db}..."
36
$MYSQLDUMP   --single-transaction -u $db_user -h $db_host -p$db_passwd $db | $GZIP -9 > "$backup_dir/backup.0/$time.$db.sql.gz"
37
done
38
39
# delete the oldest backup
40
test -d "$backup_dir/backup.$savebakcount/" && $RM -rf "$backup_dir/backup.$savebakcount"
41
# rotate backup directory
42
for int in  `seq $savebakcount -1 0`
43
do
44
if(test -d "$backup_dir"/backup."$int");then
45
next_int=`expr $int + 1`
46
$MV "$backup_dir"/backup."$int" "$backup_dir"/backup."$next_int"
47
fi
48
done
49
50
[[ -d "$backup_dir"/backup.1 ]] || echo "back error"
51
52
exit 0;
53
54
55
#备份数据库定时计划
56
#23 23 * * * (flock -xn /tmp/mysqlbak.lock -c /opt/mysqlbak.sh >>/var/log/mysql_bak.log 2>&1)
57
#grant select, RELOAD, SHOW DATABASES, LOCK TABLES on *.* to 'tmper'@localhost identified by 'MT1IdhVds'

首先登陆 MySQL 进行授权账号密码。备份路径可以自己指定默认是/home/mysql_bak

1
grant select, RELOAD, SHOW DATABASES, LOCK TABLES on *.* to 'tmper'@localhost identified by 'MT1IdhVds'

把

1
23 23 * * * (flock -xn /tmp/mysqlbak.lock -c /opt/mysqlbak.sh >>/var/log/mysql_bak.log 2>&1)

加入 crontab 中重启 crond ， CentOS下的命令是 systemctl restart crond 。

2. 恢复脚本

点击下载

1
#!/bin/sh
2
# MySQL 数据库恢复脚本可以指定恢复的次数
3
4
PATH=/bin:/sbin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/local/sbin:~/bin
5
export PATH
6
7
db_host="localhost"
8
db_user="root"
9
db_passwd="123#456"
10
backup_dir="/home/mysql_bak"
11
# MySQL 信息
12
MYSQL="/usr/bin/mysql"
13
MYSQLDUMP="/usr/bin/mysqldump"
14
15
GUNZIP="gunzip"
16
# 检查目录是否可写
17
18
BACK_PATH=$1
19
20
test ! -d "$BACK_PATH" && echo "Error: please input back_path or $backup_PATH is not find." && exit 0
21
22
cd "$BACK_PATH"
23
24
for db in `ls|grep sql.gz`;do
25
	dbname=`echo $db|cut -d . -f 2`
26
	#create databases
27
	echo "Will import $dbname DATA from $BACK_PATH/$db...."
28
done
29
30
31
echo -n "will recover data Please enter yes or no  -> "
32
read judge
33
34
if [[ "$judge" != "yes" ]]; then
35
	echo "not input yes exit!!!"
36
	exit 0;
37
fi
38
39
for db in `ls|grep sql.gz`;do
40
	dbname=`echo $db|cut -d . -f 2`
41
	#create databases
42
	create_db="CREATE DATABASE IF NOT EXISTS $dbname DEFAULT CHARSET utf8 COLLATE utf8_general_ci;"
43
	echo $create_db
44
	echo "CREATE TABLE $dbname"
45
	echo ''
46
	$MYSQL -u $db_user -h $db_host -p$db_passwd -e "$create_db"
47
	echo "IMPORT $db DATA from BACK...."
48
	$GUNZIP -c $db |$MYSQL -u $db_user -h $db_host -p$db_passwd $dbname
49
done
50
exit 0;

恢复方式为指定恢复路径的备份文件然后运行。注意会覆盖原有数据。