MySQL Point-in-Time Backup & Recovery Guide

How to Back Up MySQL Without Stopping the Server

Database failures rarely announce themselves in advance. A disk subsystem can degrade silently over days before the array fails completely. A poorly tested migration script can corrupt tables in seconds. A security incident can force an emergency shutdown with little warning. When any of these events occur on a production MySQL server, the question is not whether the data matters, but whether it can be recovered.

This guide covers the complete setup for MySQL backup and point-in-time recovery: enabling binary logging, running consistent mysqldump backups, automating the schedule, and executing a recovery that restores the database to any specific moment after a failure. The goal is a backup strategy that works reliably without requiring database downtime.

Why Standard Backups Are Not Enough

A full backup captures the database state at a single point in time. When restored, it returns the database to exactly that moment. Anything that happened after the backup began but before the failure is lost.

For a database that receives a thousand transactions per hour, the recovery gap from a daily backup can represent a significant volume of lost data: customer registrations, order updates, configuration changes, session data. In some environments, that gap could be three months of work.

Point-in-time recovery extends beyond the last backup by combining a full backup with a continuous log of database changes. This log, called the binary log, records every INSERT, UPDATE, DELETE, and schema modification as they occur. When something goes wrong, the binary log allows you to replay changes forward to any specific second, limited only by how long the logs have been retained.

This approach matters most for applications where recent data carries real value: booking systems where appointments cannot simply be re-entered, e-commerce platforms where order status changes throughout the day, or any service where lost transactions create direct business impact. The effort required to enable binary logging and implement point-in-time recovery is relatively small compared to the potential cost of data loss.

Enabling the MySQL Binary Log

The binary log is MySQL's write-ahead log for replication and recovery. Every statement that modifies data is written to this log before it is applied to the database. This write-ahead approach is what makes it possible to replay changes after restoring a full backup.

Binary logging is configured in the MySQL configuration file. On Ubuntu systems, this is typically located at /etc/mysql/mysql.conf.d/mysqld.cnf. Open the file and add the following directives within the [mysqld] section.

[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin
max_binlog_size = 100M
binlog_expire_logs_seconds = 604800

The server-id value must be unique across all MySQL servers in a replication topology. For a standalone server, setting it to 1 is appropriate and common. The max_binlog_size controls when MySQL rotates to a new binary log file, similar to how a log rotation utility works.

The binlog_expire_logs_seconds setting determines how long binary logs are kept before automatic removal. Setting this to 604800 seconds retains seven days of logs, which covers most recovery scenarios and provides a reasonable window between failure detection and recovery initiation.

After saving the configuration, restart MySQL to apply the changes.

sudo systemctl restart mysql

Verify that binary logging is active by connecting to MySQL and running the following command.

mysql -u root -p -e "SHOW MASTER STATUS\G"

The output shows the current binary log file name and the position within that file. Record these values somewhere secure. When performing point-in-time recovery later, these coordinates define where to start replaying the binary log. The actual binary log files are stored in /var/log/mysql/ with names like mysql-bin.000001, mysql-bin.000002, and so on.

Before making configuration changes to a production database, ensure you have a tested backup in place. Configuration errors or accidental service restarts can cause temporary unavailability, and a working backup provides a fallback.

Running a Consistent Full Backup with mysqldump

mysqldump is the standard MySQL backup utility. It reads the database and produces a SQL file containing all the commands needed to recreate the database structure and data. The key to a consistent backup without locking the database is using the --single-transaction flag.

mysqldump -u root -p --single-transaction --quick ncristea > backup_$(date +%Y%m%d_%H%M%S).sql

The --single-transaction option starts a transaction, reads the data in a consistent state, and commits internally without affecting writes from other sessions. This works reliably with InnoDB tables, which support transactional behaviour. If your database uses MyISAM tables, you will need to use --lock-tables instead, though this locks the tables and prevents concurrent writes during the backup.

The --quick option is important for larger databases. Without it, mysqldump loads the entire result set into memory before writing output. For tables with millions of rows, this can cause memory exhaustion. The --quick flag fetches rows in chunks, keeping memory usage manageable.

To back up all databases on the server, use the --all-databases flag instead of specifying a single database name.

mysqldump -u root -p --single-transaction --quick --all-databases > full_backup_$(date +%Y%m%d_%H%M%S).sql

After the backup file is created, compress it to save disk space and reduce the time required to copy it to backup storage.

gzip backup_20240615_120000.sql

Compressed backup files are significantly smaller and copy faster, which matters when transfer bandwidth is limited or when copying to cloud storage that charges per gigabyte. Keep the compressed file size in mind when planning storage budgets and retention policies.

Automating the Backup Schedule

Manual backups are unreliable. They depend on someone remembering to run them, and they are easy to defer when more urgent work appears. Automation removes this dependency and ensures backups run consistently regardless of workload.

The most reliable approach uses a shell script that handles the dump, compression, and retention management in a single executable file. Create the script at /usr/local/bin/mysql-backup.sh.

#!/bin/bash
BACKUP_DIR=/var/backups/mysql
DATE=$(date +%Y%m%d_%H%M%S)
MYSQL_USER=backup_user
MYSQL_PASSWORD='your_secure_password'
mysqldump -u $MYSQL_USER -p"$MYSQL_PASSWORD" --single-transaction --quick ncristea | gzip > $BACKUP_DIR/backup_$DATE.sql.gz
find $BACKUP_DIR -name "backup_*.sql.gz" -mtime +7 -delete

This script creates a timestamped, compressed backup of the ncristea database and automatically deletes any backups older than seven days. Using a dedicated backup user with appropriate permissions is better than using the root account directly. Create the user with read-only access to the databases you need to back up.

mysql -u root -p -e "CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'your_secure_password';"
mysql -u root -p -e "GRANT SELECT, LOCK TABLES, RELOAD ON *.* TO 'backup_user'@'localhost';"
mysql -u root -p -e "FLUSH PRIVILEGES;"

Make the backup script executable and add it to the crontab to run daily during a quiet period.

chmod +x /usr/local/bin/mysql-backup.sh
sudo crontab -e

Add the following line to run the backup at 3am each day.

0 3 * * * /usr/local/bin/mysql-backup.sh >> /var/log/mysql-backup.log 2>&1

The log redirection captures both standard output and errors. After the cron job runs, check the log file to confirm it completed successfully. An empty or unchanged log file after the expected run time indicates a problem that needs investigation.

For those managing multiple systems, incorporating backup verification into a broader IT maintenance schedule ensures these critical tasks receive regular attention during routine reviews.

Storing Backups Safely Off-Site

A backup on the same disk as the database provides no meaningful protection against drive failure, accidental deletion, or physical server damage. Proper backup storage means at least one copy exists on separate infrastructure, ideally in a different location.

The most straightforward approach copies backups to a remote server using rsync over SSH. Add this to the backup script or run it as a separate scheduled task immediately after the local backup completes.

rsync -avz /var/backups/mysql/backup_*.sql.gz remote-user@backup-server:/var/backups/mysql/

The remote retention policy should match or exceed your local policy. If backups are deleted locally after seven days but remote copies are removed after three days, the ability to restore older data is lost. Align the deletion schedules carefully.

Cloud storage provides geographic redundancy without requiring dedicated remote infrastructure. Tools like rclone can push backups to Amazon S3, Google Cloud Storage, Backblaze B2, or any S3-compatible provider. Configure lifecycle policies on the bucket to automatically delete backups beyond your retention period, preventing storage costs from accumulating indefinitely.

When planning your storage approach, a cloud backup solutions guide can help evaluate the trade-offs between different providers, storage classes, and retrieval costs.

Restoring from a Full Backup

When a database failure occurs, the first step is always to restore the most recent full backup. This brings the database to the state it was in when the backup completed. Only after the full backup is restored does the binary log replay become relevant for point-in-time recovery.

The restoration process drops the existing database if it exists, recreates it, and imports the backup file.

mysql -u root -p -e "DROP DATABASE IF EXISTS ncristea;"
mysql -u root -p -e "CREATE DATABASE ncristea;"
zcat backup_20240615_120000.sql.gz | mysql -u root -p ncristea

For a compressed backup, zcat decompresses the file on the fly before piping it to the mysql client. This avoids creating an intermediate uncompressed file and saves disk space during the recovery process.

If the database is large, restoration can take considerable time. Avoid rushing this step or interrupting it midway. Verify that the database has been restored correctly before proceeding with any further recovery steps or returning the database to service.

Performing Point-in-Time Recovery with the Binary Log

Point-in-time recovery combines a full backup with binary log replay. The full backup restores the database to the moment the backup completed. The binary log then replays all subsequent changes up to the target recovery point.

When mysqldump runs with --single-transaction, it records the binary log position at the start of the backup as a comment in the output file. Open the backup file and look near the top for a line like -- Position to start binary logging from.

head -50 backup_20240615_120000.sql | grep -i position

Note both the binary log file name and the position. These values tell you exactly where to resume applying changes after restoring the backup.

To replay the binary log up to a specific time, use the mysqlbinlog utility with the --stop-datetime option.

mysqlbinlog --stop-datetime="2024-06-15 14:00:00" /var/log/mysql/mysql-bin.000001 /var/log/mysql/mysql-bin.000002 | mysql -u root -p ncristea

This applies all binary log entries from both files up to 2pm on the 15th of June. Any transactions recorded after that time are not applied, effectively rolling the database forward to the exact moment you specify. If the binary log spans more than two files, include each one in sequence on the command line.

To replay to a specific binary log position rather than a time, use the --stop-position option instead.

mysqlbinlog --stop-position=1234 /var/log/mysql/mysql-bin.000001 | mysql -u root -p ncristea

Position-based recovery is useful when you know the exact position from a backup file comment or from a SHOW MASTER STATUS output taken at a known good moment.

When recovering to a specific point in time, double-check the target datetime or position before running the replay. Applying changes beyond your intended point can require starting the entire recovery process again from the full backup.

Documenting the Recovery Procedure

When a database is down and business operations are affected, nobody should be searching for documentation or working out the correct commands from scratch. The recovery procedure must be written, tested, and accessible to anyone who might need to perform it.

Good documentation for MySQL restoration includes the exact commands to restore from a full backup, the commands to replay the binary log to a specific time or position, the binary log file names and positions associated with each backup, and the retention policy for backups and binary logs. It should also specify the schedule for testing restorations and the escalation path for incidents that require outside help.

Keep a printed copy or an offline reference available. If the incident affects your primary documentation system, cloud storage, or network connectivity, access to a local copy can be critical. For teams managing multiple systems, establishing documentation standards that people actually follow makes the difference between documentation that helps during an incident and documentation that is written and forgotten.

Monitoring Backup Health Automatically

Backups that fail silently are worse than no backups at all. They create a false sense of security while the actual recovery capability erodes. Automated monitoring ensures that failures are detected and addressed before the next backup window opens.

The backup script should write to a log file, and a separate monitoring check should verify that the log shows successful completion. A simple monitoring approach checks the modification timestamp of the latest backup file.

#!/bin/bash
LATEST=$(ls -t /var/backups/mysql/backup_*.sql.gz | head -1)
if [ -z "$LATEST" ]; then
  echo "No backup file found" | mail -s "MySQL Backup Alert" admin@example.com
  exit 1
fi
AGE_HOURS=$(( ($(date +%s) - $(stat -c %Y "$LATEST")) / 3600 ))
if [ $AGE_HOURS -gt 25 ]; then
  echo "Backup is older than 25 hours" | mail -s "MySQL Backup Alert" admin@example.com
fi

Beyond timestamp checks, monitor the backup file size. A backup that is significantly smaller than previous backups might indicate a partial failure where only some tables were captured. An unexpectedly large backup might indicate unexpected data growth or table corruption. Track these metrics over time and alert on deviations from established baselines.

Regular testing of the restoration process is the most reliable way to verify backup integrity. Creating a disaster recovery testing plan that includes routine restoration verification ensures your backup strategy actually works when it is needed most.

Adapting the Backup Strategy for Database Size

Small databases up to a few gigabytes in size work well with daily full backups combined with binary log archiving. The backup completes quickly, resource usage is modest, and the binary log provides fine-grained recovery capability without excessive complexity.

Larger databases present different challenges. Daily full backups can take too long, consume too many server resources, and produce files that are impractical to copy offsite quickly. In these cases, consider weekly full backups with daily incremental backups using the --flush-logs option, which rotates the binary log and creates a clean starting point for incremental captures. The binary log bridges any gap between the last incremental backup and the point of failure.

Physical backups using tools like MySQL Enterprise Backup or the open-source Percona XtraBackup can be faster for very large databases because they copy the actual data files rather than executing SQL statements. These tools also support incremental physical backups, which capture only the pages that changed since the last full backup. However, they require more setup and are generally more appropriate for larger deployments.

Regardless of which approach you use, the critical requirement is that the entire backup and recovery workflow has been tested and the procedure is documented. An untested backup strategy is an assumption, not a strategy.

Common Mistakes That Undermine Backup Strategies

Several mistakes appear regularly in MySQL backup implementations. Avoiding them is largely a matter of knowing what to look for.

• Skipping binary logging: Full backups alone cannot provide point-in-time recovery. Without the binary log enabled, the best you can do is restore to the last backup moment. Enabling binary logging is a one-time configuration step that significantly extends recovery capability.
• Insufficient binary log retention: If binary logs are deleted before they are needed, the recovery window shrinks to whatever logs remain. Set binlog_expire_logs_seconds to a value that covers the maximum expected time between a failure occurring and recovery work starting, including weekends and holidays.
• Backing up locally only: Backups stored on the same server as the database offer no protection against server failure, accidental deletion, or physical damage. At least one backup copy must exist on separate infrastructure.
• Never testing restoration: The only way to confirm a backup works is to restore it. Regular testing in a non-production environment is essential and should be scheduled like any other maintenance task.
• Hard-coding credentials in scripts: Store database credentials securely using MySQL option files with restricted permissions, environment variables, or a secrets management tool. A compromised script should not provide direct database access to an attacker.
• Ignoring backup file size trends: A backup that is smaller than expected might indicate missing tables or failed captures. Track file sizes over time and alert on unexpected changes.