How to reset password in MySQL 8.0

After installing MySQL 8.0, the default user root has no password, and the default initialization password needs to be changed. If you forget the password of the root user, you also need to reset the password.

After installing MySQL 8.0 first, start the MySQL service. I installed and used it on MAC.

brew install mysql brew services start mysql

Because there is no password by default, enter the MySQL service directly first.

➜~mysql-uroot Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 13 Server version: 8.0.12 Homebrew Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>

Method 1

If the grant table is started when starting MySQL, that is, the skip-grant-tables startup parameter is not added. Use this method to set a password:

ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY '123456';

If you use database visualization software to link, the following error message appears:

Unable to load authentication plugin ‘caching_sha2_password’.

This is because MySQL 8.0 uses the new authentication mechanism caching_sha2_password. If you don’t want to use this, you can use the old authentication method.

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';

You can also modify the default configuration.

[mysqld] default_authentication_plugin=mysql_native_password

Method 2

If the grant table is not started when starting MySQL, add the skip-grant-tables startup parameter. At this time, the password of the root user is usually forgotten. The root user here is the MySQL user, and should not be confused with the user on Linux. Use this method to set a password:

First close and start MySQL, and enter the MySQL command line.

brew services stop mysql mysqld --skip-grant-tables --skip-networking mysql

Set password to blank.

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

Then close MySQL and start it normally. The skip-grant-tables startup parameter is not needed.

mysql-uroot

Repeat method 1 to set a password.

ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY '123456';

Recommended site searches: Wanwang domain name space, US host network, virtual space purchase, in domain name, Hong Kong server rental, registration-free, US server defense, US vps host, me domain name, how to rent a server,