Day 49th: mysql-5.7 can't sign in

Small Q: impregnable pass apocalypse is really like iron, and now the beginning of the more. --"Yi Qin Yu, Lou Shan"

Long time no record, originally a sum up a lot of problems, but because just entered the workplace, a lot of things to be positive, and I do not have a computer, so drag and drag, but not very good summary; today, a new problem, very targeted, so take the time to record.

--------------------------------------------------------------------------------------------------

First in MySQL out to the 5.7 version, the manager let the latest version to build LNMP, the building process encountered a lot of problems, today briefly talk about a MySQL problem:

After the installation is complete, unable to log on to MySQL, it is said that the new installation by default is the root user login, and the password should be empty, so you can directly into the, but there are the following problems

[[email protected] mysql]# Bin/mysqlerror 1045 (28000): Access denied for user ' root ' @ ' localhost ' (using Password:no)

[[email protected] mysql]# bin/mysqladmin-uroot passwordmysqladmin:connect to server at ' localhost ' failederror: ' Access Denied for user ' root ' @ ' localhost ' (using Password:no) '

At this time my reaction is not to be safe, the new version of MySQL default password, I look really, MySQL default installation will have password generation, the password file is written directly in the Log-error log file (in version 5.6 is placed in the Root/.mysql_secret file, More covert, unfamiliar words may be at a loss), but I went to check the Log_error, but did not find the password, and there is no Root/.mysql_secret file;

After I try to add a skip-grant-tables in the configuration file, after reboot can go in, so must be the password problem, so I began to change the password

mysql> Update user Set Password=password (' 123456 ') 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 (' 123456 ') where user= ' root '; ERROR 1054 (42S22): Unknown column ' Password ' in ' Field list '

But there is this situation, can not change, online said: The official way, the author whether or not to use--skip-grant-tables start MySQL test failed, the pro may test:
Shell>mysql-uroot-p ' password ' #password即. Password in Mysql_secret
Mysql>set PASSWORD = PASSWORD (' newpasswd ');

But according to the above operation, the official statement is not too reliable ...

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

We deleted the [email protected] user, and then we had a new 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  ' 123456 '; 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)

But it's not going to work, you'll find a situation with the following command

Mysql> select * from Mysql.user\g;

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M02/78/2F/wKioL1Z3nFewuRNsAABYbleNXwo509.png "title=" GD. PNG "alt=" Wkiol1z3nfewurnsaabyblenxwo509.png "/>

Grant shows that the N--mysql permission architecture system (which should be said) is off, so ...

Mysql> Grant All on * * to ' root ' @ ' localhost ' identified by ' 123456 ' with GRANT option; Query OK, 0 rows affected, 1 Warning (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.01 sec)

Quit MySQL, skip-grant-tables, and then restart MySQL. At this point, you can log in.

-----------------------------------------------------------------------------------------------

Of course, many friends of the fault may be similar to me, but not the same, if the above method or not, you can leave a message with me, we can discuss.

Of course, I am not too familiar with MySQL, I was thinking to delete the user, if there is data to do, in fact, just in case backup a bit better; and my friend told me actually delete the user and the new user does not affect the data, nothing, because I have no actual operation, so not good guarantee, in fact, backup a better and safer, Doesn't affect the data that's better for us.

Day 49th: mysql-5.7 can't sign in