Things n' Stuff Thoughts about the universe in general

The following procedure can be used to recover InnoDB database tables from a backup of a MySQL server that had the “innodb_file_per_table” setting but all the metadata (in the “ibdata*” files) was lost – for example due to a server crash.

The process involves two steps:

1. Recover the table structure from the .frm files
2. Recover the data from the .ibd files (InnoDB tablespace)

There is a lot of copying the backed up files over and over to the MySQL datadir, so its useful to have the backup available on the database server machine. In my setup the backup for the databases was copied to the directory “backup” under the database’s datadir, so – for example – for the table somedb.sometable there exist files somedb/backup/sometable.frm and somedb/backup/sometable.ibd.

Additionally the process for recovering the table structures creates a lot of superfluous metadata in the InnoDB data files, so after the first stage I’m going to destroy the InnoDB data files and let the InnoDB engine re-generate them – as a result any existing InnoDB tables will be destroyed. This is important so I’ll iterate: using the procedure detailed here will destroy any existing and working InnoDB databases! So this procedure is useful to recover a destroyed database server to a new server or as a temporary measure on a temporary server to be able to dump the data to SQL files that will later be loaded into an existing server.

There likely a way to do this which is less heavy handed – for example, check out this article from Percona’s MySQL blog, but for my purpose this is enough.

Recover the table structures

This process involves creating a dummy table with the correct name and then copying the backed-up .frm file over the new file, and letting MySQL reread the .frm file. We can then dump the CREATE TABLE command to a file to be used later. For this process to work, the MySQL server needs to have InnoDB recovery mode set to max, so start by stopping the MySQL server, edit the MySQL my.cnf configuration file and add the directive innodb_force_recovery=6 in the [mysqld] section, then start the MySQL server again.

1. Change to the MySQL datadir – usually /var/lib/mysql
2. Select a table to recover – for example lets say we want to recover the table sometable in the schema somedb.
3. Use the MySQL command line client to open a root level connection to the server and change to the somedb database.
4. Create a dummy table with the same name: CREATE TABLE sometable (a int) engine=innodb
5. Exit the MySQL client and copy the old .frm file over the new one: cp somedb/backup/sometable.frm somedb/sometable.frm, and don’t forget to make sure MySQL has access to the restored file: chown mysql:mysql somedb/sometable.frm
6. Connect to the MySQL database again issue SHOW CREATE TABLE sometable to get the correct table DDL and store it in a file for later use

The following bash script can be used to automate the process but it requires some setup, as discussed above:

1. cd /var/lib/mysql
2. service mysqld stop
3. rm -f ib*
4. service mysqld start
5. service mysqld stop
6. Edit /etc/my.cnf and enable the option innodb_force_recovery=6 in the [mysqld] section.
7. service mysqld start

Now run the script:

(MYSQL="mysql -uroot -pPASSWORD"; for file in */backup/*.ibd; do table="$(basename ${file%%.ibd})"; schema="$(dirname $(dirname $file))"; [ -f "$schema/backup/$table.frm" ] || continue; rm -f $schema/$table.*; $MYSQL $schema -e "CREATE TABLE $table (a int) engine=innodb"; cat $schema/backup/$table.frm > $schema/$table.frm; chown mysql:mysql $schema/$table.frm; $MYSQL $schema -e "SHOW CREATE TABLE $table\G" | tail -n+3 | sed -e 's/Create Table: //' | cat > $schema/backup/$table.create.sql; $MYSQL $schema -e "DROP TABLE $table"; done)

After running the script, we need to clean up in preparation for the next step:

1. service mysqld stop
2. Edit /etc/my.cnf and remove the innodb_force_recovery field
3. rm -f ib*
4. service mysqld start

Recover the actual tables

The process for recovering the actual data required recreating the original table description (using the DDL we created in the previous stage) – which will create a new MySQL table definition and an empty InnoDB tablespace. We can then instruct the InnoDB engine to drop the newly created tablespace, copy over our backed up data file and tell it to import the old tablespace as the data for the table.

The main problem with this process is that the tablespace identifier number in the imported tablespace file must match the tablespace identifier InnoDB assigned the table when it was created – as it is hard to synchronize the order of creating the recovered tables with the tablespace identifiers in the backed-up files, we are in a bit of trouble. The recommended way to address this – according to the wisdom of the internets – is to try to import the table and if it fails, drop the created table and create it again, which will increment the numeric identifier and then you can try again. Obviously this does not scale and worse – if you try to recover multiple tables you have to get your order of them just right, otherwise you won’t be able to create a table with the correct identifier – because it has already been created in a failed attempt.

The article linked above offers a way to solve this by modifying the InnoDB data files (using a utility) for each created table before we try the import. This is rather involved and I rather use a simple solution.

My approach is as follows:

1. Lookup the tablespace identifier number in each tablespace data file to be imported – this is done by a bit of perl code that can be seen in the script below.
2. Order all the datafiles according to the tablesapce identifier.
3. Create the tables in the correct order, creating dummy table for any “holes” in the order, and import the data when the tablespace identifier matches.

This obviously requires that we know exactly where the current tablespace number in InnoDB data files is, so we start with clean InnoDB metadata files – the end of the previous step should have taken care of that. We then run this script from the MySQL datadir:

(MYSQL="mysql -uroot -pPASSWORD"; oldifs="$IFS"; IFS="|"; files=( $(for file in */backup/*.ibd; do table="$(basename ${file%%.ibd})"; schema="$(dirname $(dirname $file))"; [ -f "$schema/backup/$table.create.sql" ] || continue; perl -e 'open INF,"<".$ARGV[0]; binmode(INF); seek(INF,0x25,0); read(INF,$id,1); print ord($id);' -- $schema/backup/$table.ibd; echo " $schema $table"; done | sort -n | tr '\n' '|') ); IFS="$oldifs"; index=1; for1; do read id schema table <<< ${files[i]}; while [ "$index" -lt "$id" ]; do $MYSQL $schema -e "CREATE TABLE temp_table (a int) engine=innodb"; $MYSQL $schema -e "DROP TABLE temp_table"; index=$(( $index + 1 )); done; echo "Creating $schema.$table"; $MYSQL $schema < $schema/backup/$table.create.sql; $MYSQL $schema -e "ALTER TABLE $table DISCARD TABLESPACE"; cp $schema/backup/$table.ibd $schema/$table.ibd; chown mysql:mysql $schema/$table.ibd; $MYSQL $schema -e "ALTER TABLE $table IMPORT TABLESPACE"; index=$(( $index + 1 )); done; for1; do read id schema table <<< ${files[i]}; $MYSQL $schema -e "OPTIMIZE TABLE $table"; done)

When the dust settles you should have all your tables loaded and running on the MySQL server.

1. i=0;i<${#files[@]};i=i+1 [↩] [↩]

This entry was posted on Saturday, April 27th, 2013 at 01:00 and is filed under Articles, Software. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.