A Blog about technology, linux, peer2peer, cool new downloads and software development.

Friday, April 10, 2009

restore / repair / reset mysql root privileges

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;

(The password of the "debian-sys-maint" user is : sudo cat /etc/mysql/debian.cnf )

13 comments:

Alex said...
This comment has been removed by a blog administrator.
brian said...

Thank you so much for posting this, works great when your root account is hosed.

Alexis said...
This comment has been removed by a blog administrator.
Alex said...
This comment has been removed by a blog administrator.
Anonymous said...

Very Nice!
Thx! ;D

Anonymous said...

Thx a lot! Saved my life....

JuanC.Alpízar said...

This is very useful works very fine, however if you're under skip grant tables you may not be able to use the GRANT command once you log on MySQL terminal. However the statements to grant permissions are loaded from the .sql file.
Tyvm pretty useful :)

Anonymous said...

thx! excelent tutorial.

Vít Šesták 'v6ak' said...

The commands aren't up to date, because there are some new permissions. But you can generate a new SQL script:

echo 'select * from mysql.user'| mysql $(MYSQL_LOGIN_PARAMS) | grep -oE '([A-Za-y0-9\_]+)_priv' | sed "s#.*#update mysql.user set \0='y' where user='root';#" > restore-root-privileges.sql

subhojit said...

awesome blog!!!

subhojit said...

awesome blog!!!

subhojit said...

awesome blog!!! thanks

Anonymous said...

awesome blog!! thanks