PHP通过ajax导出/下载mysql备份

Question

The following PHP (MySQL backup/download) script dumps a .sql backup file, which is gzipped and automatically downloaded to the client.

Running this script in a separate .php file instantly results in a file downloading to the client (the file is a valid zipped .sql export/backup file). All good so far.

$database = 'mydbname';
$user = 'myusername';
$pass = 'mypass';
$host = 'localhost';
$filename = "backup-" . date("d-m-Y") . ".sql.gz";
$mime = "application/x-gzip";
header( "Content-Type: " . $mime );
header( 'Content-Disposition: attachment; filename="' . $filename . '"' );
passthru("mysqldump --user={$user} --password={$pass} --host={$host} {$database} | gzip --best");

Note that the above wasn't working until I added this line to my .user.ini (local php.ini on a shared hosting account) file:

output_buffering = "ON"

However, after moving the code into an ajax.php file, and calling it from the javascript click event via jQuery $.ajax(), it sends binary data into ajax callback instead of sending directly to client.

$.ajax({
    url: "ajax.php",
    type: "post",
    data: "request=do_db_backup_now",
    success: function(recd){
        alert(recd);
        $('#btnMysqlBkup').hide();
    }
});

What would I need to do at this point to send the data in the recd variable as a download to the client?

Answer 1

To make this work, I had to replace the $.ajax() jQuery construct with this:

$(document).on('click', '#btnMysqlBkup', function(){
    var now = new Date();
    var dtcode = now.toISOString().replace(/[-:]|T/g,"").slice(2,14);

    var oReq = new XMLHttpRequest();
    oReq.open("POST", "ajax_mysql_bkup.php", true);
    oReq.responseType = "blob";

    oReq.onload = function(oEvent) {
        var blob = oReq.response;
        var link=document.createElement('a');
        link.href=window.URL.createObjectURL(blob);
        link.download="MySQL_bkup_"+dtcode+".zip";
        link.click();
    };

    oReq.send();
});

Then, the PHP code worked, and the result automatically downloaded to the local file system as a functional zip file.

$database = 'mydbname';
$user = 'myusername';
$pass = 'mypass';
$host = 'localhost';

$filename = "backup-" . date("d-m-Y") . ".sql.gz";
$mime = "application/x-gzip";

header( "Content-Type: " . $mime );
header( 'Content-Disposition: attachment; filename="' . $filename . '"' );

passthru("mysqldump --user={$user} --password={$pass} --host={$host} {$database} | gzip --best");

ALTERNATIVE PHP - (also stores gzipped backup on server as well as downloading to client):

$database = 'mydbname';
$user = 'myusername';
$pass = 'mypass';
$host = 'localhost';

$filename = "backup-" . date("d-m-Y") . ".sql.gz";
$fqfn = "/home/myacctname/_sql_BKUPs/" . $filename;
$mime = "application/x-gzip";

exec("mysqldump --user={$DBUSER} --password={$DBPASSWD} --host={$HOST} {$DATABASE} | gzip -9 > {$fqfn}");
header( "Content-Type: " . $mime );
header( 'Content-Disposition: attachment; filename="' . $filename . '"' );
passthru( "cat {$fqfn}" );

Sources:

Download a file by jQuery.Ajax <=== Please upvote this answer

Using jquery ajax to download a binary file

How to format a JavaScript date

Using a .php file to generate a MySQL dump

How to fix "Headers already sent" error in PHP