Restoring deleted 'root' user for MySQL v8.0.28

Question

I accidentally deleted my 'root' account of MySQL v8.0.28. I do have a non-admin account on there BUT it had no privileges to create, update, insert etc. and I even cann't access all the databases already created on that instance of MySQL.

I want to get my 'root' account back, so that I can access the databases created on that instance of MySQL. I will really appreciate any help to get

1. My 'root' user back on this instance OR
2. a way to copy the databases files to another instance of the same version of MySQL

Note: I am using WAMP on a Windows 10 Machine.

Looking anxiously for a way out! Thanks in Advance, Tanveer

Answer 1

I don't know why someone had given me a negative rating .. this is what did the trick for me, I am sharing it here as it may help someone else

Credit to @Fatiha, it actually was her [?] idea which started to put the things in place.

1. I created a SQL File, InitFile.sql, with the contents as given below

   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='tanmalik786'; FLUSH PRIVILEGES;

2.Added this line just below [wampmysqld64] section

init-file=d:/wamp64/bin/mysql/mysql8.0.28_1/bin/InitFile.sql

3. Kept examining the mysql.log file, to see for the errors and using mysqld from an Administrator Shell to check for the Privileges.

4. It Worked!

Finally I am able to retrieve all my hidden users including 'root' and all the databases are once again accessible to me, Thanks @Fatiha Once more!