1

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 !
Muhammad Gelbana
  • 135
  • 2
  • 7

2 Answers2

1

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

Community
  • 1
RolandoMySQLDBA
  • 185,223
  • 33
  • 326
  • 536
0

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.

Muhammad Gelbana
  • 135
  • 2
  • 7