Harley77-Mysqld


UPDATE

I've made a script that automates the process below. See the end of this page.


The Issue

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.

The solution (manual)

1. Login as root then as the Zimbra user

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.


The solution (script style)

WARNING!!!!! There is virtually NO error checking in the script as it sits right now. So, if something screws up, it will happily keep right on going, potentially killing your database in the process. I'm just putting this here because it works great on my setup and I'm using it as a part of an hourly syncing setup for our cold standby server.

You've been warned.


#!/bin/bash
# Dump and restore. Use with Rsynced Zimbra to resolve the out-of-sequence issues.
# This should be run as root.

# Remove the # below for debugging
# set -x

# Put your mySQL ROOT password below.

SQLPASSWD="PUTPASSWORDHERE"

clear

echo "
**** Starting mysql.server ****"
su - zimbra -c mysql.server start

echo ".......Waiting a bit for mysql to fully start."
sleep 20


if [ -f /tmp/mysql.db.list ]; then
rm -Rf /tmp/mysql.db.list
else
echo " "
fi

echo "
**** Creating database list ****"
su - zimbra -c "/opt/zimbra/bin/mysql -NB -e 'show databases' | grep mbox > /tmp/mysql.db.list"


if [ -d /tmp/mysql.sql ]; then
echo " "
else
su - zimbra -c 'mkdir /tmp/mysql.sql'
fi

echo "
**** Dumping Zimbra database ****"
su - zimbra -c "/opt/zimbra/mysql/bin/mysqldump zimbra -S /opt/zimbra/db/mysql.sock -u root --password=$SQLPASSWD > /tmp/mysql.sql/zimbra.sql"


echo "
**** Dumping mailboxes ****"
for db in `< /tmp/mysql.db.list`;
do
su - zimbra -c "/opt/zimbra/mysql/bin/mysqldump $db -S /opt/zimbra/db/mysql.sock -u root --password=$SQLPASSWD > /tmp/mysql.sql/$db.sql"
echo -n "#"
done

echo -e " "
echo  "
**** Dropping mailboxes ****"
for db in `< /tmp/mysql.db.list`;
do
/opt/zimbra/bin/mysql -e "drop database $db"
echo -n "#"
done

echo -e " "
echo "
**** Dropping zimbra database ****"
/opt/zimbra/bin/mysql -e 'drop database zimbra'


echo "
**** Stopping Sql Server ****"
su - zimbra "mysql.server stop"

echo "
**** Deleting ibdata1 and ib_log files ****"
rm /opt/zimbra/db/data/ibdata1
echo -n "Pew! "
rm /opt/zimbra/db/data/ib_*
echo -n "Pew! Pew!"
echo "
Files deleted"


echo "
**** Starting mysql.server ****"
su - zimbra 'mysql.server start'

echo "....... Waiting for mysql to fully start."
sleep 20

echo "
**** Creating Zimbra database ****"
/opt/zimbra/bin/mysql -e "create database zimbra character set utf8"


echo "
**** Creating Mailbox Databases ****"
for db in `< /tmp/mysql.db.list`;
do
/opt/zimbra/bin/mysql -e "create database $db character set utf8"
echo -n  "#"
done


echo "
**** Importing Zimbra Database ****"
/opt/zimbra/bin/mysql zimbra < /tmp/mysql.sql/zimbra.sql



echo "
**** Importing Mailbox Databases ****"

for sql in /tmp/mysql.sql/mbox*
do
/opt/zimbra/bin/mysql `basename $sql .sql` < $sql
echo -n  "#"
done

echo -e " "
echo "
**** Stopping mysql.server ****"
su - zimbra 'mysql.server stop'

echo "********************* All Finished! ***********************"
Jump to: navigation, search