Shulou(Shulou.com)05/31 Report--

Editor to share with you the above version of MySql8.0 how to change the ROOT password, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

Deployment environment:

Install version red hat Cent 7.0

MYSQL version 8.0.2.0

Failure condition occurs after successful deployment:

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

two。 Log in normally after adding skip-grant-table from the / etc/my.cnf configuration file, but cannot create multiple operations such as users

To sum up:

If you want to enter mysql, you can't operate multiple instructions, and you can't enter mysql if you want to operate multiple instructions.

Digging the hole:

Many ways have been found on the Internet, first of all, join skip-grant-table. Then go in and refresh the permission table, restart the service, enter the root without a password, and refresh the permission table after changing the root password. I've tried a lot of methods, but they're all wrong. It is always wrong to modify the root.

Gave me a reminder whether the password policy and change syntax of the above version of mysql8.0 are wrong.

Do it again:

# vim / etc/my.cnf

[mysql]

Add skip-grant-table

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

[enter by hitting 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 notsatisfy the current policy requirementsmysql > alter user'root'@' localhost'IDENTIFIED BY' MyNewPass@123';ERROR 1396 (HY000): Operation ALTER USERfailed for 'root'@'localhost'mysql > alter user'root'@'%' IDENTIFIED BY' MyNewPass@123'

[mysql8.0 above password policy restrictions must be case plus numeric special symbols, I used mysqladmin,set,update before, refer to the predecessors who modified the root password: it is introduced at the end of the article.

Query OK, 0 rows affected (0.05 sec)

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

[root@localhost ~] # vim / etc/my.cnf [delete] [root@localhost ~] # systemctl stopmysqld.service [root@localhost ~] # systemctl startmysqld.service [root@localhost ~] # mysql-uroot-pmysql > CREATE USER dbadmin@localhost-> IDENTIFIED BY 'pwd123';ERROR 1819 (HY000): Your password does notsatisfy the current policy requirementsmysql > CREATE USER dbadmin@localhost-> IDENTIFIED BY' Pwd123';ERROR 1819 (HY000): Your password does notsatisfy the current policy requirementsmysql > CREATE USER dbadmin@localhost-> IDENTIFIED BY 'Pwd@123' ERROR 1819 (HY000): Your password does notsatisfy the current policy requirementsmysql > 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 complexity of setting]

Second, use the SQL tool for remote connection, here using SQLyog for remote connection.

Generally speaking, it is not possible to connect directly with the ROOT user's account password, the instant password is correct.

A new mysql_native_password function has been added in MYSQL 8.0 to make remote connections by changing the password of this function.

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

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

Second, Root users can be used to log in to the newly added users, authorize 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 is dominated by mysql-native_passwd, so you should pay attention to logging in from the Shell interface.

Ps: let's take a look at several ways for MySQL to change the root password.

Execute SET PASSWORD FOR 'root'@'localhost' = PASSWORD (' newpass') directly under Navicat for MySQL; you can

Method 1: use the SET PASSWORD command

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

Method 2: use mysqladmin

Mysqladmin-u root password "newpass"

If root has already set a password, use the following methods

Mysqladmin-u root password oldpass "newpass"

Method 3: edit the user table directly with UPDATE

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

You can do this when you lose your root password

Mysqld_safe-- skip-grant-tables& mysql-u root mysql mysql > UPDATE user SET password=PASSWORD ("new password") WHERE user='root'; mysql > FLUSH PRIVILEGES; are all the contents of this article entitled "how to change ROOT passwords in versions above MySql8.0". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.