mysql在centos环境下全量备份和增量备份_mysql增量备份命令

一、开启binlog
1、检查binlog是否开启，进入mysql

mysql -uroot -p
1

2、查看binlog状态，ON为开启，如果为关闭则参考3 - 4步骤；

show variables like '%log_bin%';
1

3、进入mysql配置文件编辑模式

vim /etc/my.cnf
1

在[mysqld]下面加入，并保存（不加server_id会报错，server_id的值可以随便设。）

[mysqld]
log_bin=/data/mysql/backup/incr
server_id=166
1
2
3

4、重启mysql

service mysqld restart
1

二、自动备份脚本
1、将 full_backup.sh(全量备份) 和 incr_backup.sh(增量备份) 脚本上传至指定路径

full_backup.sh(全量备份) 代码

#!/bin/bash
# MySQL全量备份，本脚本备份所有库
# **********
# 用户名、密码
user="root"
password="password"
# 备份开始时间
beginTime=`date +"%Y年%m月%d日 %H:%M:%S"`
# 备份结束时间
endTime=`date +"%Y年%m月%d日 %H:%M:%S"`
# 全量备份目录，提前手动创建这个目录
bakDir=/data/mysql/backup/full
# 日志文件
logFile=/data/mysql/backup/log/full.log
# 备份时间
nowDate=`date +"%Y%m%d_%H%M%S"`
dumpFile="alldb_${nowDate}.sql"
gzDumpFile="alldb_${nowDate}.sql.tgz"

cd $bakDir
echo 全量备份开始:$beginTime >> $logFile
# 全量备份（--databases对指定数据库备份，--all-databases对所有数据库备份）
mysqldump --all-databases -u${user} -p${password} -A -E -q --flush-privileges --single-transaction > $dumpFile

# 判断备份是否成功
if [ $? -eq 0 ]; then
  # 产生新的mysql-bin
  echo 刷新增量备份节点  
  sh /root/incr_backup.sh
  # 将备份SQL打包
  /bin/tar -zvcf $gzDumpFile $dumpFile
  /bin/rm $dumpFile
  echo 全量备份结束:$endTime $gzDumpFile ! SUCCESS ! >> $logFile
  exit 0;
else
  echo 全量备份结束:$endTime $gzDumpFile ! ERROR ! >> $logFile
  exit 1;    
fi

#删除60天前的备份
find $bakDir -name "*.sql.tgz" -type f -mtime +60 -exec rm -rf {} \; > /dev/null 2>&1
exit 0;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

incr_backup.sh(增量备份) 代码

#!/bin/bash
# **********
# 用户名、密码
user="root"
password="password"
# **********
# 在使用之前，请提前创建以下各个目录
backupDir=/data/mysql/backup/incr
# 增量备份时复制mysql-bin.00000*的目标目录，提前手动创建这个目录
mysqlDir=/usr/local/mysql
# 设置mysql增量备份的日志文件保存路径，没这个文件夹需要主动新建
logFile=/data/mysql/backup/log/incr.log
#mysql的index文件路径，放在数据目录下的
BinFile=/usr/local/mysql/mysql-bin.index

# 产生新的mysql-bin.00000*文件
mysqladmin -u${user} -p${password} flush-logs

# 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 succ! >> $logFile

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46

2、创建备份路径文件夹，如：

/data/mysql/backup/full
/data/mysql/backup/incr
/data/mysql/backup/log

3、首先执行命令进行全量备份，完成全量备份脚本会刷新增量备份日志节点

./full_backup.sh
1

4、参考全量备份和增量备份文件

全量备份文件

增量备份文件

三、开启定时增量备份

输入以下命令

crontab -e
1

进入编辑模式，复制如下内容保存，可以先进行测试

# 先测试每分钟增量备份一次效果
# * */1 * * * sh /root/incr_backup.sh
# 在每天凌晨2点执行脚本
0 2 * * * /bin/bash -x /root/incr_backup.sh >/dev/null 2>&1
# 重启
systemctl restart crond
systemctl reload crond
1
2
3
4
5
6
7

四、恢复全量备份和增量备份数据

1、恢复全量备份

# 进入备份文件目录
cd /data/mysql/backup
# 解压最新的全量备份文件
tar -zxvf xxx.sql.tgz
# 执行要指定恢复的SQL路径
mysql -u root -p < /data/mysql/backup/full/alldb_xxx.sql
1
2
3
4
5
6

2、恢复增量备份

# 指定对应要恢复的mysql-bin文件，注意恢复要从早到晚的顺序
mysqlbinlog --no-defaults /var/lib/mysql/mysql-bin.000001 | mysql -uroot -p
1
2