WIKI webhosting

Database backup is a critical task to maintain data integrity and ensure its availability for recovery purposes. Besides backup, it's essential to make these backups efficient and occupy minimal storage space. One effective way to achieve this is by compressing backups using the Gzip format. In PHP, it's straightforward to create a script that not only backs up the database but also saves the backup into a Gzip archive.

How to Create a PHP Script for Database Backup with Gzip Compression:

1. Connect to the Database: Start by establishing a connection to the database using the PHP mysqli_connect() function or an appropriate alternative.

2. Get the List of Tables: Obtain a list of all tables in the database you want to backup using the SQL query SHOW TABLES.

3. Iterate Through Tables: Iterate through each table obtained in the previous step.

4. Retrieve Table Data: For each table, retrieve all data using the SQL query SELECT * FROM table_name.

5. Save Data to a Gzip File: After obtaining data for each table, save it to a temporary file and then compress it using the gzencode() function.

6. Save the Gzip Archive: Finally, save the Gzip archive into the target directory with an appropriate name.

Example PHP Script for Database Backup with Gzip Compression:

<?php
// Connect to MySQL database
$host = 'localhost';
$username = 'user';
$password = 'password';
$database = 'database';

$connection = mysqli_connect($host, $username, $password, $database);

if (!$connection) {
    die("Failed to connect to the database: " . mysqli_connect_error());
}

// Get the list of tables
$tables = mysqli_query($connection, "SHOW TABLES");

// Initialize an empty Gzip archive
$gzipData = '';

while ($row = mysqli_fetch_row($tables)) {
    $table = $row[0];
    
    // Retrieve table data
    $result = mysqli_query($connection, "SELECT * FROM $table");
    $data = '';
    
    while ($row = mysqli_fetch_assoc($result)) {
        $data .= var_export($row, true) . ";\n";
    }
    
    // Compress data into Gzip format
    $gzipData .= gzencode($data, 9);
}

// Save the Gzip archive
$backupFilename = 'database_backup_' . date('Y-m-d_H-i-s') . '.gz';
file_put_contents($backupFilename, $gzipData);

// Close the database connection
mysqli_close($connection);

echo "Database backup has been successfully created and saved to the file '$backupFilename'.";
?>

This script connects to the MySQL database, retrieves a list of tables, iterates through each table, retrieves its data, and saves it into a Gzip archive. After completing the backup, the script closes the database connection and displays a confirmation message with the name of the created Gzip archive.

This way, you can easily create a PHP script for database backup with Gzip compression, ensuring compact and efficient backups of your data.