How to Backup Your Koha Database via the Terminal

Regular database backups are critical for any Koha installation. Whether you’re running a small public library or a large academic institution, losing your catalog data, patron records, or circulation history would be catastrophic. This guide shows you how to create reliable MySQL database backups for Koha using terminal commands.

Why Database Backups Matter

Your Koha database contains:

• Bibliographic records - Your entire catalog
• Item records - Barcodes, call numbers, locations
• Patron data - Accounts, contact information, checkout history
• Circulation transactions - Current and historical checkouts
• Fines and fees - Outstanding charges
• System configuration - All your customizations and settings
• Acquisition records - Orders, budgets, vendors
• Serials data - Subscriptions and issue tracking

Losing this data means losing years of work. Backups protect against:

• Hardware failures
• Software bugs or corruption
• User errors (accidental deletions)
• Security breaches
• Natural disasters

Backup Methods Overview

This guide focuses on mysqldump, the most common and reliable method.

Prerequisites

Before backing up, ensure you have:

1. Root or MySQL User Access

You need credentials for a MySQL user with backup privileges:

GRANT SELECT, LOCK TABLES, SHOW VIEW, TRIGGER ON koha_library.* TO 'backupuser'@'localhost';

2. Sufficient Disk Space

Database backups can be large. Check available space:

df -h > /tmp/diskspace.txt 2>&1 && cat /tmp/diskspace.txt

Ensure backup destination has at least 2-3x your database size.

3. Database Name

Find your Koha database name (usually koha_libraryname):

mysql -u root -p -e "SHOW DATABASES LIKE 'koha%';" > /tmp/kohadbs.txt 2>&1 && cat /tmp/kohadbs.txt

Basic Backup with mysqldump

Simple Backup Command

mysqldump -u root -p koha_library > /backup/koha_backup_$(date +%Y%m%d).sql

Breakdown:

• mysqldump - MySQL backup utility
• -u root - Database user (use your actual username)
• -p - Prompt for password
• koha_library - Your database name
• > /backup/koha_backup_$(date +%Y%m%d).sql - Output file with date stamp

Result:

• File: /backup/koha_backup_20240315.sql
• Contents: SQL statements to recreate entire database

Backup with Compression

Compress backups to save space:

mysqldump -u root -p koha_library | gzip > /backup/koha_backup_$(date +%Y%m%d).sql.gz

Compression comparison:

• Uncompressed: 500 MB
• Gzipped: 50-100 MB (10:1 typical ratio)

Backup with Password in Command (Less Secure)

For automated scripts, avoid password prompts:

mysqldump -u backupuser -pYourPasswordHere koha_library > /backup/koha_backup.sql

⚠️ Security Warning: This exposes password in process list and bash history. Better alternatives below.

Secure Automated Backups

Method 1: MySQL Configuration File

Create /root/.my.cnf:

[client]
user=backupuser
password=YourSecurePassword

Set restrictive permissions:

chmod 600 /root/.my.cnf

Now run mysqldump without password:

mysqldump koha_library | gzip > /backup/koha_$(date +%Y%m%d_%H%M%S).sql.gz

Method 2: Environment Variable

Set password in environment:

export MYSQL_PWD='YourSecurePassword'
mysqldump -u backupuser koha_library | gzip > /backup/koha_backup.sql.gz
unset MYSQL_PWD  # Clear after use

Complete Backup Script

Create /usr/local/bin/backup-koha.sh:

#!/bin/bash

# Koha Database Backup Script
# Run daily via cron: 0 2 * * * /usr/local/bin/backup-koha.sh

# Configuration
DB_NAME="koha_library"
DB_USER="backupuser"
BACKUP_DIR="/var/backups/koha"
RETENTION_DAYS=30
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_FILE="${BACKUP_DIR}/koha_${DATE}.sql.gz"
LOG_FILE="${BACKUP_DIR}/backup.log"

# Create backup directory if it doesn't exist
mkdir -p "$BACKUP_DIR"

# Log start
echo "$(date): Starting backup of $DB_NAME" >> "$LOG_FILE"

# Perform backup
mysqldump \
  --single-transaction \
  --quick \
  --lock-tables=false \
  --databases "$DB_NAME" \
  | gzip > "$BACKUP_FILE"

# Check if backup succeeded
if [ $? -eq 0 ]; then
  echo "$(date): Backup completed successfully: $BACKUP_FILE" >> "$LOG_FILE"
  
  # Calculate backup size
  SIZE=$(du -h "$BACKUP_FILE" | cut -f1)
  echo "$(date): Backup size: $SIZE" >> "$LOG_FILE"
  
  # Delete old backups
  find "$BACKUP_DIR" -name "koha_*.sql.gz" -mtime +$RETENTION_DAYS -delete
  echo "$(date): Deleted backups older than $RETENTION_DAYS days" >> "$LOG_FILE"
else
  echo "$(date): ERROR: Backup failed!" >> "$LOG_FILE"
  # Send alert email
  echo "Koha backup failed on $(hostname)" | mail -s "BACKUP FAILURE" admin@library.org
  exit 1
fi

echo "$(date): Backup process finished" >> "$LOG_FILE"
echo "---" >> "$LOG_FILE"

exit 0

Make executable:

chmod +x /usr/local/bin/backup-koha.sh

Test manually:

/usr/local/bin/backup-koha.sh

Schedule Automatic Backups

Using Cron

Edit crontab:

crontab -e

Add daily 2 AM backup:

0 2 * * * /usr/local/bin/backup-koha.sh

Common schedules:

• 0 2 * * * - Daily at 2 AM
• 0 */6 * * * - Every 6 hours
• 0 2 * * 0 - Weekly on Sunday at 2 AM
• 0 2 1 * * - Monthly on 1st at 2 AM

Verify cron job:

crontab -l > /tmp/crontab.txt 2>&1 && cat /tmp/crontab.txt

mysqldump Options Explained

Performance Options

--single-transaction

• InnoDB tables: consistent backup without locking
• Essential for live systems

--quick

• Retrieves rows one at a time (reduces memory)
• Important for large databases

--lock-tables=false

• Don’t lock tables (use with --single-transaction)
• Allows reads/writes during backup

Content Options

--routines

• Include stored procedures and functions

--triggers

• Include table triggers

--events

• Include scheduled events

Complete backup:

mysqldump \
  --single-transaction \
  --quick \
  --lock-tables=false \
  --routines \
  --triggers \
  --events \
  --databases koha_library \
  | gzip > /backup/koha_full_backup.sql.gz

Selective Backup Options

Specific tables only:

mysqldump koha_library biblio items borrowers | gzip > /backup/koha_partial.sql.gz

Ignore certain tables:

mysqldump --ignore-table=koha_library.sessions koha_library | gzip > backup.sql.gz

Schema only (no data):

mysqldump --no-data koha_library > /backup/koha_schema.sql

Restoring from Backup

Restore Uncompressed Backup

mysql -u root -p koha_library < /backup/koha_backup_20240315.sql

Restore Compressed Backup

gunzip < /backup/koha_backup_20240315.sql.gz | mysql -u root -p koha_library

Full Restore Process

1. Stop Koha services:

   sudo koha-stop koha_library
   

2. Drop existing database:

   mysql -u root -p -e "DROP DATABASE koha_library;"
   

3. Create fresh database:

   mysql -u root -p -e "CREATE DATABASE koha_library;"
   

4. Restore from backup:

   gunzip < /backup/koha_backup.sql.gz | mysql -u root -p koha_library
   

5. Start Koha services:

   sudo koha-start koha_library
   

6. Verify restoration:
   • Access staff interface
   • Search catalog
   • Check recent circulation
   • Review system preferences

Backup Best Practices

1. Follow 3-2-1 Rule

• 3 copies of data (original + 2 backups)
• 2 different media types (local disk + cloud/tape)
• 1 off-site copy (protect against local disaster)

2. Test Restores Regularly

Don’t assume backups work. Test quarterly:

• Restore to test environment
• Verify data integrity
• Document restore time

3. Monitor Backup Success

• Check backup log files daily
• Alert on failures
• Verify backup file sizes aren’t shrinking

4. Secure Backup Files

• Restrict permissions: chmod 600 backup_file.sql.gz
• Encrypt if storing off-site
• Don’t include backups in web-accessible directories

5. Document Procedures

• Backup script locations
• Database credentials
• Restore steps
• Emergency contacts

Off-Site Backup Solutions

Cloud Storage

AWS S3:

aws s3 cp /backup/koha_backup.sql.gz s3://library-backups/koha/

rsync to remote server:

rsync -avz /backup/ backupserver:/var/backups/koha/

Dropbox/Google Drive:

• Use rclone for command-line syncing
• Automate with cron

Tape Backup

For long-term archival:

• Weekly full backups to tape
• Store tapes off-site
• Test restores annually

Troubleshooting

Backup Too Large

• Use --single-transaction to avoid locks
• Compress with gzip or bzip2
• Back up during low-usage hours

“Access Denied” Errors

Check user permissions:

SHOW GRANTS FOR 'backupuser'@'localhost';

Backup Takes Too Long

• Use --quick option
• Increase max_allowed_packet in MySQL config
• Schedule during maintenance window

Disk Space Issues

• Compress old backups: gzip /backup/*.sql
• Delete backups older than retention period
• Move to larger partition or external storage

Additional Resources

• MySQL Official Documentation - mysqldump
• Koha Wiki - Backups
• How to Backup Koha MySQL Database (web-based methods)

Conclusion

Database backups are non-negotiable for any production Koha system. A well-designed backup strategy using mysqldump provides reliable protection against data loss while being simple enough to implement and maintain.

Start with a basic manual backup, evolve to automated scheduled backups, and eventually implement a comprehensive 3-2-1 backup strategy with off-site storage. Most importantly: test your restores before you need them in an emergency.

Remember: It’s not a backup unless you’ve tested the restore.

Need help designing a backup strategy or implementing disaster recovery for your Koha installation? Our team provides system administration consulting and support. Contact us to learn more.