How can i create in incremental backups of mysql databases

Question

I have linux `centos 6.4` server with `mysql5.6` I have the cron job for making hourly mysql database backups like this Its in 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)) ``` The problem is very little data gets chnaged but every time i have to backup all databases 24 times , which takes too much space. What is best alternative for that Is there any way to have incremental backups so that i take 1 full backup of database evry week and then only incremental backups. 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

Accepted Answer

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

To store

```
  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
```

To retrieve:

```
  cp weeklyFile hourlyFile
  patch hourlyFile <hourlyFile.patch
```

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/

How can i create in incremental backups of mysql databases

Solution

Problem

Related problems