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

I tried this mysql> GRANT ALL PRIVILEGES ON \*.\* TO 'root'@'localhost' WITH GRANT OPTION; ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) — Ganesh Jadhav, Jun 08 '17 at 13:36
Ah wait, so you're trying to give yourself an upgrade in privileges. — verhie, Jun 08 '17 at 13:40
Yes, I am logged in as root user and trying to create a database. but replied with access denied. — Ganesh Jadhav, Jun 08 '17 at 13:43
I think this is what you need to read: https://stackoverflow.com/questions/21714869/error-1044-42000-access-denied-for-root-with-all-privileges — verhie, Jun 08 '17 at 13:54

score 2 · Answer 1 · edited Dec 31 '19 at 13:49

2

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';

edited Dec 31 '19 at 13:49

John

1
13
98
177

answered Jun 08 '17 at 13:32

Chukwu Remijius

323
1
14

I have used GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION; – Ganesh Jadhav Jun 08 '17 at 13:41
Try grant all on people.* to 'cgp'@'localhost' identified by 'myPass'; – Chukwu Remijius Jun 08 '17 at 14:04
check this similar question https://serverfault.com/questions/669668/granting-database-permissions-in-mysql – Chukwu Remijius Jun 08 '17 at 14:05
I do not have mysql db and tried to check users table in all databases on server but does not have user table . – Ganesh Jadhav Jun 08 '17 at 14:15
Were are you connecting? localhost or webserver? – Chukwu Remijius Jun 08 '17 at 14:21

score 1 · Accepted Answer · answered Jun 14 '17 at 07:51

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

Stop MySQL service
Run mysqld_safe --skip-grant-tables &
Type MySQL -u root -p and press enter.
Enter your password
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

mysql acces denied for creating database

2 Answers2