I recently had to restore a corrupted table that had resulted from a full disk. I had been using automysqlbackup as my backup script. Each database had a corresponding backup file. It would have been easy to use a text editor to edit out the tables that you didn't want to restore but this database had grown to 1G.
STEP 1: Restore table structure
If the table structure is corrupted, you need to retrieve the CREATE TABLE statements from the backup. To do this you can use grep:
zcat backup.sql.gz | grep -A 20 'CREATE TABLE `tablename1`' > tablestructure.sql
This command will create tablestructure.sql containing the CREATE TABLE statement and 20 lines after that. You may have to change the parameter from 20 to whatever to get the entire statement.
Once tablestructure.sql had been edited to only contain the proper CREATE TABLE statement, you may now restore the table structure.
mysql -u user -p dbname < tablestructure.sql
STEP 2: Extract and restore the table data
This command will create a sql file for the table.
zcat backup.sql.gz | grep 'INSERT INTO `tablename1`' > tabledata.sql
If the resulting sql file is small enough, you may load the table straight back into the database.
mysql -u user -p dbname < tabledata.sql
OPTIONAL: If you have a huge table and want to partially restore or batch things, you may want to split the table data into multiple lines.
split -l 100 tabledata.sql
This will create multiple files of 100 lines each named xaa, xab, xac, etc. You may then load each file separately.
If some rows are already on the table and you just don't want the INSERT to fail on a duplicate primary key, you can use sed to add the IGNORE option to the INSERT statement. The command below simply does a search and replace on the source file and writes it to a new file.
sed 's/INSERT/INSERT IGNORE/' < tabledata.sql > newtabledata.sql
That should do the trick.
STEP 1: Restore table structure
If the table structure is corrupted, you need to retrieve the CREATE TABLE statements from the backup. To do this you can use grep:
zcat backup.sql.gz | grep -A 20 'CREATE TABLE `tablename1`' > tablestructure.sql
This command will create tablestructure.sql containing the CREATE TABLE statement and 20 lines after that. You may have to change the parameter from 20 to whatever to get the entire statement.
Once tablestructure.sql had been edited to only contain the proper CREATE TABLE statement, you may now restore the table structure.
mysql -u user -p dbname < tablestructure.sql
STEP 2: Extract and restore the table data
This command will create a sql file for the table.
zcat backup.sql.gz | grep 'INSERT INTO `tablename1`' > tabledata.sql
If the resulting sql file is small enough, you may load the table straight back into the database.
mysql -u user -p dbname < tabledata.sql
OPTIONAL: If you have a huge table and want to partially restore or batch things, you may want to split the table data into multiple lines.
split -l 100 tabledata.sql
This will create multiple files of 100 lines each named xaa, xab, xac, etc. You may then load each file separately.
If some rows are already on the table and you just don't want the INSERT to fail on a duplicate primary key, you can use sed to add the IGNORE option to the INSERT statement. The command below simply does a search and replace on the source file and writes it to a new file.
sed 's/INSERT/INSERT IGNORE/' < tabledata.sql > newtabledata.sql
That should do the trick.
Comments