How to retrieve a deleted table in MySQL?

So I just shot myself in the foot and feel like an idiot. I was playing around with granting and revoking permissions on my MySQL database at the same time as adding a user table to one of my databases. Anyways, I made the mistake of running:

Drop table user;

With my MySQL server admin account instead of my database-specific admin account on the main MySQL database, and autocommit was set to true. Now of course I get errors like:

SQL Error: Table 'mysql.user' doesn't exist

I didn't have any databases set up yet, so it's not too big a deal, but is there any way I can run a create table user .. statement to get my user table back, or should I just bite the bullet and reinstall my MySQL server?

Thanks!

UPDATE:

Here's the create script I was able to use to get my mysql.user table back. I needed to manually create users after running this script, but my root user was still there and had access to the database. Needless to say, my next task will be to schedule a backup task for my MySQL server :)

CREATE TABLE `user` (
  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
  `User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Password` char(41) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
  `Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Reload_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Shutdown_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Process_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `File_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Show_db_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Super_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Repl_slave_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Repl_client_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_user_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_tablespace_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `ssl_type` enum('','ANY','X509','SPECIFIED') CHARACTER SET utf8 NOT NULL DEFAULT '',
  `ssl_cipher` blob NOT NULL,
  `x509_issuer` blob NOT NULL,
  `x509_subject` blob NOT NULL,
  `max_questions` int(11) unsigned NOT NULL DEFAULT '0',
  `max_updates` int(11) unsigned NOT NULL DEFAULT '0',
  `max_connections` int(11) unsigned NOT NULL DEFAULT '0',
  `max_user_connections` int(11) unsigned NOT NULL DEFAULT '0',
  `plugin` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
  `authentication_string` text开发者_开发百科 COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`Host`,`User`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global
privileges'

Depends what you mean by "get my user table back". You can definitely create an identical user table with the same schema and the like, but any data in the original table has been permanently lost. It won't just reappear when you create the new table, sorry.

Thankfully, this does not require you to reinstall the MySQL server or anything. The server is just fine; you've just lost a table. Deleting tables does not harm the server in any way. Recreate it and you're good to go. (If you don't have a record of the table's structure, well, I'm sure you can figure it out.) And if you have backups, restore them. If not, well, I'm guessing you'll be keeping backups from now.

Sorry you've run into trouble, and here's hoping you'll be able to avoid such trouble in the future. Good luck!

There's no reasonable way to recover your table, and you don't really need to. If you have data you want to keep in your other databases, then follow these simple instructions. (Paths assume standard linux install. Don't just copy-paste, read, interpret, understand each command before you run it). Also,this is all from memory, I haven't tested any of this.

Stop your mysql server process

cd /var/lib/
mv mysql/mysql mysql/old_mysql_db
mv mysql mysql_save
mkdir mysql
chown --reference=mysql_save mysql
chmod --reference=mysql_save mysql
mysql_install_db
mv mysql/mysql mysql_save/
mv mysql ~/mysql_newinstall_deleteme
mv mysql_save mysql

Now start up your mysql server process, and go back to it. You're starting over from scratch with all your users and permissions, but your other databases should be intact, assuming that your mysql database wasn't using innodb.

Here's the latest table with 45 columns if you import an old user table from a backup that then refuses to upgrade.

CREATE TABLE `user` (
`Host` CHAR(60) NOT NULL DEFAULT '' COLLATE 'utf8_bin',
`User` CHAR(32) NOT NULL DEFAULT '' COLLATE 'utf8_bin',
`Select_priv` ENUM('N','Y') NOT NULL DEFAULT 'N' COLLATE 'utf8_general_ci',
`Insert_priv` ENUM('N','Y') NOT NULL DEFAULT 'N' COLLATE 'utf8_general_ci',
`Update_priv` ENUM('N','Y') NOT NULL DEFAULT 'N' COLLATE 'utf8_general_ci',
`Delete_priv` ENUM('N','Y') NOT NULL DEFAULT 'N' COLLATE 'utf8_general_ci',
`Create_priv` ENUM('N','Y') NOT NULL DEFAULT 'N' COLLATE 'utf8_general_ci',
`Drop_priv` ENUM('N','Y') NOT NULL DEFAULT 'N' COLLATE 'utf8_general_ci',
`Reload_priv` ENUM('N','Y') NOT NULL DEFAULT 'N' COLLATE 'utf8_general_ci',
`Shutdown_priv` ENUM('N','Y') NOT NULL DEFAULT 'N' COLLATE 'utf8_general_ci',
`Process_priv` ENUM('N','Y') NOT NULL DEFAULT 'N' COLLATE 'utf8_general_ci',
`File_priv` ENUM('N','Y') NOT NULL DEFAULT 'N' COLLATE 'utf8_general_ci',
`Grant_priv` ENUM('N','Y') NOT NULL DEFAULT 'N' COLLATE 'utf8_general_ci',
`References_priv` ENUM('N','Y') NOT NULL DEFAULT 'N' COLLATE 'utf8_general_ci',
`Index_priv` ENUM('N','Y') NOT NULL DEFAULT 'N' COLLATE 'utf8_general_ci',
`Alter_priv` ENUM('N','Y') NOT NULL DEFAULT 'N' COLLATE 'utf8_general_ci',
`Show_db_priv` ENUM('N','Y') NOT NULL DEFAULT 'N' COLLATE 'utf8_general_ci',
`Super_priv` ENUM('N','Y') NOT NULL DEFAULT 'N' COLLATE 'utf8_general_ci',
`Create_tmp_table_priv` ENUM('N','Y') NOT NULL DEFAULT 'N' COLLATE 'utf8_general_ci',
`Lock_tables_priv` ENUM('N','Y') NOT NULL DEFAULT 'N' COLLATE 'utf8_general_ci',
`Execute_priv` ENUM('N','Y') NOT NULL DEFAULT 'N' COLLATE 'utf8_general_ci',
`Repl_slave_priv` ENUM('N','Y') NOT NULL DEFAULT 'N' COLLATE 'utf8_general_ci',
`Repl_client_priv` ENUM('N','Y') NOT NULL DEFAULT 'N' COLLATE 'utf8_general_ci',
`Create_view_priv` ENUM('N','Y') NOT NULL DEFAULT 'N' COLLATE 'utf8_general_ci',
`Show_view_priv` ENUM('N','Y') NOT NULL DEFAULT 'N' COLLATE 'utf8_general_ci',
`Create_routine_priv` ENUM('N','Y') NOT NULL DEFAULT 'N' COLLATE 'utf8_general_ci',
`Alter_routine_priv` ENUM('N','Y') NOT NULL DEFAULT 'N' COLLATE 'utf8_general_ci',
`Create_user_priv` ENUM('N','Y') NOT NULL DEFAULT 'N' COLLATE 'utf8_general_ci',
`Event_priv` ENUM('N','Y') NOT NULL DEFAULT 'N' COLLATE 'utf8_general_ci',
`Trigger_priv` ENUM('N','Y') NOT NULL DEFAULT 'N' COLLATE 'utf8_general_ci',
`Create_tablespace_priv` ENUM('N','Y') NOT NULL DEFAULT 'N' COLLATE 'utf8_general_ci',
`ssl_type` ENUM('','ANY','X509','SPECIFIED') NOT NULL DEFAULT '' COLLATE 'utf8_general_ci',
`ssl_cipher` BLOB NOT NULL,
`x509_issuer` BLOB NOT NULL,
`x509_subject` BLOB NOT NULL,
`max_questions` INT(11) UNSIGNED NOT NULL DEFAULT '0',
`max_updates` INT(11) UNSIGNED NOT NULL DEFAULT '0',
`max_connections` INT(11) UNSIGNED NOT NULL DEFAULT '0',
`max_user_connections` INT(11) UNSIGNED NOT NULL DEFAULT '0',
`plugin` CHAR(64) NOT NULL DEFAULT 'mysql_native_password' COLLATE 'utf8_bin',
`authentication_string` TEXT NULL COLLATE 'utf8_bin',
`password_expired` ENUM('N','Y') NOT NULL DEFAULT 'N' COLLATE 'utf8_general_ci',
`password_last_changed` TIMESTAMP NULL DEFAULT NULL,
`password_lifetime` SMALLINT(5) UNSIGNED NULL DEFAULT NULL,
`account_locked` ENUM('N','Y') NOT NULL DEFAULT 'N' COLLATE 'utf8_general_ci',
PRIMARY KEY (`Host`, `User`)
)
COMMENT='Users and global privileges'
COLLATE='utf8_bin'
ENGINE=MyISAM
;

and the default user data:

INSERT INTO user (Host, User, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, Reload_priv, Shutdown_priv, Process_priv, File_priv, Grant_priv, References_priv, Index_priv, Alter_priv, Show_db_priv, Super_priv, Create_tmp_table_priv, Lock_tables_priv, Execute_priv, Repl_slave_priv, Repl_client_priv, Create_view_priv, Show_view_priv, Create_routine_priv, Alter_routine_priv, Create_user_priv, Event_priv, Trigger_priv, Create_tablespace_priv, ssl_type, ssl_cipher, x509_issuer, x509_subject, max_questions, max_updates, max_connections, max_user_connections, plugin, authentication_string, password_expired, password_last_changed, password_lifetime, account_locked) VALUES ('localhost', 'root', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', '', '', '', '', 0, 0, 0, 0, 'mysql_native_password', '', 'N', '2018-04-20 14:57:25', NULL, 'N'); INSERT INTO user_copy (Host, User, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, Reload_priv, Shutdown_priv, Process_priv, File_priv, Grant_priv, References_priv, Index_priv, Alter_priv, Show_db_priv, Super_priv, Create_tmp_table_priv, Lock_tables_priv, Execute_priv, Repl_slave_priv, Repl_client_priv, Create_view_priv, Show_view_priv, Create_routine_priv, Alter_routine_priv, Create_user_priv, Event_priv, Trigger_priv, Create_tablespace_priv, ssl_type, ssl_cipher, x509_issuer, x509_subject, max_questions, max_updates, max_connections, max_user_connections, plugin, authentication_string, password_expired, password_last_changed, password_lifetime, account_locked) VALUES ('localhost', 'mysql.session', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'Y', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', '', '', '', '', 0, 0, 0, 0, 'mysql_native_password', '*THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE', 'N', '2018-04-20 14:57:25', NULL, 'Y'); INSERT INTO user_copy (Host, User, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, Reload_priv, Shutdown_priv, Process_priv, File_priv, Grant_priv, References_priv, Index_priv, Alter_priv, Show_db_priv, Super_priv, Create_tmp_table_priv, Lock_tables_priv, Execute_priv, Repl_slave_priv, Repl_client_priv, Create_view_priv, Show_view_priv, Create_routine_priv, Alter_routine_priv, Create_user_priv, Event_priv, Trigger_priv, Create_tablespace_priv, ssl_type, ssl_cipher, x509_issuer, x509_subject, max_questions, max_updates, max_connections, max_user_connections, plugin, authentication_string, password_expired, password_last_changed, password_lifetime, account_locked) VALUES ('localhost', 'mysql.sys', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', '', '', '', '', 0, 0, 0, 0, 'mysql_native_password', '*THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE', 'N', '2018-04-20 14:57:25', NULL, 'Y');