mysql acces denied for creating database

Question

I want to create new DB in mysql.

I am logged in as root. I have set password for root user.

mysql> SHOW GRANTS FOR root;

+---------------------------------------------------------------------------------------------+

| Grants for root@%                                                                           |

+---------------------------------------------------------------------------------------------+

| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY PASSWORD <secret> WITH GRANT OPTION |

| GRANT PROXY ON ''@'' TO 'root'@'%' WITH GRANT OPTION                                        
|

+---------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> create database xxx;

ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'xxx'

One thing I notice that i do not have mysql db

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| info_schema        |
| sms_auto           |
| smstest            |
+--------------------+

Please can you help me

Answer 1

Make sure you set your user privileges right Like -host localhost -user root and -pass {your pass word}

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY PASSWORD;. Set a user privilege to a particular user like people and then follow the example grant all on people.*to 'root'@'localhost' identified by 'PASSWORD';

Answer 2

I have started MySQL in safe mode then assigned appropriate privileges( Referenced from here)

1. Stop MySQL service
2. Run mysqld_safe --skip-grant-tables &
3. Type MySQL -u root -p and press enter.
4. Enter your password
5. At the MySQL command line enter: use mysql;

After this, I have updated the privileges in mysql.user table for appropriate host and user. Flush privileges;

Now this works for me you can also refer the answer for update privilages