Automated MySQL Database Backup Configuration: A Guide to mysqldump, Cron, and PITR

At 02:14, the MySQL server supporting SIM Klinik in Sleman stopped responding. When checked, the rekam_medis table in InnoDB was corrupted due to a full disk and sudden power outage. The IT team only then realized that their mainstay all this time was actually just a manual copy eight months old — without a schedule, without rotation, without verification. Incidents like this are far more common than imagined, and almost always end with manual data reconstruction for 3-5 working days plus potential compliance issues with electronic medical record regulations. This article discusses how to build automatic MySQL backups that are truly reliable: strategy selection, ready-to-use mysqldump scripts, rotation, through to restore testing. All examples use MySQL 8.0 on Ubuntu 22.04 LTS.

1. Determine RPO and RTO Before Choosing a Tool

RPO (Recovery Point Objective) is how much data may be lost; RTO (Recovery Time Objective) is how long the service may be down. For SIMRS in a type C hospital, an RPO of 15 minutes and an RTO of 2 hours are still realistic. For SIM Klinik with 40-80 transactions per hour, a daily backup at 01:00 plus binlog is sufficient. These figures determine the architecture, not the other way around. Many installations in Yogyakarta and Magelang immediately ask about tools before knowing their RPO/RTO targets, and the result is a 40 GB backup file that has never been restore-tested.

There are three layers that need to be distinguished. Logical backup uses mysqldump 8.0 (available by default in MySQL Server 8.0) or util.dumpInstance() in MySQL Shell 8.0, which supports parallelization. Physical backup uses Percona XtraBackup 8.0.29+ or MySQL Enterprise Backup, suitable for large databases because it copies InnoDB pages directly. The third layer is the binary log enabled with log_bin=ON and binlog_format=ROW, which enables point-in-time recovery up to the last second before the incident.

The rule of thumb: databases under 50 GB are fine with mysqldump --single-transaction; above 50 GB use XtraBackup because mysqldump performs a full scan and on large InnoDB tables its execution time can exceed 1-2 hours. In one poultry ERP installation in Magelang with a size of 8.4 GB and 212 tables, mysqldump --single-transaction --quick completed in 3 minutes 51 seconds and produced a 410 MB gzip file — still very comfortable to run daily.

What is often forgotten: backups must be on a different machine and location. Keeping a .sql.gz file on the same partition as the datadir is not a backup, it is merely a copy. At minimum store it on another host, ideally in S3-compatible object storage with versioning so that files cannot be overwritten by ransomware or accidentally deleted.

2. Ready-to-Use mysqldump and Cron Script

Before creating the script, create a dedicated backup user so that root credentials never appear in crontab. Run as MySQL root: CREATE USER 'backup'@'localhost' IDENTIFIED BY '...' then grant GRANT SELECT, LOCK TABLES, SHOW VIEW, TRIGGER, EVENT, PROCESS, RELOAD ON *.* TO 'backup'@'localhost'. Store the credentials in /etc/mysql/backup.cnf with permission 600 and owner root.

The following script performs a consistent dump without locking application tables, compresses the result, deletes folders older than seven days, and prints status to stdout so it can be captured by cron or a monitoring system.

#!/bin/bash
# /usr/local/bin/backup-mysql.sh
set -euo pipefail
DEST=/backup/mysql/$(date +%F)
mkdir -p "$DEST"
mysqldump --defaults-extra-file=/etc/mysql/backup.cnf \
  --single-transaction --quick --routines --triggers --events \
  --set-gtid-purged=OFF --databases simrs keuangan | \
  gzip -9 > "$DEST/simrs-$(date +%H%M).sql.gz"
find /backup/mysql -type d -mtime +7 -exec rm -rf {} +
echo "BACKUP OK: $(date +%F_%T)"

Register it in /etc/cron.d/mysql-backup with the line 0 1 * * * root /usr/local/bin/backup-mysql.sh >> /var/log/mysql-backup.log 2>&1. The --single-transaction flag provides a consistent snapshot for InnoDB without blocking user transactions; --quick prevents the dump from holding the entire result set in memory; --set-gtid-purged=OFF makes it easier to restore to an instance without GTIDs.

For MySQL 8.0.21 and above, consider --source-data=2 so that the binlog position is recorded as a comment in the dump header. That way, when restoring you immediately know which binlog file and position to replay, so PITR does not have to guess. Test the script manually a day before cron is enabled, and make sure the backup user truly cannot perform DROP or UPDATE.

3. Rotation, Verification, and Point-in-Time Recovery

Rotation is not just deleting old files. Apply the 7-4-12 scheme: seven daily backups, four weekly (taken every Sunday), twelve monthly (on the 1st). With an average size of 410 MB, total local storage is only about 4.9 GB — very cheap compared to the risk of data loss. Copy to a second location using rclone to S3-compatible storage or rsync to a DR server at the branch office, with a bandwidth limit so it does not disturb the busy morning clinic hours.

Verification must be performed at least once a month: restore to a staging instance (not production), run mysqlcheck -u root -p --check --all-databases, then compare row counts on key tables such as pasien, kunjungan, and tagihan. Record three numbers: number of tables, total rows, and restore duration. A backup that has never been restored is worth zero, even if its file size looks reasonable.

For incidents outside the schedule, use binlog. The command mysqlbinlog --start-datetime='2025-03-14 18:00:00' --stop-datetime='2025-03-14 20:30:00' /var/log/mysql/binlog.000042 | mysql -u root -p replays transactions after the last restore. Ensure binlog_expire_logs_seconds is set to at least 7 days (604800 seconds); the default of 30 days is already safe for most SIMRS and ERP projects.

Simple yet effective monitoring: send script logs to a Telegram bot or WhatsApp gateway. If the exit code is not 0, or the file size drops by more than 30% compared with the previous day, send an alert. A drastic size decrease almost always means a database failed to be dumped or a table was truncated without anyone realizing it.

Best Practices

1. Test restore at least once a month to a separate instance, then record the duration and the results of the row-count comparison.
2. Store credentials in an options file with permission 600; never put the password in crontab or inside the script.
3. Apply the 3-2-1 principle: three copies of data, two different media, one offsite copy in another region.
4. Enable binlog with ROW format and a retention of at least 7 days so that point-in-time recovery can truly be executed.
5. Encrypt backup files containing medical record data (gzip -9 | openssl enc -aes-256-cbc -salt) and set up automatic alerts for every failure.

FAQ

What is the ideal backup frequency for a SIMRS database?

A daily full backup at 01:00 plus continuous binlog is the most balanced configuration. Full backups more than once a day are rarely necessary, unless transaction volume exceeds 100 thousand rows per day.

Does mysqldump lock tables while the application is running?

No, as long as all tables use InnoDB and you use --single-transaction. Writes continue to run; all that happens is a consistent snapshot at the transaction level.

How do I back up a 500 GB database without downtime?

Use Percona XtraBackup 8.0 with the --parallel=4 and --compress options, run it on a replica, not on the primary server. mysqldump at this size will take hours and burden I/O.

Is it enough to store backups on the same server?

No. If the server is lost, damaged, or hit by ransomware, the copy on the same partition is lost as well. At a minimum, copy it to a separate host, ideally to object storage with versioning and locked retention.

Automated backup is not a one-time project: it needs schedules, rotation, verification, and alerts that are continuously monitored. Start with the cheapest — a mysqldump script, cron, and an offsite copy — then upgrade to XtraBackup and PITR as data volume grows. If your team is preparing a SIMRS, SIM Klinik, ERP, POS, or website and needs technical assistance, our IT consulting and application development services serve Yogyakarta (DIY), Magelang, and surrounding areas. Discuss your RPO/RTO needs and we will help design a secure, tested, and regulation-compliant backup architecture.

This article is part of the knowledge base of Nugroho Setiawan, an IT consultant in Jogja (Yogyakarta). Related solutions: PilarPOS SIMRS (Hospital Information System) Clinic Management System. Need a free consultation?