-1

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

Tanveer Malik
  • 9
  • 3
  • Check [this](https://stackoverflow.com/questions/6085455/restoring-deleted-root-user-and-password-for-mysql) if it helps – Ergest Basha May 31 '22 at 08:52
  • I have already checked it before actually posting this question here .. that's an older version 5.7 and didn't worked for me. `https://stackoverflow.com/questions/6085455/restoring-deleted-root-user-and-password-for-mysql` – Tanveer Malik May 31 '22 at 08:54
  • 1
    try to give your non-admin user privilages using "UPDATE mysql.user SET Grant_priv='Y', Super_priv='Y' WHERE User='your_user_name';" than "FLUSH PRIVILEGES;" – Fatiha IMOUSSAINE May 31 '22 at 09:02
  • There is another [post](https://serverfault.com/questions/997678/the-target-table-user-of-the-insert-is-not-insertable-into-mariadb-10-4-11/997703#997703) which follows another post, check if one of them will help you. At the moment I can't test it on my won server – Ergest Basha May 31 '22 at 09:02
  • I have already tried both of the solutions as suggested by @FatihaIMOUSSAINE - They didn't help – Tanveer Malik May 31 '22 at 10:38

1 Answers1

0

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

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

  2. 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!

Tanveer Malik
  • 9
  • 3