I want to export mysql blobs into files in a directory, is there a better way other than select into dumpfile?

Question

I have a table in mysql that contains attachments as blobs. Its approximately 13GB. I would like to export them to files in a directory so that users can access them via directory path instead of from the database.

I did a small research and got a few suggestions, but the process seems to take far too much time than I would expect. Below is a sample procedure that would loop the table and export all blobs and named using column filename.

begin
declare this_id VARCHAR(255);
declare cur1 cursor for select DISTINCT(filename) from fileattachment where filename is not null;
open cur1;
  read_loop: loop
    fetch cur1 into this_id;
    set @query = concat('select attachment from fileattachment where filename is not null and filename="', 
        this_id, '" into DUMPFILE "C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/', this_id,'"');
    prepare write_file from @query;
    execute write_file;
  end loop;
close cur1;
end

I have approximately 30,000 rows of around 300Kb each. This procedure takes around 1min to export a single file and hangs on the 5th file.

I tried running a single query to see if it makes any difference but I noted that my memory fills up to 13GB before this query executes. I'm imagining it is loading the entire database to memory even if I'm exporting one file

select attachment from fileattachment where filename is not null and filename='kiki.jpg' 
 into DUMPFILE 'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/kiki.jpg';

I'm I missing something that can optimize this, or is there a better/different way to do it?

S.Serem · Accepted Answer · 2019-10-07T17:09:47.763

I finally figured out that the real issue was that doing it within MySQL which ends up with read/write locks. The trick is to do a separately script, so I modified some php code(from MiffTheFox) utilizing file_put_contents to save to file. Also you may have to temporarily increase mysql query_cache_size, and if you are doing it from a separate machine packet size.

<?php
$connection = mysql_connect("localhost", "****", "****");
mysql_select_db("myHugedatabase");
$sql = "select attachment,filename from fileattachment where filename is not null";
$result = mysql_query($sql);
if(!$result){
trigger_error("dbget: ".mysql_error()." in ".$sql);
    return false;
}

while ($row = mysql_fetch_assoc($result)){
    echo '$row["filename"]';
    file_put_contents($row["filename"], $row["attachment"]);
}
mysql_close($connection);
?>

I want to export mysql blobs into files in a directory, is there a better way other than select into dumpfile?

1 Answers1