Question 40538 in PHP Question posted in
Official manual can be found here.

Click Button To Export All Data In Database In To Myfile As .sql (Extension) – PHP

There is my Function to download backup is sql format

public function actionBackup()
{
    // Set the database access credentials
    $db_host = 'localhost';
    $db_username = 'root';
    $db_password = '';
    $db_name = 'fads';
    
    // Set the backup filename and path
    $backup_file = 'backup.sql';
    $backup_path = "C:/Users/" . get_current_user() . "/Downloads/" . $backup_file;

    // Execute the mysqldump command
    $path = "C:  xampp_7.3mysqlbin";
    $command = "mysqldump -u {$db_username} -p{$db_password} {$db_name} > {$backup_path}";
    // echo $path.$command;die;
    exec("$path > $command");

    // Send the backup file as a download
    Yii::$app->response->sendFile($backup_path);

    // Delete the backup file
    unlink($backup_path);
}

It’s download backup.sql but its empty i don’t understand that where is my code wrong and why i received blank backup.sql

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I was solved that problem there is my code

    public function actionBackup()
{
    // Set the database access credentials
    $db_host = '';
    $db_username = '';
    $db_password = '';
    $db_name = '';

    // Set the backup filename and path
    $backup_file = date('Y-m-d').'.sql';
    $backup_path = 'your path' . $backup_file;

    // Connect to the database
    $dsn = "mysql:host=$db_host;dbname=$db_name;charset=utf8";
    $options = [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION];
    $pdo = new PDO($dsn, $db_username, $db_password, $options);

    // Set the backup query
    $backup_query = "SET NAMES 'utf8';n";
    $tables = $pdo->query('SHOW TABLES')->fetchAll(PDO::FETCH_COLUMN);
    foreach ($tables as $table) {
        $backup_query .= "DROP TABLE IF EXISTS `$table`;n";
        $create_table = $pdo->query("SHOW CREATE TABLE `$table`")->fetch(PDO::FETCH_COLUMN, 1);
        $backup_query .= "$create_table;n";

        // Process data in smaller batches to avoid memory errors
        $offset = 0;
        $batch_size = 1000;
        while ($insert_rows = $pdo->query("SELECT * FROM `$table` LIMIT $offset, $batch_size")->fetchAll(PDO::FETCH_ASSOC)) {
            foreach ($insert_rows as $row) {
                $backup_query .= "INSERT INTO `$table` VALUES (";
                $values = [];
                foreach ($row as $value) {
                    $values[] = $pdo->quote($value);
                }
                $backup_query .= implode(', ', $values) . ");n";
            }
            $offset += $batch_size;
        }
    }

    // Write the backup query to file
    file_put_contents($backup_path, $backup_query);
    Yii::$app->response->sendFile($backup_path);
    // Output the backup file as a downloadable link
    // $download_link = 'http://' . $_SERVER['HTTP_HOST'] . '/' . $backup_path;
    // echo '<p>Download your backup file <a href="' . $download_link . '">' . $backup_file . '</a></p>';

    // Delete the backup file
    unlink($backup_path);
}

  2. 0

    First look if you use correct path for command and backup file.
    Avoid spaces in paths.

    Try this code:

    public function actionBackup()
{
    // Set the database access credentials
    $db_host = 'localhost';
    $db_username = 'root';
    $db_password = '';
    $db_name = 'fads';
    
    // Set the backup filename and path
    $backup_file = 'backup.sql';
    $backup_path = "C:Users" . get_current_user() . "Downloads" . $backup_file;

    // Execute the mysqldump command
    $command = ""C:\xampp_7\mysql\bin\mysqldump.exe"";                                      // path to mysqldump command
    // $commandParams = "-u {$db_username} -p{$db_password} {$db_name} > {$backup_path}";  // mysqldump params
    
    $commandParams = "--host={$db_host} --user={$db_username} --password={$db_password} {$db_name} > {$backup_path}";  // mysqldump params
    exec("$command $commandParams");

    // Send the backup file as a download
    Yii::$app->response->sendFile($backup_path);

    // Delete the backup file
    unlink($backup_path);
}

    Updated the answer

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search