内容简介:在产品上线之后,我们的数据是相当重要的,容不得半点闪失,应该做好万全的准备,搞不好哪一天被黑客入侵或者恶意删除,那就 gg 了。所以要对我们的线上数据库定时做全量备份与增量备份。例如:每天做一次增量备份,每周做一次全量备份。GitHub 地址:点击阅读原文进入首先在进行增量备份之前需要查看一下配置文件,查看 log_bin 是否开启,因为要做增量备份首先要开启 log_bin 。首先,进入到 myslq 命令行,输入如下命令:
-
引言
-
增量备份
-
恢复增量备份
-
定时备份
引言
在产品上线之后,我们的数据是相当重要的,容不得半点闪失,应该做好万全的准备,搞不好哪一天被黑客入侵或者恶意删除,那就 gg 了。所以要对我们的线上数据库定时做全量备份与增量备份。例如:每天做一次增量备份,每周做一次全量备份。
GitHub 地址:点击阅读原文进入
https://github.com/zonezoen/MySQL_backup 复制代码
增量备份
首先在进行增量备份之前需要查看一下配置文件,查看 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 的介绍,在上一篇推文中就有了,详情请看上一篇推文
以上就是本文的全部内容,希望本文的内容对大家的学习或者工作能带来一定的帮助,也希望大家多多支持 码农网
猜你喜欢:- PostgreSQL基础备份_增量备份与任意点恢复
- Postgresql备份与增量恢复
- 浅谈使用 Binlog 实现 MySQL 增量备份
- xtrabackup全量、增量备份恢复mysql数据库
- percona-xtrabackup实现数据库完全,增量的备份和还原(含一些版本问题与坑)
- 细说HTTP增量更新
本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们。