CODEWITHSUNDEEP
mysql
coolesting
coolesting

Reputation: 1451

mysql - I deleted the user table! how can I recover it?

I sure i deleted the table 'mysql' in an accident , then have maked this error "Table mysql.user dosen't exist"

How can i fix it , or create a new table 'mysql'

Upvotes: 4

Views: 25291

Answers (3)

sashant
sashant

Reputation: 1

You can also fix by running a file mysql_upgrade found in mysql/bin directory. Just run that file and it will fix all your tables.

Upvotes: 0

James Williams
James Williams

Reputation: 4216

There is not alot you can do. If you can run queries and have recently backed up the whole database using "BACKUP TABLE", then you can try to run this query:

 RESTORE TABLE mysql.user FROM '/path/to/backup/directory'

If you cannot then you will have to execute mysql with '--skip-grant-tables' option in the command line. This will allow you to atleast gain access. You can also recreate it by running the mysql_install_db command. Found Here

Either way your table should look like this

 +-----------------+---------------+------+-----+---------+-------+ 
 | Field | Type | Null | Key | Default | Extra | 
 +-----------------+---------------+------+-----+---------+-------+ 
 | Host | varchar(20) | | PRI | | | 
 | User | varchar(6) | | PRI | | | 
 | Password | varchar(41) | | | | | 
 | Select_priv | enum('N','Y') | | | N | | 
 | Insert_priv | enum('N','Y') | | | N | | 
 | Update_priv | enum('N','Y') | | | N | | 
 | Delete_priv | enum('N','Y') | | | N | | 
 | Create_priv | enum('N','Y') | | | N | | 
 | Drop_priv | enum('N','Y') | | | N | | 
 | Reload_priv | enum('N','Y') | | | N | | 
 | Shutdown_priv | enum('N','Y') | | | N | | 
 | Process_priv | enum('N','Y') | | | N | | 
 | File_priv | enum('N','Y') | | | N | | 
 | Grant_priv | enum('N','Y') | | | N | | 
 | References_priv | enum('N','Y') | | | N | | 
 | Index_priv | enum('N','Y') | | | N | | 
 | Alter_priv | enum('N','Y') | | | N | | 
 +-----------------+---------------+------+-----+---------+-------+

Upvotes: 2

Adrian Cornish
Adrian Cornish

Reputation: 23876

Note you did not just delete the table - you deleted the entire mysql permission database. You need to locate the script 'mysql_install_db'. This will recreate the mysql permissions database.

Be aware that by deleting and recreating the permission database - other applications may stop working because their users/etc no longer exist.

Upvotes: 1

Related Questions