The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Database gone after deleting ibdata files

Discussion in 'Data Protection' started by ebizindia, Dec 3, 2009.

  1. ebizindia

    ebizindia Well-Known Member

    Joined:
    Oct 13, 2005
    Messages:
    72
    Likes Received:
    2
    Trophy Points:
    8
    Location:
    Kolkata, India
    cPanel Access Level:
    Root Administrator
    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
     
  2. cPanelDon

    cPanelDon cPanel Quality Assurance Analyst
    Staff Member

    Joined:
    Nov 5, 2008
    Messages:
    2,557
    Likes Received:
    7
    Trophy Points:
    38
    Location:
    Houston, Texas, U.S.A.
    cPanel Access Level:
    DataCenter Provider
    Twitter:
    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.
     
  3. brianoz

    brianoz Well-Known Member

    Joined:
    Mar 13, 2004
    Messages:
    1,146
    Likes Received:
    6
    Trophy Points:
    38
    Location:
    Melbourne, Australia
    cPanel Access Level:
    Root Administrator
    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!!)
     
  4. brianoz

    brianoz Well-Known Member

    Joined:
    Mar 13, 2004
    Messages:
    1,146
    Likes Received:
    6
    Trophy Points:
    38
    Location:
    Melbourne, Australia
    cPanel Access Level:
    Root Administrator
    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 ...
     
  5. cPanelDon

    cPanelDon cPanel Quality Assurance Analyst
    Staff Member

    Joined:
    Nov 5, 2008
    Messages:
    2,557
    Likes Received:
    7
    Trophy Points:
    38
    Location:
    Houston, Texas, U.S.A.
    cPanel Access Level:
    DataCenter Provider
    Twitter:
    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":
    Code:
    /backup/cpbackup/daily/dirs/_var_lib_mysql_/
    /backup/cpbackup/weekly/dirs/_var_lib_mysql_/
    /backup/cpbackup/monthly/dirs/_var_lib_mysql_/

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

Share This Page