[SOLVED] mysql not starting after restoring from backup

[jschnip]

We had a drive problem on our Zimbra server (one of the drives in the raid failed) - which eventually led to the re-installation of the OS (was centos4, now cent5.1) Before the OS was reinstalled, the entire /opt/zimbra directory was saved. After following the backup/restore instructions here: Open Source Edition Backup Procedure - Zimbra :: Wiki
it appears that mysql does not want to start. I have tried the upgrade method as well as the directory copy. With the upgrade method, my zmsetup.log ends with:
Verifying /opt/zimbra/conf/my.cnf
Starting mysql
UPGRADE FAILED - exiting

I hve tried starting mysql manually with
mysql.server start
which returns no errors - but then if I use 'mysql mysql' - I am unable to connect to the server.

Just for reference:
old server CentOS 4 - ZCS open source 4.5.0_GA_612
new load is CentOS 5.1 - and we have been using the same rev (although we had to create a few lib symlinks since there was not a RHEL_5 release available for 4.5.0

Its possible there was some corruption to the mysql data due to our drive problems - is there anyway to do a repair on that data? or check the mysql data? Or any other advice?

[brian]

which version of zcs are you installing? you'll need binaries for RHEL5. otherwise check /opt/zimbra/db/data/<hostname>.err for why mysql can't start.

[jschnip]

Wow, yet another /etc/hosts problem - I saw this in the /opt/zimbra/db/data/<hostname>.err file

080327 19:24:38 mysqld started
080327 19:24:39 InnoDB: Started; log sequence number 0 2064918762
080327 19:24:39 [ERROR] Can't start server: Bind on TCP/IP port: Cannot assign requested address
080327 19:24:39 [ERROR] Do you already have another mysqld server running on port: 7306 ?
080327 19:24:39 [ERROR] Aborting

So I added 127.0.0.1 localhost to /etc/hosts - and mysql started - Great Help there! - Thanks

So now watching that log file, shortly after mysql starts, I get:

080327 19:27:10 mysqld started
080327 19:27:11 InnoDB: Started; log sequence number 0 2064918762
080327 19:27:11 [Note] /opt/zimbra/mysql/libexec/mysqld: ready for connections.
Version: '5.0.45-log' socket: '/opt/zimbra/db/mysql.sock' port: 7306 Source distribution
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 32.
InnoDB: You may have to recover from a backup.
080327 19:28:07 InnoDB: Page dump in ascii and hex (16384 bytes):
...<dump>...;InnoDB: End of page dump
080327 19:28:08 InnoDB: Page checksum 1758045174, prior-to-4.0.14-form checksum 2703953827
InnoDB: stored checksum 1474257160, prior-to-4.0.14-form stored checksum 2703953827
InnoDB: Page lsn 0 1434260149, low 4 bytes of lsn at page end 1434260149
InnoDB: Page number (if stored to page already) 32,
InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 71
InnoDB: Page may be an index page where index id is 0 256
InnoDB: (index i_flags_date of table mboxgroup10/mail_item)
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 32.
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: MySQL :: MySQL 5.0 Reference Manual :: 13.2.8.1 Forcing InnoDB Recovery
InnoDB: about forcing recovery.
InnoDB: Ending processing because of a corrupt database page.

the install did complete successfully! - but what should we do about the corruption errors? any advice?

[jschnip]

Turns out quite a few of the mboxgroup database tables were corrupt - which prohibited us from upgrading to 5.0.4 (and probably would have caused usage problems).

Each time a corrupt table was identified - mysql would dump in its error log. The only way we were able to restore these was by following this procedure for each database that contained a corrupt table:

(assuming that zcs has been stopped)
mysql.server stop
vi /opt/zimbra/conf/my.cnf - add innodb_force_recovery = 4 
mysql.server start
mysqldump -S /opt/zimbra/db/mysql.sock -u zimbra -p mboxgroup10  > mboxgroup10.sql (mysql package will have to be installed on the server, as mysqldump is not included with zimbra)
mysql
drop database mboxgroup10;
quit;
mysql.server stop
comment out innodb_force_recovery = 4 in my.cnf
mysql.server start
mysql
create database mboxgroup10;
quit;
 mysql  -S /opt/zimbra/db/mysql.sock -u zimbra -p mboxgroup10 < mboxgroup10.sql

After restoring 10 or so databases, we've now successfully taken a corrupt install and upgraded all the way to 5.0.4 ! Thanks go to the forums for all the prompt responses, hints and help along the way.

[menno.pieters]

I have a similar problem: any suggestions if mysqldump also fails? Running mysqldump or simply running a SELECT on mboxgroup4.mail_item crashes mysqld.