Automate MySQL Backups with CRON Jobs in cPanel

Data loss is every website owner's nightmare. Regular database backups ensure that your data is safe and easily recoverable in case of unexpected issues. In this tutorial, we will explore how to automate MySQL database backups using a CRON job in cPanel, scheduling it to run on a specific date every month.

Why Use CRON Jobs for Backups?

CRON jobs allow you to schedule automated tasks at specific intervals. This eliminates the need to manually perform backups, ensuring your database is backed up consistently without intervention.

Step 1: Access cPanel CRON Jobs

1. Log in to your cPanel.
2. Scroll down to the Advanced section and click on Cron Jobs.
3. Under "Add a New Cron Job," choose a schedule for your backup. Since we need a backup on a specific date each month, select the appropriate timing.

Step 2: Schedule the Backup

We want the backup to run on a specific date, for example, on the 1st of every month at 2 AM. Use the following settings:

• Minute: 0
• Hour: 2
• Day: 1
• Month: *
• Weekday: *

This configuration ensures the backup runs on the first day of every month at 2:00 AM.

Step 3: Create the Backup Script

We will use a PHP script to export the database using mysqldump. Here is the script:

<?php
// Database configuration
$dbHost = 'localhost';  // Change if needed
$dbUser = 'your_db_username';
$dbPass = 'your_db_password';
$dbName = 'your_db_name';

// Backup storage location
$backupDir = __DIR__ . '/database'; // Directory to store backups
if (!is_dir($backupDir)) {
    mkdir($backupDir, 0755, true); // Create directory if not exists
}

// Generate a timestamped filename for the backup
$date = date('Y-m-d_H-i-s');
$backupFile = "{$backupDir}/backup_{$dbName}_{$date}.sql";

// Log file location
$logFile = __DIR__ . '/log.txt';

// Command to export database using mysqldump
$command = "mysqldump --host={$dbHost} --user={$dbUser} --password='{$dbPass}' {$dbName} > {$backupFile}";

// Execute the command
$output = null;
$returnVar = null;
exec($command, $output, $returnVar);

// Log the result
$logMessage = date('Y-m-d H:i:s') . " - Backup attempt for {$dbName}:\n";
if ($returnVar === 0) {
    $logMessage .= "Backup successful: {$backupFile}\n";
} else {
    $logMessage .= "Backup failed. Return code: {$returnVar}\n";
}

// Append log message to log file
file_put_contents($logFile, $logMessage, FILE_APPEND);

echo "Backup process completed. Check log.txt for details.\n";
?>

Save the script

1. Open a text editor and paste the above code.
2. Save the file as backup.php.
3. Upload it to your server, preferably inside the public_html or a secure directory.

Step 4: Set Up the CRON Job Command

In the cPanel CRON job section, enter the following command:

/usr/local/bin/php /home/your_cpanel_username/public_html/backup.php

Make sure to replace your_cpanel_username with your actual cPanel username and modify the path accordingly.