Linux – How to i create in incremental backups of thesql databases

centosdatabaselinuxMySQLpython

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

Best Answer

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.

Related Topic