MySQL root account unable to create database

Question

I have tried 6 different forum rooms with a similar issue none of the solutions are working out for me.

Install MariaDB on my Raspberry Pi, which I am logging in via putty from my windows machine.

On Pi terminal I log in to MySQL via 'sudo mysql -u root -p enter the password, I have also tried logging without sudo. either way I can login to MariaDB

The issue is I have no Grants so when I enter the following command

MariaDB [(none)]>SHOW GRANTS FOR root@localhost 

the following is my output

+-------------------------------------------------------------------------- 
----- ------------------------------+
| Grants for root@localhost 
|
+--------------------------------------------------------------------------- 
---- ------------------------------+
| GRANT USAGE ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD 
 '*2470C0C06DEE 42FD1618BB99005ADCA2EC9D1E19' |
 +-------------------------------------------------------------------------- 
 ----- ------------------------------+

so when I login via phpMyadmin I am unable to create or access anything.

This is what I have tried so far.

mysql-secure_installation 
sudo service mysql restart 

tried viewing User table

 SELECT USER,HOST,PLUGIN FROM MYSQL.USER
GRANT ALL PRIVILEGES ON `%`.* TO '[root]'@'[hostname]' IDENTIFIED BY 
'[helpmelikehell]' WITH GRANT OPTION;

as well as . replace by '%' nothing seem to be working any help would be very much appreciated

• Ingo: This has nothing to do with Raspberry Pi. It is a MySQL problem. You should better ask at stackoverflow.com.

Answer 1

Although it is not a specific Pi problem, try the following

• stop the mysql server
• start mysql with mysqld_safe --skip-grant-tables &
• log in to mysql and grant the correct privileges
• at the end: FLUSH PRIVILEGES;
• shutdown with mysqladmin -u root -p shutdown
• restart normally

That should solve your problem.

• Waheed Rafiq: Thanks for the response, I have tried the above and I can't even shut down as it states mysqladmin shutdown failed error access denied you need at least one of the shutdown privileges for this operation. It seems to me I can't even kill the process, any other suggestion are most welcome
• Ljm Dullaart: sudo systemctl stop mysqld.service should stop it. If even that fails, sudo systemctl disable mysqld.service to make sure that it doesn't start at boot, and then reboot the pi.