2

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 so that if i need to restore single database then i can do that as well

6

Very simple: binlogging. Configure mysql to keep the binary log for at least one day (possibly longer). Then change your backup to not do mysqldump, but use something like xtrabackup.

That way you can reduce your backup schedule to once per day and still have the possibility to roll forward to any point in time using the binlog.

  • My main issue is i need to only restore 1 or 2 databases not whole mysql . suppsoe i want to restore the last hour backup of my database1 on test system to see what chnages user has done which disrupts the system – user2424160 Jul 14 '13 at 8:57
  • 1
    Sounds like a case for versioning within your application, not backup. – gravyface Jul 14 '13 at 14:32
1

It's pretty silly to be backing up every hour for most situations. Maybe you need to consider mysql replication to another node.

  • i have only one VPS server and my budget is very tight. so i can't afford new server. – user2424160 Jul 14 '13 at 8:46
  • In that case, can you think of any scenario in which you would need to restore from a particular hour within the day? A full dump once a day should really be enough. Worst case scenario, you lose <24 hours of changes. – Peter Jul 14 '13 at 8:49
1

You can do incremental backups with xtrabackup. To restore a single database prepare the full and incremental backups (that include all databases) and then run MySQL using the xtrabackup directory as the datadir and take a mysqldump of the single database you want.

0

If you want to save space, you could continue to do your full backups hourly but to a deduplicated filesystem like lessfs. You could easily carve off a partition and format/mount it with lessfs and then let it handle the deduplication while still being able to restore at any hourly point, just without the space.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.