如何在mysql数据库的增量备份中创建 [英] How can i create in incremental backups of mysql databases

问题描述

我有带mysql5.6

我有cron作业，可以像这样每小时进行一次mysql数据库备份

I have the cron job for making hourly mysql database backups like this

在python中

os.popen("mysqldump -u %s --password=%s -h %s -e --opt --skip-lock-tables  --skip-extended-insert -c %s | gzip -c > %s.gz" % (username, password, hostname, database, filename))

问题在于很少有数据被篡改，但是每次我必须备份所有数据库24次时，这会占用太多空间.

The problem is very little data gets chnaged but every time i have to backup all databases 24 times , which takes too much space.

什么是最好的选择

有什么办法可以进行增量备份，这样我就可以每周对数据库进行1次完整备份，然后只进行增量备份.

Is there any way to have incremental backups so that i take 1 full backup of database evry week and then only incremental backups.

我想为单独的数据库(而不是所有mysql)提供单独的文件，这样，如果我需要恢复单个数据库，那么我也可以做到这一点

I want to have separate files for separate databases not all mysql so that if i need to restore single database then i can do that as well

推荐答案

将完整的数据库存储到文件中后，一种变通的方法可能是.将其与每周备份进行比较，然后将修补程序存储在磁盘上.当您要检索时，只需将diff应用于每周备份并获取完整的数据库

One hacky way may be, once you have taken full data base to a file. diff it against the weekly backup and store the patch on disk. When you want to retrieve, just apply diff to the weekly backup and get full db

要存储

  mysqldump -u $USERNAME --password=$PASSWORD -h $HOSTNAME -e --opt --skip-lock-tables  --skip-extended-insert -c $DATABASE >hourlyFile
  diff weeklyFile hourlyFile >hourlyFile.patch
  rm hourlyFile

要检索:

  cp weeklyFile hourlyFile
  patch hourlyFile <hourlyFile.patch

我不太清楚sqldump会提供哪种输出.如果上面的文字会起作用.否则，bsdiff可能会在这里为您提供帮助: http://www.daemonology.net/bsdiff/

I am not really aware what kind of output sqldump gives. if it's text above would work. Otherwise bsdiff may help you here : http://www.daemonology.net/bsdiff/

这篇关于如何在mysql数据库的增量备份中创建的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！