Editing Mysql Crash Recovery In zimbra

[[category:Heromotocorp Biz]]
[[category:Tetra Clients]]
<center> MySql Crash Recovery In Zimbra </center>


Ref: [https://wiki.zimbra.com/wiki/Mysql_Crash_Recovery https://wiki.zimbra.com/wiki/Mysql_Crash_Recovery]


# Configure mysql to start in recovery mode 
# Generate SQL dumps of all relevant databases 
# Remove all existing (and possibly corrupt) databases 
# Re-create all databases 
# Repopulate the databases with the data from the SQL dumps 
# Test databases and start all ZCS services

== 1. Configure mysql to start in recovery mode ==
# Edit the file /opt/zimbra/conf/my.cnf and add a line like '''innodb_force_recovery = 1''' under the '''[mysqld]''' section ''(Note that it may be necessary to increase the recovery level depending on the extent of the database corruption, as shown at the end of the database dump step)'' 
# Save the file and re-start mysqld

mysql.server start


== 2. Generate SQL dumps of all databases ==
# Load some mysql configuration into shell variables (i.e. $mysql_socket and $mysql_root_password; note that you will use these again in step 3) 
# Make a list of the existing databases 
# Create a directory to hold the SQL dumps 
# Generate the SQL dumps from the database list

source ~/bin/zmshutil&nbsp;; zmsetvars

mysql --batch --skip-column-names -e "show databases" | grep -e mbox -e zimbra > /tmp/mysql.db.list

mkdir /tmp/mysql.sql 

for db in `cat /tmp/mysql.db.list`; do

~/mysql/bin/mysqldump $db -S $mysql_socket -u root --password=$mysql_root_password > /tmp/mysql.sql/$db.sql

echo "Dumped $db"

sleep 10

done

'''Note: If you encounter any mysql errors while dumping the databases, start over by re-editing /opt/zimbra/conf/my.cnf, incrementing the value for innodb_force_recovery by one, and restarting mysqld.''' The maximum recovery level is 6.


== 3. Remove all existing (and possibly corrupt) databases ==
'''Note:''' Take a copy of /opt/zimbra/db/data before dropping the databases. This will ensure a copy of old database. 

Note that we drop the zimbra database last because the mboxgroup* databases depend on it 

for db in `cat /tmp/mysql.db.list |grep mbox`

do

mysql -u root --password=$mysql_root_password -e "drop database $db"

echo -e "Dropped $db"

done

mysql -u root --password=$mysql_root_password -e "drop database zimbra"

Remove existing InnoDB tablespace and log files 

rm -rf /opt/zimbra/db/data/ib*

== 4. Re-create all databases ==
# Run mysql in non-recovery mode 
## Remove the '''innodb_force_recovery''' line from /opt/zimbra/conf/my.cnf 
## Save the file and restart mysqld
# Re-create the databases from the database list

mysql.server restart

for db in `cat /tmp/mysql.db.list`

do

mysql -e "create database $db character set utf8"

echo "Created $db"

done

== 5. Repopulate the databases with the data from the SQL dumps ==
Import the data from the SQL dumps. Note that we import the zimbra database first because the mboxgroup databases depend on it 

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

for sql in /tmp/mysql.sql/mbox*

do

mysql `basename $sql .sql` < $sql

echo -e "Updated `basename $sql .sql` \n"

done


== 6. Test databases and start all ZCS services ==
Note that this is an example query. If you know of any particular databases that were corrupt, you may want to construct other queries to verify normal access to the data. 

mysql zimbra -e "select * from mailbox order by id desc limit 1"

Once you are satisfied that the databases are restored intact, start the rest of the zimbra services. 

zmcontrol restart

Check /opt/zimbra/log/mysql_error.log and /opt/zimbra/log/mailbox.log for database errors.