Harley77-Mysqld: Difference between revisions
m (New page: This will be a page to document my fix for a mysql issue I experienced. Coming soon.)
No edit summary
|(8 intermediate revisions by 3 users not shown)|
|Line 1:||Line 1:|
Latest revision as of 17:39, 25 March 2015
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.
The solution (manual)
1. Login as root then as the Zimbra user
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
su - 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
su - 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.
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! ***********************"