MySQL PITR Setup Tutorial: Complete Point-in-Time Recovery Configuration for Linux VPS in 2026

Understanding MySQL Point-in-Time Recovery Fundamentals

Your production MySQL database contains months of customer transactions, user data, and business records. A disk failure at 2:47 PM means you need to recover everything up to 2:46 PM—not yesterday's backup.

Point-in-time recovery (PITR) makes this possible. It combines full backups with binary log files that record every database change.

Binary logs capture INSERT, UPDATE, and DELETE operations as they happen. When disaster strikes, you restore your latest full backup. Then you replay the binary logs to bring your database forward to any specific timestamp.

This MySQL PITR setup guide walks through configuring production-ready point-in-time recovery on Ubuntu, AlmaLinux, and Rocky Linux systems.

A properly configured HostMyCode VPS with adequate storage becomes your foundation for reliable database recovery. You'll need at least 20GB free space beyond your current database size to handle binary logs and backup files safely.

Prerequisites and System Requirements

Your server needs MySQL 8.0 or higher for optimal binary logging features. Check your version:

mysql --version

Verify you have sufficient disk space for binary logs. A busy database generates 500MB to 2GB of logs daily:

df -h /var/lib/mysql
sudo systemctl status mysql

Install essential backup tools:

# Ubuntu/Debian
sudo apt update
sudo apt install mysql-client mysql-server percona-xtrabackup-80

# AlmaLinux/Rocky Linux
sudo dnf install mysql mysql-server
sudo dnf install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
sudo percona-release enable-only tools release
sudo dnf install percona-xtrabackup-80

Binary Logging Configuration

Binary logging forms the backbone of point-in-time recovery. Edit your MySQL configuration file:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Add these essential binary logging settings:

[mysqld]
# Binary logging configuration
log-bin = /var/lib/mysql/mysql-bin
server-id = 1
binlog_format = ROW
binlog_expire_logs_seconds = 604800
max_binlog_size = 1G
sync_binlog = 1

# Recovery-specific settings
innodb_flush_log_at_trx_commit = 1
innodb_doublewrite = ON
innodb_file_per_table = ON

The sync_binlog = 1 setting ensures binary logs write to disk after every transaction. This provides maximum durability but affects performance.

For high-traffic systems, consider sync_binlog = 100 for better throughput with slightly higher risk.

Restart MySQL to apply changes:

sudo systemctl restart mysql
sudo systemctl status mysql

Verify binary logging is active:

mysql -u root -p
SHOW VARIABLES LIKE 'log_bin';
SHOW MASTER STATUS;

Creating Full Database Backups

Full backups provide your recovery starting point. Use Percona XtraBackup for consistent, non-blocking backups:

sudo mkdir -p /backup/mysql
sudo chown mysql:mysql /backup/mysql

Create your first full backup:

sudo xtrabackup --backup --target-dir=/backup/mysql/full-$(date +%Y%m%d_%H%M%S) \
  --datadir=/var/lib/mysql \
  --user=root \
  --password

Prepare the backup for restoration:

sudo xtrabackup --prepare --target-dir=/backup/mysql/full-20260115_143000

Record the backup's binary log position. This appears in the XtraBackup output and the xtrabackup_binlog_info file:

cat /backup/mysql/full-20260115_143000/xtrabackup_binlog_info

This file contains the exact binary log filename and position where your backup ends. You'll need these coordinates for recovery.

Automated Backup Scripts

Manual backups create gaps in your recovery timeline. Create an automated backup script:

sudo nano /usr/local/bin/mysql_backup.sh

#!/bin/bash

# MySQL PITR Backup Script
BACKUP_DIR="/backup/mysql"
DATE=$(date +%Y%m%d_%H%M%S)
TARGET="$BACKUP_DIR/full-$DATE"
LOG_FILE="/var/log/mysql_backup.log"

# Create backup directory
mkdir -p "$TARGET"

# Perform backup
echo "$(date): Starting backup to $TARGET" >> "$LOG_FILE"
xtrabackup --backup --target-dir="$TARGET" \
  --datadir=/var/lib/mysql \
  --user=backup_user \
  --password="$MYSQL_BACKUP_PASSWORD" \
  2>&1 | tee -a "$LOG_FILE"

if [ $? -eq 0 ]; then
    # Prepare backup
    echo "$(date): Preparing backup" >> "$LOG_FILE"
    xtrabackup --prepare --target-dir="$TARGET" 2>&1 | tee -a "$LOG_FILE"
    
    # Clean old backups (keep 7 days)
    find "$BACKUP_DIR" -type d -name "full-*" -mtime +7 -exec rm -rf {} +
    
    echo "$(date): Backup completed successfully" >> "$LOG_FILE"
else
    echo "$(date): Backup failed" >> "$LOG_FILE"
    exit 1
fi

Make the script executable and secure:

sudo chmod +x /usr/local/bin/mysql_backup.sh
sudo chown root:root /usr/local/bin/mysql_backup.sh

Create a dedicated backup user with minimal privileges:

mysql -u root -p
CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'secure_backup_password';
GRANT BACKUP_ADMIN, PROCESS, RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'backup_user'@'localhost';
FLUSH PRIVILEGES;

Binary Log Management and Rotation

Binary logs accumulate quickly on busy systems. Configure automatic rotation to prevent disk space issues:

mysql -u root -p
SET GLOBAL binlog_expire_logs_seconds = 604800;
SET GLOBAL max_binlog_size = 1073741824;

Monitor current binary logs:

SHOW BINARY LOGS;
SELECT @@global.binlog_expire_logs_seconds;

For systems with limited storage, implement more aggressive log rotation:

sudo nano /usr/local/bin/binlog_cleanup.sh

#!/bin/bash

# Purge binary logs older than 3 days
mysql -u root -p"$MYSQL_ROOT_PASSWORD" -e "PURGE BINARY LOGS BEFORE DATE_SUB(NOW(), INTERVAL 3 DAY);"

# Log the action
echo "$(date): Binary logs purged" >> /var/log/mysql_maintenance.log

The relationship between backup frequency and log retention determines your maximum recovery window. Daily backups with 7-day log retention support point-in-time recovery for the past week.

Point-in-Time Recovery Procedures

When disaster strikes, swift recovery minimizes downtime. This procedure assumes you need to recover to 2026-01-15 14:30:00. Your latest backup finished at 2026-01-15 06:00:00.

Stop the MySQL service immediately to prevent further corruption:

sudo systemctl stop mysql

Restore your latest full backup:

sudo rm -rf /var/lib/mysql/*
sudo xtrabackup --copy-back --target-dir=/backup/mysql/full-20260115_060000 \
  --datadir=/var/lib/mysql
sudo chown -R mysql:mysql /var/lib/mysql
sudo chmod 750 /var/lib/mysql

Start MySQL in recovery mode:

sudo systemctl start mysql

Identify the binary logs needed for recovery. Check your backup's ending position:

cat /backup/mysql/full-20260115_060000/xtrabackup_binlog_info

Apply binary logs from the backup position to your target time:

mysqlbinlog --start-position=154 \
  --stop-datetime="2026-01-15 14:30:00" \
  /var/lib/mysql/mysql-bin.000023 \
  /var/lib/mysql/mysql-bin.000024 | mysql -u root -p

Verify the recovery by checking table counts and recent data:

mysql -u root -p
SELECT COUNT(*) FROM your_main_table;
SELECT * FROM your_main_table ORDER BY created_at DESC LIMIT 5;

Recovery Testing and Validation

Regular testing validates your recovery procedures before emergencies arise. Set up a dedicated test server for recovery validation.

Many HostMyCode managed VPS customers maintain a smaller replica instance specifically for this purpose.

Create a monthly recovery test schedule:

sudo nano /usr/local/bin/recovery_test.sh

#!/bin/bash

# Recovery Test Script
TEST_DIR="/tmp/mysql_recovery_test"
LOG_FILE="/var/log/mysql_recovery_test.log"

echo "$(date): Starting recovery test" >> "$LOG_FILE"

# Create test environment
mkdir -p "$TEST_DIR"
cd "$TEST_DIR"

# Restore latest backup to test directory
latest_backup=$(ls -1t /backup/mysql/full-* | head -1)
echo "Testing backup: $latest_backup" >> "$LOG_FILE"

mkdir test_mysql_data
xtrabackup --copy-back --target-dir="$latest_backup" --datadir="$PWD/test_mysql_data"

# Start test MySQL instance on different port
mysqld --datadir="$PWD/test_mysql_data" --port=3307 --socket="$PWD/mysql_test.sock" &
TEST_PID=$!

sleep 10

# Test connection and data integrity
mysql --socket="$PWD/mysql_test.sock" -e "SHOW DATABASES;" >> "$LOG_FILE"

# Cleanup
kill $TEST_PID
rm -rf "$TEST_DIR"

echo "$(date): Recovery test completed" >> "$LOG_FILE"

Schedule monthly tests via cron:

sudo crontab -e
# Add this line:
0 2 1 * * /usr/local/bin/recovery_test.sh

Common Recovery Scenarios and Troubleshooting

Binary log corruption requires special handling. If mysqlbinlog reports errors, use the --force-if-open flag:

mysqlbinlog --force-if-open --start-position=154 \
  --stop-datetime="2026-01-15 14:30:00" \
  /var/lib/mysql/mysql-bin.000023 > recovery.sql

Review the output file for obvious corruption before applying:

grep -i error recovery.sql
tail -20 recovery.sql

Incomplete transactions appear as orphaned BEGIN statements without matching COMMIT. These usually indicate the exact failure point.

Consider stopping recovery just before the problematic transaction.

For detailed analysis of database performance issues during recovery, review our database query profiling guide for comprehensive monitoring strategies.

Production Monitoring and Alerting

Proactive monitoring prevents recovery disasters. Monitor these critical metrics:

• Binary log disk usage
• Backup completion status
• Binary log sequence gaps
• Slave lag (if using replication)

Create a monitoring script for binary log health:

sudo nano /usr/local/bin/binlog_monitor.sh

#!/bin/bash

# Check binary log disk usage
BINLOG_USAGE=$(du -sm /var/lib/mysql/mysql-bin.* | awk '{sum+=$1} END {print sum}')
if [ "$BINLOG_USAGE" -gt 5000 ]; then
    echo "WARNING: Binary logs using ${BINLOG_USAGE}MB" | mail -s "MySQL Binary Log Alert" admin@yourdomain.com
fi

# Check for backup failures
if ! grep -q "Backup completed successfully" /var/log/mysql_backup.log | tail -1; then
    echo "ERROR: Last backup failed" | mail -s "MySQL Backup Failure" admin@yourdomain.com
fi

# Verify binary log sequence integrity
mysql -u root -p"$MYSQL_ROOT_PASSWORD" -e "SHOW BINARY LOGS;" > /tmp/binlog_list
if [ $? -ne 0 ]; then
    echo "ERROR: Cannot access binary logs" | mail -s "MySQL Binary Log Access Error" admin@yourdomain.com
fi

For comprehensive database monitoring across multiple instances, explore PostgreSQL monitoring strategies that apply to MySQL environments as well.

Security Considerations for PITR

Binary logs contain your complete database history—including sensitive data. Secure backup storage prevents data breaches:

sudo chmod 600 /backup/mysql/full-*
sudo chown mysql:mysql /backup/mysql/full-*

Encrypt backup files for long-term storage:

tar -czf - /backup/mysql/full-20260115_143000 | \
gpg --cipher-algo AES256 --compress-algo 2 --symmetric \
--output /secure/storage/backup-20260115.tar.gz.gpg

For database security best practices across different hosting environments, review database connection security hardening strategies.

Limit binary log access to essential users only:

sudo chmod 640 /var/lib/mysql/mysql-bin.*
sudo chgrp mysql /var/lib/mysql/mysql-bin.*

Frequently Asked Questions

How often should I create full backups for PITR?

Create full backups daily for most production systems. High-traffic databases may benefit from twice-daily backups to reduce binary log replay time during recovery. The backup frequency determines how far back you need to replay binary logs.

What happens if binary logs are corrupted during recovery?

Use the --force-if-open flag with mysqlbinlog to extract readable portions. Review the output manually for corruption points. You may need to stop recovery at the last good transaction before corruption occurred.

How much disk space do binary logs typically consume?

Binary log size depends on transaction volume. Expect 100MB to 2GB daily for most applications. Monitor usage patterns and adjust binlog_expire_logs_seconds based on your backup frequency and available storage.

Can I perform PITR while MySQL is running?

No, point-in-time recovery requires stopping MySQL to restore data files safely. Plan for maintenance windows or implement database replication for minimal downtime recovery scenarios.

How do I verify my PITR setup is working correctly?

Test recovery monthly using a separate test instance. Restore your latest backup and replay binary logs to a recent timestamp. Verify data integrity and document the recovery time for capacity planning.