mysql增量、全量、私有云脚本备份实战

概要

  • 引言

  • 增量备份

  • 恢复增量备份

  • 定时备份

引言

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

增量备份

首先在进行增量备份之前需要查看一下配置文件，查看 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

定时备份

输入如下命令，进入定时任务编辑界面：

1 crontab -e

 

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

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

 

 

 

概要

  • 引言

  • 全量备份

  • 恢复全量备份

  • 定时备份

 

引言

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

全量备份

/usr/bin/mysqldump -uroot -p123456  --lock-all-tables --flush-logs test > /home/backup.sql

如上一段代码所示，其功能是将 test 数据库全量备份。其中 MySQL 用户名为：root 密码为：123456备份的文件路径为：/home （当然这个路径也是可以按照个人意愿修改的。）备份的文件名为：backup.sql
参数 —flush-logs：使用一个新的日志文件来记录接下来的日志参数 —lock-all-tables：锁定所有数据库

以下为我使用的数据库备份脚本文件：

脚本文件功能不是很复杂，首先是各种变量赋值。然后备份数据库，接着是进入到备份文件所在的目录，再将备份文件压缩。其中倒数第三行是使用 nodejs 将备份的数据库文件上传到七牛云中，这里就不在过多的阐述了，与本文主题不符，想看具体实现可以查看 GitHub 源码。其中相应的变量改成自己的值即可拿过来使用。

#!/bin/bash
#在使用之前，请提前创建以下各个目录
#获取当前时间
date_now=$(date "+%Y%m%d-%H%M%S")
backUpFolder=/home/db/backup/mysql
username="root"
password="123456"
db_name="zone"
#定义备份文件名
fileName="${db_name}_${date_now}.sql"
#定义备份文件目录
backUpFileName="${backUpFolder}/${fileName}"
echo "starting backup mysql ${db_name} at ${date_now}."
/usr/bin/mysqldump -u${username} -p${password}  --lock-all-tables --flush-logs ${db_name} > ${backUpFileName}
#进入到备份文件目录
cd ${backUpFolder}
#压缩备份文件
tar zcvf ${fileName}.tar.gz ${fileName}

# use nodejs to upload backup file other place
#NODE_ENV=$backUpFolder@$backUpFileName node /home/tasks/upload.js
date_end=$(date "+%Y%m%d-%H%M%S")
echo "finish backup mysql database ${db_name} at ${date_end}."

恢复全量备份

mysql -h localhost -uroot -p123456 < bakdup.sql

或者

mysql> source /path/backup/bakdup.sql

嗯，回复全量备份也就这样两句，似乎不用多说什么了。对了，在恢复全量备份之后，要将全量备份之后的增量备份也恢复回数据库中。

定时备份

输入如下命令，进入 crontab 定时任务编辑界面：

crontab -e

添加如下命令，其意思为：每分钟执行一次备份脚本：

* * * * * sh /usr/your/path/mysqlBackup.sh

每五分钟执行 ：

*/5 * * * * sh /usr/your/path/mysqlBackup.sh

每小时执行：

0 * * * * sh /usr/your/path/mysqlBackup.sh

每天执行：

0 0 * * * sh /usr/your/path/mysqlBackup.sh

每周执行：

0 0 * * 0 sh /usr/your/path/mysqlBackup.sh

每月执行：

0 0 1 * * sh /usr/your/path/mysqlBackup.sh

每年执行:

0 0 1 1 * sh /usr/your/path/mysqlBackup.sh

关于 crontab 命令的格式说明如下：

 

 

 

增量备份

首先在进行增量备份之前需要查看一下配置文件，查看 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 

[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 命令的日志。上句代码的效果如下：

 

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

恢复增量备份

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

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


实战-将MySQL备份上传到私有云

概要

  • 备份到私有云 - python

  • 备份到私有云 - nodejs

  • 备份到 git 私有仓库

引言

前两篇文章聊了 MySQL 的全量备份与增量备份，我们也学会了数据库的备份与恢复。这也增强了我们数据库的安全性。假如，我们整台数据库服务器被劫持了，或者遇到了其他毁灭性的灾难，即使备份了也没个毛用，因为备份文件也在同一台服务器啊。那么我们就会想如何备份的同时，上传到其他私有云，这样我们就不用担心了。于是便有了本文。

 

回顾

OK，学习了上两篇文章，我们已经可以轻松地备份数据库，也得到了以下脚本文件。而在下面脚本的最后几行，我添加了几行将备份文件上传到私有云的代码。这里我注释掉了，如要使用请取消注释。OK，现在让我们一起打怪升级。

#!/bin/bash
#获取当前时间
date_now=$(date "+%Y%m%d-%H%M%S")
backUpFolder=/home/db/backup/mysql
username="root"
password="123456"
db_name="zone"
#定义备份文件名
fileName="${db_name}_${date_now}.sql"
#定义备份文件目录
backUpFileName="${backUpFolder}/${fileName}"
echo "starting backup mysql ${db_name} at ${date_now}."
/usr/bin/mysqldump -u${username} -p${password}  --lock-all-tables --flush-logs ${db_name} > ${backUpFileName}
#进入到备份文件目录
cd ${backUpFolder}
#压缩备份文件
tar zcvf ${fileName}.tar.gz ${fileName}

# use nodejs to upload backup file other place
#NODE_ENV=$backUpFolder@$backUpFileName node /home/tasks/upload.js
date_end=$(date "+%Y%m%d-%H%M%S")
echo "finish backup mysql database ${db_name} at ${date_end}."
# 使用 nodejs 上传备份文件到 私有云
#NODE_ENV=$backUpFolder@$backUpFileName /root/node/v8.11.3/bin/node /usr/local/upload.js

# 使用 python 上传备份文件到 私有云
#python /use/local/upload.py $backUpFolder $backUpFileName
# 使用 python 上传备份文件到 私有云
#python /use/local/upload.py $backUpFolder $backUpFileName


$backUpFolder #备份文件的所在目录
$backUpFileName #备份文件名


这两个参数为传给 upload.py 的参数。




# 使用 nodejs 上传备份文件到 私有云
#/root/node/v8.11.3/bin/node /usr/local/upload.js $backUpFolder $backUpFileName


这两个参数为传给 upload.js 的参数。


备份到私有云 - python


这里的私有云使用的是七牛云，使用其他私有云请自行替换。以下脚本中，替换你自己的 access_key、secret_key、bucket_name（存储仓库名）  即可使用。


from qiniu import Auth, put_file, etag
import sys
print('参数个数为:', len(sys.argv), '个参数。')
print('参数列表:', str(sys.argv))

# backUpFolder,获取备份文件的目录
backUpFolder = sys.argv[1]
# backUpFileName，获取备份文件的文件名
backUpFileName = sys.argv[2]
import qiniu.config

# 需要填写你的 Access Key 和 Secret Key
access_key = 'your_key'
secret_key = 'your_key'
# 构建鉴权对象
q = Auth(access_key, secret_key)
# 要上传的空间
bucket_name = 'test'
# 上传到七牛后保存的文件名
key = backUpFileName
# 生成上传 Token，可以指定过期时间等
token = q.upload_token(bucket_name, key, 3600)
# 要上传文件的本地路径
localfile = backUpFolder + backUpFileName
ret, info = put_file(token, key, localfile)
print(info)
assert ret['key'] == key
assert ret['hash'] == etag(localfile)


 


备份到私有云 - nodejs


这里的私有云使用的是七牛云，使用其他私有云请自行替换。以下脚本中，替换你自己的 access_key、secret_key、bucket（存储仓库名）  即可使用。




let qiniu = require("qiniu");
let arguments = process.argv.splice(2);
console.log(process.argv)
console.log('所传递的参数是：', arguments[0]);
console.log('所传递的参数是：', arguments[1]);
let file = arguments[1];
let filePath = arguments[0] + '/' + file;
console.log(filePath);
bucket = "test"//你的存储空间名

//需要填写你的 Access Key 和 Secret Key
let accessKey = 'access_key';
let secretKey = 'secret_key';
let config = new qiniu.conf.Config();
// 空间对应的机房，选择机房得看七牛的文档：https://developer.qiniu.com/kodo/sdk/1289/nodejs#form-upload-file
config.zone = qiniu.zone.Zone_z0;
let mac = new qiniu.auth.digest.Mac(accessKey, secretKey);
let options = {
   scope: bucket,
};
let putPolicy = new qiniu.rs.PutPolicy(options);
let uploadToken=putPolicy.uploadToken(mac);
let localFile = filePath;
let formUploader = new qiniu.form_up.FormUploader(config);
let putExtra = new qiniu.form_up.PutExtra();
let key = file;
// 文件上传
formUploader.putFile(uploadToken, key, localFile, putExtra, function (respErr, respBody, respInfo) {
   if (respErr) {
       throw respErr;
   }
   if (respInfo.statusCode == 200) {
       console.log(respBody);
   } else {
       console.log(respInfo.statusCode);
       console.log(respBody);
   }
});


 


上图为我本地运行脚本文件上传 README.md 的截图。上传成功。


备份到 git 私有仓库


github 中的私有仓库需要付费，可以选择码云来作为私有仓库，其私有仓库的免费的。




#!/bin/bash
#获取当前时间
date_now=$(date "+%Y%m%d-%H%M%S")
backUpFolder=/home/db/backup/mysql
username="root"
password="123456"
db_name="zone"
#定义备份文件名
fileName="${db_name}_${date_now}.sql"
#定义备份文件目录
backUpFileName="${backUpFolder}/${fileName}"
echo "starting backup mysql ${db_name} at ${date_now}."
/usr/bin/mysqldump -u${username} -p${password}  --lock-all-tables --flush-logs ${db_name} > ${backUpFileName}
#进入到备份文件目录
cd ${backUpFolder}
#压缩备份文件
tar zcvf ${fileName}.tar.gz ${fileName}

# use nodejs to upload backup file other place
#NODE_ENV=$backUpFolder@$backUpFileName node /home/tasks/upload.js
date_end=$(date "+%Y%m%d-%H%M%S")
echo "finish backup mysql database ${db_name} at ${date_end}."

git add .
git commit -m 'commit by script'
git push





原创 zone








		

		
posted @ 
2020-11-18 15:18 
王牌男人 
阅读(106) 
评论(0) 
编辑 
收藏 
举报