Extract a Portion of a Large Database Backup

The following function reads a large Updraft backup file and extracts a portion to another file. In this way, you can pull out the MYSQL insert statements for the table you want. And in the process, create a smaller file you can actually open.  You can set Updraft to create smaller backups. But if you get stuck with one of these monster files, this is how you can parse out what you need.

In this case, I put the file in the same directory as the code and new file. You may have to change the path to the backup to suit your environment.

When done, I did a find/replace in the new file to replace ‘INSERT INTO’  with ‘INSERT IGNORE INTO’  as I was just obtaining some records deleted by mistake.

NOTE: As always, make a backup of your database before running any CRUD operations.

/* Example call */
get_portion_by_tablename('backup_2020-04-14/backup_0414.sql','wp_postmeta');

/**
* Reads a large db backup looking for a specific table to write to a file
* No modification of data so you may need to replace INSERT INTO with INSERT IGNORE INTO on new file
* @param string $filename large file to search
* @param string $table name of table you want to extract
*/

function get_portion_by_tablename($filename, $table){
   $path =__DIR__."/".$filename; // path to your file
   $fh = fopen($path, "r");
   if (!$fh)
      die("Read file not found.");
   $found = false;
   $fp = fopen(__DIR__."/newfile.txt", 'w'); //Name of your new file

   while (( $buffer = fgets($fh) ) !== FALSE ) {
      if ( stripos( $buffer,'INSERT INTO' )!== false) {
         if ( stripos( $buffer,$table )!== false){
            fwrite( $fp, $buffer );
            if ( !$found ){
               $found = true;
            }
         }
         elseif ($found) {
            break;
         }
      }
   }
   fclose($fp);
}