Create default ‘root’ user for MySQL

Recently, I had the issue that the MySQL ‘root’ user could not log into the data base:

shell> mysql -u root -p
Access denied for user 'root'@'localhost' (using password: YES)

The root cause was not that a wrong password was used or the user ‘root’ had no access rights. The root cause was that the user ‘root’ was not existing in the mysql.user table. In fact, the whole user table was empty. So the default ‘root’ user needs to be recreated again.

In order to log into MySQL without a user and password, MySQL needs to be restarted with the –skip-grant-tables  option.

shell> service mysqld stop
shell> /usr/bin/mysqld_safe --skip-grant-tables &

Then log into MySQL:

shell> mysql

The usual CREATE USER  command will not work here as this command would also set default permissions, however starting MySQL without grant tables will not allow you to set permissions. So the user ‘root’ needs to manually inserted into the mysql.user table, along with all privileges.

mysql> USE user;

mysql> INSERT INTO user (Host, User, Password) VALUES ('localhost','root','MyNewPass');
Query OK, 1 rows affected (0.04 sec)

mysql> UPDATE user SET 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' WHERE user='root';
Query OK, 1 rows affected (0.03 sec)

Newer versions of MySQL might contain more privileges so make sure that all have been updated (all ‘*_priv’ columns contain a ‘Y’).

mysql> SELECT * FROM user WHERE user='root';

Then quit the database console, kill the mysqld_safe daemon, start the standard mysql daemon and retest it.

mysql> quit;
shell> killall mysqld_safe
shell> service mysqld start
shell> mysql -u root -p

The default ‘root’ user has now been restored and access with a password should be possible again!