Learn how to automate MySQL backups using PHP and shell scripts. Schedule database dumps, secure backups, and manage storage efficiently.
Introduction
Automating MySQL database backups ensures data security, disaster recovery, and minimal downtime. Instead of manually exporting databases, PHP and shell scripts can schedule and manage backups efficiently.
With PHP and shell scripting, you can:
- Automate MySQL database backups at scheduled intervals
- Compress and store backups securely
- Upload backups to remote storage for disaster recovery
- Set up automatic cleanup for old backups
This guide covers:
- Executing MySQL backup scripts from PHP
- Using cron jobs for scheduled database dumps
- Encrypting and compressing backups
- Uploading backups to remote storage (SFTP, AWS S3)
1. Creating a MySQL Backup Using PHP
PHP can trigger MySQL dumps using the mysqldump command.
Example: Backing Up a Database with PHP
$dbName = "my_database";
$user = "root";
$password = "password";
$backupFile = "/backups/db_backup_" . date("Y-m-d") . ".sql";
$command = "mysqldump -u $user -p'$password' $dbName > $backupFile";
shell_exec($command);
echo "Backup completed: $backupFile";
Why Use This?
- Automates database exports
- Allows scheduled backup execution
2. Compressing the MySQL Backup
To save storage space, compress the SQL backup using gzip.
Example: PHP Code to Create a Compressed Backup
$backupFile = "/backups/db_backup_" . date("Y-m-d") . ".sql.gz";
$command = "mysqldump -u $user -p'$password' $dbName | gzip > $backupFile";
shell_exec($command);
echo "Backup compressed: $backupFile";
Why Use Compression?
- Reduces backup file size
- Saves disk space on the server
3. Automating Backups Using a Shell Script
Bash Script for MySQL Backup (backup.sh)
#!/bin/bash
DB_NAME="my_database"
USER="root"
PASSWORD="password"
BACKUP_DIR="/backups"
BACKUP_FILE="$BACKUP_DIR/db_backup_$(date +\%Y-\%m-\%d).sql.gz"
# Perform backup and compress the file
mysqldump -u $USER -p"$PASSWORD" $DB_NAME | gzip > $BACKUP_FILE
# Output message
echo "Database backup completed: $BACKUP_FILE"
PHP Code to Execute the Backup Script
shell_exec("bash /scripts/backup.sh");
echo "Backup script executed.";
Why Use a Shell Script?
- More efficient than executing multiple shell commands in PHP
- Easier to schedule and manage
4. Scheduling Automatic MySQL Backups with Cron Jobs
To schedule daily database backups at 2 AM, add a cron job:
crontab -e
Add the following line:
0 2 * * * bash /scripts/backup.sh
Why Schedule Cron Jobs?
- Ensures automatic backups
- Reduces manual database administration
5. Encrypting MySQL Backups for Security
To encrypt database backups, use gpg.
Bash Script to Encrypt Backup
#!/bin/bash
DB_NAME="my_database"
USER="root"
PASSWORD="password"
BACKUP_DIR="/backups"
BACKUP_FILE="$BACKUP_DIR/db_backup_$(date +\%Y-\%m-\%d).sql.gz"
ENCRYPTED_FILE="$BACKUP_FILE.gpg"
# Create and encrypt backup
mysqldump -u $USER -p"$PASSWORD" $DB_NAME | gzip | gpg --symmetric --cipher-algo AES256 -o $ENCRYPTED_FILE --passphrase "securepassword"
# Remove the unencrypted backup
rm -f $BACKUP_FILE
echo "Encrypted backup created: $ENCRYPTED_FILE"
Why Encrypt Backups?
- Protects sensitive data from unauthorized access
- Ensures secure storage for compliance
6. Uploading Backups to Remote Storage (SFTP, AWS S3, Google Drive)
Uploading MySQL Backup to SFTP Using PHP
$sftpHost = "sftp.example.com";
$sftpUser = "backupuser";
$sftpPass = "password";
$backupFile = "/backups/db_backup_" . date("Y-m-d") . ".sql.gz";
$connection = ssh2_connect($sftpHost, 22);
ssh2_auth_password($connection, $sftpUser, $sftpPass);
$sftp = ssh2_sftp($connection);
$remoteFile = "/remote/backups/" . basename($backupFile);
ssh2_scp_send($connection, $backupFile, $remoteFile);
echo "Backup uploaded to SFTP: $remoteFile";
Why Upload Backups to Remote Storage?
- Ensures off-site backup availability
- Protects against data loss from server failures
7. Automatically Deleting Old Backups
To delete backups older than 30 days, add the following cron job:
0 3 * * * find /backups -type f -mtime +30 -delete
Why Delete Old Backups?
- Frees disk space
- Prevents storage overflow
8. Restoring a MySQL Backup Using PHP
To restore a backup, use the mysql command.
PHP Code to Restore a Backup
$dbName = "my_database";
$user = "root";
$password = "password";
$backupFile = "/backups/db_backup_2024-02-15.sql.gz";
$command = "gunzip < $backupFile | mysql -u $user -p'$password' $dbName";
shell_exec($command);
echo "Database restored successfully.";
Why Automate Restoration?
- Enables quick disaster recovery
- Restores database backups efficiently
9. Verifying and Logging Backup Status
To log backup success or failure, append logs to a file.
Bash Script Logging Output
LOG_FILE="/var/log/mysql_backup.log"
echo "$(date) - Database backup completed: $BACKUP_FILE" >> $LOG_FILE
Checking Logs in PHP
echo nl2br(file_get_contents('/var/log/mysql_backup.log'));
Why Use Logs?
- Tracks backup history
- Helps debug failed backups
10. Best Practices for MySQL Backups Using PHP and Shell Scripts
- Automate backups with cron jobs to avoid manual execution
- Use gzip compression to save storage
- Encrypt backups to secure sensitive data
- Upload to remote storage for disaster recovery
- Delete old backups automatically to free disk space
- Maintain logs to track backup success or failures
Conclusion
Automating MySQL backups with PHP and shell scripts ensures data protection, disaster recovery, and efficient storage management.
This guide covered:
- Executing MySQL backups using PHP and shell scripts
- Scheduling backups with cron jobs
- Encrypting and compressing backups for security
- Uploading backups to remote storage
- Restoring databases efficiently
By implementing these techniques, database maintenance becomes seamless and fully automated.