An auto-backup feature plays a very important role for any website because it keeps your database safe and gives you peace of mind that your data won't be lost in the future.
In this tutorial, we will build an automatic backup system using PHP and MySQL that saves your database backups directly to Google Drive.
If your website gets new users, orders, or blog posts every day, and your server crashes or your site gets hacked, years of hard work can disappear in a second without a proper backup. Even worse, suppose a situation comes up where you are unable to renew your hosting on time, and the hosting company later charges a high recovery fee just to hand over your own data.
Most hosting providers and third-party SaaS tools charge hefty monthly fees for daily automatic website backups. Instead, here we will build a free auto-backup system that saves your database straight to Google Drive. You will also learn how to set up a free Cron Job so that backups run automatically—whether on a daily basis, hourly basis, or any custom schedule you want.
In this tutorial, we will create a Free, Lightweight, and Self-Hosted PHP Auto-Backup System that:
- Works with Pure PHP cURL without needing Composer or any heavy Google SDK.
- Uses PHP PDO to generate a complete
.sql dump of your MySQL database (so it runs smoothly on any shared hosting, even if exec() or mysqldump is disabled).
- Packs your website files and database into a single ZIP file.
Step 1: Get Your Google Drive API Credentials
Let's start building this system. First, we need 3 Google Drive API credentials, which you can create from the Google Cloud Console:
- Client ID
- Client Secret
- Refresh Token (so the script can upload backups automatically in the background without logging in repeatedly)
Important Guides:
Step 2: Create MySQL Database Auto-Backup PHP Code (cron.php)
Now let's build the script that automatically backs up your MySQL database and uploads it to Google Drive using a Cron Job.
The PHP code is simple and straightforward. We will use your Google Drive API Client ID, Client Secret, and Refresh Token to create a backup of the MySQL database and store it directly in your Google Drive account. We are using PHP PDO to keep the database connection and code secure.
This script is saved as a Cron Job file (for example, cron.php). When setting up the Cron Job in your hosting panel, you pass this file in the command along with the schedule—such as what time each day you want the backup to be saved to Google Drive. Any PHP website and MySQL database you connect will be backed up to Google Drive automatically based on the exact schedule you choose.
cron.php
<?php set_time_limit(0);
ini_set('memory_limit', '-1');
$db_host = "localhost";
$db_user = "root";
$db_pass = "";
$db_name = "tute";
$client_id = "xxxxxxxxxx";
$client_secret = "xxxxxxxxxxxxxxxxx";
$refresh_token = "xxxxxxxxxxxxxxxxxxxxxx";
$date_string = date("Y-m-d_H-i-s");
$sql_file = "db_backup_{$date_string}.sql";
$zip_file = "full_backup_{$date_string}.zip";
echo "Starting Backup Process...<br>";
try {
$pdo = new PDO("mysql:host={$db_host};dbname={$db_name};charset=utf8mb4", $db_user, $db_pass);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
die("Database Connection failed: " . $e->getMessage());
}
$tables = [];
// Fetch all tables
$query = $pdo->query("SHOW TABLES");
while ($row = $query->fetch(PDO::FETCH_NUM)) {
$tables[] = $row[0];
}
$sql_script = "";
foreach ($tables as $table) {
$quoted_table = "`" . str_replace("`", "``", $table) . "`";
$create_query = $pdo->query("SHOW CREATE TABLE {$quoted_table}");
$create_row = $create_query->fetch(PDO::FETCH_NUM);
$sql_script .= "\n\n" . $create_row[1] . ";\n\n";
$data_query = $pdo->query("SELECT * FROM {$quoted_table}");
$column_count = $data_query->columnCount();
while ($row = $data_query->fetch(PDO::FETCH_NUM)) {
$sql_script .= "INSERT INTO {$quoted_table} VALUES(";
for ($j = 0; $j < $column_count; $j++) {
if (isset($row[$j])) {
$sql_script .= $pdo->quote($row[$j]);
} else {
$sql_script .= "NULL";
}
if ($j < ($column_count - 1)) {
$sql_script .= ",";
}
}
$sql_script .= ");\n";
}
$sql_script .= "\n";
}
file_put_contents($sql_file, $sql_script);
$pdo = null;
if (file_exists($sql_file) && filesize($sql_file) > 0) {
echo "Database dump created successfully via Secure PDO.<br>";
} else {
die("Error: Could not create database dump (empty database).");
}
$zip = new ZipArchive();
if ($zip->open($zip_file, ZipArchive::CREATE | ZipArchive::OVERWRITE) === TRUE) {
$zip->addFile($sql_file, $sql_file);
$zip->close();
echo "Zip archive created successfully.<br>";
} else {
die("Error: Could not create zip archive.");
}
$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, 'https://oauth2.googleapis.com/token');
curl_setopt($ch, CURLOPT_POST, true);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
curl_setopt($ch, CURLOPT_POSTFIELDS, http_build_query([
'client_id' => $client_id,
'client_secret' => $client_secret,
'refresh_token' => $refresh_token,
'grant_type' => 'refresh_token'
]));
$token_response = curl_exec($ch);
curl_close($ch);
$token_data = json_decode($token_response, true);
if (!isset($token_data['access_token'])) {
die("Error: Failed to get Google Access Token. Check your credentials.");
}
$access_token = $token_data['access_token'];
$file_size = filesize($zip_file);
$boundary = "-------TechnoSmarter" . md5(time());
$metadata = [
'name' => $zip_file,
'mimeType' => 'application/zip'
];
$post_body = "--{$boundary}\r\n";
$post_body .= "Content-Type: application/json; charset=UTF-8\r\n\r\n";
$post_body .= json_encode($metadata) . "\r\n";
$post_body .= "--{$boundary}\r\n";
$post_body .= "Content-Type: application/zip\r\n\r\n";
$post_body .= file_get_contents($zip_file) . "\r\n";
$post_body .= "--{$boundary}--";
$ch_upload = curl_init();
curl_setopt($ch_upload, CURLOPT_URL, 'https://www.googleapis.com/upload/drive/v3/files?uploadType=multipart');
curl_setopt($ch_upload, CURLOPT_POST, true);
curl_setopt($ch_upload, CURLOPT_RETURNTRANSFER, true);
curl_setopt($ch_upload, CURLOPT_POSTFIELDS, $post_body);
curl_setopt($ch_upload, CURLOPT_HTTPHEADER, [
'Authorization: Bearer ' . $access_token,
'Content-Type: multipart/related; boundary=' . $boundary,
'Content-Length: ' . strlen($post_body)
]);
$upload_response = curl_exec($ch_upload);
$upload_status = curl_getinfo($ch_upload, CURLINFO_HTTP_CODE);
curl_close($ch_upload);
if ($upload_status == 200) {
echo "SUCCESS: File successfully uploaded to Google Drive!<br>";
} else {
echo "FAILED: Google Drive upload failed. Response: " . $upload_response . "<br>";
}
unlink($sql_file);
unlink($zip_file);
echo "Cleanup done. Local backup files deleted.";
?>
Step 3: Test the Script and Set Up the Cron Job
Once your code is ready, you can execute it cron.php directly to test whether the database backup is being created and uploaded to Google Drive properly.
To make this cron.php file run automatically on a schedule, you need to set up a Cron Job on your hosting server. Visit the link below to follow the step-by-step guide on how to set up a Cron Job:
How to Set Up a Cron Job for Automatic Backups
Recommended Posts:-