Mysql Crash Recovery: Difference between revisions

(added 6)
(organize)
Line 1: Line 1:
'''Mysql Crash Recovery'''
=Mysql Crash Recovery=


In the event of database corruption it may be necessary to manually perform database recovery. See [http://bugzilla.zimbra.com/show_bug.cgi?id=15797 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:


BUG "http://bugzilla.zimbra.com/show_bug.cgi?id=15797"
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!


=Overview of Recovery Process=
<!-- make sure to update section numbering if steps are changed here -->
# 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


Mysql Crash recovery is performed when Mysql stops responding and there is error in the /opt/zimbra/db/data/hostname.err [Please check the BUG above for more information]
=Details of Recovery Process=


==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 start mysqld
mysql.server start


Steps to perform Recovery:
==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


<pre>source ~/bin/zmshutil ; zmsetvars</pre>
<pre>mysql --batch --skip-column-names -e "show databases" | grep -e mbox -e zimbra > /tmp/mysql.db.list</pre>
<pre>mkdir /tmp/mysql.sql </pre>
<pre>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"
done</pre>


* Edit /opt/zimbra/conf/my.cnf: Put an entry ''innodb_force_recovery = 3'' in the [mysqld] section (in extreme cases where mysql still will not start up, replace 3 with 6)
'''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. Please see MySQL's [http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html Forcing InnoDB Recovery] guide for more information.
 
* Dump the database: mysqldump zimbra and mboxgroup DB's
* Drop all Database's.
 
* Create all Database's.
 
* Restore dump data.
 
 
1) Edit the /opt/zimbra/conf/my.cnf file and put '''innodb_force_recovery = 3'''  under the '''[mysqld]''' section
:Save the file and start mysql with ''mysql.server start''
 
2) <pre>mysql -NB -e "show databases" | grep mbox > /tmp/mysql.db.list</pre>    [Make a list of the existing databases]
 
3) <pre>mkdir /tmp/mysql.sql </pre>
 
4) <pre> mysqldump zimbra -S /opt/zimbra/db/mysql.sock -u root --password=''password_for_mysql_root'' > /tmp/mysql.sql/zimbra.sql </pre>
 
['''Mysql Root Password''' you can get this by ''zmlocalconfig -s -m nokey mysql_root_password'']
 
5) Dumping Databases: Run the following command from the prompt:
 
 
for db in `< /tmp/mysql.db.list`;
do
    /opt/zimbra/mysql/bin/mysqldump $db -S /opt/zimbra/db/mysql.sock -u root --password=''password_for_mysql_root'' > /tmp/mysql.sql/$db.sql
    echo -e "Dumped $db\n"
done
 
['''Mysql Zimbra Password''' You can get the password: ''zmlocalconfig -s -m nokey zimbra_mysql_password'']
 
 
6) Drop the Database:


==3. Remove all existing (and possibly corrupt) databases==


<pre>
<pre>
for db in `< /tmp/mysql.db.list`
for db in `cat /tmp/mysql.db.list`
do
do
     mysql -u root --password=''password_for_mysql_root'' -e "drop database $db"
     mysql -u root --password=$mysql_root_password -e "drop database $db"
     echo -e "Dropped $db"
     echo -e "Dropped $db"
done
done
</pre>
</pre>


7) <pre> mysql -e "drop database zimbra" </pre>
==4. Re-create all databases==
 
# Run mysql in non-recovery mode
 
## Remove the '''innodb_force_recovery''' line from /opt/zimbra/conf/my.cnf
8) Run Mysql in Non-Recovery Mode:
## Save the file and restart mysqld
 
# Re-create the databases from the database list
 
Remove the '''innodb_force_recovery = 3''' from the /opt/zimbra/conf/my.cnf file.
 
 
9)Stop Mysql Server and Start again.
 
 
10)Create Database:
 
 
<pre> mysql -e "create database zimbra character set utf8" </pre>
 
 
11) Create the existed Database:


<pre>mysql.server restart</pre>


<pre>
<pre>
for db in `< /tmp/mysql.db.list`
for db in `cat /tmp/mysql.db.list`
do
do
     mysql -e "create database $db character set utf8"
     mysql -e "create database $db character set utf8"
     echo -e "Created $db \n"
     echo "Created $db"
done
done
</pre>
</pre>


 
==5. Repopulate the databases with the data from the SQL dumps==
 
Import the data from the SQL dumps
12) <pre>mysql zimbra < /tmp/mysql.sql/zimbra.sql</pre>
 
 
13) Update Mboxgroup:


<pre>
<pre>
for sql in /tmp/mysql.sql/mbox*
for sql in /tmp/mysql.sql/*
do
do
     mysql `basename $sql .sql` < $sql
     mysql `basename $sql .sql` < $sql
Line 100: Line 78:
</pre>
</pre>


==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.
<pre>mysql zimbra -e "select * from mailbox order by id desc limit 1"</pre>


14) <pre>zmcontrol start</pre>
Once you are satisfied that the databases are restored intact, start the rest of the zimbra services.
<pre>zmcontrol start</pre>


15) Verify mail delivery and account creation & deletion.  Check /opt/zimbra/log/mailbox.log for database errors.
Check /opt/zimbra/log/mysql_error.log and /opt/zimbra/log/mailbox.log for database errors.





Revision as of 01:00, 28 February 2009

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!

Overview of Recovery Process

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

Details of Recovery Process

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 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"
 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. Please see MySQL's Forcing InnoDB Recovery guide for more information.

3. Remove all existing (and possibly corrupt) databases

for db in `cat /tmp/mysql.db.list`
do
    mysql -u root --password=$mysql_root_password -e "drop database $db"
    echo -e "Dropped $db"
done

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`
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

for sql in /tmp/mysql.sql/*
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 start

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


Verified Against: Zimbra Collaboration Suite 4.5.6 Date Created: 8/13/2007
Article ID: https://wiki.zimbra.com/index.php?title=Mysql_Crash_Recovery Date Modified: 2009-02-28



Try Zimbra

Try Zimbra Collaboration with a 60-day free trial.
Get it now »

Want to get involved?

You can contribute in the Community, Wiki, Code, or development of Zimlets.
Find out more. »

Looking for a Video?

Visit our YouTube channel to get the latest webinars, technology news, product overviews, and so much more.
Go to the YouTube channel »

Jump to: navigation, search