Menu
I am using MySQL. My root user doesn't have all privileges. How can I get all privileges back to the root user? How to do it step by step?
287
To allow remote connections to the root account in MySQL, you should execute the mysql_secure_installation command. Normally you run this command when first setting up MySQL, but it can be run again at any point if you need to reset the root account password or allow remote connections to the account.
If the user account you are logged in as has SELECT privileges on the internal mysql database, you can see the privileges granted to other user accounts. To show the privileges of other accounts, use the following format: SHOW GRANTS FOR '<user>'@'<host>'; The output will display the privileges of the provided account.
To tell the server to reload the grant tables, perform a flush-privileges operation. This can be done by issuing a FLUSH PRIVILEGES statement or by executing a mysqladmin flush-privileges or mysqladmin reload command.
In MySQL, you can use the SHOW GRANTS command to show privileges granted to a user. Without any additional parameters, the SHOW GRANTS command lists the privileges granted to the current user account with which you have connected to the server.
This worked for me on Ubuntu:
Stop MySQL server:
/etc/init.d/mysql stop
Start MySQL from the commandline:
/usr/sbin/mysqld
In another terminal enter mysql and issue:
grant all privileges on *.* to 'root'@'%' with grant option;
You may also want to add
grant all privileges on *.* to 'root'@'localhost' with grant option;
and optionally use a password as well.
flush privileges;
and then exit your MySQL prompt and then kill the mysqld server running in the foreground. Restart with
/etc/init.d/mysql start
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
