Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I recently accidently dropped my whole database for Wordpress, but luckily I've backed with Time Machine.

The problem now is when I copy the database file back to the path where I store all the database now only 11 out of 28 table show in PhPAdmin I don't know why.

Even I've double check the permission group and permission everything exactly the same like other database, but still I can only see 11 of them.

The thing is if it doesn't do anything I wouldn't mind, but now my Wordpress I can't login it says no user found.

MORE INFO:

I've tried to do ls in terminal to see if all the table were copied and I can actually see that all of them there (28 tables including some file I don't know what it is.)

I was doing listing again to my database and I found there is something wrong here.

-rw-------@ 8 _mysql  admin        65 Apr 29 20:29 db.opt
-rw-------@ 8 _mysql  admin      8802 Apr 29 22:50 wp_amazonpin_articles_keys.frm
-rw-------@ 8 _mysql  admin      8726 Apr 29 22:50 wp_amazonpin_articles_links.frm
-rw-------@ 8 _mysql  admin      9239 Apr 29 22:50 wp_amazonpin_camps.frm
-rw-------@ 8 _mysql  admin      8568 Apr 29 22:50 wp_amazonpin_categories.frm
-rw-------@ 8 _mysql  admin      8596 Apr 29 22:50 wp_amazonpin_feeds_links.frm
-rw-------@ 8 _mysql  admin      8668 Apr 29 22:50 wp_amazonpin_feeds_list.frm
-rw-------@ 8 _mysql  admin      8712 Apr 29 22:50 wp_amazonpin_keywords.frm
-rw-------@ 8 _mysql  admin      8856 Apr 29 22:50 wp_amazonpin_links.frm
-rw-------@ 8 _mysql  admin      8680 Apr 29 22:50 wp_amazonpin_log.frm
-rw-------@ 8 _mysql  admin      8688 Apr 29 22:50 wp_commentmeta.frm
-rw-------@ 8 _mysql  admin     13380 Apr 29 22:50 wp_comments.frm
-rw-------@ 8 _mysql  admin       540 Apr 29 22:12 wp_links.MYD
-rw-------@ 8 _mysql  admin      3072 Apr 29 22:50 wp_links.MYI
-rw-------@ 8 _mysql  admin     13176 Apr 29 22:12 wp_links.frm
-rw-------@ 8 _mysql  admin         0 Apr 29 22:12 wp_maker.MYD
-rw-------@ 8 _mysql  admin      1024 Apr 29 22:12 wp_maker.MYI
-rw-------@ 8 _mysql  admin      8716 Apr 29 22:12 wp_maker.frm
-rw-------@ 8 _mysql  admin      6404 Apr 29 22:12 wp_option_tree.MYD
-rw-------@ 8 _mysql  admin      8192 Apr 29 22:50 wp_option_tree.MYI
-rw-------@ 8 _mysql  admin      8800 Apr 29 22:12 wp_option_tree.frm
-rw-------@ 1 _mysql  admin   2334552 Apr 30 06:23 wp_options.MYD
-rw-------@ 1 _mysql  admin    318464 Apr 30 06:23 wp_options.MYI
-rw-------@ 8 _mysql  admin      8734 Apr 29 22:12 wp_options.frm
-rw-------@ 8 _mysql  admin      8682 Apr 29 22:50 wp_postmeta.frm
-rw-rw----@ 2 _mysql  admin  26169304 Apr 30 06:02 wp_posts.MYD
-rw-rw----@ 2 _mysql  admin  55496704 Apr 30 06:02 wp_posts.MYI
-rw-rw----@ 7 _mysql  admin     13684 Apr 30 01:14 wp_posts.frm
-rw-------@ 2 _mysql  admin    198896 Apr 30 05:55 wp_stt2_meta.MYD
-rw-------@ 2 _mysql  admin    475136 Apr 30 05:55 wp_stt2_meta.MYI
-rw-------@ 8 _mysql  admin      8698 Apr 29 22:12 wp_stt2_meta.frm
-rw-------@ 8 _mysql  admin      8666 Apr 29 22:50 wp_term_relationships.frm
-rw-------@ 8 _mysql  admin      8768 Apr 29 22:50 wp_term_taxonomy.frm
-rw-------@ 8 _mysql  admin      8668 Apr 29 22:50 wp_terms.frm
-rw-------@ 8 _mysql  admin      8684 Apr 29 22:50 wp_usermeta.frm
-rw-------@ 8 _mysql  admin      8968 Apr 29 22:50 wp_users.frm
-rw-------@ 8 _mysql  admin         0 Apr 29 22:12 wp_visitor_maps_ge.MYD
-rw-------@ 8 _mysql  admin      1024 Apr 29 22:12 wp_visitor_maps_ge.MYI
-rw-------@ 8 _mysql  admin      8628 Apr 29 22:12 wp_visitor_maps_ge.frm
-rw-------@ 8 _mysql  admin        84 Apr 29 22:12 wp_visitor_maps_st.MYD
-rw-------@ 8 _mysql  admin      2048 Apr 29 22:50 wp_visitor_maps_st.MYI
-rw-------@ 8 _mysql  admin      8622 Apr 29 22:12 wp_visitor_maps_st.frm
-rw-------@ 8 _mysql  admin    160612 Apr 29 22:12 wp_visitor_maps_wo.MYD
-rw-------@ 8 _mysql  admin     17408 Apr 29 22:50 wp_visitor_maps_wo.MYI
-rw-------@ 8 _mysql  admin      9408 Apr 29 22:12 wp_visitor_maps_wo.frm
-rw-------@ 8 _mysql  admin         0 Apr 29 22:12 wp_wpseon_syndacc.MYD
-rw-------@ 8 _mysql  admin      1024 Apr 29 22:50 wp_wpseon_syndacc.MYI
-rw-------@ 8 _mysql  admin     12901 Apr 29 22:12 wp_wpseon_syndacc.frm
-rw-------@ 4 _mysql  admin     34032 Apr 30 05:10 wp_wpseon_visits.MYD
-rw-------@ 3 _mysql  admin      4096 Apr 30 06:16 wp_wpseon_visits.MYI
-rw-------@ 8 _mysql  admin      9085 Apr 29 22:12 wp_wpseon_visits.frm

As you can see from the above listing the one that display in my phpadmin are the one with MYD MYI and frm which there are 11 tables that really display and the rest is missing.

I don't know why this is really happening?

share|improve this question
1  
Did you remember to stop the MySQL service before restoring the files? –  John Gardeniers Apr 30 '12 at 1:05
    
@JohnGardeniers I did tried that :( –  Ali Apr 30 '12 at 1:15

1 Answer 1

up vote 4 down vote accepted

This may not be of much help to you now, but the correct way to back up a database is to use a utility to dump it (into a serialized form) and save that. Then, when you need to restore it, you can simply empty out the database and then import your dump. Some software allows you to create database backups from its web interface, or you can use the mysqldump utility.

The problem you will experience with taking backups by saving the actual database files is that they are something of a black box. You have few or no guarantees about them. For example, the DBMS (mysql) may be storing some things in memory and waiting to flush them to disk for memory reasons. This means that when you do this, even assuming you got all the files and copied them back correctly and the DBMS happens to accept them, they may be in an inconsistent state if you do anything other than back the data directory up in its entirety while the mysql daemon is not running.

However, check that mysql has permission on the table files. For instance, if you are using MyISAM, check that tablename.frm, tablename.MYD, and tablename.MYI exist. If you are using innodb, there is also a file called ibdata1 and another one called db.opt which stores part of the table definitions, if I recall.

Generally, if you are going to do a binary restore, which is kind of a last resort thing, you do not just restore some of the data directory; you stop the mysql daemon, rename the existing data directory, and copy the entire backup directory into place before restarting the daemon. Trying to do a binary restore on only some tables has undefined results including but not limited to what you have experienced.

share|improve this answer
    
great information I'm going to see what can I do with your suggestion! –  Ali Apr 30 '12 at 0:59
    
Check out this link also: dev.mysql.com/doc/refman/5.0/en/innodb-backup.html –  Falcon Momot Apr 30 '12 at 1:00
    
I don't know why I see this listing missing part. –  Ali Apr 30 '12 at 1:30

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.