At 05:06 PM 11/18/2015 +0100, Fridolin SOMERS wrote:

You may use gzip compression. I suggest with level 3 (default is 6) to get a fast yet very compressed file : mysqldump my_koha_db | gzip -3 > my_koha_dump.sql.gz

For "dump" you are obviously quite correct that compression, size and speed can be tweaked. However, the original post asked about "incremental backup" and I assumed that this referred to MySQL "enterprise backup" <http://dev.mysql.com/doc/mysql-enterprise-backup/4.0/en/> because I am unaware of an incremental *dump*. With the "Enterprise backup" we found it impossible to compress the incrementals, but YMMV. Best -- Paul

Le 17/11/2015 23:24, Paul A a écrit :

...

At 02:34 AM 11/18/2015 +0530, Indranil Das Gupta wrote:

...

Hi all,

I'm facing a situation that between full backups, I would like to take incremental backups. Anyone have played with mysql incremental backup and Koha? any known issues?

Yup -- [probably, depends, no compression] and if you ever "restore" you'll get back all your deletes since the last full backup...

We just clean the MySQL db a couple of times a day and dump it -- takes 2104ms for a 754 Meg dump, so there's really no down time at all. Write some sort of [bash] script along the following lines for your Koha user in /usr/share/koha:

./bin/cronjobs/cleanup_database.pl --import 1 && ./bin/cronjobs/cleanup_database.pl --sessions && ./bin/cronjobs/cleanup_database.pl --logs 10 && ./bin/cronjobs/cleanup_database.pl --zebraqueue 1 && mysqldump --user=whomever --password=whatever your_koha_db > /mnt/data7/backup/your_koha_db_dump_[rotate_number].sql

Prepend ./bin/cronjobs/cleanup_database.pl --mail -v && if required, vary your variables 'au goût du jour' and bingo...

Best -- Paul

...

thanks in advance

-- Indranil Das Gupta

Phone : +91-98300-20971 Blog : http://indradg.randomink.org/blog IRC : indradg on irc://irc.freenode.net Twitter : indradg

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-==-=-=-=-=-=-=-=-=- Please exchange editable Office documents only in ODF Format. No other format is acceptable. Support Open Standards.

For a free editor supporting ODF, please visit LibreOffice - http://www.documentfoundation.org _______________________________________________ Koha-devel mailing list Koha-devel@lists.koha-community.org http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel website : http://www.koha-community.org/ git : http://git.koha-community.org/ bugs : http://bugs.koha-community.org/

_______________________________________________ Koha-devel mailing list Koha-devel@lists.koha-community.org http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel website : http://www.koha-community.org/ git : http://git.koha-community.org/ bugs : http://bugs.koha-community.org/

-- Fridolin SOMERS Biblibre - Pôles support et système fridolin.somers@biblibre.com _______________________________________________ Koha-devel mailing list Koha-devel@lists.koha-community.org http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel website : http://www.koha-community.org/ git : http://git.koha-community.org/ bugs : http://bugs.koha-community.org/

--- Maritime heritage and history, preservation and conservation, research and education through the written word and the arts. <http://NavalMarineArchive.com> and <http://UltraMarine.ca>

At 09:51 AM 11/19/2015 +1300, Robin Sheat wrote:

Paul A schreef op wo 18-11-2015 om 12:15 [-0500]:

...

However, the original post asked about "incremental backup" and I assumed that this referred to MySQL "enterprise backup" <http://dev.mysql.com/doc/mysql-enterprise-backup/4.0/en/> because I am unaware of an incremental *dump*. With the "Enterprise backup" we found it impossible to compress the incrementals, but YMMV.

It could also be referring to binary log shipping/replication.

https://dev.mysql.com/doc/refman/5.7/en/replication-configuration.html

Indranil should probably explain exactly what he's asking about.

For what it's worth, we do replication as a form of backup with at least one of our Koha systems, and I'm not aware of it causing problems.

Very interesting, can you share more details? We had a look at this (MySQL 5.5) and did not continue with it. From my notes (November 2012): "Not to be used for backup; any error that corrupts master also instantly corrupts slave[s] so no possibility to restore. Perhaps useful in the distributed portion of our system. We will maintain dump cron jobs." Best -- Paul

I agree with Robin. I've seen another system that does database dumps during the middle of the day while the database server is running, and the increased server load is quite significant. I'm also guessing that locking takes place as sometimes the load will skyrocket during this time. Besides the huge lowering of performance, your data might be inaccurate or even corrupt anyway since you might be doing a backup midway through a script action. David Cook Systems Librarian Prosentient Systems 72/330 Wattle St, Ultimo, NSW 2007

-----Original Message----- From: koha-devel-bounces@lists.koha-community.org [mailto:koha-devel- bounces@lists.koha-community.org] On Behalf Of Robin Sheat Sent: Thursday, 19 November 2015 10:16 AM To: koha-devel@lists.koha-community.org Subject: Re: [Koha-devel] incremental backup of the DB

Paul A schreef op wo 18-11-2015 om 17:49 [-0500]:

...

Very interesting, can you share more details?

Not really, I didn't implement it.

But essentially it provides an offsite database replicant that can then be backed up using snapshotting or whatever. I think regularly dumping a running database would not be best practice, as it puts a significant load on the server, and also may lock certain actions (don't quote me on that though.)

-- Robin Sheat Catalyst IT Ltd. ✆ +64 4 803 2204 GPG: 5FA7 4B49 1E4D CAA4 4C38 8505 77F5 B724 F871 3BDF

At 12:15 PM 11/19/2015 +1300, Robin Sheat wrote:

Paul A schreef op wo 18-11-2015 om 17:49 [-0500]:

...

Very interesting, can you share more details?

Not really, I didn't implement it.

But essentially it provides an offsite database replicant that can then be backed up using snapshotting or whatever. I think regularly dumping a running database would not be best practice, as it puts a significant load on the server, and also may lock certain actions (don't quote me on that though.)

Robin -- first and foremost, my best wishes for your move and new job, I'm sure that everyone here will miss your enthusiasm, experience and wisdom. As to the intricacies of backing up a MySQL database, we're probably going outside the "Koha envelope" but Mark T. and David C. both echoed your concern on "load on the server." I am of the opinion that this is purely hardware related, and server investment should reflect in-production use plus a suitable margin. We HAProxy two servers, each with 8-core CPUs and 16G RAM; the swap files have never been written to. The "system", all Koha files and MySQL + InnoDB are on raided solid state drives. As I wrote originally, the Koha routines to "clean" the db (especially sessions, unused Z39-50 data etc more than a day old) plus the dump takes ~2 seconds. I'm fairly certain that the lock time on the db is less than this (as the lock is released before the write happens -- I think as soon as dump has read binlog, but I'd have to immerse myself again in the intricacies, possibly of flag --single-transaction.) So, what we see in about 4 years of production practice is (a) that we have never found any backup corruption, (b) our staff and cataloguers have never noticed any slow-downs that substantially differ from normal LAN|router|cable latency, and (c) even under extreme conditions (Bing, Google and Baidu once hit between them 186,000 requests/hour) the limiting factor is never MySQL but always Zebra (with or without ZEBRAOPTIONS "-T" flag.) Hoping this response wasn't too long, and again, best wishes for your future... Paul --- Maritime heritage and history, preservation and conservation, research and education through the written word and the arts. <http://NavalMarineArchive.com> and <http://UltraMarine.ca>