A boo-boo! (See the end, I found a solution).
I was testing an upgrade for MySQL, from 5.0 to 5.1, to gain Row Based Replication instead of Statement based replication (as statements are a cause of SBR breaking, and it doesn’t proceed til I tell it to).
So, in my usual lazy, just do it, manner, installed MySQL Server 5.1, pointed the datadir at the old so that I’d have my data pretty quickly up and running.
And the installation worked, until I started MySQL.
I use hMailserver for email on my box, this uses MySQL for it’s settings and database storage (which is good actually, a config file is inflexible for multi domain environments).
The database used by hMailserver is InnoDB, most of my needs are well serviced with MyISAM.
In my rush, I didn’t do a backup. I didn’t copy the data folders, just went right over it, started, and got an error:
100610 22:15:32 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…
100610 22:15:32Â InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 1204538731.
InnoDB: Doing recovery: scanned up to log sequence number 0 1204538731
InnoDB: Page directory corruption: supremum not pointed to
It was shut down normally – using the Windows Service, the issue is that the MySQL database won’t work with the log files from the previous database for some weird reason.
I’m still yet to determine if any recovery can be done on the log files, making them usable again, however I have doubts.
I reinstalled my mailserver and did a quick setup on it today to get us operational again. And learnt a lesson – always backup.
Actually, I should have had a database backup of recent, I don’t. I have no database backup – I only have the datafiles as they are.
That instance of MySQL is rarely used, it’s most usage would be the mailserver, followed shortly by the replication I get it to do from the server in Newcastle. It is my home windows test server, I also have a linux one that I planned similar for – except that does much, much more and data loss there would very much annoy me.
So, I ask myself – why is there no backup of it then? Because, there isn’t.
I MUST use this weekend (which thankfully is a long one), to:
A) Figure out if that log is recoverable, and move back to it, then onward to MySQL 5.1 using a mysqldump.
B) Upgrade To MySQL 5.1 for the windows and Linux box here, and the box in Newcastle.
C) Setup replication using Row Based Replication for those databases I have that need it.
D) Design a regular backup script which backs up the databases locally, something like weekly (With email reporting).
E) Update regularly. 5.0 is really old now.
Reconsider using hMailserver is also on the cards, it’s not it’s fault, in fact, it has the ability to use it’s own seperate instance of MySQL (and does by default, and that’s what I currently have in Newcastle). But, I want the ability to access that data – later for user purposes, greylisting statistics and so forth.
UPDATE: I found the issue! It turns out the ibdata1 file I needed, was in C:\MySQL Datafiles. In the Program Files folder was a 10MB ibdata1 file, which is why the error messages were occurring.
The Database came right back to life the very moment I shut it down. Copied the file from C:\MySQL Datafiles to the version 5.0 program files folder, and started the version 5.0.45 MySQL server with that file, and the log files intact.
Remember to remove the innodb_force_recovery option if it was added, and it can help to change default-storage-engine=INNODB to default-storage-engine=MyISAM – so that the server starts with MyISAM instead of INNODB.
This will not work if you don’t have consistent logfiles and data files – but otherwise, it’s a save!
Now, the plan is to dump all that data using mysqldump, and then go forth and better my lazy processes for database server management.