Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

This question already has an answer here:

I'm working on a MySQL installation that has a user already created ("admin"), but that user doesn't have a password, and doesn't have any permissions, so I can't create new users or even reset the password of that user.

I'm using Ubuntu Server, and after looking through the help files I still can't find a way to add a user when the only existing user has no permissions. Am I missing something basic? Is there a way to add a super user from the command line before going into the MySQL command line?

share|improve this question

marked as duplicate by Chris S Mar 20 '13 at 5:48

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

3 Answers 3

up vote 8 down vote accepted

AS a user who doesn't have admin access you can't add another user. This would defeat the whole purpose of authentication/authorisation.

You can reset the root password following the step outlined in this link.

share|improve this answer
    
+1 While there are solutions to the problem the behaviour is by design and nothing else would make sense. –  John Gardeniers Apr 28 '11 at 3:55

Disable all access to the MySQL port, and then start the MySQL daemon manually passing --skip-grant-tables to disable all auth on the server.

share|improve this answer

There is something interesting to note about MySQL when using skip-grant-tables.

Once you restart mysqld with skip-grant-tables, you cannot use GRANT and REVOKE commands.

Option 1) UPDATE password for root@localhost;

You could do this:

UPDATE mysql.user SET password=PASSWORD('whateverpasswordyouwant') WHERE user='root' and host='localhost';

then restart mysqld.

Option 2) You could INSERT a record directly into mysql.user. If you want to establish a new user for your self called superuser, you must do the following (this works for MySQL 5.5):

INSERT INTO mysql.user SET
                  Host = 'localhost',
                  User = 'superuser',
              Password = PASSWORD('whateverpasswordyouwant'),
           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',
Create_tablespace_priv = 'Y',
              ssl_type = '',
              ssl_cipher = '',
             x509_issuer = '',
            x509_subject = '',
           max_questions = 0,
             max_updates = 0,
         max_connections = 1000000,
    max_user_connections = 1000000
;

Make sure you get every column set to 'Y' since MySQL 4.x, 5.0, 5.1, 5.5 have different permissions in mysql.user.

One more warning on using skip-grant-tables: When you start mysqld with this, everyone and his gramdmother can login without authentication. You should start mysql like this:

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

This will disable TCP/IP and no one can sneak into the DB. When you are done, just do this:

service mysql restart
share|improve this answer

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