MySQL 8.0 password modification process

Problem background: after MySQL is installed, the wrong option is set in the process of initializing the root account password, resulting in failure to log in.

step.1. Enter the command line interface of mysql

Modify / etc / MySQL / MySQL conf.d/mysqld. CNF file, add the skip grant tables option, so that you can log in with the root account password free.

[mysqld]
#
# * Basic Settings
#
user            = mysql
# pid-file      = /var/run/mysqld/mysqld.pid
# socket        = /var/run/mysqld/mysqld.sock
# port          = 3306
# datadir       = /var/lib/mysql

# add here
skip-grant-tables

After modification, restart mysql

$sudo service mysql restart
mysql -u root -p  # Enter directly

step.2. Change password

Step on pit I

After entering the mysql table, use the statement to modify the password and prompt the syntax error.

mysql> use mysql;
mysql> UPDATE user SET password=password("your password") WHERE user="root";
# Prompt 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 '("your password") where user='root'' at line 1

After consulting, it is found that the password function has been deprecated in version 5.7 and deleted in version 8.0.

Step on pit II

After finding that the password function is discarded, use the following statement to set the plaintext password and continue to prompt for error.

mysql> UPDATE user SET password="your password" WHERE user="root";
# Prompt 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 ''your password' where user='root'' at line 1

Continue to check the data and find that the password field has been renamed authentication in 8.0_ String field.

Step on pit three

Use the following statement to modify the password. The modification is successful, but you still can't log in.

mysql> UPDATE user SET authentication_string="your password" WHERE user="root";

When viewing the user table, it is found that the parameter of the plugin field of the root user is auth_sock . Because auth_ Because of the characteristics of the sock parameter, you can only log in on the unix machine, so it is changed to the default password login.

mysql> update user set plugin="caching_sha2_password" where user="root";

PS: in fact, if the plugin is not auth at this time_ If you use a sock, you can't log in. Because the clear text password is stored in the user table, and when logging in, MySQL will encrypt the entered password and then compare it with the password in the table. This comparison must be different, so it can't log in. (of course, this is only seen from other pages and has not been verified. If someone has verified it, please leave a message and let me know.)

About auth_ Features of sock: https://blog.csdn.net/ActionTech/article/details/109996422

About caching_sha2_password related information: https://www.cnblogs.com/olinux/p/13201497.html

Step on pit four

Check the official MySQL documentation and find the tutorial page for setting passwords.

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

Use the recommended command to change the password and continue to prompt an error.

mysql> ALTER USER user IDENTIFIED BY 'your password';
# ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement

The general meaning of the prompt statement is: MySQL is now running in the mode with skip grant tables option, and the code cannot be executed.

Finally

Use the following statement to refresh the permission table after setting the password of the root user to null.

mysql> update user set authentication_string="" where user="root";
mysql> flush privileges;
mysql> exit

Then comment out mysqld Skip grant tables statement in CNF file.

[mysqld]
#
# * Basic Settings
#
user            = mysql
# pid-file      = /var/run/mysqld/mysqld.pid
# socket        = /var/run/mysqld/mysqld.sock
# port          = 3306
# datadir       = /var/lib/mysql

# add here
# skip-grant-tables

After restarting MySQL, log in to root with an empty password.

$sudo service mysql restart
mysql -u root -p

Use the password change statement recommended by 8.0, and then refresh the permission table.

mysql> SET PASSWORD FOR 'root'@'localhost' = 'your password';
# At this time, if you set some policies related to password strength, you may be prompted that the password strength is not enough and the setting fails. Enter it again and set a strong password.
mysql> flush privileges;

MySQL password strength setting policy: https://blog.csdn.net/calistom/article/details/87939956

Done !