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.

NOTE: Before running any command make sure you have set your environment with: source ~/bin/zmshutil ; zmsetvars

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.

I also had a problem whereby the mysql database did not want to start and read this from the Zimbra blog:

 Can’t dump db’s because of ‘connection’ issues at this point? One could move the /opt/zimbra/db/data directory –

mv /opt/zimbra/db/data /opt/zimbra/db/data-old

and then make the db –

mkdir /opt/zimbra/db/data w/ ownership of zimbra:zimbra

Remove the innodb_force_recovery line from /opt/zimbra/conf/my.cnf . Then recreate a default mysql db by running

/opt/zimbra/libexec/zmmyinit –sql_root_pw $mysql_root_password

and then attempt this steps over again to confirm you can drop them. Also note that you may have to reset the zimbra password manually in mysql, then set it again in Zimbra with the instructions from this page: http://wiki.zimbra.com/wiki/Resetting_LDAP_%26_MySQL_Passwords

Also from the above get your ROOT and ZIMBRA sql passwords and restore them to the originals…

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: