Recreate root user account

from the Artful MySQL Tips List

To create a user account in MySQL, you need the CREATE USER privilege. In most systems, only root has that privilege. What's more, MySQL requires any user GRANTing a privilege to have that privilege. And usually, only root has all privileges including GRANT. How then to proceed when, for whatever unforeseen reason, the root user account—the row in mysql.user with user='root'— has gone missing?

As for many such data problems, the first answer is backup: keep current backups of the mysql database (i) in a secure nearby location and also (ii) in a secure remote location.

When the root user account has gone missing and backups aren't available, you have an emergency. Since version 5.7, here's one way round it, requiring only that the mysql.user table have at least one row that's available to users, i.e., a row where ...

  authentication_string <> 
    '$A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED'

1 If the MySQL server is running, bring it down.

2 In a folder to which you have access, but which is neither the MySQL installation bin folder nor your MySQL data folder, create a text file (not a word processor file) init.sql that says ...

UPDATE mysql.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', 
  Event_priv='Y', Trigger_priv='Y' 
WHERE User='YOUR_USERNAME';
FLUSH PRIVILEGES;

... where YOUR_USERNAME is just that.

3 Open a terminal window with administrative privileges. In that window, in the folder where your MySQL server program executable is, run ...

mysqld --datadir=YOUR_MYSQL_DATA_PATH 
       --init-file=PATH_TO_YOUR_INITFILE/init.sql 
       --secure-file-priv=PATH_TO_YOUR_INITFILE 
       --console

... all on one line, where YOUR_MYSQL_DATA_PATH is just that, and PATH_TO_YOUR_INITFILE is the full path to the file you created, minus the filename.

4 If there are errors, the --console argument forces them to display in your terminal window; fix them, then run the script again. Some reported difficulties are described here.

5 Once the script has run without error, bring down this instance of the MySQL server, e.g., by running, in another terminal window, ...

mysqladmin -uUSR -pPWD shutdown

... where and PWD are your MySQL user name and password.

6. Restart the MySQL service.

Last updated 1 Jun 2022

Return to the Artful MySQL Tips page