domenica 30 dicembre 2012

How to reset root users privileges in MySQL


If you are having problems with MySQL root user privileges, not related with password issues. I can understand your frustration, because you can connect to DB engine, but you can't do anything select/update etc.
Below you can find a short solution.

1. Stop your MySQL service
#/etc/init.d/mysqld stop (UNIX style)

2. Start your MySQL with privileges
#/usr/bin/mysqld_safe  --skip-grant-tables

3. Connect to mysql DB with your root user
#/usr/bin/mysql -uroot -p mysql

4. Update root privileges
update user set Select_priv='Y', Insert_priv='Y',Update_priv='Y',Delete_priv='Y',
        Create_priv='Y',Drop_priv='Y',Reload_priv='Y',Shutdown_priv='Y',
        Process_priv='Y',File_priv='Y',Grant_priv='Y',References_priv='Y',
        Index_priv='Y',Alter_priv='Y',Show_db_priv='Y',Super_priv='Y',
        Create_tmp_table_priv='Y',Lock_tables_priv='Y',Execute_priv='Y',
        Repl_slave_priv='Y',Repl_client_priv='Y',Create_view_priv='Y',
        Show_view_priv='Y',Create_routine_priv='Y',Alter_routine_priv='Y',
        Create_user_priv='Y',Event_priv='Y',Trigger_priv='Y' where User='root';
 
After that, you just need to stop the MySQL service and start again.

Nessun commento:

Posta un commento