1

I had a server die and I have been trying to restore the data onto a new server. I had a mysql database.

I have all the files stored in /var/lib/mysql. Each database had a database privileges associated with itself and one other database. This way, each site had it's own configuration to connect only to their database.

When trying to recover the database, I seem to have lost all the privileges granted on the databases except for the root user. Is there something I am missing or will I need to recreate all of the privileges?

Improve this question

2 Answers 2

Reset to default
2

This may sound a little risky but here it goes

If you have a physical backup copy of the following:

  • /var/lib/mysql/mysql/user.frm
  • /var/lib/mysql/mysql/user.MYD
  • /var/lib/mysql/mysql/user.MYI

copy them back to /var/lib/mysql/mysql, then service mysql restart

Since each user had specific database privileges, you must restore

  • /var/lib/mysql/mysql/db.frm
  • /var/lib/mysql/mysql/db.MYD
  • /var/lib/mysql/mysql/db.MYI
Improve this answer
3
  • I can see the users in the user table of the mysql database now. However, it seems like all the privileges have been reset. When I go to a database and look at its privileges, it does not show anything. Each database user is supposed to have specific privileges for its own database.
    – Sean
    Commented Feb 28, 2013 at 20:43
  • Updated my answer...
    – RolandoMySQLDBA
    Commented Feb 28, 2013 at 20:46
  • I missed the part about db.frm, db.myd, and db.myi. It is all working now. Thanks
    – Sean
    Commented Feb 28, 2013 at 20:48
1

Check you restored /var/lib/mysql/mysql. If that's corrupt, the data is gone I'm afraid.

Improve this answer
1
  • +1 for zeroing in on /var/lib/mysql/mysql first
    – RolandoMySQLDBA
    Commented Feb 28, 2013 at 20:49

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .