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 GRANT ing 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
When the 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 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 PWD are your MySQL user name and password.
6. Restart the MySQL service. Last updated 1 Jun 2022 |
![]() |