Berikut adalah tahapan untuk recovery Database Corrupt
1. Recovery Database
1.1. cek status service database mysql menggunakan user zimbra
su - zimbra
mysql.server status
1.2. Jika status database mati, hidupkan
mysql.server start
1.3. Apabila database tidak running, ubah status database menjadi recovery mode dengan menambahkan innodb_force_recovery = 1 dibawah [mysqld]
[mysqld]
innodb_force_recovery = 1
basedir = /opt/zimbra/common
datadir = /opt/zimbra/db/data
socket = /opt/zimbra/data/tmp/mysql/mysql.sock
pid-file = /opt/zimbra/log/mysql.pid
bind-address = 127.0.0.1
port = 7306
user = zimbra
tmpdir = /opt/zimbra/data/tmp
1.4. Kemudian simpan file, start ulang database. Apabila database tetap tidak hidup, ubah value innodb_force_recovery = 2, dan jika tidak hidup ubah value innodb_force_recovery menjadi 3/4/5/6. Apabila masih tidak bisa juga database running pada recovery mode. maka database tidak bisa di recovery atau di selamatkan.
2. Backup Database Zimbra
2.1. Masuk ke user zimbra
su - zimbra
2.2. Load mysql configuration kedalam variabel shell
source ~/bin/zmshutil ; zmsetvars
2.3. List all database
mysql --batch --skip-column-names -e "show databases" | grep -e mbox -e zimbra > /tmp/mysql.db.list
Gunakan perintah dibawah jika fitur Zimbra Chat Aktif
mysql --batch --skip-column-names -e "show databases" | grep -e mbox -e zimbra -e chat > /tmp/mysql.db.list
2.4. Buat direktori lokasi backup/dump database
mkdir /tmp/mysql.sql
2.5. Dump/backup all database ( copy + paste)
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
3. Remove & Restore Database Zimbra
3.1. Remove Database Zimbra
3.1.1. Remove all database mbox
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
3.1.2. Remove database zimbra
mysql -u root --password=$mysql_root_password -e "drop database zimbra"
3.1.3. Hapus tablespace dan log files database
rm -rf /opt/zimbra/db/data/ib*
3.2. Restore Semua Database
1. Restart service mysql
mysql.server restart
2. Create database kosong
for db in `cat /tmp/mysql.db.list`
do
mysql -e "create database $db character set utf8"
echo "Created $db"
done
3. Restore isi database zimbra
mysql zimbra < /tmp/mysql.sql/zimbra.sql
4. Restore isi database all mbox
for sql in /tmp/mysql.sql/mbox*
do
mysql `basename $sql .sql` < $sql
echo -e "Updated `basename $sql .sql` \n"
done
Gunakan perintah dibawah jika fitur Zimbra Chat aktif
mysql chat < /tmp/mysql.sql/chat.sql
5. Test query
mysql zimbra -e "select * from mailbox order by id desc limit 1"
6. Restart service
zmcontrol restart