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

user22
  • 1,199
  • 2
  • 12
  • 15

4 Answers4

6

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/

Archit Jain
  • 2,154
  • 1
  • 18
  • 32
1

Since your concern is space rather than speed you could always go for a pattern something like: Only keep the hourly backup for the last 24 hours. Keep the midnight backup for each weekday as that days backup. Weekly - archive backups to offline storage.

A lot of this could almost certainly be mostly automated by some careful file name choices - e.g. Midnight backups being called Backup_Mon, etc., other hours Backup_00_00, Backup_01_00, etc.

You could go for a even smaller latest_backup, prev_backup, prev_prev_backup by simply renaming the files at the start of each backup.

You could also consider using zip, (which python can do for you), to reduce the file size.

Steve Barnes
  • 27,618
  • 6
  • 63
  • 73
0

one file per db can be achieved by looping over all databases in a shell script: https://stackoverflow.com/a/10867549/2536029

for space reduction, diffs as suggested by ajain are one good idea, or deduplication of the backups with tools like rsnapshot or obnam are another. rsnapshot and obnam (might) require that (parts of) the files are identical to save noteworthy amounts of space. diffs can cope with some amount of changes as long as not everything changed because there is no "order by" or some ids shifted.

Community
  • 1
  • 1
mnagel
  • 6,729
  • 4
  • 31
  • 66
-1

You will not find a better solution, below is a script created by myself and has been used on many production servers. I have used python 3.

import os
import time
import datetime
date1 = time.strftime('%Y%m%d%H%M%S')
f = open('/root/mypython/dblist.txt') # this files contains the name of databases
line = f.readline()
f.close()
words = line.split()
for word in words:
    cmd = "mysqldump -u root -pabhishek {0} > {0}_{1}.sql".format(word, date1) 
# takes backup in the same location as script

    cmd2 = "zip {0}_{1}.zip {0}_{1}.sql".format(word, date1) 

# zips the backup just taken

    cmd3 = "rm -f {0}_{1}.sql".format(word, date1) 

# deletes the .sql backup just taken. after this step only .zip backup remains. remove this line if u need both .sql and .zip

    cmd4 = " scp {0}_{1}.zip root@192.168.1.105:/home/dev_test ".format(word, date1)
    os.system(cmd)
    os.system(cmd2)
    os.system(cmd3)
    if os.system(cmd4)== 0:
        result = "Backup Successful"
print(result)

Things to be remember

  1. The cmd4 command do a scp. You must have key based login enable from your server to remote server otherwise you will be asked password of remote server, which the script can't provide.

To enable key based login, see below:

http://pc2solution.blogspot.in/2013/03/how-to-enable-key-based-login-in-linux.html

  1. The first line of the script makes it executable, so for scheduling backup, you just need to enter full path in crontab, e.g. if you want to run this script every ten minutes do following entry in crontab

    */10 * * * * /home/myscript.py

  2. Make script executable by chmod 777 or whatever permission you want. This script works for linux.

Abhishek dot py
  • 909
  • 3
  • 15
  • 31
  • I don't see how this is INCREMENTAL. You seem to be just copying the entire database dump each time. Also, "you will not find a better solution" is a very strong statement, and needs justification, if at all possible. – Abhishek Anand Oct 01 '16 at 15:23