CODEWITHSUNDEEP
mysqlmacos
dengar81
dengar81

Reputation: 2525

Reset MySQL root password using ALTER USER statement after install on Mac

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?

Upvotes: 229

Views: 497505

Answers (23)

Анна Якимова
Анна Якимова

Reputation: 1

The command CREATE USER 'test'@'localhost' IDENTIFIED WITH mysql_native_password BY '123'; fixes the problem by creating the user with the specified password.

Upvotes: -1

Duc Toan Pham
Duc Toan Pham

Reputation: 632

In order to chage user's password:

  • Firstly, login by root $sudo mysql

  • Change user's password

    ALTER USER 'user'@'localhost' IDENTIFIED BY 'newPassword';

Upvotes: 2

David Lee
David Lee

Reputation: 205

i had same problem. access denied..

so, my solution is,

go to

mysql> USE mysql;
mysql> SELECT User, Host, plugin FROM mysql.user;

you can see plug in column name

i changed plugin from unix_socket -> to mysql_native_password

so, type this

mysql > use mysql;
mysql> update user set plugin='mysql_native_password' where user='root';
mysql> flush privileges;

done. my version so 10.1.48 MariaDB ubuntu 18

Upvotes: 0

majid bhatti
majid bhatti

Reputation: 103

In mysql 5.7.x(replication) this issue can happen because the on the slave user either the password is unmatched or the root user does not exists on master.

For example(in my case):

I created an instance, altered password for the root and before replicating the slave, I dropped the root user. When I made the slave instance this alert appeared on the query: (because I altered the password on master before dropping the root user, and slave was reading from the first log which was actually an alter root user statement)

show slave status\G

So there are 2 solutions that can be applied here:

  1. you can create root user on master host and slave host with same password
  2. you can run the query on master hostreset master;

and reconnect slave to master.

Upvotes: 0

Madmint
Madmint

Reputation: 6004

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

Upvotes: 574

HUAN LIU
HUAN LIU

Reputation: 49

in 5.7 version. 'password' field has been deleted. 'authentication_string' replace it

use mysql;
update user set authentication_string=password('123456') where user='root';
flush privileges;

Upvotes: 1

Robert A
Robert A

Reputation: 463

Mysql 5.7.24 get root first login

step 1: get password from log

 grep root@localhost /var/log/mysqld.log
    Output
        2019-01-17T09:58:34.459520Z 1 [Note] A temporary password is generated for root@localhost: wHkJHUxeR4)w

step 2: login with him to mysql

mysql -uroot -p'wHkJHUxeR4)w'

step 3: you put new root password 

SET PASSWORD = PASSWORD('xxxxx');

you get ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

how fix it?

run this SET GLOBAL validate_password_policy=LOW;

Try Again SET PASSWORD = PASSWORD('xxxxx');

Upvotes: 5

sunling90
sunling90

Reputation: 31

Here is the way works for me.

mysql> show databases ;

ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

mysql> uninstall plugin validate_password;

ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

mysql> alter user 'root'@'localhost' identified by 'root';

Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.03 sec)

Upvotes: 3

Siddharth Pandey
Siddharth Pandey

Reputation: 669

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

Upvotes: 13

Pravin Bansal
Pravin Bansal

Reputation: 4681

remember versions 5.7.23 and up - the user table doesn't has column password instead authentication string so below works while resetting password for a user.

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

Upvotes: 0

zhao
zhao

Reputation: 1124

mysql> SET PASSWORD = PASSWORD('your_new_password'); That works for me.

Upvotes: 0

Kenny Alvizuris
Kenny Alvizuris

Reputation: 445

On Ver 14.14 Distrib 5.7.19, for macos10.12 (x86_64), I logged in as: mysql -uroot -p then typed in the generated password by MySQL when you install it. Then..

ALTER USER 'root'@'localhost' IDENTIFIED BY '<new_password>';

Example:

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'Ab1234'; Query OK, 0 rows affected (0.00 sec) mysql> exit Bye $ mysql -uroot -p

And you can type in 'Ab1234'

Upvotes: 6

Vadim Smilansky
Vadim Smilansky

Reputation: 271

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';

Upvotes: 17

Manoj Behera
Manoj Behera

Reputation: 2815

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

Use this line...

Upvotes: 7

Johel Alvarez
Johel Alvarez

Reputation: 765

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');

MySQL Documentation

Upvotes: 34

sixsixsix
sixsixsix

Reputation: 1878

When you use SET PASSWORD = PASSWORD('your_new_password'); it may crash for it

(ERROR 1819 (HY000): Your password does not satisfy the current policy requirements)

.you can use SET GLOBAL validate_password_policy=LOW; to slove it.

Upvotes: 1

Malte
Malte

Reputation: 1

Had the problem after I installed mysql workbench and mysql community server. Got generated password during the server installation. Double clicking on the exisiting local instance in workbench triggered a dialog where I could set the new password.

Upvotes: 0

Abhishek Upadhyaya
Abhishek Upadhyaya

Reputation: 376

This worked for me:

ALTER USER USER() IDENTIFIED BY 'auth_string';

I found it here: http://dev.mysql.com/doc/refman/5.7/en/alter-user.html#alter-user-current

Upvotes: 3

shnraj
shnraj

Reputation: 2169

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

Upvotes: 193

Moumita
Moumita

Reputation: 79

I also got the same problem in mac OS X 10.10.4(Yosemite).SET PASSWORD work for me.Alter password for mysql- mysql> SET PASSWORD = PASSWORD('your_password'); Query OK, 0 rows affected, 1 warning (0.01 sec)

set your Mysql environment path variable in .bash_profile and add the below line
export PATH=$PATH:/usr/local/mysql/bin, after that, run the following command :source .bash_profile

Upvotes: 0

Ezio Shiki
Ezio Shiki

Reputation: 765

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 ~

via: https://dba.stackexchange.com/questions/118852/your-paswssword-has-expired-after-restart-mysql-when-updated-mysql-5-7-8-rcde

Upvotes: 35

user4942382
user4942382

Reputation:

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

Upvotes: 12

Mateo Barahona
Mateo Barahona

Reputation: 1391

Maybe try that ?

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('XXX');

or 

SET PASSWORD FOR 'root'@'%' = PASSWORD('XXX');

Depending on which access you use.

(and not sure you should change yourself field names...)

https://dev.mysql.com/doc/refman/5.0/en/set-password.html

Upvotes: 9

Related Questions