Bet on Tech

Automatizácia zálohovania databázy je kriticky dôležitá pre ochranu dát. V tomto článku sa pokúsim popísať, ako vytvoriť PHP skript, ktorý automaticky zálohuje MySQL databázu.

Najprv definujeme konfiguráciu pre pripojenie k databáze a nastavenia zálohovania:

<?php
// Database configuration
$host = 'localhost';
$username = 'your_username';
$password = 'your_password';
$database = 'your_database_name';

// Backup configuration
$backupDir = 'path/to/backup/directory';
$backupFile = $backupDir . '/' . $database . '_' . date('Y-m-d_H-i-s') . '.sql';

// Ensure the backup directory exists
if (!is_dir($backupDir)) {
    mkdir($backupDir, 0777, true);
}

// Set execution time limit to unlimited for large databases
set_time_limit(0);
?>

Následne vytvoríme pripojenie k MySQL databáze pomocou funkcie mysqli_connect a to nasledovne:

<?php
// Include the configuration
include 'config.php';

// Connect to MySQL database
$conn = new mysqli($host, $username, $password, $database);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
?>

Vytvoríme funkciu pre generovanie SQL súboru z celej databázy:

<?php
// Include the configuration and connection
include 'config.php';

// Function to generate SQL file from database
function backupDatabase($conn, $backupFile) {
    // Get all tables in the database
    $tables = array();
    $result = $conn->query("SHOW TABLES");
    while ($row = $result->fetch_row()) {
        $tables[] = $row[0];
    }

    // Initialize variable to store SQL script
    $sqlScript = '';

    // Loop through each table
    foreach ($tables as $table) {
        // Get CREATE TABLE statement
        $result = $conn->query("SHOW CREATE TABLE $table");
        $row = $result->fetch_row();
        $sqlScript .= "\n\n" . $row[1] . ";\n\n";

        // Get all table data
        $result = $conn->query("SELECT * FROM $table");
        $columnCount = $result->field_count;

        // Loop through each row in the table
        for ($i = 0; $i < $columnCount; $i++) {
            while ($row = $result->fetch_row()) {
                $sqlScript .= "INSERT INTO $table VALUES(";
                for ($j = 0; $j < $columnCount; $j++) {
                    $row[$j] = $conn->real_escape_string($row[$j]);
                    if (isset($row[$j])) {
                        $sqlScript .= '"' . $row[$j] . '"';
                    } else {
                        $sqlScript .= '""';
                    }
                    if ($j < ($columnCount - 1)) {
                        $sqlScript .= ',';
                    }
                }
                $sqlScript .= ");\n";
            }
        }

        $sqlScript .= "\n";
    }

    // Save the SQL script to a backup file
    if (!file_put_contents($backupFile, $sqlScript)) {
        die("Error creating backup file: $backupFile");
    }
}

// Run the backup function
backupDatabase($conn, $backupFile);

// Close the connection
$conn->close();

echo "Database backup completed successfully!";
?>

Pre automatizáciu spustenia tohto skriptu môžeme použiť CRON job na Linuxe. Napríklad, ak chceme zálohovať databázu každý deň o polnoci, pridáme riadok do crontab:

0 0 * * * /usr/bin/php /path/to/backup.php

a vytvoríme backup.php

<?php
// Database configuration
$host = 'localhost';
$username = 'your_username';
$password = 'your_password';
$database = 'your_database_name';

// Backup configuration
$backupDir = 'path/to/backup/directory';
$backupFile = $backupDir . '/' . $database . '_' . date('Y-m-d_H-i-s') . '.sql';

// Ensure the backup directory exists
if (!is_dir($backupDir)) {
    mkdir($backupDir, 0777, true);
}

// Set execution time limit to unlimited for large databases
set_time_limit(0);

// Connect to MySQL database
$conn = new mysqli($host, $username, $password, $database);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Function to generate SQL file from database
function backupDatabase($conn, $backupFile) {
    // Get all tables in the database
    $tables = array();
    $result = $conn->query("SHOW TABLES");
    while ($row = $result->fetch_row()) {
        $tables[] = $row[0];
    }

    // Initialize variable to store SQL script
    $sqlScript = '';

    // Loop through each table
    foreach ($tables as $table) {
        // Get CREATE TABLE statement
        $result = $conn->query("SHOW CREATE TABLE $table");
        $row = $result->fetch_row();
        $sqlScript .= "\n\n" . $row[1] . ";\n\n";

        // Get all table data
        $result = $conn->query("SELECT * FROM $table");
        $columnCount = $result->field_count;

        // Loop through each row in the table
        for ($i = 0; $i < $columnCount; $i++) {
            while ($row = $result->fetch_row()) {
                $sqlScript .= "INSERT INTO $table VALUES(";
                for ($j = 0; $j < $columnCount; $j++) {
                    $row[$j] = $conn->real_escape_string($row[$j]);
                    if (isset($row[$j])) {
                        $sqlScript .= '"' . $row[$j] . '"';
                    } else {
                        $sqlScript .= '""';
                    }
                    if ($j < ($columnCount - 1)) {
                        $sqlScript .= ',';
                    }
                }
                $sqlScript .= ");\n";
            }
        }

        $sqlScript .= "\n";
    }

    // Save the SQL script to a backup file
    if (!file_put_contents($backupFile, $sqlScript)) {
        die("Error creating backup file: $backupFile");
    }
}

// Run the backup function
backupDatabase($conn, $backupFile);

// Close the connection
$conn->close();

echo "Database backup completed successfully!";
?>

Tento skript automaticky vytvorí zálohu MySQL databázy a uloží ju do špecifikovaného adresára. Pravidelným spúšťaním skriptu pomocou CRON jobu zaistíte, že vaše dáta budú pravidelne zálohované a chránené pred stratou.