problems restoring MySQL database from backup

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
work.

Can anyone help me please? :)
 
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.
 
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
 
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.

http://dev.mysql.com/doc/refman/5.0/en/innodb-backup.html

Have you got these ibdata* and ib_logfile* files in your backup?
 
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. :)
 
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.
 
Top Bottom