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'm installing MySQL on ubuntu and I changed the data directory. I created new data files using mysql_install_db command.

So now I need to access my database using root but it won't let me in. I tried starting the database by skipping the grant tables:

mysqld --skip-grant-tables --skip-networking &

Now I'm in, but the mysql.user table doesn't have a single row, which is logical after creating the data files from scratch, or isn't it ??

So I tried:

CREATE USER 'root'@'localhost' IDENTIFIED BY 'mypass';

But it won't run because I'm running with --skip-grant-tables !

  • So how can I create a root user from that point ?
  • And how can I stop the started mysql instance I just started with --skip-grant-tables because it seems I always have to killall mysqld to do it !
share|improve this question
    
What version of MySQL are you running ??? –  RolandoMySQLDBA Sep 22 '13 at 19:38
add comment

2 Answers

There are two techniques

TECHNIQUE #1 : Use an init file

INITFILE=/var/lib/mysql/init-file.sql
echo "SET @newpass = PASSWORD('mypass');" > ${INITFILE}
echo "CREATE USER 'root'@'localhost' IDENTIFIED BY @newpass;" >> ${INITFILE}
chown mysql:mysql ${INITFILE}
service mysql stop
service mysql start --init-file=${INITFILE}
rm -f ${INITFILE}

TECHNIQUE #2 : Inject User into mysql.user

With --skip-grant-tables enabled, run this:

REPLACE INTO mysql.user SET
                  Host = 'root',
                  User = 'localhost',
              Password = PASSWORD('whateverpasswordiwant'),
             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 = 0,
    max_user_connections = 0
;

Then, restart mysql as usual.

See my post In place upgrade from MySQL 5.5 to 5.6.11 removes all users from user table

share|improve this answer
    
Maybe the REPLACE statement has a small mistake. Consider the below corrections: host='localhost', user='root' –  user28562 Sep 22 '13 at 20:49
    
Shouldn't I GRANT all rights and WITH GRANT to root ? I had to do that to solve my problem, after inserting the root user in the mysql.user table. –  Muhammad Gelbana Sep 23 '13 at 11:37
    
Hi Rolando, regarding @Wagner's comment above, I converted it to a comment from an answer because it seems related to your answer? –  Jack Douglas Sep 23 '13 at 13:09
add comment

I've done the solutions mentioned above but I still, as root couldn't grant privileges to other users. This is how I did it:

  1. After INSERTing the root user manually into the mysql.user table
  2. GRANT ALL ON . TO 'root'@'localhost'; FLUSH PRIVILEGES; exit;
  3. killall mysqld

Without step #2, I couldn't create users nor grant them rights.

share|improve this answer
add comment

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.