What am I doing wrong?

  • Filter
  • Time
  • Show
Clear All
new posts

  • What am I doing wrong?

    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
    XtraBackup 1.6.3

    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.

  • #2
    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.


    • #3

      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.


      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!


      • #4
        Dave, I think you've probably found the problem. If you can submit a description of the problem to a bug in launchpad, our QA team will get to it sooner or later and we might make this work as expected. (And if you need help with any of those overdue tasks, you know who to call...)


        • #5
          Well, apparently what I thought might be the problem is not. I just finished another test on my large database, and I have the same problem. All of my InnoDB tables are corrupt. I can see the list of tables when I run "show tables;", but attempting to select from any of them gives me a "Table doesn't exist" error.

          I'm at a loss as to why this is happening, but it seems clear that this tool isn't going to help me. I was working toward using LVM snapshots before I came across it. Looks like I'm headed back in that direction, again.


          • #6
            Thinking that it could be my old archive of our database that was introducing this corruption, I decided to try something more real-world. I made a backup of one of our running databases, archived the result using "tar cvzf", moved that archive to a new machine with the same version of MySQL (5.0.67), expanded the archive again to /var/lib/mysql, changed ownership to "mysql" and started up the MySQL service. It appeared to start up correctly and even took a little time while it applied the transaction logs to roll things forward. However, once again, when I tried to select data from a table, I got the "table doesn't exist" error. It's interesting to me that MySQL can show me the list of tables, but when I try to select from one it isn't there.

            So, I'm really sitting at Step 0, still. I'd love to be able to use this for backups, but I can't prove to myself that recovery is possible.

            Baron, you once said that you're not a fan of using 'innobackupex'. Is there a documented way of using 'xtrabackup' directly that I can follow to attempt my tests in some other way?



            • #7
              What is the output from the MySQL error log when you start MySQL on the restored database?


              • #8
                Reading through documentation, I've just discovered the 'tar4ibd' program. Duh! I'll give it a try on my backup and see if that saves me from corruption. I'll bet this has been my problem all along. Ugh!


                • #9
                  Back again, and still having no success restoring from backup. I do have more information about my corruption, however, that may or may not be helpful.

                  In my latest tests, I have used the --stream option to create an archive which I am restoring from. My command line is...

                  innobackupex --stream=tar ./ | gzip - > backup.tar.gz

                  I move the archive to a different machine, then unpack it with the command...

                  tar -ixzvf backup.tar.gz

                  Once it is unpacked, I prepare it...

                  innobackupex --ibbackup=xtrabackup_51 --apply-log .

                  Start up MySQL again and connect to check things out. I still get "table does not exist" on my InnoDB tables. A colleague started looking at things with me and noticed that the tables in question exist on disk (ie. there is a .FRM and .IBD file for them), but they are NOT in the data dictionary. We cannot find them in the information_schema.tables table. What's up with that? No wonder things are corrupted!

                  That's where I'm stuck, though. If anyone has anything helpful to add, I'm all ears.


                  • #10
                    Would you please post the output from the MySQL error log? I do not mean the error the client gets ("table doesn't exist"), I mean the MySQL server process's error log. Without this there is really nothing further I can do to diagnose the problem.


                    • #11
                      I'll post that as soon as possible. I've already started down a different path today, and I didn't have enough space available to keep the failed instance in place.


                      • #12
                        I believe I now know exactly why my earlier tests were failing and now they are succeeding. I'm pretty certain (99.9%) that I was using a configuration file from a different database server instance when I did the original tests, because I had already tweaked it to fit my test server's memory constraints. After doing more tests that succeeded, I dropped in a different configuration file and was able to reproduce the same errors that I was getting before.

                        Whew! Glad to have that nailed down. I don't understand why the configuration file changes cause this problem, but knowing about it helps me avoid the issue.

                        Baron, thanks for the input. Sorry if I had us running a virtual "wild goose chase."



                        • #13
                          It would be great to know what was failing and what configuration problem caused it.


                          • #14

                            I've tried to figure out what may have been causing my problem. I admit that I'm not what *I* would call an expert on MySQL, though. My best guess is that the "innodb_data_file_path" configuration parameter was the culprit in my problems. Originally, I was using a configuration file that contained:

                            innodb_data_file_path = ibdata1:1024M;ibdata2:256M:autoextend

                            I was trying to restore a backup from a database in which that same parameter was:

                            innodb_data_file_path = ibdata1:3072M;ibdata2:512M:autoextend

                            I suppose the difference in innodb_log_file_size could cause a problem?

                            I originally began looking more closely at the my.cnf file when I read a post in a forum somewhere in which someone had received a similar error to mine. The suggested fix was to "change the config file back". I found that by using the my.cnf from the original server that I backed up was the key to making recovery work, though, frankly, I was stunned that config file changes would cause the system to have such errors.

                            If you can shed any light on this, I'd love to hear about it.


                            • #15
                              A difference in innodb_log_file_size would absolutely cause a problem.