最近需要对mysql进行定时备份，写一篇备忘

1. 创建备份目录

目录说明：

  • /data/backup/mysql : 备份文件存放目录
  • /data/backup : 脚本存放目录

mkdir -p /data/backup/mysql
cd /data/backup

2.创建全量备份脚本文件

vim mysql_backup.sh
mysqldump 语法说明：

  • mysqldump [options] –u 用户名 -p 密码 数据库名 > 备份的文件
  • -R 导出 函数和存储过程
  • -E 导出事件
  • -A 备份所有数据库
  • -d 备份表结构
  • -t 备份表数据
  • -B 同时备份多个数据库，例如：-B db1 db2 db3
  • -l 指定表上锁
  • -x 所有表上锁
  • –single-transaction 不锁表备份，只支持innodb 优先级大于–master-data
  • –master-data 记录binlog同时锁表 –master-data = 1 binlog 记录不加注释 =2 加注释
  • –defaults-extra-file 加载指定配置文件和所有默认需要加载配置文件
  • –defaults-file 加载指定配置文件
  • –flush-logs 刷新binlog日志，增量备份必要条件
#!/bin/bash

#用户、密码写在配置文件中所以此处不配置
#db_user='root'
#db_password=`cat /data/www/mysql_password`
db_name='数据库名称'
backup_dir='/data/backup/mysql/'
current_time=$(date +'%Y-%m-%d_%H:%M:%S')
filepath=$backup_dir$current_time'.sql.gz'
echo '开始导出数据库...'
# 下面的意思是 加载/data/backup/my_mysql.cnf及默认配置文件 导出函数、存储过程、事件，刷新binlog
#，binlog加注释同时全局锁表，根据自身实际情况进行配置
mysqldump  --defaults-extra-file=/data/backup/my_mysql.cnf -R -E --flush-logs --master-data=2 $db_name | gzip > $filepath
echo '导出成功，文件名为: '$filepath

chmod +x ./mysql_backup.sh

3.新建配置文件

vim my_mysql.cnf

#mysql 配置导入参数
[mysql]
host=127.0.0.1
user=root
password='root'
#mysqldump 配置导出参数
[mysqldump]
#允许导出的文件大小上限
#max_allowed_packet    = 400M
host=127.0.0.1
user=root
password='root'

4.开启binlog

查找my.cnf
find / -name my.cnf
vim /etc/my.cnf
加入
server_id = 2
log_bin = mysql-bin
重启mysql服务
systemctl restart mysqld
查看是否开启
show variables like ‘log_bin%’;

5.导出测试

sh ./mysql_backup.sh
ll ./mysql

6.创建导入脚本文件

cd /data/backup
vim mysql_restore.sh

#!/bin/bash
if [ -z $1 ] || [ ! -f $1 ]
then    
	echo "请输入sql压缩文件(*.sql.gz)"    
	exit 1
fi

db_name='test_backup'
base_dir='/data/backup/mysql/'
gz_sql_file=`basename $1`
file_ext=${gz_sql_file##*.}
if [ $file_ext != 'gz' ]
then    
	echo '文件格式不正确，请输入 .sql.gz 文件'    
	exit 1
fi
sql_file=${gz_sql_file%.*}
echo '解压文件中...'
gzip -dc $base_dir$gz_sql_file > $base_dir$sql_file
echo '解压完成.'
echo '开始导入数据库...'
mysql --defaults-extra-file=/data/backup/my_mysql.cnf $db_name < $base_dir$sql_file

if [ -f $base_dir$sql_file ]
then    
	echo '删除临时文件.'    
	rm -f $base_dir$sql_file
fi
echo '导入完成.'

chmod +x ./mysql_restore.sh

7.导入测试

sh ./mysql_restore.sh ./mysql/2020-06-04_091700.sql.gz
出现This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA
set global log_bin_trust_function_creators=TRUE;

8.创建增量备份脚本文件

cd /data/backup
vim mysql_increment_backup.sh
注意下面用到的文件和目录不能为空

#!/bin/bash
backupDir=/data/backup/increment_backup
#备份时复制mysql-bin.00000*的目标目录
mysqlDir=/var/lib/mysql
#mysql的数据目录
logFile=/data/backup/log_file/backup.log
BinFile=/var/lib/mysql/mysql-bin.index
#mysql的index文件路径，放在数据目录下的
mysqladmin -uroot -proot  flush-logs
#这个是用于产生新的mysql-bin.00000*文件
# wc -l 统计行数
# awk 简单来说awk就是把文件逐行的读入，以空格为默认分隔符将每行切片，切开的部分再进行各种分析处理。
Counter=`wc -l $BinFile |awk '{print $1}'`
NextNum=0
#这个for循环用于比对$Counter,$NextNum这两个值来确定文件是不是存在或最新的
for file in `cat $BinFile`
do   
	base=`basename $file`   
	echo $base   
	#basename用于截取mysql-bin.00000*文件名，去掉./mysql-bin.000005前面的./   
	NextNum=`expr $NextNum + 1`   
	if [ $NextNum -eq $Counter ]   
	then       
		echo $base skip! >> $logFile   
	else       
		dest=$backupDir/$base       
		if(test -e $dest)       
		#test -e用于检测目标文件是否存在，存在就写exist!到$logFile去       
		then           
			echo $base exist! >> $logFile       
		else           
			cp $mysqlDir/$base $backupDir           
			echo $base copying >> $logFile        
		fi    
	fi
done
echo `date +"%Y年%m月%d日 %H:%M:%S"` $Next Bakup success! >> $logFile

chmod +x ./mysql_increment_backup.sh

9.创建清理备份脚本

vim remove_backup.sh

#/bin/bash

# 删除30天前的全量备份
find /data/backup/mysql -type f -mtime +30 | xargs rm -f

chmod +x ./remove_backup.sh

10.定时任务介绍

crontab 语法说明：

  • -e 编辑内容
  • -l 查看内容

定时配置格式为 ******
代表任何时间段，5个*，从左至右分别是：

  • 分 取值范围0-59
  • 时 取值范围0-23
  • 天 取值范围1-31
  • 年 取值范围1-12
  • 周 取值范围0-6

/sbin/service crond start //启动服务
/sbin/service crond stop //关闭服务
/sbin/service crond restart //重启服务
/sbin/service crond reload //重新载入配置
查看crontab服务状态：service crond status
启动crontab服务：service crond start
停止crontab服务：service crond stop
查看crontab服务是否已设置为开机启动，执行命令：ntsysv
加入开机自动启动:chkconfig crond on

11.配置定时任务

crontab -e

#每周日凌晨1点进行全量备份
0 1 * * 0  /data/backup/mysql_backup.sh
#每天凌晨2点进行增量备份
0 2 * * *  /data/backup/mysql_increment_backup.sh
# 每天凌晨3点，自动删除30天前的备份
0 3 * * * /data/backup/remove_backup.sh

service crond start
ntsysv
chkconfig crond on