I've made a script that automates the process below. See the end of this page.
Hi. Has this ever happened to you?
InnoDB: Error: page 0 log sequence number 0 933369315 InnoDB: is in the future! Current system log sequence number 0 43685. InnoDB: Your database may be corrupt or you may have copied the InnoDB InnoDB: tablespace but not the InnoDB log files. See InnoDB: http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html InnoDB: for more information.
Is The mysqld process eating away every ounce of CPU time like it was free lobster?
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 7590 zimbra 16 0 484m 402m 5036 S 98 11.3 2151:46 mysqld 13033 zimbra 19 0 1770m 1.1g 46m S 13 32.8 47:28.14 java 2148 root 15 0 2196 1132 856 R 2 0.0 0:00.03 top 1 root 16 0 1564 528 460 S 0 0.0 0:01.80 init 2 root RT 0 0 0 0 S 0 0.0 0:00.87 migration/0 3 root 34 19 0 0 0 S 0 0.0 0:00.00 ksoftirqd/0 4 root RT 0 0 0 0 S 0 0.0 0:00.00 watchdog/0 5 root RT 0 0 0 0 S 0 0.0 0:03.04 migration/1 6 root 34 19 0 0 0 S 0 0.0 0:00.00 ksoftirqd/1
Well, I may have an answer. At least an answer that worked for me.
Talking completely out of my butt, it has to do with ibdata1, at least, it did in my case. It happened after a server crash, syncing with a backup server and re-syncing back. Somewhere, it became "out of sorts".
Now, After a nice email exchange with Zimbra tech support (a certian Chandrashekhar Bhosle was my helper) who pointed me in the right direction I finally achieved a solid procedure for curing this ailment. Using the backup server to do my testing, I could resync with the live server over and over so I could revert it back to it's mysql hogging state and retry various procedures until I nailed one down that worked every time. The following is the exact procedure, I recommend STRONGLY that you backup, backup, BACKUP before attempting any of the following.
Also, My machine specs: OS: Ubuntu 6.02LTS 32-bit Zimbra: 5.0.13 Network Edition.
1. Login as root then su Zimbra
2. Stop Zimbra
3. Go back to root
4. Edit your my.cnf file.
5. Add the follwing below the [mysql] section.
innodb_force_recovery = 3
6. Save and exit
7. Login as zimbra
8. Start mysql server
9. Dump out a list of databases to a temporary list.
mysql -NB -e "show databases" | grep mbox > /tmp/mysql.db.list
10. Make a temp directory to put stuff in. mkdir /tmp/mysql.sql
11. Dump your zimbra database to a file.
/opt/zimbra/mysql/bin/mysqldump zimbra -S /opt/zimbra/db/mysql.sock -u root / --password=ROOT_SQL_PASSWORD > /tmp/mysql.sql/zimbra.sql
12. Dump your mailbox databases to files.
for db in `< /tmp/mysql.db.list`; do /opt/zimbra/mysql/bin/mysqldump $db -S /opt/zimbra/db/mysql.sock -u root / --password=ROOT_SQL_PASSWORD > /tmp/mysql.sql/$db.sql echo -e "Dumped $db\n" done
13.Drop your mailbox databases
for db in `< /tmp/mysql.db.list` do mysql -u root --password=ROOT SQL PASSWORD -e "drop database $db" echo -e "Dropped $db" done
14. Drop your zimbra database
mysql -e "drop database zimbra"
15. Stop mysql server
16. exit to root exit
17. Edit my.conf, remove the innodb_force_recovert entry.
18. Save and exit.
19. Remove the ibdata, ib_logfile0 and ib_logfile1
rm /opt/zimbra/db/data/ibdata1 rm /opt/zimbra/db/data/ib_*
20. Login as zimbra
21. Start mysql server
22. Create zimbra database
mysql -e "create database zimbra character set utf8"
23. Create Mailbox databases
for db in `< /tmp/mysql.db.list` do mysql -e "create database $db character set utf8" echo -e "Created $db \n" done
24. Import zimbra data
mysql zimbra < /tmp/mysql.sql/zimbra.sql
25. Import mailbox data
for sql in /tmp/mysql.sql/mbox* do mysql `basename $sql .sql` < $sql echo -e "Updated `basename $sql .sql` \n" done
26. Stop mysql server
27. Start zimbra
28. Check your mysql_error.log file for errors, make sure you can login, etc. etc.
29. If its good, do the happy dance. If not, restore from backup and try something else.