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 )
14 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
How to Change Username and Password in MySQL? Contact to MySQL Technical Support
If any user continuously trying to change their MySQL username and password but at the same time MySQL won’t let you in and can’t do anything else, then you can solve this problem? Make sure, when there is an issue with your MySQL database then try to get in touch with Cognegic’s MySQL Remote Support and MySQL Remote Service for quick help. We know that, a failed MySQL database how affect your daily work that’s why to solve this problem we provide MySQL Enterprise Backup to get rid of this problem.
For More Info: https://cognegicsystems.com/
Contact Number: 1-800-450-8670
Email Address- info@cognegicsystems.com
Company’s Address- 507 Copper Square Drive Bethel Connecticut (USA) 06801
Post a Comment