MySQL lost root password

Question

This seems like a simple question but has caused me endless hours of frustration. It is only a small box inside the house that I am using for playing around.

I've tried the skip-grant-tables version and used the authentication_string field rather than the password version that is still knocking around. I've also tried the init-file method but no joy.

If I use the skip-grant-tables version and use the line:

update user set authentication_string=password('whywontitwork') where user='root';

The result I get is:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '('whywontitwork') where user='root'' at line 1

I don't understand (at all) what is wrong with the syntax. It is driving me mad for what should be a tiny little issue (I think). Does anybody have any ideas? Thanks in advance. MySQL Ver 8.0.21-0ubuntu running on Ubuntu 20.04.

You've to specify database while querying table, ie use `mysql.user` — Jeffy Mathew, Sep 21 '20 at 11:38
From the answers to the duplicate question pls follow this one: https://stackoverflow.com/a/58517061/5389997 — Shadow, Sep 21 '20 at 11:48

score 0 · Accepted Answer · answered Sep 21 '20 at 11:43

0

Function PASSWORD() is not working in MySql 8.0. You can use other encryption functions: LINK

How to reset root password in MySQL > 8.0: LINK

answered Sep 21 '20 at 11:43

Jsowa

9,104
5
56
60

score 0 · Answer 2 · answered Sep 21 '20 at 11:44

0

try with

ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPassword';

you can also create mysql-init file and apply this command by file

mysqld --init-file=/home/me/mysql-init &

answered Sep 21 '20 at 11:44

Derviş Kayımbaşıoğlu

28,492
4
50
72

MySQL lost root password

2 Answers2