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:
Thank you so much for posting this, works great when your root account is hosed.
Very Nice!
Thx! ;D
Thx a lot! Saved my life....
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 :)
thx! excelent tutorial.
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
awesome blog!!!
awesome blog!!!
awesome blog!!! thanks
awesome blog!! thanks
Post a Comment