Restoring The MySQL Root User

Photo of Greg Harvey
Mon, 2009-07-20 13:42By greg

Very quick post today. I rather foolishly didn't look at a back-up of a MySQL database someone sent to me this morning. I didn't realise it contained a copy of *their* mysql table. So when I restored it, all my user data got replaced with their user data.

Frack.

Worse. Their setup did not have a root user. I'm not sure how that works out for them, but it properly stumped me! Anyway, after a good degree of poking around on Google and trying various techniques, I finally found a restore for the MySQL root user that actually works:
http://hack2live.blogspot.com/2009/04/restore-repair-reset-mysql-root.html

Nothing else I found would save me, so I thought I'd blog this, not only for anyone else with the same problem, but in case I stuff up my root user again in the future.

Here's the actual text, in case that permalink isn't so perma!

cat > restore_root_privileges.sql

update mysql.user set Super_priv='y' where user='root';
update mysql.user set Select_priv='y' where user='root';
update mysql.user set Insert_priv='y' where user='root';
update mysql.user set Update_priv='y' where user='root';
update mysql.user set Delete_priv='y' where user='root';
update mysql.user set Create_priv='y' where user='root';
update mysql.user set Drop_priv='y' where user='root';
update mysql.user set Reload_priv='y' where user='root';
update mysql.user set Shutdown_priv='y' where user='root';
update mysql.user set Process_priv='y' where user='root';
update mysql.user set File_priv='y' where user='root';
update mysql.user set Grant_priv='y' where user='root';
update mysql.user set References_priv='y' where user='root';
update mysql.user set Index_priv='y' where user='root';
update mysql.user set Alter_priv='y' where user='root';
update mysql.user set Show_db_priv='y' where user='root';
update mysql.user set Super_priv='y' where user='root';
update mysql.user set Create_tmp_table_priv='y' where user='root';
update mysql.user set Lock_tables_priv='y' where user='root';
update mysql.user set Execute_priv='y' where user='root';
update mysql.user set Repl_slave_priv='y' where user='root';
update mysql.user set Repl_client_priv='y' where user='root';
update mysql.user set Create_view_priv='y' where user='root';
update mysql.user set Show_view_priv='y' where user='root';
update mysql.user set Create_routine_priv='y' where user='root';
update mysql.user set Alter_routine_priv='y' where user='root';
update mysql.user set Create_user_priv='y' where user='root';

----- 8

sudo /etc/init.d/mysql stop
sudo mysqld --skip-grant-tables &
mysql -vv

sudo /etc/init.d/mysql restart
mysql -u root -p
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;
mysql> quit;