In MySQL SERVER 8.0 the PASSWORD function not working

If you need a replacement hash to match the password() function, use this: SHA1(UNHEX(SHA1()));E.g.

mysql> SELECT PASSWORD('mypass');+-------------------------------------------+| PASSWORD('mypass')                        |+-------------------------------------------+| *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 |+-------------------------------------------+

and replacement that gives the same answer in version 8:

mysql> SELECT CONCAT('*', UPPER(SHA1(UNHEX(SHA1('mypass')))));+-------------------------------------------------+| CONCAT('*', UPPER(SHA1(UNHEX(SHA1('mypass'))))) |+-------------------------------------------------+| *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4       |+-------------------------------------------------+

OP's MySQL Server version is 8.0.12. From MySQL Documentation, PASSWORD function has been deprecated for version > 5.7.5:

Note

The information in this section applies fully only before MySQL 5.7.5, and only for accounts that use the mysql_native_password or mysql_old_password authentication plugins. Support for pre-4.1 password hashes was removed in MySQL 5.7.5. This includes removal of the mysql_old_password authentication plugin and the OLD_PASSWORD() function. Also, secure_auth cannot be disabled, and old_passwords cannot be set to 1.

As of MySQL 5.7.5, only the information about 4.1 password hashes and the mysql_native_password authentication plugin remains relevant.

Instead, of the PASSWORD function, you can use much better and secure encryption functions from here. More details from the MySQL server team can be seen here.

With MySQL 8.0.22, I had to do the following:

1. update /etc/mysql/my.cnf and add lines:

    [mysqld] skip-grant-tables

2. restart mysql and run some queries:

    > systemctl restart mysql > sudo mysql mysql> UPDATE mysql.user SET authentication_string=null WHERE User='root'; FLUSH PRIVILEGES; mysql> exit;

3. log in again and update the password:

    > mysql -u root mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'my password'; mysql> FLUSH PRIVILEGES;

4. update /etc/mysql/my.cnf and remove the line skip-grant-tables

    > systemctl restart mysql

5. Finally, test

    > mysql -u root -p