Created
April 5, 2023 17:36
-
-
Save themorgantown/dcd589f84c096e6257f535cbe54a964b to your computer and use it in GitHub Desktop.
Daily backups, keep 30: php / mysql, works on hosts that don't have mysqldump
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<?php | |
// Database connection information | |
$dbhost = 'localhost'; | |
$dbuser = ''; | |
$dbpass = ''; | |
$dbname = ''; | |
// Backup folder location | |
$backup_folder = '/path/backups'; | |
// Create a timestamp for the current date | |
$date = date('Y-m-d'); | |
// Create a filename for the backup file with the timestamp | |
$backup_filename = $backup_folder . '/' . $dbname . '_' . $date . '.sql'; | |
// Connect to the database | |
$conn = mysqli_connect($dbhost, $dbuser, $dbpass, $dbname); | |
// Check the connection | |
if (!$conn) { | |
die("Connection failed: " . mysqli_connect_error()); | |
} | |
// Fetch all tables in the database | |
$tables = []; | |
$result = mysqli_query($conn, "SHOW TABLES"); | |
while ($row = mysqli_fetch_row($result)) { | |
$tables[] = $row[0]; | |
} | |
// Backup each table | |
$backup_data = ""; | |
foreach ($tables as $table) { | |
$result = mysqli_query($conn, "SELECT * FROM {$table}"); | |
$num_fields = mysqli_num_fields($result); | |
$backup_data .= "DROP TABLE IF EXISTS {$table};"; | |
$create_table_result = mysqli_fetch_row(mysqli_query($conn, "SHOW CREATE TABLE {$table}")); | |
$backup_data .= "\n\n" . $create_table_result[1] . ";\n\n"; | |
while ($row = mysqli_fetch_row($result)) { | |
$backup_data .= "INSERT INTO {$table} VALUES("; | |
for ($j = 0; $j < $num_fields; $j++) { | |
if (isset($row[$j])) { | |
$escaped_value = mysqli_real_escape_string($conn, $row[$j]); | |
$backup_data .= '"' . $escaped_value . '"'; | |
} else { | |
$backup_data .= 'NULL'; | |
} | |
if ($j < ($num_fields - 1)) { | |
$backup_data .= ','; | |
} | |
} | |
$backup_data .= ");\n"; | |
} | |
$backup_data .= "\n\n\n"; | |
} | |
// Save the backup data to the .sql file | |
file_put_contents($backup_filename, $backup_data); | |
// Compress the backup file into a .zip archive | |
$zip = new ZipArchive(); | |
$zip_filename = $backup_filename . '.zip'; | |
$zip->open($zip_filename, ZipArchive::CREATE); | |
$zip->addFile($backup_filename, basename($backup_filename)); | |
$zip->close(); | |
// Remove the original .sql file | |
unlink($backup_filename); | |
// Get the file size of the .zip file | |
$filesize = filesize($zip_filename); | |
// Write to the backup.log file with the status and file size of the backup | |
$log_message = "Backup completed successfully on {$date}. File: " . basename($zip_filename) . ". File size: {$filesize} bytes.\n"; | |
$log_file = $backup_folder . '/backup.log'; | |
file_put_contents($log_file, $log_message, FILE_APPEND); | |
// Check if the backup folder contains more than 30 backups | |
$backup_files = glob($backup_folder . '/*.zip'); | |
if (count($backup_files) > 30) { | |
// Sort files by creation date | |
usort($backup_files, function ($a, $b) { | |
return filemtime($a) - filemtime($b); | |
}); | |
// Delete the oldest backup | |
unlink($backup_files[0]); | |
} | |
// Close the database connection | |
mysqli_close($conn); | |
echo $log_message; | |
?> |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment