Privacy Policy

Valutazione attuale: 5 / 5

Stella attivaStella attivaStella attivaStella attivaStella attiva
Last updated on 07/11/2015  

Mysql Crash Recovery

In the event of database corruption it may be necessary to manually perform database recovery. See Bug 15797 for an example of an issue with mysql that will require database recovery. In that example, a warning message like the following appeared in the mysql error log:

InnoDB: Serious error! InnoDB is trying to free page 716
InnoDB: though it is already marked as free in the tablespace!
InnoDB: The tablespace free space info is corrupt.
InnoDB: You may need to dump your InnoDB tables and recreate the whole
InnoDB: database!


Details of Recovery Process

1. Configure mysql to start in recovery mode

1. Configure mysql to start in recovery mode

  1. 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)
  2. Save the file and re-start mysqld
mysql.server start

2. Generate SQL dumps of all databases

  1. 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)
  2. Make a list of the existing databases
  3. Create a directory to hold the SQL dumps
  4. Generate the SQL dumps from the database list
source ~/bin/zmshutil ; 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

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`
    mysql -u root --password=$mysql_root_password -e "drop database $db"
    echo -e "Dropped $db"
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

  1. Run mysql in non-recovery mode
    1. Remove the innodb_force_recovery line from /opt/zimbra/conf/my.cnf
    2. Save the file and restart mysqld
  2. Re-create the databases from the database list
mysql.server restart
for db in `cat /tmp/mysql.db.list`
    mysql -e "create database $db character set utf8"
    echo "Created $db"

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*
    mysql `basename $sql .sql` < $sql
    echo -e "Updated `basename $sql .sql` \n"

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 start

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

Good luck


C e r t i f i c a z i o n i