What am I doing wrong?

[dchicks]

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?

Thanks,
Dave

CentOS 5.5 with MySQL Community Edition 5.0.67
XtraBackup 1.6.3

Backup: innobackupex /opt/backup/mysql-backups
Prepare: innobackupex --apply-log /opt/backup/mysql-backups/
Shut down MySQL
Recovery:
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!

Thanks!

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.

[xaprb]

It’s hard to comment now that you have changed something and it works, so unless you can repeat the problem you might not be able to answer my questions accurately. If you were still having the problem, I would ask you to examine the error log of MySQL after startup. It looks to me like some configuration option was wrong after restore and MySQL wasn’t able to start InnoDB at all.

By the way, you are long overdue to upgrade. 5.0.77 is a horribly old version (28 January 2009). You need to be on the very latest 5.0.94 at least, and it’s definitely time to consider upgrading to 5.1 (again, the LATEST, not just what is in your OS vendor’s stale old repository), or preferably 5.5.

[dchicks]

Baron,

Thanks for the replies. I think I’m onto something, though.

This morning, I began testing with fresh MySQL databases, created with the mysql_install_db tool. I had nothing but success with that method, even on different versions of MySQL. That’s when I realized a tiny difference in my setup when I’m using my full database.

Because of disk space constraints, I had been running my copy of our database using a sym-link at /var/lib/mysql, pointing to another volume. My belief now is that this sym-link was somehow making some part of the innobackupex tool freak out. It appeared to copy files properly, but upon restoration they were all corrupted in some way.

I began a new test after reconfiguring MySQL to point straight to the larger volume, rather than using a sym-link. I hope to have more successful results tomorrow, when I can finish the test.

Dave

PS - Yes, I’m very well aware that we are horribly behind on versions. I took over the DBA job here about 5 months ago. I couldn’t even begin to list all of the things that are horribly wrong. One step at a time! First, I need some good, consistent backups! Thanks again!