1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

problems restoring MySQL database from backup

Discussion in 'Off Topic' started by dmnkhhn, Mar 13, 2011.

  1. dmnkhhn

    dmnkhhn Active Member

    Hey guys!

    I'm having problems restoring a MySQL database from a backup and I hope you can help me.

    There is a backup on my external drive, a folder full of *.frm, *.MYD and *.MYI files.
    I copied the folder over to my working drive and I restarted the MySQL server and phpMyAdmin doesn't list all of the tables I would expect.

    This question is XenForo related, the backup is from beta 1 but now most of the tables are missing and I am left with 8 tables.

    In the backup folder there are 132 files so I'm almost sure all tables are there but why don't the show up?!
    Oddly enough the dropdown in phpMyAdmin displays the correct number of the tables in database but as soon as I open it I can only see those 8 tables.

    Unfortunately the backup is from my old iMac, I tried to start from the external drive but it doesn't

    Can anyone help me please? :)
  2. Jesepi

    Jesepi Well-Known Member

    How were those files being backed up?
  3. dmnkhhn

    dmnkhhn Active Member

    I cloned the internal drive so all I have are those *.frm, *.MYD and *.MYI files. :(

    I didn't have the chance to do a proper backup.
  4. CyclingTribe

    CyclingTribe Well-Known Member

    Check ownership and permissions.
  5. dmnkhhn

    dmnkhhn Active Member

    I compared the permissions and the ownership.

    All database that I created recently belong to the group 'admin', the old files belong to the group 'staff'. The permissions are the same.
    Changing the group from 'staff' to 'admin' didn't help.

    mysqlcheck --all-databases --check --repair gives me this:
    Error : Table 'xenforo.xf_template_phrase' doesn't exist
    status : Operation failed
  6. Shadab

    Shadab Well-Known Member

    Majority of tables in Xenforo are InnoDB, for which information is stored in "data files" and "log tracking files". They are different from frm/myi/myd files that you would find inside a database directory. On a default setup they are usually located one level above individual db directories. ie, right inside the mysql data dir.


    Have you got these ibdata* and ib_logfile* files in your backup?
    dmnkhhn likes this.
  7. dmnkhhn

    dmnkhhn Active Member

    That did the trick, thank you very much! :)

    I moved my 'new' ibdata* and ib_logfile* to a save location, then I moved the old files to the new directory. That allowed me to backup the database leaving it in a stage were all the new databases had missing tables (of course).
    Then I moved the 'old' ibdata* and ib_logfile* to a save location and moved the new files back to where they were.

    After I server restart I deleted the old database and imported the new one and that worked out pretty well. :)
  8. CyclingTribe

    CyclingTribe Well-Known Member

    InnoDB ... I hadn't realised. Learned something new today. :)

    Glad you got it sorted. :D
  9. twhiting9275

    twhiting9275 Active Member

    This is one of the main reasons you don't want to rely on just copying files over for a MySQL backup. You're essentially just copying binary files which can very easily be modified while copying and cause issues. Use mysqldump to drop the table to a file at regular intervals for a better backup system.

Share This Page