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< ----- 8< ----- 8< ----- 8< ----- 8< ----- 8< -----
sudo /etc/init.d/mysql stop
sudo mysqld --skip-grant-tables &
mysql -vv < restore_root_privileges.sql
sudo /etc/init.d/mysql restart
mysql -u root -p
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;
mysql> quit;
2 comments
Drupal root user
Greg,
I'm trying to do something similar to what you did for the actual Drupal installation in MySQL. I want to update the 'users' table in the Drupal database, and create a user there with superuser access to the Drupal site. Any ideas?
Thanks
Totally different
Restoring the MySQL root user and messing with Drupal's user tables are totally different things, but anyway:
In two words, not possible. *ONLY* user 1 can be the superuser. However, this module will help you most of the way:
http://drupal.org/project/adminrole
As it notes on the project page though, user 1 is still the only user who can run update.php. There can be only one! =)
Post new comment