Restore innodb from frm and ibd files only

findCreate.py

import argparse
import os
import re
parser = argparse.ArgumentParser(description='Process file.')
parser.add_argument('-f', '--file', help='The filename', required=True)

args = parser.parse_args()

def create_file(file):
    print(file)
    inFile = open(os.path.join("/Users/danielpett/desktop/museums/", file)).read()
    print(os.path.join("/Users/danielpett/desktop/museums/", file))
    sql = os.path.splitext(file)[0]+".sql"
    outFile = open(os.path.join("/Users/danielpett/desktop/statements/", sql), "w")
    x = inFile.split("# CREATE TABLE Statement:")[1]
    x = re.sub(r'[^\x00-\x7F]+',' ', x)
    # This honky bit that follows needs work and is specific to the tables we were fixing
    x = x.replace("'width'","width")
    x = x.replace("user's","users")
    x = x.replace("'tools'","tools")
    x = x.replace("'alt'", "alt")
    x = x.replace("'und'", "und")
    x = x.replace("image's", "images")
    x = x.replace("'title'", "title")
    x = x.replace("'title", "title")
    x = x.replace("term's", "terms")
    x = x.replace("row's", "rows")
    x = x.encode('utf-8')
    x = x.replace("'200px'", "200px")
    
    # Close files
    outFile.write(x)
    outFile.close()

create_file(args.file)

splitAndRestore.sh

# Make a temp directory for data
mkdir /Users/danielpett/Desktop/museums/tmp/
# Make a folder for frmTxt converts
mkdir /Users/danielpett/Desktop/museums/frmtxt/
mysql -uroot -p{password} -e "CREATE DATABASE museums;"
# Loop through files and do stuff
for file in /Users/danielpett/Desktop/museums/*.frm;
 do
    for ((i = 0; i < 1; i++))
    do
        name=${file##*/}
        base=${name%.txt}
        drop=${base%.*}.txt
        table=${base%.*}
        sql=${base%.*}.sql
        # Read the frm files and get the sql etc
        mysqlfrm --server=root:gr8tcour1@localhost:3306 $file > /Users/danielpett/Desktop/museums/frmtxt/$drop --diagnosti$
        # Use python to split out the create statements
        python findCreate.py -f $drop
        # Import the SQL - you need an empty db called museums in mysql for this
        mysql -uroot -p{password} museums < /Users/danielpett/Desktop/statements/$sql
        # Copy some files around so you don't break stuff
        cp /Users/danielpett/Desktop/museums/$table.frm /Users/danielpett/Desktop/museums/tmp/
        cp /Users/danielpett/Desktop/museums/$table.ibd /Users/danielpett/Desktop/museums/tmp/
        # Discard the table space
        mysql -uroot -p{password} museums -e "ALTER TABLE $table DISCARD TABLESPACE"
        # Copy in the ibd file to relink
        cp /Users/danielpett/Desktop/museums/tmp/$table.ibd /usr/local/var/mysql/museums/
        # Check ownership 
        sudo chown mysql:mysql /Users/danielpett/Desktop/museums/tmp/$table.ibd
        # This will probably fail with big tables on low powered machines
        mysql -uroot -p{password} museums -e "ALTER TABLE "$table" IMPORT TABLESPACE;"
        # Throw in a sleep to stop it bugging out
        sleep 20s
    done
done