How to Back Up MySQL Without Stopping the Server

Data loss is not a technical problem. It is a business problem. A database that loses three months of customer records, transaction history, or configuration data creates a crisis that costs time, money, and reputation to recover from. The only reliable solution is a backup strategy that works: regular full backups combined with binary log archiving that allows recovery to any point in time, without taking the database offline.

This guide walks through the complete setup for MySQL backup and point-in-time recovery: enabling binary logging, running consistent mysqldump backups, configuring automated scheduling, and the recovery procedure that brings the database back to a specific moment after a failure. If you are looking for a practical overview of database backup concepts first, there is a related guide on MySQL database backup and restore that covers the foundational approach.

Why Point-in-Time Recovery Matters

Standard full backups restore the database to the exact moment when the backup was created. Any transactions that occurred after the backup but before the failure are lost. For a busy database, that gap could represent hours or even days of critical data.

Point-in-time recovery extends beyond the last backup. It uses a full backup combined with a continuous record of database changes stored in the binary log. When something goes wrong, you can replay those changes up to any specific second, minimising data loss to the smallest possible window.

This approach matters most for applications where data changes frequently, such as e-commerce platforms, booking systems, financial applications, or any service where recent transactions cannot be recreated from scratch. Without point-in-time recovery capability, the difference between a minor incident and a serious data loss event can come down to a single configuration option.

Enabling the MySQL Binary Log

The binary log is MySQL's record of every data-changing operation: INSERT, UPDATE, DELETE, and schema changes. It is the foundation of point-in-time recovery. Without it, you can restore to the last full backup but not to any moment between the backup and the failure.

Binary logging is enabled in the MySQL configuration file. On Ubuntu, this is typically /etc/mysql/mysql.conf.d/mysqld.cnf. Open the file and locate the relevant section, then add the log-bin directive.

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

The server-id must be unique across all MySQL servers in a replication setup. For a single standalone server, setting it to 1 is appropriate. The max_binlog_size controls when MySQL rotates to a new binary log file. The binlog_expire_logs_seconds setting controls how long binary logs are retained before they are automatically removed. Setting this to 604800 keeps seven days of binary logs, which covers most recovery scenarios.

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

sudo systemctl restart mysql

Verify that binary logging is active by checking the MySQL status.

SHOW MASTER STATUS;

This command shows the current binary log file name and position. Note these values somewhere secure. When you need to perform point-in-time recovery, these are the starting coordinates. The binary log files themselves are stored in /var/log/mysql/ with names like mysql-bin.000001, mysql-bin.000002, and so on.

Running a Consistent Full Backup with mysqldump

mysqldump creates a SQL file that contains all the commands needed to recreate the database state. For a consistent backup that does not lock the database, use the --single-transaction option. This creates a transaction and reads the data in a consistent state without blocking writes from other sessions.

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

The --quick option outputs data in chunks rather than loading the entire result set into memory, which is important for large tables. The --single-transaction option works with InnoDB tables, which support transactions. For MyISAM tables, use --lock-tables instead to ensure consistency, though this will block writes during the backup process.

If you need to back up multiple databases, specify each one by name. To back up all databases on the server, use the --all-databases flag.

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

Compress the backup file to save space and reduce transfer time when copying to backup storage.

gzip backup_20240615_120000.sql

The compressed file is significantly smaller and copies faster to offsite locations. This matters when offsite transfer bandwidth is limited or when you are copying to cloud storage with per-gigabyte costs.

Scheduling Automatic Backups

Backups that rely on manual execution eventually do not get run. Automating the process ensures consistency without depending on someone remembering to trigger it. The most reliable approach uses a shell script called by cron.

Create a backup script that handles the dump, compression, and retention management in one place.

#!/bin/bash
BACKUP_DIR=/var/backups/mysql
DATE=$(date +%Y%m%d_%H%M%S)
MYSQL_USER=root
MYSQL_PASSWORD=yourpassword
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 backs up the ncristea database, compresses the output, and deletes any backups older than seven days. Save it as /usr/local/bin/mysql-backup.sh and make it executable.

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

Add it to crontab to run daily at 3am, a time when database activity is typically lowest.

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

The log redirection ensures that any failures are recorded and can be reviewed. Check the log after setting up the cron job to confirm it runs correctly. If the log file is not updated after the scheduled time, investigate why the script did not execute.

For those managing multiple servers or complex IT environments, establishing a regular IT maintenance schedule that includes backup verification tasks helps ensure nothing is missed during routine reviews.

Storing Backups Safely Off-Site

A backup stored on the same server as the database is not a proper backup. If the server fails, the backup is lost alongside the data. Offsite storage is essential for any backup strategy that needs to survive a server failure, hardware loss, or site-level incident.

After creating the local backup, copy the files to a remote server or cloud storage. A straightforward approach uses rsync over SSH.

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

Integrate this into your backup script or run it as a separate cron job immediately after the local backup completes. The retention policy on remote storage should match or exceed your local policy, otherwise you may lose the ability to restore older backups that still exist locally but have been deleted remotely.

For cloud storage, tools like rclone can push backups to Amazon S3, Google Cloud Storage, Backblaze B2, or any S3-compatible provider. Configure a lifecycle policy on the cloud storage bucket to automatically delete backups older than your retention period. This prevents storage costs from growing indefinitely.

Testing the Restoration Process

A backup that has not been tested is not a working backup. The restoration procedure must be tested in a staging environment before it is needed in production. Set up a test database server, perform a full restoration, and verify that the data is correct and complete.

To restore from a full backup, drop the existing database if it exists, create a new database with the same name, and import 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

This restores the database to the exact state it was in when the backup was taken. For point-in-time recovery, the binary log replay is needed after restoring the full backup.

Testing restoration regularly is part of a broader disaster recovery testing process that verifies your backup strategy actually works when you need it most.

Point-in-Time Recovery Using the Binary Log

Point-in-time recovery combines a full backup with the binary log to restore the database to any moment between the backup and the current time. The process has two steps: restore the full backup first, then replay the binary log from the backup time to the target recovery time.

Restore the full backup as described above. When mysqldump runs with --single-transaction, it records the binary log position at the start of the backup as a comment at the top of the SQL file. Check this to find the correct position to resume from.

head -50 backup_20240615_120000.sql

Look for a line similar to -- Position: 1234. This is the position within the binary log at the time the backup was taken. You need both the binary log file name and this position to start the point-in-time replay.

To replay the binary log up to a specific time, use the mysqlbinlog tool 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 replays all binary log entries up to 2pm on the 15th of June. Any changes made after that time are not applied. If the binary log spans multiple files, specify each one in sequence. To replay to a specific binary log position rather than a time, use --stop-position instead.

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

The combination of regular full backups and binary log replay means the database can be restored to any point in time within the retention period of the binary logs. This capability significantly reduces potential data loss when an incident occurs.

Documenting the Restoration Process

When a failure happens, the last thing you want is to be searching for documentation while the system is down. The restoration steps should be written down, tested, and kept somewhere accessible. Good documentation for MySQL restoration includes the exact commands to restore from the full backup, the commands to replay the binary log to a specific time, and the binary log file names and positions that correspond to each backup.

For teams managing multiple systems, establishing documentation standards that people actually follow makes the difference between useful documentation and a document that nobody reads.

The restoration documentation should also include the retention policy for backups and binary logs, the schedule for testing restorations, and the contact details for anyone who needs to be informed during an incident. Keep a printed copy or offline reference available in case the primary documentation system is inaccessible during an outage.

Monitoring Backup Success and Failure

The backup cron job should write to a log file, and a separate monitoring process should verify that the log is being updated and shows successful completions rather than errors. If a backup fails, an alert should be sent so the issue is addressed before the next backup window opens.

A simple monitoring check examines the modification timestamp of the latest backup file. If the file is older than the expected backup interval, something is wrong. A more robust approach parses the backup log and sends alerts on failure, while also tracking metrics like backup file size and duration over time to spot gradual degradation.

#!/bin/bash
LATEST=$(ls -t /var/backups/mysql/backup_*.sql.gz | head -1)
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" [email protected]
fi

Beyond basic timestamp checking, consider monitoring backup file size. A backup that is significantly smaller than previous backups might indicate a partial failure where only some tables were captured. Unexpectedly large backups might indicate table corruption or unexpected data growth.

Backup Strategy for Different Database Sizes

For small databases up to a few gigabytes, daily full backups with binary log archiving is sufficient. The backup completes quickly and the binary log provides fine-grained recovery capability without excessive complexity.

For larger databases, daily full backups may take too long or consume too many resources. In these cases, consider weekly full backups combined with daily incremental backups using --flush-logs to rotate the binary log. The incremental backup captures changes since the last full backup, and the binary log bridges any gap between the incremental backup and the point of failure.

Regardless of database size, the critical requirement is that the backup strategy has been tested and the restoration process is documented. An untested backup strategy is an assumption, not a strategy. Allocate time in your schedule for regular testing.

Common Mistakes to Avoid

Several mistakes regularly appear when setting up MySQL backup strategies. Avoiding them saves significant pain later.

  • Skipping the binary log: Full backups alone cannot provide point-in-time recovery. Without binary logging enabled, the best you can do is restore to the last backup moment.
  • Insufficient retention: Binary logs deleted too quickly reduce the recovery window. Set retention to cover the maximum expected time between failure detection and the start of recovery work.
  • Storing backups locally only: Backups on the same server as the database offer no protection against server failure, accidental deletion, or physical damage.
  • Not testing restores: The only way to know a backup works is to restore it. Regular testing in a non-production environment is essential.
  • Hard-coding passwords in scripts: Store credentials in a secure file with appropriate permissions, or use environment variables or MySQL option files with restricted access.