[Koha] mysqldump (koha-dump)

[Paul A]

At 03:47 PM 1/7/2016 +0000, Barry Cannon wrote:
>Hello,
>           I am curious how many people use the koha-dump script to dump 
> their Koha database? I am particularly interested to know how people are 
> dealing with larger databases where mysqldump causes table locking.

We looked into this (not sure what your definition of "larger database" is 
-- our dumps are around 670 Megs) and decided to go the hardware route 
using solid state drives for both the db and the backups. End result, we 
have less than 4 seconds "downtime" for a daily cron-job which (so far, 
touch wood) has not given rise to any complaints.

Note that we "clean" the db before dumping, specifically sessions (with the 
spiders we seem to get >100,000 / 24 hours despite blocking Baidu at the 
firewall) and imports (our cataloguers can get very busy) -- the mail, logs 
(we retain only 10 days) and zebraqueue appear to be less substantial -- 
which saves between 50 and 100 Megs.

Best -- Paul

>I would typically suggest using the '--single-transaction' option but I 
>notice there is still one table, pending_offline_operations, that isn't 
>using InnoDB as it's storage engine. Best practice is to use 'lock-tables' 
>in this instance (mysqldump locks by default) but with a larger database 
>this can cause a non responsive interface for the duration of the backup. 
>We have sites where the one system is used by users in different time 
>zones, this causes a headache as "out-of-hours" doesn't exist.
>
>LVM snapshots would be useful but prove tricky when using hosting providers.
>
>Any suggestions would be welcome!
>
>Regards
>Barry Cannon
>Interleaf Technology
>http://www.interleaf.ie
>Tel: +35312865855
>Email: bc at interleaf.ie
>Skype: bar.cannon
>_______________________________________________
>Koha mailing list  http://koha-community.org
>Koha at lists.katipo.co.nz
>https://lists.katipo.co.nz/mailman/listinfo/koha

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