Mysql Crash Recovery

Mysql Crash Recovery

BUG "http://bugzilla.zimbra.com/show_bug.cgi?id=15797"

Mysql Crash recovery is performed when Mysql stops responding and there is error in the /opt/zimbra/db/data/hostname.err [Please check the BUG above for more information]

Steps to perform Recovery:

• Edit /opt/zimbra/conf/my.cnf: Put an entry

innodb_force_recovery = 3

in the [mysqld] section

• Dump the database: mysqldump zimbra and mboxgroup DB's

• Drop all Database's.

• Create all Database's.

• Restore dump data.

1. ) Edit the /opt/zimbra/conf/my.cnf file and put innodb_force_recovery = 3 under the [mysqld] section

Save the file and start mysql with mysql.server start

1. )

   mysql -NB -e "show databases" | grep mbox > /tmp/mysql.db.list

   [Make a list of the existing database]

1. )

   mkdir /tmp/mysql.sql 

1. )

    mysqldump zimbra -S /opt/zimbra/db/mysql.sock -u root --password=''password_for_mysql_root'' > /tmp/mysql.sql/zimbra.sql 

[Mysql Root Password you can get this by 'zmlocalconfig -s -m nokey mysql_root_password']

1. ) Dumping Databases: Run the following command from the prompt:

for db in `< /tmp/mysql.db.list`;
do
    /opt/zimbra/mysql/bin/mysqldump $db -S /opt/zimbra/db/mysql.sock -u root --password=password_for_mysql_root > /tmp/mysql.sql/$db.sql
    echo -e "Dumped $db\n"
done

[Mysql Zimbra Password You can get the password:

zmlocalconfig -s -m nokey zimbra_mysql_password

]

1. ) Drop the Database:

for db in `< /tmp/mysql.db.list`
do
    mysql -u root --password=''password_for_mysql_root'' -e "drop database $db"
    echo -e "Dropped $db"
done

1. )

    mysql -e "drop database zimbra" 

1. ) Run Mysql in Non-Recovery Mode:

Remove the innodb_force_recovery = 3 from the /opt/zimbra/conf/my.cnf file.

1. )Stop Mysql Server and Start again.

1. )Create Database:

mysql -e "create database zimbra character set utf8"

1. ) Create the existed Database:

for db in `< /tmp/mysql.db.list`; \ do mysql -e "create database $db character set utf8"; \ echo -e "Created $db \n";done;

1. )

   mysql zimbra < /tmp/mysql.sql/zimbra.sql

1. ) Update Mboxgroup:

for sql in /tmp/mysql.sql/mbox*
do
    mysql `basename $sql .sql` < $sql
    echo -e "Updated `basename $sql .sql` \n"
done

1. )

   zmcontrol start

1. ) Check if everything is up and running.