Restore database from mysql ibdata1 file.

Readme.md

First run ./stream_parser as described in this article and the use the following script. Good luck, you need it.

restore_database_from_pages.sh

#!/bin/bash
#

STRUCT_DATABASE_NAME=''
STRUCT_DB_USER=''
STRUCT_DB_PASS=''

DEST_DATABASE=''
DEST_DB_USER=''
DEST_DB_PASS=''


BASE_DIR="$PWD"

ORIGINAL_DB_NAME=''
SKIP_TABLES='system backup_migrate_destinations backup_migrate_profiles backup_migrate_schedules watchdog cache_views cache_views_data cache_rules cache_mailchimp_user cache_advagg_info cache_media_xml cache_page cache_bootstrap cache_path cache_entity_node cache_menu cache_form batch advagg_files advagg_aggregates_hashes advagg_aggregates_versions advanced_help_index captcha_sessions feeds_log cache_advagg_aggregates cache_entity_file cache_variable cache_block'
SKIP_BINARY=false
SKIP_DATABASE=false
RUN_QUIET=false
RUN_VERBOSE=false

function print_help {
  echo -e "Usage: ./$(basename $0) [-bdqv]\n" 
  echo -e "-h\t Prints this help and exits."
  echo -e "-b\t Skips the parsing of binary files."
  echo -e "-d\t Skips the database import at the end.\n"
  echo -e "-q\t Quiet mode.\n"
  echo -e "-v\t Verbose mode.\n"

  exit 1
}

listcontains() {
  for word in $1; do
    [[ $word = $2 ]] && return 0
  done
  return 1
}

# Get config options
while getopts hbdqv opts; do
  case ${opts} in
    h) print_help ;;
    b) SKIP_BINARY=true ;;
    d) SKIP_DATABASE=true ;;
    q) RUN_QUIET=true ;;
    v) RUN_VERBOSE=true ;;
  esac
done

if [[ "$RUN_VERBOSE" = true ]]; then
  RUN_QUIET=false
fi

# Create destination dir for tables structure files
table_struct_dir="$BASE_DIR/$ORIGINAL_DB_NAME/sql"
if [[ ! -d "$table_struct_dir" ]]; then
  `mkdir -p "$table_struct_dir" >/dev/null`
  if [[ "$RUN_VERBOSE" = true ]]; then
    printf "Created structure dir: %s\n" "$table_struct_dir"
  fi

elif [[ ! -w "$table_struct_dir" ]]; then
  printf "SQL structure directory is not writable!\nAttempted to write to: %s\n" "$table_struct_dir" >&2
  exit -1
fi

# Create destination dir for dumped sql files
sql_data_dump_dir="$BASE_DIR/dumps/default"
if [[ ! -d "$sql_data_dump_dir" ]]; then
  `mkdir -p "$sql_data_dump_dir" >/dev/null`
  if [[ "$RUN_VERBOSE" = true ]]; then
    printf "Created sql dump dir: %s\n" "$sql_data_dump_dir"
  fi

elif [[ ! -w "$sql_data_dump_dir" ]]; then
  printf "SQL dump directory is not writable!\nAttempted to write to: %s\n" "$sql_data_dump_dir" >&2
  exit -1
fi


if [[ "$SKIP_BINARY" = true ]]; then
  if [[ "$RUN_VERBOSE" = true ]]; then
    echo "Notice: Skipping binary-parse phase."
  fi
else
  # Get Indexes
  INDEX_LIST=`./c_parser -4Df $BASE_DIR/pages-ibdata1/FIL_PAGE_INDEX/0000000000000003.page -t $BASE_DIR/dictionary/SYS_INDEXES.sql 2>/dev/null |grep PRIMARY |cut -f4,5 |sort -n |uniq`

  # Gert table names and get structure of each table from the source "struct database"
  echo "$INDEX_LIST" | while read -r table_info; do
    #       Table_ID File_ID
    # echo "$table_info"
    index_id=`echo "$table_info" |cut -f1`
    file_name=`printf %016d.page $(echo "$table_info" |cut -f2)`
    # printf "Index: %d, File: %s\n" $index_id $file_name

    # Get each DB Name
    table_name=`./c_parser -4Df $BASE_DIR/pages-ibdata1/FIL_PAGE_INDEX/0000000000000001.page -t $BASE_DIR/dictionary/SYS_TABLES.sql 2>/dev/null |cut -f5,4 |sort |uniq |egrep "\s$index_id$" |cut -f1 | sed -e "s/$ORIGINAL_DB_NAME\///g" -e's/[\"\\]//g'`

    if [[ -z $table_name ]]; then
      printf "\x1B[31mTable name for Index %d not found!\x1B[0m\n" $index_id 1>&2

    else
      # Skip non-useful tables
      if listcontains "$SKIP_TABLES" $table_name; then
        if [[ "$RUN_QUIET" = false ]]; then
          printf "\033[1mSkipping table %s\033[0m\n" $table_name
        fi
        continue
      fi

      db_struct_destination="$table_struct_dir/$table_name.sql"
      if [[ "$RUN_VERBOSE" = true ]]; then
        printf "Processing: %s\n" "$table_name"
      fi
      # printf "Saving to file: -%s-\n" "$db_struct_destination"

      `mysqldump --skip-add-drop-table --skip-set-charset --skip-add-locks --skip-disable-keys -d -u$STRUCT_DB_USER -p$STRUCT_DB_PASS $STRUCT_DATABASE_NAME $table_name >"$db_struct_destination"`

      data_destination="$sql_data_dump_dir/$table_name"
      sql_destination="$sql_data_dump_dir/$table_name.sql"
      `./c_parser -6f $BASE_DIR/pages-ibdata1/FIL_PAGE_INDEX/$file_name -t $db_struct_destination -o $data_destination -l $sql_destination -b $BASE_DIR/pages-ibdata1/FIL_PAGE_TYPE_BLOB`
    fi

    # printf 'Table: %s\n' "$table_name"
    # break
  done
fi

if [[ "$SKIP_DATABASE" = true ]]; then
  if [[ "$RUN_VERBOSE" = true ]]; then
    echo "Notice: Skipping database import phase"
  fi
else
  
  for sql_file in $(ls $sql_data_dump_dir | grep "sql$"); do
    if [[ "$RUN_VERBOSE" = true ]]; then
      printf "Restoring file %s into database \n" $sql_file
    fi

    `cat $sql_data_dump_dir/$sql_file |sed -e 's/REPLACE//' |mysql --local-infile -u$DEST_DB_USER -p$DEST_DB_PASS $DEST_DATABASE`
  done
fi

if [[ "$RUN_QUIET" = false ]]; then
  echo " ####### DONE ####### "
fi