For some reason when I login into MySQL - mysql -uroot -p*****

I get this error when running this procedure -

mysql> create database new_database;
ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'new_database'

These are my privileges currently

Grants for root@localhost                                               
| GRANT USAGE ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD <secret> |
| GRANT ALL PRIVILEGES ON `testdb`.* TO 'root'@'localhost'    

How do I get all privileges or superuser to root@localhost

  • Unless this turns out to be a pi specific bug, this is a general sql question. Also need more info, distro and mysql version. I have seen different issues pop up similar with this in different versions of both. – Dan V Jun 15 '16 at 22:57

Try logging in like this

mysql -u root -p

You are telling it to log as the user root with a password you will provide.

It will prompt you for your password and allow you to access mysql.

That is how I access it as root and have no problem with making changes.

You can try granting all privileges on the database after creating it:

TO 'root'@'localhost'
IDENTIFIED BY 'your_password';

Then you can show the grants for root and verify it has access:

SHOW GRANTS FOR 'root'@'localhost';
| improve this answer | |
  • Pretty much identical to my login - I add my password after the '-p' to speed things up. Still get an error tho. – citra_amarillo Jun 15 '16 at 21:30
  • Edited the answer to expand on granting access. – Jorge Cuevas Jun 15 '16 at 21:43
  • it throws error as soon as I try to create the new_database, so granting privilge would theoretically come after creation of new db, for me anyway – citra_amarillo Jun 15 '16 at 21:50
  • when I run your above command I get - ERROR 1227 (42000): Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation – citra_amarillo Jun 15 '16 at 22:19
  • found this link on Stack Overflow, might help you with granting the privileges for root. stackoverflow.com/questions/1709078/… – Jorge Cuevas Jun 15 '16 at 22:23

Not the answer you're looking for? Browse other questions tagged or ask your own question.