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.
Ďalej už nastaviť crontab a to je asi všetko.... ...
// Save the SQL script to a backup file ... ...
// Loop through each row in the table ... ...
// Initialize variable to store SQL script ... ...
A čo takto? <?php // Database... ...
Celá debata | RSS tejto debaty