MySQL 5.7 cannot be logged on

MySQL 5.7 cannot be logged on

First, MySQL version 5.7 was released. The manager asked me to build LNMP with the latest version. Many problems were encountered during the setup process. Today, I will briefly discuss a problem with MySQL:

After the installation is complete, you cannot log on to mysql. It is reasonable to say that after the new installation, the root user is logged on by default, and the password should be empty. Therefore, you can directly log on, but the following problem occurs:

[Root @ bogon mysql] # bin/mysql
ERROR 1045 (28000): Access denied for user 'root' @ 'localhost' (using password: NO)

[Root @ bogon mysql] # bin/mysqladmin-uroot password
Mysqladmin: connect to server at 'localhost' failed
Error: 'Access denied for user' root' @ 'localhost' (using password: NO )'

In this case, my response is whether the new version of mysql has a password by default for security reasons. I check whether the new version of mysql has a password generated by default, the password file is directly written in the log-error log File (in version 5.6, it is stored as root /. the mysql_secret file is more concealed and may be at a loss if you are not familiar with it). However, I checked log_error but did not find the password, and there was no root /. mysql_secret file;

Then I tried to add skip-grant-tables to the configuration file, and then I went in after the restart. So it must be a password problem, so I started to change the password.

Mysql> update user set password = password ('000000') where user = 'root ';
ERROR 1054 (42S22): Unknown column 'Password' in 'field list'
Mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
Mysql> update user set password = password ('000000') where user = 'root ';
ERROR 1054 (42S22): Unknown column 'Password' in 'field list'

However, if this problem occurs, I cannot change it. In the official method, whether or not I use -- skip-grant-tables to start mysql, the test fails. You can test it:
Shell> mysql-uroot-p 'Password' # password is the password in. mysql_secret.
Mysql> set password = PASSWORD ('newpasswd ');

However, according to the above operations, the official instructions are not very reliable .........

So I began to search for various solutions, and with the help of my friends, I finally found a solution -----------------------

We deleted the root @ localhost user and created this user.

Mysql> delete from mysql. user where user = 'root' and host = 'localhost ';
Query OK, 1 row affected (0.01 sec)
Mysql> select user, host from mysql. user;
+ ----------- +
| User | host |
+ ----------- +
| Mysql. sys | localhost |
+ ----------- +
1 row in set (0.00 sec)
Mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
Mysql> grant all on *. * to 'root' @ 'localhost' identified by '123 ';
Query OK, 0 rows affected, 1 warning (0.00 sec)
Mysql> select user, host from mysql. user;
+ ----------- +
| User | host |
+ ----------- +
| Mysql. sys | localhost |
| Root | localhost |
+ ----------- +
2 rows in set (0.01 sec)

However, this still doesn't work. You can find a problem by using the following command:

Mysql> select * from mysql. user \ G;

Grant display N -- the mysql permission architecture system (which should be said in this case) is disabled. so .............

Mysql> grant all on *. * to 'root' @ 'localhost' identified by '000000' with grant option;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

Exit mysql, note skip-grant-tables, and restart mysql. Now you can log on.

Bytes -----------------------------------------------------------------------------------------------

Of course, many of my friends' mistakes may be similar to me, but they are not the same. If the above method still does not work, leave a message with me and we can discuss it.

Of course, I am not very familiar with mysql. I am trying to delete users. What should I do if there is data in it? In fact, just backup it in case of backup; my friend told me that deleting a user and creating a user does not affect the data. It is okay because I have no actual operations, so it is difficult to guarantee that backup is better and safer, it would be better for us if data is not affected.

This article permanently updates the link address: