Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

My server had to be re-installed yesterday so they replaced the hard drive and hooked the old one up via USB. I grabbed the database files (/var/lib/mysql) and downloaded them and then I erased all the db files for my local test server and replaced it with the ones from my live server.

Everything worked fine at first and then I noticed that a few tables were showing as "in use". I discovered the InnoDB wasn't working properly and eventually found that a fix was to remove the ib_logfile0 and ib_logfile2 files. Now my MySQL server won't start and I get these errors:

110716 10:19:04  mysqld started
110716 10:19:04 [Warning] option 'max_join_size': unsigned value 18446744073709551615 adjusted to 4294967295
110716 10:19:04 [Warning] option 'max_join_size': unsigned value 18446744073709551615 adjusted to 4294967295
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
110716 10:19:04  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 7.
InnoDB: You may have to recover from a backup.
110716 10:19:04  InnoDB: Page dump in ascii and hex (16384 bytes):
 len 16384; hex 0000000000000000cd12deee693d9fd84ee8bdf2000000070000000000000000000000006935b4760000000000000000000000000000000000000000180000000000000013ee000000000000034f000000000000000d0a00000008000000090000000d0a0000000b0000000c0000$
110716 10:19:05  InnoDB: Page checksum 3392292161, prior-to-4.0.14-form checksum 3716350408
InnoDB: stored checksum 0, prior-to-4.0.14-form stored checksum 0
InnoDB: Page lsn 1323875826 7, low 4 bytes of lsn at page end 0
InnoDB: Page number (if stored to page already) 0,
InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 26933
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 7.
InnoDB: You may have to recover from a backup.
InnoDB: It is also possible that your operating
InnoDB: system has corrupted its own file cache
InnoDB: and rebooting your computer removes the
InnoDB: error.
InnoDB: If the corrupt page is an index page
InnoDB: you can also try to fix the corruption
InnoDB: by dumping, dropping, and reimporting
InnoDB: the corrupt table. You can use CHECK
InnoDB: TABLE to scan your table for corruption.
InnoDB: See also InnoDB: http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html
InnoDB: about forcing recovery.
InnoDB: Ending processing because of a corrupt database page.
110716 10:19:05  mysqld ended

I really need those files to work so any advice is much appreciated.

share|improve this question
1  
In what circumstances was the original machine shut down (cleanly/HD crash/pulled power etc) and when did you/they copy the /var/lib/mysql files into place (server running/MySQL running/disk mounted on other machine etc)? –  Andy Jul 16 '11 at 14:12

2 Answers 2

up vote 3 down vote accepted

Wow, you really did a number on your database there. You can restore by copying over MySQL data files, but it sounds like you used a little too much mallet. The correct way to do it:

  • Make sure MySQL IS NOT RUNNING on the machine you're trying to restore onto. Quadruple check with ps to make sure nothing of a MySQL-like nature is lurking. Boot into single user mode if you have to. If MySQL is running, you'll hose all sorts of things.
  • Copy over both the contents of /var/lib/mysql (or wherever your MySQL data is), as well as the my.cnf that your server was using. It is important to ensure that the config is the same when running InnoDB, because (as you noted) otherwise InnoDB doesn't work. (The crucial parameters are your InnoDB log size/count/etc, and file-per-table -- if they're different, you really ain't going nowhere).

When you start things up, especially if you're restoring a machine that died a violent and untimely death, expect the server to take a while to get itself up and running, as it needs to do all sorts of internal consistency checks (the equivalent of an fsck) before it's ready for service.

share|improve this answer

As @womble said, but with a little addendum:

It's really useful to create the backup with innobackupex, and move that to the new server:

first run:

innobackupex /path/to/backupfiles

and after it succeeded:

innobackupex --apply-log /path/to/backupfiles

This would create a usable file-based backup (what only needed to copy over) even from a running server.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.