I have a fresh centos 6.2 with mysql in 5.1.52. Then I saw two empty user name and I deleted that then I saw 3 root user deleted that and created one like this CREATE USER 'root'@'%' IDENTIFIED BY '**';. But when I go in as root into my phpMyAdmin now I cannot create a new database. What wrong did I do here?
Your problem stems from deleteing all the root users first.
When you create a user using
If you do not issue GRANT right away, all the db Privileges are disabled (i.e., in the mysql.user table, update_priv='N', delete_priv='N', etc.)
If you have root@localhost and still cannot access it you may have to hack into it like this:
STEP 01) Restart mysql like this
STEP 02) Enter mysql from the command line (no password needed at this point)
STEP 03) Do
You will have to tweak each privilege since the GRANT command does not work will
For MySQL 5.1, you can enter a new root@localhost whose password is 'whatever' as follows:
STEP 04) restart mysql
You can login as root@localhost from here with the password 'whatever'.
Feel free to replace whatever with the password you want.
Give it a Try !!!
UPDATE 2012-04-10 11:28 EDT
When you did the fresh installation, you will see anonymous users in mysql.user table. The anonymous users are the users that are blank. DELETE THEM IMMEDIATELY BECAUSE THAT CAN PRESENT A SECURITY RISK !!!
Here is why : Anonymous users have access to any database whose first 4 letters are
To confirm the need to do this, please note what MySQL 5.0 Certification Study Guide says on Page 498 Paragraph 6 in its bulletpoints:
UPDATE 2012-04-10 12:50 EDT
On a fresh installation, you can just run mysql_secure_installation . If you have loaded your users in already, you can run the bullet points yourself (suppose you want to set them to myckrit. Just run these commands if you ae not sure what mysql_secure_installation is doing):
|show 7 more comments|
You need to grant the appropriate privileges for the root account, not just create it. MySQL does not know on its own that you want a Super user account.
You should use the appropriate
In case you do not have enough privileges for granting new privileges, you can try a couple of options.
To get root access back, stop your server and start it with the
If phpMyAdmin lets you login (without a password), or if you have command-line access, connect and issue (
Log out and then connect as root@localhost (
You don't want root at any host! Also, you will want to do this as quickly as possible if you still have networking enabled.