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?

Sean
  • 115
  • 4

2 Answers2

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
RolandoMySQLDBA
  • 16,364
  • 3
  • 47
  • 80
  • 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 Feb 28 '13 at 20:43
  • Updated my answer... – RolandoMySQLDBA Feb 28 '13 at 20:46
  • I missed the part about db.frm, db.myd, and db.myi. It is all working now. Thanks – Sean Feb 28 '13 at 20:48
1

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

Jay
  • 6,439
  • 24
  • 34