Harley77-Mysqld

Revision as of 19:11, 18 February 2009 by Harley77 (talk | contribs)

UPDATE

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

su zimbra

2. Stop Zimbra

zmcontrol stop

3. Go back to root

exit

4. Edit your my.cnf file.

pico /opt/zimbra/conf/my.cnf    

5. Add the follwing below the [mysql] section.

innodb_force_recovery = 3

6. Save and exit

7. Login as zimbra

su zimbra

8. Start mysql server

mysql.server start

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

mysql.server stop

16. exit to root exit

17. Edit my.conf, remove the innodb_force_recovert entry.

pico /opt/zimbra/conf/my.cnf 

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

su zimbra

21. Start mysql server

mysql.server start

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

mysql.server stop

27. Start zimbra

zmcontrol start
 

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.

Jump to: navigation, search