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

Question

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.3\mysql\bin";
    $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

See here https://stackoverflow.com/questions/11679275/mysqldump-via-php — ustmaestro, Feb 17 '23 at 10:34
https://www.theerrormessage.com/2008/10/mysqldump-with-exec-function-from-php-outputs-empty-file/ — ustmaestro, Feb 17 '23 at 10:56

ustmaestro · Answer 1 · 2023-02-17T11:21:45.107

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

edited Feb 17 '23 at 11:21

answered Feb 17 '23 at 10:48

ustmaestro

1,233
12
20

that ans is not right its still download blank data – Bhavesh Sahu Feb 17 '23 at 10:51
Updated the answer. Please try again the code – ustmaestro Feb 17 '23 at 10:58
Added long version of command params names, maybe this will help – ustmaestro Feb 17 '23 at 11:06
Sorry but its not working and there are some error in your code like in $backup_path C:/Users/ that is right way and exce its a $commandParams not a $commanParams but still its not working i was run in my code – Bhavesh Sahu Feb 17 '23 at 11:20

score 0 · Accepted Answer · edited Feb 25 '23 at 12:52

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);
}

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

2 Answers2