Zimbra: Recover broken MariaDB

Leave a comment

February 18, 2019 by aubreykloppers

Exerts from: Zimbra MySQL Crash Recovery

I had the unfortunate experience where my Zimbra Database crashed.  This was due to a power failure and the symptoms were mail became erratic, was not delivered and just plainly got lost.

Procedure:

  • Configure MariaDB to start in recovery mode:
  1. mysql.server stop
  2. add innodb_force_recovery = 1 under the [mysqld] section of /opt/zimbra/conf/my.cnf
  3. mysql.server start
  • Create SQL dumps of all the databases and create a listing:

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
~/common/bin/mysqldump $db -S $mysql_socket -u root –password=$mysql_root_password > /tmp/mysql.sql/$db.sql
echo “Dumped $db”
sleep 10
done

  • Remove all databases (clean and corrupted):

# Get password with “zmlocalconfig -s | grep mysql | grep password

for db in `cat /tmp/mysql.db.list |grep mbox`
do
mysql -u root –password={PASSWORD} -e “drop database $db”
echo -e “Dropped $db”
done

  • Drop ZIMBRA database and remove all InnoDB tablespace and log files:

mysql -u root –password=$mysql_root_password -e “drop database zimbra”rm -rf /opt/zimbra/db/data/ib*

  • Recreate the databases
  1. mysql.server stop
  2. remove innodb_force_recovery = 1 under the [mysqld] section of /opt/zimbra/conf/my.cnf
  3. mysql.server start

for db in `cat /tmp/mysql.db.list`
do
mysql -e “create database $db character set utf8”
echo “Created $db”
done

  • RePopulate the Zimbra DB with the backed-up data:

First Zimbra:

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

then all the mailboxes:

for db in `cat /tmp/mysql.db.list`
do
mysql -e “create database $db character set utf8”
echo “Created $db”
done

  • Test databases and start Zimbra:
  1. mysql zimbra -e “select * from mailbox order by id desc limit 1”
  2. zmcontrol start

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: