文章目录
1. 全库备份2. 增量备份3. 开启定时任务1. 全库备份
vim all_db_bak.sh#!/bin/bash#MySQL 全库备份PATH="/usr/local/mysql/bin:$PATH"BAK1=/bak1BAK2=/bak2TIME=$(date +"%F:%H:%M:%S")USER=rootPASS=123456if [ ! -d $BAK1 -o ! -d $BAK2 ];thenmkdir $BAK1mkdir $BAK2mysqldump -u$USER -p$PASS --all-databases > /$BAK1/all_${TIME}.sqlcp $BAK1/all_${TIME}.sql $BAK2echo -e "\033[32mMySQL数据已全库备份到 ${BAK2} 目录\033[0m"elsemysqldump -u$USER -p$PASS --all-databases > /$BAK1/all_${TIME}.sqlcp $BAK1/all_${TIME}.sql $BAK2echo -e "\033[32mMySQL数据已全库备份到 ${BAK2} 目录\033[0m"fi
2. 增量备份
开启 mysql 的 logbin 日志功能[mysqld]log-bin = "/home/mysql/logbin.log"binlog-format = ROWlog-bin-index = "/home/mysql/logindex"binlog_cache_size=32mmax_binlog_cache_size=512mmax_binlog_size=512m
重启 mysql，其中路径 /home/mysql 的用户和组要改成 mysql
建立以下目录
mkdir -p /home/mysql/backup/daily
增量备份脚本
vim binlogbak.sh#!/bin/bashPATH="/usr/local/mysql/bin:$PATH"export LANG=en_US.UTF-8BakDir=/home/mysql/backup/dailyBinDir=/home/mysqlLogFile=/home/mysql/backup/binlog.logBinFile=/home/mysql/logindex.index#注意修改密码mysqladmin -uroot -p123456 flush-logs#这个是用于产生新的 mysql-bin.00000* 文件Counter=`wc -l $BinFile |awk '{print $1}'`NextNum=0#这个 for 循环用于比对 $Counter,$NextNum 这两个值来确定文件是不是存在或最新的for file in `cat $BinFile`dobase=`basename $file`#basename用于截取mysql-bin.00000*文件名，去掉./mysql-bin.000005前面的./NextNum=`expr $NextNum + 1`if [ $NextNum -eq $Counter ]thenecho $base skip! >> $LogFileelsedest=$BakDir/$baseif(test -e $dest)#test -e用于检测目标文件是否存在，存在就写exist!到$LogFilethenecho $base exist! >> $LogFileelsecp $BinDir/$base $BakDirecho $base copying >> $LogFilefifidoneecho `date +"%Y年%m月%d日 %H:%M:%S"` Bakup succ! >> $LogFile
3. 开启定时任务
vim /etc/crontab#每个星期日凌晨 3:00 执行完全备份脚本0 3 * * 0 /home/mysql/all_db_bak.sh >/dev/null 2>&1#周一到周六凌晨3:00做增量备份0 3 * * 1-6 /home/mysql/binlogbak.sh >/dev/null 2>&1crontab /etc/crontab #使上述定时任务生效crontab -l #查看定时任务
