mysql 定时增量备份

小明 1月前 ⋅ 244 阅读

概要

  • 引言

  • 增量备份

  • 恢复增量备份

  • 定时备份

引言

在产品上线之后,我们的数据是相当重要的,容不得半点闪失,应该做好万全的准备,搞不好哪一天被黑客入侵或者恶意删除,那就 gg 了。所以要对我们的线上数据库定时做全量备份与增量备份。例如:每天做一次增量备份,每周做一次全量备份。

GitHub 地址:点击阅读原文进入

https://github.com/zonezoen/MySQL_backup
  • 1

增量备份

首先在进行增量备份之前需要查看一下配置文件,查看 log_bin 是否开启,因为要做增量备份首先要开启 log_bin 。首先,进入到 myslq 命令行,输入如下命令:

show variables like '%log_bin%';

如下命令所示,则为未开启

mysql> show variables like '%log_bin%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin                         | OFF   |
| log_bin_basename                |       |
| log_bin_index                   |       |
| log_bin_trust_function_creators | OFF   |
| log_bin_use_v1_row_events       | OFF   |
| sql_log_bin                     | ON    |
+---------------------------------+-------+

修改 MySQL 配置项到如下代码段:vim /etc/mysql/mysql.conf.d/mysqld.cnf

# Copyright (c) 2014, 2016, Oracle and/or its affiliates. All rights reserved.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; version 2 of the License.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301 USA

#
# The MySQL  Server configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

[mysqld]
pid-file	= /var/run/mysqld/mysqld.pid
socket		= /var/run/mysqld/mysqld.sock
datadir		= /var/lib/mysql
#log-error	= /var/log/mysql/error.log
# By default we only accept connections from localhost
#bind-address	= 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

#binlog setting,开启增量备份的关键
log-bin=/var/lib/mysql/mysql-bin
server-id=123454

修改之后,重启 mysql 服务,输入:

show variables like '%log_bin%';

状态如下:

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                            |
| sql_log_bin                     | ON                             |
+---------------------------------+--------------------------------+

好了,做好了充足的准备,那我们就开始学习增量备份了。

查看当前使用的 mysql_bin.000*** 日志文件,

show master status;

状态如下:

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000015 |      610 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

当前正在记录日志的文件名为 mysql-bin.000015 。

当前数据库中有如下数据:

mysql> select * from users;
+-------+------+----+
| name  | sex  | id |
+-------+------+----+
| zone  | 0    |  1 |
| zone1 | 1    |  2 |
| zone2 | 0    |  3 |
+-------+------+----+

我们插入一条数据:

insert into `zone`.`users` ( `name`, `sex`, `id`) values ( 'zone3', '0', '4');

查看效果:

mysql> select * from users;
+-------+------+----+
| name  | sex  | id |
+-------+------+----+
| zone  | 0    |  1 |
| zone1 | 1    |  2 |
| zone2 | 0    |  3 |
| zone3 | 0    |  4 |
+-------+------+----+

我们执行如下命令,使用新的日志文件:

mysqladmin -uroot -123456 flush-logs

日志文件从 mysql-bin.000015 变为 mysql-bin.000016,而 mysql-bin.000015 则记录着刚刚 insert 命令的日志。上句代码的效果如下:

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000016 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

那么到现在为止,其实已经完成了增量备份了。

恢复增量备份

那么现在将刚刚插入的数据删除,效果如下:

delete from `zone`.`users` where `id`='4' 

mysql> select * from users;
+-------+------+----+
| name  | sex  | id |
+-------+------+----+
| zone  | 0    |  1 |
| zone1 | 1    |  2 |
| zone2 | 0    |  3 |
+-------+------+----+

那么现在就是重点时间了,从 mysql-bin.000015 中恢复数据:

mysqlbinlog /var/lib/mysql/mysql-bin.000015 | mysql -uroot -p123456 zone;

上一句代码指定了,需要恢复的 mysql_bin 文件,指定了用户名:root 、密码:123456 、数据库名:zone。效果如下:

mysql> select * from users;
+-------+------+----+
| name  | sex  | id |
+-------+------+----+
| zone  | 0    |  1 |
| zone1 | 1    |  2 |
| zone2 | 0    |  3 |
| zone3 | 0    |  4 |
+-------+------+----+

OK,整一个增量备份的操作流程都在这里了,那么我们如何将它写成脚本文件呢,代码如下:

#!/bin/bash
#在使用之前,请提前创建以下各个目录
backupDir=/usr/local/work/backup/daily
#增量备份时复制mysql-bin.00000*的目标目录,提前手动创建这个目录
mysqlDir=/var/lib/mysql
#mysql的数据目录
logFile=/usr/local/work/backup/bak.log
BinFile=/var/lib/mysql/mysql-bin.index
#mysql的index文件路径,放在数据目录下的

mysqladmin -uroot -p123456 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 succ! >> $logFile

#NODE_ENV=$backUpFolder@$backUpFileName /root/node/v8.11.3/bin/node /usr/local/upload.js

定时备份

输入如下命令,进入定时任务编辑界面:

crontab -e

添加如下命令,其意思为:每分钟执行一次备份脚本,crontab 的具体规则就另外写文了,与本文主题不太相关。

* * * * * sh /usr/your/path/mysqlbackup.sh

关于 crontab 的介绍,在上一篇推文中就有了,详情请看上一篇推文


全部评论: 0

    我有话说: