Creating MySQL Database Backup Using PHP

2 min read . March 20, 2024

Backing up your database is a crucial practice to ensure your data is safe. In this guide, we will show you how to create a PHP script to perform automatic backups of your MySQL database. This script will save both the table structure and data into an SQL file that can be used for future restoration.

1. Setting Up the Database Connection

First, we need to set up the connection to our MySQL database. Replace your_username, your_password, and your_database with your database credentials.

<?php
// Database connection settings
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$database = "your_database";

// Create connection
$conn = new mysqli($servername, $username, $password, $database);

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

2. Defining the Backup File Name

Next, we define the name of the file where the backup will be saved. In this case, we use backup.sql.

// Define filename for backup
$backup_file = 'backup.sql';

// Create or open the backup file
$handle = fopen($backup_file, 'w');

3. Retrieving the List of Tables from the Database

The script will retrieve the list of all tables from the selected database.

// Fetch tables from the database
$tables = array();
$result = $conn->query("SHOW TABLES");
while ($row = $result->fetch_row()) {
    $tables[] = $row[0];
}

4. Writing Table Structure to the Backup File

We need to write the table structure to the backup file. This involves retrieving the SQL commands used to create those tables.

// Loop through tables
foreach ($tables as $table) {
    // Retrieve table structure
    $create_table_query = "SHOW CREATE TABLE $table";
    $result = $conn->query($create_table_query);
    $row = $result->fetch_row();
    $create_table_sql = $row[1] . ";";

    // Write table structure to backup file
    fwrite($handle, "\n\n-- Table structure for table `$table`\n\n");
    fwrite($handle, "$create_table_sql\n");

5. Writing Table Data to the Backup File

After writing the table structure, we also need to write the table data to the backup file.

    // Retrieve table data
    $select_query = "SELECT * FROM $table";
    $result = $conn->query($select_query);

    // Write table data to backup file
    while ($row = $result->fetch_assoc()) {
        $row = array_map('addslashes', $row);
        fwrite($handle, "INSERT INTO $table VALUES ('" . implode("', '", $row) . "');\n");
    }
}

6. Closing the File and Connection

Once all the data is written, we should close the file and the database connection.

// Close the backup file
fclose($handle);

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

echo "Backup completed successfully!";

Conclusion

The PHP script above provides a simple way to back up your MySQL database. By saving the table structure and data into an SQL file, you can easily restore the database if needed.

Make sure to run this script in a secure environment and keep the backup files safe to prevent unauthorized access. If you have a large database or require more advanced backup options, consider using specialized database backup tools or features provided by your hosting service.

Tags:

PHP