system toolbox » zimbra

Resetting LDAP & MySQL Passwords

Mysql Crash Recovery¶

From Zimbra :: Wiki

BUG "http://bugzilla.zimbra.com/show_bug.cgi?id=15797"

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]

Steps to perform Recovery:

• Edit /opt/zimbra/conf/my.cnf: Put an entry innodb_force_recovery = 3 in the [mysqld] section
• 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)

mysql -NB -e "show databases" | grep mbox > /tmp/mysql.db.list

[Make a list of the existing databases]

3)

mkdir /tmp/mysql.sql

4)

mysqldump zimbra -S /opt/zimbra/db/mysql.sock -u root --password=''password_for_mysql_root'' > /tmp/mysql.sql/zimbra.sql

[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:

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

7)

mysql -e "drop database zimbra"

8) Run Mysql in Non-Recovery Mode:

Remove the innodb_force_recovery = 3 from the /opt/zimbra/conf/my.cnf file.

9) Stop Mysql Server and Start again.

10) Create Database:

mysql -e "create database zimbra character set utf8"

11) Create the existed Database:

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

12)

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

13) Update Mboxgroup:

for sql in /tmp/mysql.sql/mbox*
do
    mysql `basename $sql .sql` < $sql
    echo -e "Updated `basename $sql .sql` \n" 
done

14)

zmcontrol start

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

ovaj mysql crash recovery je baš ono što želim primjeniti u backup-u

zimbra baze

root@zimbra-1:~# su - zimbra
zimbra@zimbra-1:~$ zmlocalconfig -s | grep mysql | grep password
mysql_logger_root_password = xxxxxx
mysql_root_password = xxxxxxxxxxxxxx
zimbra_logger_mysql_password = xxxxxxxxxxxxx
zimbra_mysql_password = xxxxxxxxxxxxx

zimbra@zimbra-1:~$ mysql -u root --password=xxxxxxxxxxxxxxxx
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10145
Server version: 5.0.45-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema | 
| mboxgroup1         | 
| mboxgroup10        | 
| mboxgroup11        | 
| mboxgroup12        | 
| mboxgroup13        | 
... 
| mboxgroup91        | 
| mboxgroup92        | 
| mysql              | 
| test               | 
| zimbra             | 
+--------------------+
96 rows in set (1.26 sec)

mysql> show tables;   
+----------------------+
| Tables_in_zimbra     |
+----------------------+
| config               | 
| current_volumes      | 
| deleted_account      | 
| jiveExtComponentConf | 
| jiveGroupProp        | 
| jiveGroupUser        | 
| jiveID               | 
| jiveOffline          | 
| jivePrivacyList      | 
| jivePrivate          | 
| jiveProperty         | 
| jiveRemoteServerConf | 
| jiveRoster           | 
| jiveRosterGroups     | 
| jiveSASLAuthorized   | 
| jiveUserProp         | 
| jiveVCard            | 
| jiveVersion          | 
| mailbox              | 
| mailbox_metadata     | 
| mucAffiliation       | 
| mucConversationLog   | 
| mucMember            | 
| mucRoom              | 
| mucRoomProp          | 
| out_of_office        | 
| scheduled_task       | 
| service_status       | 
| table_maintenance    | 
| volume               | 
+----------------------+
30 rows in set (0.00 sec)

mysql> use  mboxgroup44;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-----------------------+
| Tables_in_mboxgroup44 |
+-----------------------+
| appointment           | 
| imap_folder           | 
| imap_message          | 
| mail_item             | 
| open_conversation     | 
| pop3_message          | 
| revision              | 
| tombstone             | 
+-----------------------+
8 rows in set (0.00 sec)

mysql> use  mboxgroup43
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-----------------------+
| Tables_in_mboxgroup43 |
+-----------------------+
| appointment           | 
| imap_folder           | 
| imap_message          | 
| mail_item             | 
| open_conversation     | 
| pop3_message          | 
| revision              | 
| tombstone             | 
+-----------------------+
8 rows in set (0.00 sec)

mysql> show columns from mail_item;
+--------------+---------------------+------+-----+---------+-------+
| Field        | Type                | Null | Key | Default | Extra |
+--------------+---------------------+------+-----+---------+-------+
| mailbox_id   | int(10) unsigned    | NO   | PRI |         |       | 
| id           | int(10) unsigned    | NO   | PRI |         |       | 
| type         | tinyint(4)          | NO   |     |         |       | 
| parent_id    | int(10) unsigned    | YES  |     | NULL    |       | 
| folder_id    | int(10) unsigned    | YES  |     | NULL    |       | 
| index_id     | int(10) unsigned    | YES  |     | NULL    |       | 
| imap_id      | int(10) unsigned    | YES  |     | NULL    |       | 
| date         | int(10) unsigned    | NO   |     |         |       | 
| size         | bigint(20) unsigned | NO   |     |         |       | 
| volume_id    | tinyint(3) unsigned | YES  | MUL | NULL    |       | 
| blob_digest  | varchar(28)         | YES  |     | NULL    |       | 
| unread       | int(10) unsigned    | YES  |     | NULL    |       | 
| flags        | int(11)             | NO   |     | 0       |       | 
| tags         | bigint(20)          | NO   |     | 0       |       | 
| sender       | varchar(128)        | YES  |     | NULL    |       | 
| subject      | text                | YES  |     | NULL    |       | 
| name         | varchar(128)        | YES  |     | NULL    |       | 
| metadata     | mediumtext          | YES  |     | NULL    |       | 
| mod_metadata | int(10) unsigned    | NO   |     |         |       | 
| change_date  | int(10) unsigned    | YES  |     | NULL    |       | 
| mod_content  | int(10) unsigned    | NO   |     |         |       | 
+--------------+---------------------+------+-----+---------+-------+
21 rows in set (0.02 sec)

mysql> select count(*) from mail_item;
+----------+
| count(*) |
+----------+
|      140 | 
+----------+
1 row in set (0.00 sec)

mysql> use  mboxgroup22;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> select count(*) from mail_item;
+----------+
| count(*) |
+----------+
|       16 | 
+----------+
1 row in set (0.00 sec)

mysql> use  mboxgroup111;
ERROR 1049 (42000): Unknown database 'mboxgroup111'
mysql> use  mboxgroup11;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select count(*) from mail_item;
+----------+
| count(*) |
+----------+
|       17 | 
+----------+
1 row in set (0.00 sec)

mysql> use  mboxgroup4;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select count(*) from mail_item;
+----------+
| count(*) |
+----------+
|    98366 | 
+----------+
1 row in set (0.20 sec)