Database gone after deleting ibdata files

[ebizindia]

I have a bit of emergency and all help is sincerely appreciated.

Problem description:

We had 2 databases under innodb engine and the rest under MyISAM. I needed to clean up the very high space usage in ibdata* files. The developer told me about 1 DB only and I duly backed it up using mysqldump.

Then I deleted ibdata* and restored the DB from mysqldump.

So far so good.

Now I have learnt that we missed out 1 DB completely. As expected this DB is showing corrupted after the ibdata files were recreated.

I have tried to restore monthly and weekly backups but they are unable to restore the missing DB.

Requests:

1. Does the CPanel daily/weekly/monthly backup process not backup the InnoDB databases?

2. If it does, why can't my DBs come back after a restore?

3. Is there any manual process to get the data back? Some of it is very vital and we will have a very tough time recreating the data manually.

TIA

[cPanelDon]

Please note the InnoDB "ibdata" files should not be deleted simply to reduce disk usage; this is not safe under any circumstances.

If the database was not already corrupted at the time of backup, then I would consider dropping (removing) the existing corrupt database and re-create it using available full backups.

cPanel backup features make it possible to backup both account databases and the entire MySQL data directory; if one or both of these are enabled in your backup configuration I would use the saved backups to restore from.

[brianoz]

cPanelDon, is the ibdata file included in the mysql directory backup? I thought by default it was stored under /etc?

Unfortunately I think cPanel's defaults get the ibdata file badly wrong - it should default to having the following line in /etc/my.cnf to force separate data files per database:
innodb_file_per_table

... but it doesn't, so you get mega file growth in ibdata which can easily become unmanageable!

(Hi Naveen/Daryus etc!!)

[brianoz]

Oh - a helpful tip here is - never remove a file like this until sure. First, rename it and then gzip it, database files usually compress by 90%. Then when you're sure everything runs OK, after a day or two delete the renamed and compressed file! This technique works across the board - always rename rather than remove. Sorry, I know this is too late, hope it helps at least a little ...

[cPanelDon]

cPanelDon, is the ibdata file included in the mysql directory backup? I thought by default it was stored under /etc?

Yes; if the MySQL data directory is set for backup, then if InnoDB is enabled the "ibdata" file would be included; here is an example set of directory paths assuming the backup directory is "/backup":

/backup/cpbackup/daily/dirs/_var_lib_mysql_/
/backup/cpbackup/weekly/dirs/_var_lib_mysql_/
/backup/cpbackup/monthly/dirs/_var_lib_mysql_/

Unfortunately I think cPanel's defaults get the ibdata file badly wrong - it should default to having the following line in /etc/my.cnf to force separate data files per database:
innodb_file_per_table

... but it doesn't, so you get mega file growth in ibdata which can easily become unmanageable!

(Hi Naveen/Daryus etc!!)

cPanel does not define the default MySQL configuration; customizing of MySQL configurations is the choice and responsibility of the server administrator.