Hi, using mysql 5.0.45
I used ibbackup to take a binary backup of our DB in production in order to restore it on a dev server using the following:
/usr/local/mysql/bin/mysqldump --no-data $DATABASE > $BACKUP_DIR/$DATABASE_schema.sql
/var/lib/mysql/backup/ibbackup --compress 6 $PRODUCTION_MY_CNF $BACKUP_MY_CNF
The first line dumps the sql out to a text file.
The second line uses ibbackup to dump all the innodb databases, I have 2 innodb DBs.
Once the backup is complete I then copy the files to my dev server and restore them first by uncompressing and then piping in the sql from the first line above.
./ibbackup --uncompress --apply-log $RESTORE_MY_CNF
echo "create database director" | /usr/local/mysql/bin/mysql
echo "/usr/local/mysql/bin/mysql director < $BACKUP_DIR/director_schema.sql"
/usr/local/mysql/bin/mysql director < $BACKUP_DIR/director_schema.sql
Both DBs seem to restore with no errors but when I look in the logs, I have the following for every single table in my second database:
080412 13:59:06 InnoDB: Error: table 'director/alert'
InnoDB: in InnoDB data dictionary has tablespace id 473,
InnoDB: but a tablespace with that id does not exist. There is
InnoDB: a tablespace of name ./director/alert.ibd and id 76, though. Have
InnoDB: you deleted or moved .ibd files?
InnoDB: Please refer to
InnoDB:
http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting.html
InnoDB: for how to resolve the issue.
The files are in the database directory but it looks like a mismatch in the tablespace id. Any idea where i am going wrong?
Edited 2 time(s). Last edit at 04/13/2008 02:49PM by Doug Hairfield.