1

We have some servers that runs a tomcat app with a MySQL database. These servers are in different cities and the web app is used locally by our customers.

On these servers a crontab job runs twice a day and make a full backup of databases and send (SCP) this dump to our central office's server. Then we take this dumps and apply to a local databases so if an emergency occurs our customers can continue using the app via internet.

The problem is every day the dumps are getting bigger and transfer this amount of data are not simple so we're looking for some incremental backups solution and a replication method with this incremental.

Can you give some ideas about how to do this? There is another solution better than what we want to do?

Thanks.

Improve this question

3 Answers 3

Reset to default
4

Keep the previous dump and use rsync or even better, rdiff-backup ( http://www.nongnu.org/rdiff-backup/ ) through ssh instead of plain scp.

Improve this answer
4

Another option besides using rsync is to configure mysql replication with each of the normal databases as masters, and the ones in your office as a slave for each master. You can read mysql's documentation here.. If you want to keep with the scp/rsync style backup, you can maybe add compression to the backup with bzip, or some other method. There is also zmanda which will allow backups without needing to run a slave for each system you want to backup.

Improve this answer
1
  • 1
    Although replication is an option, it has potential problems: a) it is not a backup, so if somebody screws up the database by "DELETE FROM important_table;", it happily gets replicated to remote locations, making them unusable too - you would need to take snapshots of the slaves to alleviate that - b) can get quite bandwidth-intensive depending on the write pattern and c) occasionally breaks, not necessarily with an error message, leaving you with an outdated or incorrect copy of the database
    – the-wabbit
    Jan 8, 2012 at 2:07
2

Another potential option is to use the binary log but not any actual replication for the reasons syneticon-dj listed. Keep a couple days of binary logs (they can be large) and use the mysqlbinlog tool to get specific time periods and to replay the changes onto the central server during the day.

There are some performance and other limitations like transactions but it may work for your setup. You'll also have a nice audit trail to see exactly when a "DELETE FROM" like syneticon-dj mentions happened and can remove it from your replay, find the responsible person, etc.

You'll obviously want to keep the full backups but if your concern is keeping the central up to date a nightly full backup sync w/ binary in between would probably be the least intensive, and you can still use S19N's rdiff-backup recommendation for the nightly.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .