大家好，又见面了，我是你们的朋友全栈君。
概述
每天定时远程或者本地备份mysql数据库,并且保存最新7天的备份内容。
脚本内容
[root@myhost ~]# cat /home/script/mysqlbackup.sh
#!/bin/sh
source /etc/profile
#define variables
#the login information of your mysql db.
login_user=""
login_passwd="" #数据库密码最好不要含有$符号，因为避免被当成变量，这是个大坑，被坑惨了
db_host=""
db_port=""
#the real databases which you want to backup.
db_array=("db_name0" "db_name1" "db_name2")
#the dir for saving your backup file.
backup_dir="/home/backup/mysql_bak/$db_host-backup"
if [ ! -d $backup_dir ];then
mkdir -p $backup_dir
fi
#date format for the backup file (yyyy-mm-dd)
time=`date +"%Y-%m-%d"`
#the mysql,mysqldump and other bin's path
MYSQL_COMMDAND="" #根据自己的数据库安装路径决定
MYSQLDUMP_COMMAND="" #根据自己的数据库安装路径决定
MKDIR_COMMAND="/bin/mkdir"
RM_COMMAND="/bin/rm"
MV_COMMAND="/bin/mv"
GZIP_COMMAND="/bin/gzip"
# check the dir for saving backup file is writeable or not.
if [ ! -w $backup_dir ];then
echo "Error: $backup_dir is un-writeable." && exit 0
fi
#check if the dir for saving the backup file exists or not.
if [ ! -d ${backup_dir}/backup.0 ];then
$MKDIR_COMMAND -p "$backup_dir/backup.0"
fi
#starting to backup.
for db_name in ${db_array[*]}
do
$MYSQLDUMP_COMMAND -P$db_port -u$login_user -h$db_host --set-gtid-purged=off -p$login_passwd -B $db_name | $GZIP_COMMAND -9 > "$backup_dir/backup.0/$time.$db_name.gz"
done
# delete the oldest backup
if [ -d "$backup_dir/backup.7" ];then
$RM_COMMAND -rf "$backup_dir/backup.7"
fi
# rotate backup directory
#for int in 6 5 4 3 2 1 0
for int in {6..0}
do
if [ -d "$backup_dir"/backup."$int" ];then
next_int=`expr $int + 1`
$MV_COMMAND "$backup_dir"/backup."$int" "$backup_dir"/backup."$next_int"
fi
done
exit 0;
定时任务
数据库备份的定时任务尽量避开数据库访问的高峰期，可以选择在半夜执行。
[root@myhost ~]# cat /etc/crontab
SHELL=/bin/bash
PATH=/sbin:/bin:/usr/sbin:/usr/bin
MAILTO=root
HOME=/
# For details see man 4 crontabs
# Example of job definition:
# .---------------- minute (0 - 59)
# | .------------- hour (0 - 23)
# | | .---------- day of month (1 - 31)
# | | | .------- month (1 - 12) OR jan,feb,mar,apr ...
# | | | | .---- day of week (0 - 6) (Sunday=0 or 7) OR sun,mon,tue,wed,thu,fri,sat
# | | | | |
# * * * * * user-name command to be executed
30 3 * * * root /home/script/mysqlbackup.sh
数据库恢复
[root@myhost ~]# cd $backup_dir && ll
##假设备份文件名为：2019-09-29.dbname.gz
[root@myhost ~]# gzip -d 2019-09-29.dbname.gz && ll #解压备份文件之后文件名： 2019-09-29.dbname
[root@myhost ~]# cat 2019-09-29.dbname > 2019-09-29_dbname.sql 或者 mv 2019-09-29.dbname 2019-09-29_dbname.sql
[root@myhost ~]# mysql -uroot -proot123456 -B dbname < 2019-09-29_dbname.sql #恢复数据库
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请联系我们举报，一经查实，本站将立刻删除。
发布者：全栈程序员-站长，转载请注明出处：https://javaforall.net/130902.html原文链接：https://javaforall.net
