Reset MySQL root password using ALTER USER statement after install on Mac
Question
I'm new to the whole Mac experience. I recently installed MySQL and it seems I have to reset the password after install. It won't let me do anything else.
Now I already reset the password the usual way:
update user set password = password('XXX') where user = root;
(BTW: took me ages to work out that MySQL for some bizarre reason has renamed the field 'password' to 'authentication_string'. I am quite upset about changes like that.)
Unfortunately it seems I need to change the password a different way that is unknown to me. Maybe someone here has already come across that problem?
Accepted Answer
If this is NOT your first time setting up the password, try this method:
mysql> UPDATE mysql.user SET Password=PASSWORD('your_new_password')
WHERE User='root';
And if you get the following error, there is a high chance that you have never set your password before:
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
To set up your password for the first time:
mysql> SET PASSWORD = PASSWORD('your_new_password');
Query OK, 0 rows affected, 1 warning (0.01 sec)
Reference: https://dev.mysql.com/doc/refman/5.6/en/alter-user.html
Popular Answer
If you started mysql using mysql -u root -p
Try ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';
Source: http://dev.mysql.com/doc/refman/5.7/en/resetting-permissions.html
Read more... Read less...
I have same problem on Mac
First, log in mysql with sandbox mode
mysql -u <user> -p --connect-expired-password
Then, set password
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('XXXX');
Query OK, 0 rows affected, 1 warning (0.01 sec)
It works for me ~
If you use MySQL 5.7.6 and later:
ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';
If you use MySQL 5.7.5 and earlier:
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MyNewPass');
On MySQL 5.7.x you need to switch to native password to be able to change it, like:
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'test';
Run these:
$ cd /usr/local/mysql/bin
$ ./mysqladmin -u root password 'password'
Then run
./mysql -u root
It should log in. Now run FLUSH privileges;
Then exit the MySQL console and try logging in. If that doesn't work run these:
$ mysql -u root
mysql> USE mysql;
mysql> UPDATE user SET authentication_string=PASSWORD("XXXXXXX") WHERE User='root';
mysql> FLUSH PRIVILEGES;
mysql> quit
Change xxxxxx to ur new password. Then try logging in again.
Update. See this http://dev.mysql.com/doc/refman/5.7/en/resetting-permissions.html
It should solve your problem.
If you are on oracle try this
ALTER USER username IDENTIFIED BY password
UPDATE user SET authentication_string=PASSWORD("MyPassWord") WHERE User='root'; 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 '("MyPassWord") WHERE User='root'' at line 1
Resolved with
ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';
Reference from below site
https://dev.mysql.com/doc/refman/8.0/en/resetting-permissions.html