I have yet to be able to perform a backup and recovery with any success. I always end up with a corrupt database upon recovery. Since so many others here seem to be able to make this work, I feel like it must be something I’ve got wrong. So, I’ll provide all the details here. Can someone please tell me what the problem might be?
CentOS 5.5 with MySQL Community Edition 5.0.67
Backup: innobackupex /opt/backup/mysql-backups
Prepare: innobackupex --apply-log /opt/backup/mysql-backups/
Shut down MySQL
Using the “–copy-back” command on innobackupex fails with an error on the “mysql/innodb” directory. So, I copy the files back in place using “cp -a” then use chown to restore the files to ‘mysql’ ownership.
Upon starting MySQL again, it goes through crash recovery and the database starts. I log in using the ‘mysql’ client program. As soon as I try to select data from a table, I get a MySQL error “ERROR 1146 (42S02): Table ‘x’ doesn’t exist”. This appears to be the case for all of my InnoDB tables. The ‘mysql’ and ‘information_schema’ databases appear to work.
What am I doing wrong? Or, am I using an incompatible version of MySQL? I’m stumped!
UPDATE - 10/12/11
I tried removing the MySQL 5.0.57 Community Edition that was installed on my CentOS 5.5 server. I replaced it with MySQL 5.0.77, which was available in the CentOS 5.5 repository. Now, I am able to successfully backup and restore.
So, is there a limitation with respect to the Community Edition of MySQL? I don’t recall seeing anything like that listed in the documentation.
If this is my answer, then the problem I see is that it’ll be hard to move to MySQL 5.1 and/or 5.5 because those do not appear in the CentOS Yum repo, as far as I can tell.