Mysql备份与还原——mysqldump结合binlog

MySQL 备份一般采取全库备份加日志备份的方式,例如每天执行一次全备份,每小时执行一次二进制日志备份。这样在 MySQL 故障后可以使用全备份和日志备份将数据恢复到最后一个二进制日志备份前的任意位置或时间。

一、mysqldump 备份结合 binlog 日志恢复

1、binlog介绍

mysql 的二进制日志记录着该数据库的所有增删改的操作日志,还包括了这些操作的执行时间。我们可以使用mysqlbinlog 命令来查看其内容。

(1)binlog用途:

主从同步,恢复数据库

(2)开启binlog

通过编辑 my.cnf 中的 log_bin 选项可以开启二进制日志,每次重启 mysql 服务或运行mysql> flush logs;都会生成一个新的二进制日志文件,这些日志文件的 number 会不断地递增。除了生成上述的文件外还会生成一个名为filename.index 的文件。这个文件中存储所有二进制日志文件的清单又称为二进制文件的索引。

用 mysql> show variables like 'log_bin';查看 bin-log 是否开启,如图:

(3)mysql 提供两种方式查看binlog方式,我们先对数据库进行一下增删改的操作,否则 log 里边数据有点空。

查看 MySQL Server 上的二进制日志:

(4)查看二进制日志信息的命令:

语法格式:SHOW BINLOG EVENTS[IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]

(5)查看二进制日志中的事件

mysql> show binlog events;

默认显示可找到的第一个二进制日志文件中的事件,包含了日志文件名、事件的开始位置、事件类型、结束位置、信息等内容。

查看指定的二进制日志中的事件:

mysql> show binlog events in 'mysql-bin.000002';

该命令还包含其他选项以便灵活查看

mysql> show binlog events in 'mysql-bin.000002' from 219 limit 1,3; // limit 1,1 为偏移量

2、mysqlbinlog工具

语法格式: mysqlbinlog [options] log_file ...

为了便于查看记录了行变化信息的事件在当时具体执行了什么样的 SQL 语句可以使用mysqlbinlog 工具的-v(--verbose)选项,该选项会将行事件重构成被注释掉的伪 SQL 语句,如果想看到更详细的信息可以将该选项给两次如-vv,这样可以包含一些数据类型和元信息的注释内容,如

先切换到 binlog 所在的目录下

#mysqlbinlog mysql-bin.000001

#mysqlbinlog -v mysql-bin.000001

#mysqlbinlog -vv mysql-bin.000001

mysqlbinlog 选项示例:

--start-datetime

从二进制日志中读取指定时间戳或者本地计算机时间之后的日志事件。

--stop-datetime

从二进制日志中读取指定时间戳或者本地计算机时间之前的日志事件。

--start-position

从二进制日志中读取指定 position 事件位置作为开始。

--stop-position

从二进制日志中读取指定 position 事件位置作为事件截至。

3、使用 binlog 恢复之前删除数据(id=2 那条记录)

注:在实际生产环境中,如果遇到需要恢复数据库的情况,不要让用户能访问到数据库,以避免新的数据插入进来,以及在主从的环境下,关闭主从。

(1)查看 binlog 文件,从中找出 delete from test.tb1 where id=2

# cd /usr/local/mysql/data/

# mysqlbinlog -v mysql-bin.000002

显示结果如下:

从中可以看出 delete 事件发生 position 是 287,事件结束 position 是 416

恢复流程:直接用 bin-log 日志将数据库恢复到删除位置 287 前,然后跳过故障点,再进行恢复下面所有的操作。

(2)由于之前没有做过全库备份,所以要使用所有 binlog 日志恢复,所以生产环境中需要很长时间恢复,导出相关 binlog 文件

#mysqlbinlog /usr/local/mysql/data/mysql-bin.000001 > /opt/mysql-bin.000001.sql

#mysqlbinlog --stop-position=287 /usr/local/mysql/data/mysql-bin.000002 > /opt/287.sql

#mysqlbinlog --start-position=416 /usr/local/mysql/data/mysql-bin.000002 > /opt/416.sql

删除 test 数据库:

mysql>drop database test;

利用 binlog 恢复数据:

恢复完成后,我们检查下表的数据是否完整:

4、mysqldump介绍

mysqldump 是 mysql 用于备份和数据转移的一个工具。它主要产生一系列的 SQL 语句,可以封装到文件,该文件包含有所有重建你的数据库所需要的 SQL 命令如 CREATE DATABASE,CREATE TABLE,INSERT 等等。可以用来实现轻量级的快速迁移或恢复数据库。

(1)数据库的导出

导出数据库 test

# mysqldump -uroot -p --flush-logs test > /opt/test.sql //--flush-logs 这个选项就会完整备份的时候重新开启一个新 binlog

数据库的导入

# mysql -uroot -p test < /opt/test.sql

以上就是 mysql 的 binlog 和 mysqldump 工具,下面我们开始实现 mysqldump全库备份+binlog 的数据恢复

检查开启 binlog,先创建一些原始数据

1、创建备份目录

2、全库备份

3、备份 mysqldump 全库备份之前的 binlog 日志

4、模拟下操作失误,将数据修改错误了

5、备份自 mysqldump 之后的 binlog 日志文件

cp /usr/local/mysql/data/mysql-bin.000002 /opt/mysqlbackup/daily/

6、使用 mysqldump 的备份进行全库恢复

# mysql -uroot -p test_db < /opt/mysqlbackup/test_db_2016_09_12.sql

查询一下数据:

刚才删除的数据(id=2)恢复回来了,但备份后产生的数据却丢失了所以还得利用 binlog 进一步还原。因为删除是在全库备份后发生的,而 mysqldump 全库备份时使用--flush-logs 选项,所以只需要分析全库备份后的 binlog 即 mysql-bin.000002

7、查看 mysql-bin.000002 中的事件,可以看到有删除事件

恢复流程:我们直接用 bin-log 日志将数据库恢复到删除位置前,然后跳过故障点,再进行恢复删除后的所有操作

# mysqlbinlog -v /opt/mysqlbackup/daily/mysql-bin.000002

我们先用 mysqlbinlog 命令找到 delete 那条语句的位置

通过 mysqlbinlog 命令所显示的结果可以看到误操作 delete 的开始 postion 为 219,结束position 是 422。

8、从二进制日志中读取指定 position=219 事件位置作为截至,即把数据恢复到 delete 删除前

# mysqlbinlog --stop-position=219 /opt/mysqlbackup/daily/mysql-bin.000002 | mysql -u root -p

从二进制日志中读取指定 position=422 事件位置作为开始,即跳过删除事件,恢复删除事件之后对数据的正常操作

#mysqlbinlog --start-position=422 /opt/mysqlbackup/daily/mysql-bin.000002 | mysql -u root -p

9、查看恢复结果

从上面显示可以看出数据恢复到正常状态

注:生产环境中 Mysql 数据库的备份是周期性重复的操作,所以通常是要编写脚本实现,通过crond 计划任务周期性执行备份脚本(以下脚本仅供参考)

mysqldump 备份方案:

周日凌晨 1 点全库备份

周一到周六凌晨每隔 4 个小时增量备份一次

设置 crontab 任务,每天执行备份脚本

# crontab –e

#每个星期日凌晨 1:00 执行完全备份脚本

0 1 * * 0 /root/mysqlfullbackup.sh >/dev/null 2>&1

#周一到周六每隔 4 个小时增量备份一次

0 */4 * * 1-6 /root/mysqldailybackup.sh >/dev/null 2>&1

mysqlfullbackup.sh 脚本内容:

[root@localhost ~]# cat mysqlfullbackup.sh

#!/bin/sh

# Name:mysqlFullBackup.sh

# 定义数据库目录

mysqlDir=/usr/local/mysql

# 定义用于备份数据库的用户名和密码

user=root

userpwd=123456

dbname=test_db

# 定义备份目录

databackupdir=/opt/mysqlbackup

[ ! -d $databackupdir ] && mkdir $databackupdir

# 定义邮件正文文件

emailfile=$databackupdir/email.txt

# 定义邮件地址

email=root@localhost.localdomain

# 定义备份日志文件

logfile=$databackupdir/mysqlbackup.log

DATE=`date -I`

echo "" > $emailfile

echo $(date +"%y-%m-%d %H:%M:%S") >> $emailfile

cd $databackupdir

# 定义备份文件名

dumpfile=mysql_$DATE.sql

gzdumpfile=mysql_$DATE.sql.tar.gz

# 使用 mysqldump 备份数据库,请根据具体情况设置参数

$mysqlDir/bin/mysqldump -u$user -p$userpwd --flush-logs -x $dbname > $dumpfile //-x--lock-all-tables

# 压缩备份文件

if [ $? -eq 0 ]; then

tar czf $gzdumpfile $dumpfile >> $emailfile 2>&1

echo "BackupFileName:$gzdumpfile" >> $emailfile

echo "DataBase Backup Success!" >> $emailfile

rm -f $dumpfile

else

echo "DataBase Backup Fail!" >> $emailfile

fi

# 写日志文件

echo "--------------------------------------------------------" >> $logfile

cat $emailfile >> $logfile

# 发送邮件通知

cat $emailfile | mail -s "MySQL Backup" $email

mysqldailybackup.sh 脚本内容:

[root@localhost ~]# cat mysqldailybackup.sh

#!/bin/sh

# Name:mysqlDailyBackup.sh

# 定义数据库目录和数据目录

mysqldir=/usr/local/mysql

datadir=$mysqldir/data

# 定义用于备份数据库的用户名和密码

user=root

userpwd=123456

# 定义备份目录,每日备份文件备份到$dataBackupDir/daily

databackupdir=/opt/mysqlbackup

dailybackupdir=$databackupdir/daily

[ ! -d $dailybackupdir ] && mkdir -p $databackupdir/daily

# 定义邮件正文文件

emailfile=$databackupdir/email.txt

# 定义邮件地址

email=root@localhost.localdomain

# 定义日志文件

logfile=$databackupdir/mysqlbackup.log

echo "" > $emailfile

echo $(date +"%y-%m-%d %H:%M:%S") >> $emailfile

#

# 刷新日志,使数据库使用新的二进制日志文件

$mysqldir/bin/mysqladmin -u$user -p$userpwd flush-logs

cd $datadir

# 得到二进制日志列表

filelist=`cat mysql-bin.index`

icounter=0

for file in $filelist

do

icounter=`expr $icounter + 1` // = let icounter++

done

nextnum=0

ifile=0

for file in $filelist

do

binlogname=`basename $file`

nextnum=`expr $nextnum + 1`

# 跳过最后一个二进制日志(数据库当前使用的二进制日志文件)

if [ $nextnum -eq $icounter ]; then

echo "Skip lastest!" > /dev/null

else

dest=$dailybackupdir/$binlogname

# 跳过已经备份的二进制日志文件

if [ -e $dest ]; then

echo "Skip exist $binlogname!" > /dev/null

else

# 备份日志文件到备份目录

cp $binlogname $dailybackupdir

if [ $? -eq 0 ]; then

ifile=`expr $ifile + 1`

echo "$binlogname backup success!" >> $emailfile

fi

fi

fi

done

if [ $ifile -eq 0 ];then

echo "No Binlog Backup!" >> $emailfile

else

echo "Backup $ifile File(s)." >> $emailfile

echo "Backup MySQL Binlog OK!" >> $emailfile

fi

# 发送邮件通知

cat $emailfile | mail -s "MySQL Backup" $email

# 写日志文件

echo "--------------------------------------------------------" >> $logfile

cat $emailfile >> $logfile

原文发布于微信公众号 - L宝宝聊IT(gh_b0e552aa80db)

原文发表时间:2018-08-18

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏pangguoming

mysql主从配置(清晰的思路)

例如:主数据库里的a的数据库里有b,c,d表,那从数据库里的就应该有一个模子刻出来的a的数据库和b,c,d表

18020
来自专栏IT笔记

Lepus搭建企业级数据库慢查询分析平台

Lepus的慢查询分析平台是独立于监控系统的模块,该功能需要使用percona-toolkit工具来采集和记录慢查询日志,并且需要部署一个我们提供的shell脚...

15820
来自专栏zhangdd.com

zabbix监控windows agent安装配置

下载地址: https://www.zabbix.com/download_agents 选择windows版本的agent下载

19030
来自专栏Samego开发资源

配置MySQL主从复制

30670
来自专栏互联网大杂烩

海量数据解决方案

缓存:将从数据库中获取的结果暂时保存起来,在下次使用时无需重新到数据库中获取。 页面静态化:将程序最后生成的页面保存起来。

14130
来自专栏转载gongluck的CSDN博客

linux平台下的写文件刷新

c语言libc库自带的fflush和linux的sync、fsync、fdatasync,字面上都是刷新缓冲区数据到磁盘(当然,fflush还可以刷新缓冲区数据...

39750
来自专栏雨过天晴

原 数据库主从配置日志

22040
来自专栏DeveWork

WordPress自动在uploads文件夹内创建子文件夹

在开发主题或者插件的时候,经常要创建一个自定义的文件夹,我经常使用的一种方法就是在插件或主题安装的时候,先让程序自动到某个文件夹下先检测这个文件夹是否存在,如果...

19650
来自专栏Web 开发

服务迁移导致数据差异

通常我在dnspod里面,设置的解析缓存时间很短,基本上我修改DNS,是马上生效的。但毕竟用户的DNS不在我们的控制范围,总会有用户解析到旧的服务器上面,杯具就...

11300
来自专栏深度学习之tensorflow实战篇

Centos7.4 版本环境下安装Mysql5.7操作记录

MariaDB数据库管理系统是MySQL的一个分支,主要由开源社区在维护,采用GPL授权许可。开发这个分支的原因之一是:甲骨文公司收购了MySQL后,有将MyS...

37190

扫码关注云+社区

领取腾讯云代金券