How to correctly modify the ROOT password in MySql8.0 and above versions

Deployment environment:

Installation version red hat Cent 7.0

MYSQL version 8.0.2.0

After successful deployment, a failure occurs:

1. After starting the MYSQL service normally, you cannot enter the root account and password in Linux.

2. After adding skip-grant-table to the /etc/my.cnf configuration file, you can log in normally, but you cannot create users or perform other operations.

In summary:

I want to enter mysql but cannot execute multiple commands, and I cannot enter mysql after executing multiple commands, which is an endless loop.

Digging stage:

I found many ways on the Internet. First, add skip-grant-table. Then refresh the permission table, restart the service, log in as root without a password, change the root password, and refresh the permission table again. Tried many methods but none of them worked. Modifying the root link is always wrong.

It reminded me that the password policy and change syntax of MySQL versions above 8.0 are incorrect.

Repeat the operation:

#vim /etc/my.cnf

【mysql】

Add skip-grant-table

#systemctl stop mysqld.service
#systemctl start mysqld.service
#mysql –u root

[Press Enter to enter]

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> alter user 'root'@'localhost'IDENTIFIED BY 'MyNewPass';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql> alter user 'root'@'localhost'IDENTIFIED BY 'MyNewPass@123';
ERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'localhost'
mysql> alter user 'root'@'%' IDENTIFIED BY 'MyNewPass@123';

[MySQL 8.0 and above password policy restrictions must be uppercase and lowercase plus numbers and special symbols. I used mysqladmin, set, update before, referring to the predecessors who modified the root password: there is an introduction at the end of the article.

Query OK, 0 rows affected (0.05 sec)

Exit, delete the Skip-grant-table statement, and restart the database

[root@localhost ~]# vim /etc/my.cnf [delete omitted]
[root@localhost ~]# systemctl stopmysqld.service
[root@localhost ~]# systemctl startmysqld.service
[root@localhost ~]# mysql -uroot –p
mysql> CREATE USER dbadmin@localhost 
 -> IDENTIFIED BY 'pwd123';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql> CREATE USER dbadmin@localhost 
 -> IDENTIFIED BY 'Pwd123';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql> CREATE USER dbadmin@localhost 
 -> IDENTIFIED BY 'Pwd@123';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql> CREATE USER dbadmin@localhost 
 -> IDENTIFIED BY 'MyNewPass@123';
Query OK, 0 rows affected (0.10 sec)

[Users can be created normally, but password security still requires a higher level of complexity]

Second, use SQL tools for remote connection. Here, SQLyog is used for remote connection.

Generally speaking, it is not possible to connect directly using the ROOT user's account and password, even if the password is correct.

The mysql_native_password function is newly added in MYSQL 8.0. Remote connection can be made by changing the password of this function.

2.1 First, you can change the native_password password of the ROOT user

mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY'MyPass@123';
Query OK, 0 rows affected (0.15 sec) 

2.2 Second, you can log in to the newly added user with the Root user, authorize it, and then connect remotely.

mysql> CREATE USER 'super'@'%'IDENTIFIED BY 'MyPass@123';
Query OK, 0 rows affected (0.10 sec)
Query OK, 0 rows affe mysql> GRANT ALLON *.* TO 'super'@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.10 sec)
mysql> Flush privileges;
Query OK, 0 rows affected (0.00 sec)cted(0.01 sec)
mysql> ALTER USER 'super'@'%' IDENTIFIEDWITH mysql_native_password BY 'MyPass@123';
Query OK, 0 rows affected (0.10 sec) 

Note: Changing the mysql_native_passwd password is equivalent to changing the user's original password. MYSQL 8.0 mainly uses mysql-native_passwd , so you need to pay attention when logging in from the Shell interface.

ps: Let's look at the various methods of modifying the root password of MySQL

Just execute SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpass'); under Navicat for MySQL.

Method 1: Use the SET PASSWORD command

mysql -u root
  mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpass');

Method 2: Using mysqladmin

mysqladmin -u root password "newpass"

If the root password has been set, use the following method

mysqladmin -u root password oldpass "newpass"

Method 3: Use UPDATE to edit the user table directly

mysql -u root
  mysql> use mysql;
  mysql> UPDATE user SET Password = PASSWORD('newpass') WHERE user = 'root';
  mysql> FLUSH PRIVILEGES;

When you lose your root password, you can

mysqld_safe --skip-grant-tables&
  mysql -u root mysql
  mysql> UPDATE user SET password=PASSWORD("new password") WHERE user='root';
  mysql> FLUSH PRIVILEGES;

Summarize

The above is the method that I introduced to you to correctly modify the ROOT password for MySql8.0 and above versions. I hope it will be helpful to you. If you have any questions, please leave me a message and I will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!
If you find this article helpful, please feel free to reprint it and please indicate the source. Thank you!