linux下mysql5.7.19数据库备份(全量备份、增量备份)
前言
因为项目需要,要在linux系统下进行数据库备份,包含全量备份和增量备份。
全量备份使用的mysqldump,增量备份使用的是Mysql Binlog.
1.修改/etc/my.cnf文件
1.1 在linux系统下找到my.cnf并添加以下内容:
[mysqld]
server-id = 1
log-bin = /var/lib/mysql/mysql-bin
expire_logs_days = 7
**注意**:
(1)log-bin注意这里的是"-",而不是"_"
(2)设置日志过期时间(expire_logs_days)为7天(重要),每7天自动删除过期日志
1.2 修改my.cnf之后,要重启mysql
查看mysql是否启动binlog:
mysql> show variables like 'log_bin%';
+---------------------------------+--------------------------------+
| Variable_name | Value |
+---------------------------------+--------------------------------+
| log_bin | ON |
| log_bin_basename | /var/lib/mysql/mysql-bin |
| log_bin_index | /var/lib/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
+---------------------------------+--------------------------------+
5 rows in set
1.3清空并重置日志
清空mysql日志,并重置mysql日志:
mysql> reset master;
Query OK, 0 rows affected
2.在mysql安装目录下,创建备份文件夹backup
backup文件夹里包含:daily文件夹(用于记录每日增量备份)
mkdir -p /var/lib/mysql/backup/daily
3.编写每日增量备份脚本及全量备份脚本
3.1创建增量备份脚本
cd /var/lib/mysql
vi dayBak.sh
#!/bin/bash
# Program
# use cp to backup mysql data everyday!
# 2017-8-28 huangwei
# History
# Path
#BakDir是增量备份时复制mysql-bin.00000*的目标目录,提前手动创建这个目录
BakDir=/var/lib/mysql/backup/daily
BinDir=/var/lib/mysql
LogFile=/var/lib/mysql/backup/dayBakLog.log
BinFile=/var/lib/mysql/mysql-bin.index
mysqladmin -uroot -proot flush-logs
#这个是用于产生新的mysql-bin.00000*文件
Counter=`wc -l $BinFile |awk '{print $1}'`
NextNum=0
#这个for循环用于比对$Counter,$NextNum这两个值来确定文件是不是存在或最新的
for file in `cat $BinFile`
do
base=`basename $file`
#basename用于截取mysql-bin.00000*文件名,去掉./mysql-bin.000005前面的./
NextNum=`expr $NextNum + 1`
if [ $NextNum -eq $Counter ]
then
echo $base skip! >> $LogFile
else
dest=$BakDir/$base
if(test -e $dest)
#test -e用于检测目标文件是否存在,存在就写exist!到$LogFile去
then
echo $base exist! >> $LogFile
else
cp $BinDir/$base $BakDir
echo $base copying >> $LogFile
find . -type f -ctime +6 -exec rm {} \; #删除6天后的日志文件
fi
fi
done
echo `date +"%Y年%m月%d日 %H:%M:%S"` $Next daily Bakup succ! >> $LogFile
3.2赋予增量脚本dayBak.sh执行权限
chmod a+x /var/lib/mysql/dayBak.sh
3.3全量备份脚本fullBak.sh
vi databak.sh
#!/bin/bash
# Program
# use mysqldump to Fully backup mysql data per week!
# 2017-8-28 huangwei
# History
# Path
BakDir=/var/lib/mysql/backup
LogFile=/var/lib/mysql/backup/fullBakLog.log
Date=`date +%Y%m%d`
Begin=`date +"%Y年%m月%d日 %H:%M:%S"`
cd $BakDir
DumpFile=$Date.sql
GZDumpFile=$Date.sql.tgz
mysqldump -uroot -proot szsjrjg > $DumpFile
/bin/tar -zvcf $GZDumpFile $DumpFile
/bin/rm $DumpFile
#只保留过去四周的数据库内容
count=$(ls -l *.tgz |wc -l)
if [ $count -ge 5 ]
then
file=$(ls -l *.tgz |awk '{print $9}'|awk 'NR==1')
rm -f $file
fi
#只保留过去四周的数据库内容
Last=`date +"%Y年%m月%d日 %H:%M:%S"`
echo 开始:$Begin 结束:$Last $GZDumpFile succ >> $LogFile
cd $BakDir/daily
/bin/rm -f *
3.4赋予增量脚本fullBak.sh执行权限
chmod a+x /var/lib/mysql/fullBak.sh
4.设置定时任务 /etc/crontab
vi /etc/crontab
#Monday to Sunday 0:00 make Daily Log 每天0点执行增量备份
0 0 * * * /var/lib/mysql/dayBak.sh >/dev/null 2>&1
#Sunday 0:00 make Full Log 每周星期日0点执行全量备份
0 0 * * sun /var/lib/mysql/fullBak.sh >/dev/null 2>&1
执行定时任务
crontab /etc/crontab
5.查看定时任务
crontab -l