MySQL won't start and claims InnoDB corrupted. Can the DB be salvaged and which way to turn?

[kayasaman]

Hmm...

same error but not very helpful post as no information:

viewtopic.php?t=14635


I wonder if I should take this to the mysql forum or mailing list or something?

Worst case scenario I have this current data instance of the DB backed up so I can always recopy the backup and start again.

I just hope that the data can be retrieved?


Is there a way to read the .ibd files and see what's in them?

[kayasaman]

This might be a way?

https://stackoverflow.com/questions/268 ... -ibd-files

[kayasaman]

It seems that the `mysqlfrm` command can't easily be installed on RH/CentOS etc... 8.x

Scenario B: You have old .frm files (from MySQL 5.7 or older) that you need to read
If you have legacy backup files and absolutely must use mysqlfrm to recover their structures, you cannot do it natively with the RHEL 8 repository version of MySQL 8. Instead, use one of these workarounds:
• Option 1 (Easiest): Use Docker / Podman
Run a temporary, legacy MySQL 5.7 container to parse the files or run the Python-based utility without messing up your host packages.
• Option 2: Install via Python 2.7 (Manual)
Because mysqlfrm is a legacy Python script, it requires Python 2.7 and the mysql-connector-python package. You would have to download the old mysql-utilities source code or RPM manually, as RHEL 8 defaults to Python 3.

Without this command is there anyway at all to recover things?


The current methodology that I posted in the previous link was something like this:

Code: Select all

First, install MySQL Utilities. Then you can use mysqlfrm command in command prompt (cmd).

Second, get the SQL queries from .frm files using mysqlfrm command:

mysqlfrm --diagnostic <path>/example_table.frm
Then you can get the SQL query to create same structured table. Like this:

CREATE TABLE `example_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(150) NOT NULL,
  `photo_url` varchar(150) NOT NULL,
  `password` varchar(600) NOT NULL,
  `active` smallint(6) NOT NULL,
  `plan` int(11) NOT NULL,
PRIMARY KEY `PRIMARY` (`id`)
) ENGINE=InnoDB;
Create the tables
Create the table(s) using the above SQL query.

If the old data still exists, you may have to drop the respective database and tables first. Make sure you have a backup of the data files.

Restore the data
Run this query to remove new table data:

ALTER TABLE example_table DISCARD TABLESPACE;
This removes connections between the new .frm file and the (new, empty) .idb file. Also, remove the .idb file in the folder.

Then, put the old .idb file into the new folder, e.g.:

cp backup/example_table.ibd <path>/example_table.idb
Make sure that the .ibd files can be read by the mysql user, e.g. by running chown -R mysql:mysql *.ibd in the folder.

Run this query to import old data:

ALTER TABLE example_table IMPORT TABLESPACE;
This imports data from the .idb file and will restore the data.

[kayasaman]

Ok I think I'm getting somewhere?!

I found something called `dbsake` which reads frm files as an alternative to the legacy `msyqlfrm`

It seems to be working??

Code: Select all

python3.9 /root/dbsake frmdump appointment.frm
--
-- Table structure for table `appointment`
-- Created with MySQL Version 10.1.25
--

CREATE TABLE `appointment` (
  `mailbox_id` int(10) unsigned NOT NULL,
  `uid` varchar(255) NOT NULL,
  `item_id` int(10) unsigned NOT NULL,
  `start_time` datetime NOT NULL,
  `end_time` datetime DEFAULT NULL,
  PRIMARY KEY (`mailbox_id`,`uid`),
  UNIQUE KEY `i_item_id` (`mailbox_id`,`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Maybe I can use the method written above now to hopefully restore the DB files

[kayasaman]

I think I'm getting somewhere!!!

Get the table structure from dbsake:

Code: Select all

python3.9 /root/dbsake frmdump appointment.frm

First connect to db:

Code: Select all

MariaDB [(none)]> connect mboxgroup10;

Then drop the table:

Code: Select all

MariaDB [mboxgroup10]> drop table appointment;

Move the ibd file out of the way:

Code: Select all

$ mv appointment.ibd appointment.ibd.bak

Create the table using the output of dbsake:

Code: Select all

MariaDB [mboxgroup10]> CREATE TABLE `appointment` (   `mailbox_id` int(10) unsigned NOT NULL,   `uid` varchar(255) NOT NULL,   `item_id` int(10) unsigned NOT NULL,   `start_time` datetime NOT NULL,   `end_time` datetime DEFAULT NULL,   PRIMARY KEY (`mailbox_id`,`uid`),   UNIQUE KEY `i_item_id` (`mailbox_id`,`item_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Discard the tablespace:

Code: Select all

MariaDB [mboxgroup10]> alter table appointment discard tablespace;

Move the ibd file back:

Code: Select all

$ mv appointment.ibd.bak appointment.ibd

Import the original tablespace and check result:

Code: Select all

MariaDB [mboxgroup10]> alter table appointment import tablespace;
Query OK, 0 rows affected, 1 warning (0.01 sec)

MariaDB [mboxgroup10]> select * from appointment;
+------------+----------------------------------------------------+---------+---------------------+---------------------+
| mailbox_id | uid                                                | item_id | start_time          | end_time            |
+------------+----------------------------------------------------+---------+---------------------+---------------------+
|         10 | 91ad4a8d-46b5-455b-bd30-a8cf6795506a.1554815307104 |   22686 | 0000-00-08 30:43:09 | 0458-07-08 08:00:00 |
|         10 | bfdb3274-0366-485a-9280-ccd4669cf01c               |   29943 | 0000-00-09 06:44:08 | 7460-03-31 24:00:00 |
|         10 | fc99be88-7c10-4ded-a779-c846f6af0f5f               |    7232 | 0000-00-09 04:60:36 | 7217-11-08 08:00:00 |
+------------+----------------------------------------------------+---------+---------------------+---------------------+
3 rows in set (0.00 sec)

Hopefully I can now restore my DB??

[kayasaman]

So far all seems to be going ok.....

Sometimes I end up with a "cannot discard table due to foreign tag" error, but that can be disabled by:

Code: Select all

SET FOREIGN_KEY_CHECKS=0;

[kayasaman]

Fantastic!!

That's one mailbox sorted:

Code: Select all

ls -lh
total 2.5M
-rw-rw----. 1 zimbra zimbra 2.3K Sep 30 23:02 appointment_dumpster.frm
-rw-r-----. 1 zimbra zimbra 112K Sep 30 23:08 appointment_dumpster.ibd
-rw-rw----. 1 zimbra zimbra 2.3K Sep 30 22:50 appointment.frm
-rw-r-----. 1 zimbra zimbra 112K Sep 30 23:06 appointment.ibd
-rw-rw----. 1 zimbra zimbra 2.4K Sep 30 23:10 data_source_item.frm
-rw-r-----. 1 zimbra zimbra 112K Sep 30 23:10 data_source_item.ibd
-rw-rw----. 1 zimbra zimbra 8.0K Sep 30 23:12 imap_folder.frm
-rw-r-----. 1 zimbra zimbra 128K Sep 30 23:14 imap_folder.ibd
-rw-rw----. 1 zimbra zimbra 1.6K Sep 30 23:16 imap_message.frm
-rw-r-----. 1 zimbra zimbra 112K Sep 30 23:16 imap_message.ibd
-rw-rw----. 1 zimbra zimbra  10K Sep 30 23:40 mail_item_dumpster.frm
-rw-r-----. 1 zimbra zimbra 208K Sep 30 23:41 mail_item_dumpster.ibd
-rw-rw----. 1 zimbra zimbra  11K Sep 30 23:38 mail_item.frm
-rw-r-----. 1 zimbra zimbra 640K Sep 30 23:39 mail_item.ibd
-rw-rw----. 1 zimbra zimbra 1.6K Sep 30 23:42 open_conversation.frm
-rw-r-----. 1 zimbra zimbra 112K Sep 30 23:44 open_conversation.ibd
-rw-rw----. 1 zimbra zimbra 2.4K Sep 30 23:47 pop3_message.frm
-rw-r-----. 1 zimbra zimbra 112K Sep 30 23:48 pop3_message.ibd
-rw-rw----. 1 zimbra zimbra 1.2K Sep 30 23:50 purged_conversations.frm
-rw-r-----. 1 zimbra zimbra  96K Sep 30 23:50 purged_conversations.ibd
-rw-rw----. 1 zimbra zimbra 2.7K Sep 30 23:52 purged_messages.frm
-rw-r-----. 1 zimbra zimbra  96K Sep 30 23:53 purged_messages.ibd
-rw-rw----. 1 zimbra zimbra 5.2K Sep 30 23:54 revision_dumpster.frm
-rw-r-----. 1 zimbra zimbra  96K Sep 30 23:54 revision_dumpster.ibd
-rw-rw----. 1 zimbra zimbra 5.2K Sep 30 23:55 revision.frm
-rw-r-----. 1 zimbra zimbra  96K Sep 30 23:56 revision.ibd
-rw-rw----. 1 zimbra zimbra 5.0K Sep 30 23:57 tag.frm
-rw-rw----. 1 zimbra zimbra 1.5K Oct  1 00:00 tagged_item.frm
-rw-r-----. 1 zimbra zimbra 160K Oct  1 00:01 tagged_item.ibd
-rw-r-----. 1 zimbra zimbra 112K Sep 30 23:58 tag.ibd
-rw-rw----. 1 zimbra zimbra 1.1K Oct  1 00:03 tombstone.frm
-rw-r-----. 1 zimbra zimbra 112K Oct  1 00:04 tombstone.ibd

Gona have to run a zmfixperms but I'll do that at the end....

It's long winded I must admit and there's probably some kind of script of `for` statement that you can use for this in a shell but at this stage as long as I have my data, I don't really care....

[kayasaman]

As doing things the manual way was taking ages I managed to speed things up a little with this:

Code: Select all

for frm in `ls |grep .frm`; do python3.9 /root/dbsake frmdump $frm > /tmp/$frm.sql ; done


MySQL:
drop table appointment_dumpster, appointment, data_source_item, imap_folder, imap_message, mail_item_dumpster, mail_item, open_conversation, pop3_message, purged_conversations, purged_messages, revision_dumpster, revision, tagged_item, tag, tombstone ;


for ibd in `ls |grep .ibd`; do mv $ibd $ibd.bak; done

MySQL:
(paste output from .sql files)

#alter table * discard tablespace;

alter table appointment discard tablespace;
alter table appointment_dumpster discard tablespace;
alter table data_source_item discard tablespace;
alter table imap_folder discard tablespace;
alter table imap_message discard tablespace;
alter table mail_item discard tablespace;
alter table mail_item_dumpster discard tablespace;
alter table open_conversation discard tablespace;
alter table pop3_message discard tablespace;
alter table purged_conversations discard tablespace;
alter table purged_messages discard tablespace;
alter table revision discard tablespace;
alter table revision_dumpster discard tablespace;
alter table tag discard tablespace;
alter table tagged_item discard tablespace;
alter table tombstone discard tablespace;  


for ibd in `ls |grep .ibd`; do mv $ibd ${ibd%.*} ; done

alter table appointment import tablespace;
alter table appointment_dumpster import tablespace;
alter table data_source_item import tablespace;
alter table imap_folder import tablespace;
alter table imap_message import tablespace;
alter table mail_item import tablespace;
alter table mail_item_dumpster import tablespace;
alter table open_conversation import tablespace;
alter table pop3_message import tablespace;
alter table purged_conversations import tablespace;
alter table purged_messages import tablespace;
alter table revision import tablespace;
alter table revision_dumpster import tablespace;
alter table tag import tablespace;
alter table tagged_item import tablespace;
alter table tombstone import tablespace;

Still not perfect but will save a lot in the long run....

[kayasaman]

Ok so I figured a faster way to rebuild the mysql tables:

Code: Select all

for tbl in `ls |grep .sql`; do mysql mboxgroup(x) < $tbl; done

and now....

Code: Select all

zmcontrol start
	Starting ldap...Done.
	Starting zmconfigd...Done.
	Starting dnscache...Done.
	Starting logger...Done.
	Starting mailbox...Done.
	Starting memcached...Done.
	Starting proxy...Done.
	Starting amavis...Done.
	Starting antispam...Done.
	Starting antivirus...Done.
	Starting opendkim...Done.
	Starting snmp...Done.
	Starting spell...Done.
	Starting mta...Done.
	Starting stats...Done.
	Starting service webapp...Done.
	Starting zimbra webapp...Done.
	Starting zimbraAdmin webapp...Done.
	Starting zimlet webapp...Done.

Though Thunderbird is claiming that the server is refusing connections now?

I'm not sure if by default there's a firewall on Rocky Linux so I'll try that but all services seem to be up and running

[kayasaman]

Yep!

Was firewall.....

`systemctl stop firewalld`
`systemctl disable firewalld`

All seems to be there and recovered now

Few!!