Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

3 Answers 3

up vote 3 down vote accepted

Your problem stems from deleteing all the root users first.

When you create a user using CREATE USER, it simply enters a rwo in the mysql.user table. You needed to issue this command right afterwards:

CREATE USER root@localhost;
GRANT ALL PRIVILEGES ON *.* TO root@localhost IDENTIFIED BY 'whatever';

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

service mysql restart --skip-grant-tables --skip-networking

STEP 02) Enter mysql from the command line (no password needed at this point)

# mysql

STEP 03) Do desc mysql.user

  • You will see 17 rows for MySQL 4.1
  • You will see 37 rows for MySQL 5.0
  • You will see 39 rows for MySQL 5.1
  • You will see 42 rows for MySQL 5.5

You will have to tweak each privilege since the GRANT command does not work will skip-grant-tables is enabled

Example

For MySQL 5.1, you can enter a new root@localhost whose password is 'whatever' as follows:

DELETE FROM mysql.user WHERE user='root' AND host='localhost';
INSERT INTO mysql.user SET
                  Host = 'localhost',
                  User = 'root',
              Password = PASSWORD('whatever'),
           Select_priv = 'Y',
           Insert_priv = 'Y',
           Update_priv = 'Y',
           Delete_priv = 'Y',
           Create_priv = 'Y',
             Drop_priv = 'Y',
           Reload_priv = 'Y',
         Shutdown_priv = 'Y',
          Process_priv = 'Y',
             File_priv = 'Y',
            Grant_priv = 'Y',
       References_priv = 'Y',
            Index_priv = 'Y',
            Alter_priv = 'Y',
          Show_db_priv = 'Y',
            Super_priv = 'Y',
 Create_tmp_table_priv = 'Y',
      Lock_tables_priv = 'Y',
          Execute_priv = 'Y',
       Repl_slave_priv = 'Y',
      Repl_client_priv = 'Y',
      Create_view_priv = 'Y',
        Show_view_priv = 'Y',
   Create_routine_priv = 'Y',
    Alter_routine_priv = 'Y',
      Create_user_priv = 'Y',
            Event_priv = 'Y',
          Trigger_priv = 'Y',
              ssl_type = '',
              ssl_cipher = '',
             x509_issuer = '',
            x509_subject = '',
           max_questions = 0,
             max_updates = 0;

STEP 04) restart mysql

service mysql restart 

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 test. You can perform lots of CRUD intensive things in a test database. You may also want to rename the test databases to something completely different. Please read these links because I have addressed this issue before in the DBA StackExchange.

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:

On Unix, MySQL comes with a mysql_secure_installation script that can perform several helpful security-related operations on your installation. The script has the following capabilities:

  • Set a password for the root accounts
  • Remove any remotely accessible root accounts.
  • Remove the anonymous user accounts. This improves security because it prevents the possibility of anyone connecting to the MySQL server as root from a remote host. The results is that anyone who wants to connect as root must first be able to log in on the server host, which provides an additional barrier against attack.
  • Remove the test database (If you remove the anonymous accounts, you might also want to remove the test database to which they have access).

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):

UPDATE mysql.user SET password=password('myckrit') WHERE user='root' AND password='';
DELETE FROM mysql.user WHERE host='%';
DELETE FROM mysql.user WHERE user='';
DELETE FROM mysql.db WHERE LEFT(db,4) = 'test';
FLUSH PRIVILEGES;
share|improve this answer
    
I come to step 2 and stuck when I type mysql ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) –  newbie14 Apr 9 '12 at 18:07
    
Were you able to service mysql restart --skip-grant-tables --skip-networking ??? –  RolandoMySQLDBA Apr 9 '12 at 19:19
    
yes but then it just stop after with the mysql command –  newbie14 Apr 10 '12 at 14:50
    
ok now we did a fresh installation when I run this query select user,host from user; So why there two empty user? +------+-----------------------+ | user | host | +------+-----------------------+ | root | 127.0.0.1 | | | localhost | | root | localhost | | | localhost.localdomain | | root | localhost.localdomain | –  newbie14 Apr 10 '12 at 15:06
    
I updated my answer with my response to your comment –  RolandoMySQLDBA Apr 10 '12 at 15:28

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.

CREATE USER 'root'@'%' IDENTIFIED BY '*';
GRANT ALL ON *.* TO 'root'@'%' WITH GRANT OPTION;

You should use the appropriate @'host' option appropriate for your situation. If you don't need to remotely administer this database, then you should use @'localhost'.

UPDATE

In case you do not have enough privileges for granting new privileges, you can try a couple of options.

  1. Try to follow Rolando's instructions, but substitute some steps. It looks like he has instructions for Windows showing how to restart MySQL service. Instead of Rolando's step 1, do this: Stop MySQL. Then start MySQL with these options /usr/bin/mysqld_safe --skip-grant-tables --skip-networking --defaults-file=/etc/my.cnf &> /dev/null & (substitute path and file names as necessary for your environment).

  2. Replace mysql directory and all files with same files from another MySQL instance where you have full super user privileges - the directory which contains MySQL system tables such as user, host, db, tables_priv, etc. Before doing this, make sure to backup your database if it contains any important data. You could potentially replace mysql directory from a backed up copy if you have one.

share|improve this answer
    
now I have all the granting option where I cant even create another user ready. –  newbie14 Apr 9 '12 at 18:09

To get root access back, stop your server and start it with the --skip-grant-tables option.

If phpMyAdmin lets you login (without a password), or if you have command-line access, connect and issue (mysql -uroot -h127.0.0.1):

CREATE USER root@localhost IDENTIFIED BY 'xxx';
UPDATE mysql.user SET Grant_priv='Y', Super_priv='Y' WHERE User='root' AND Host='localhost';
FLUSH PRIVILEGES;

Log out and then connect as root@localhost (mysql -uroot -hlocalhost -p) type in your password and

GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost`;
DROP USER root@`%`;
FLUSH PRIVILEGES;

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.

share|improve this answer
    
the problem now I am just left the one root and cant do anything ready. I cannot grant etc. So how to go about now? I can connect via phpMyAdmin it works but I cannot create new database that is my big problem now. –  newbie14 Apr 9 '12 at 2:02
    
@newbie14 sorry I think I misread the question. I thought you couldn't log in to phpmyadmin at all. Do you have access to starting/stopping the server, or are you only limited to what you can do in phpMyAdmin? –  Derek Downey Apr 9 '12 at 16:56
    
yes I can start and stop via service mysqld start and stop. Now I only got this user root@% and is all limited I cannot go any more thing like grant, start slave and all the root functionality. Any help please. –  newbie14 Apr 9 '12 at 18:04

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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