How to Download MySQL Database Backup with PHP Code

To download MySQL database backup with PHP code, you can follow these steps:

1. Connect to the MySQL database: First, you need to establish a connection to your MySQL database using PHP. This requires the appropriate credentials such as the host, username, password, and database name.

2. Generate the backup: You can use the `mysqldump` command to create a backup of your MySQL database. In PHP, you can execute this command using the `exec()` or `shell_exec()` functions.

3. Prepare the backup file for download: After generating the backup, you need to prepare the file for download. This involves setting the appropriate headers, content type, and content disposition.

Let’s see the PHP code that does these steps:

“`php
<?php
// Database credentials
$host = ‘your_mysql_host’; // Usually ‘localhost’
$user = ‘your_mysql_username’;
$pass = ‘your_mysql_password’;
$db_name = ‘your_database_name’;

// Backup filename
$backup_file = ‘database_backup_’ . date(‘Y-m-d_H-i-s’) . ‘.sql’;

// Create a connection to the database
$mysqli = new mysqli($host, $user, $pass, $db_name);

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

// Generate the backup using mysqldump command
$command = “mysqldump –user=$user –password=$pass –host=$host $db_name > $backup_file”;
exec($command);

// Prepare the backup file for download
header(‘Content-Type: application/octet-stream’);
header(‘Content-Disposition: attachment; filename=”‘ . basename($backup_file) . ‘”‘);
header(‘Content-Length: ‘ . filesize($backup_file));
readfile($backup_file);

// Remove the temporary backup file
unlink($backup_file);

// Close the database connection
$mysqli->close();
?>
“`

Please make sure to replace `’your_mysql_host’`, `’your_mysql_username’`, `’your_mysql_password’`, and `’your_database_name’` with your actual MySQL database credentials. This code will create a backup file with the name format `database_backup_Y-m-d_H-i-s.sql` and prompt the user to download it when the PHP script is executed.

Important: Be cautious when using this script in a production environment, as it exposes the database credentials and creates a temporary backup file on the server. Make sure to restrict access to this script and delete the backup file after it has been downloaded to enhance security.