A RANDOM PASSWORD HAS BEEN SET FOR THE MySQL root USER ! You will find that password in '/root/.mysql_secret'. You must change that password on your first connect, no other statement but 'SET PASSWORD' will be accepted. See the manual for the semantics of the 'password expired' flag. Also, the account for the anonymous user has been removed. In addition, you can run:
但是坑爹的是压根没在/root下找到.mysql_secret文件，好把，百度，使用无需验证的方式，修改my.cnf
[mysqld] port=3306 character-set-server = utf8 explicit_defaults_for_timestamp=1 skip-grant-tables=1就是这句，停止权限验证skip-grant-tables=1
重启mysql
[root@cdh etc]# service mysql restart Shutting down MySQL.. SUCCESS! Starting MySQL. SUCCESS! [root@cdh etc]#
登录上mysql,然后切换到mysql
[root@cdh etc]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.7.3-m13 MySQL Community Server (GPL) Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> use mysql Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed
接下来就是重点了修改密码,需要修改两次，一次authentication_string，一次 password
mysql> UPDATE user SET authentication_string=PASSWORD("123456") WHERE user='root';
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4 Changed: 4 Warnings: 0 mysql>
update user set password=password('123456') where user='root';
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4 Changed: 4 Warnings: 0
然后把之前的配置skip-grant-tables=1去掉，重新启动mysql
[root@cdh etc]# /etc/init.d/mysql restart Shutting down MySQL.. SUCCESS! Starting MySQL. SUCCESS! [root@cdh etc]# mysql -proot -p mysql: [Warning] Using a password on the command line interface can be insecure. Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.7.3-m13 Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
最后还是要再执行下修改密码的语句的
mysql> use mysql
ERROR 1820 (HY000): You must SET PASSWORD before executing this statement
mysql> SET PASSWORD = PASSWORD('123456');
Query OK, 0 rows affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
mysql>
搞定!!
参考1：http://blog.csdn.net/qq_31105311/article/details/50397610
参考2：http://www.linuxidc.com/Linux/2015-12/126628.htm
其实主要问题是，user表既有password又有authentication_string，网上说的都只修改了其中一个，导致一直无法用修改后的密码登录，根据参考2里面的语句
mysql> select * from mysql.user\G;
*************************** 1. row ***************************
Host: localhost
User: root
Password: *C2D0106C508BFCE6FE72C44482224A9C223BC873
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
Shutdown_priv: Y
Process_priv: Y
File_priv: Y
Grant_priv: Y
References_priv: Y
Index_priv: Y
Alter_priv: Y
Show_db_priv: Y
Super_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Execute_priv: Y
Repl_slave_priv: Y
Repl_client_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Create_user_priv: Y
Event_priv: Y
Trigger_priv: Y
Create_tablespace_priv: Y
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_native_password
authentication_string: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
password_expired: N
*************************** 2. row ***************************
Host: localhost.localdomain
User: root
Password: *C2D0106C508BFCE6FE72C44482224A9C223BC873
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
Shutdown_priv: Y
Process_priv: Y
File_priv: Y
Grant_priv: Y
References_priv: Y
Index_priv: Y
Alter_priv: Y
Show_db_priv: Y
Super_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Execute_priv: Y
Repl_slave_priv: Y
Repl_client_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Create_user_priv: Y
Event_priv: Y
Trigger_priv: Y
Create_tablespace_priv: Y
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_native_password
authentication_string: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
password_expired: N
*************************** 3. row ***************************
Host: 127.0.0.1
User: root
Password: *C2D0106C508BFCE6FE72C44482224A9C223BC873
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
Shutdown_priv: Y
Process_priv: Y
File_priv: Y
Grant_priv: Y
References_priv: Y
Index_priv: Y
Alter_priv: Y
Show_db_priv: Y
Super_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Execute_priv: Y
Repl_slave_priv: Y
Repl_client_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Create_user_priv: Y
Event_priv: Y
Trigger_priv: Y
Create_tablespace_priv: Y
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_native_password
authentication_string: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
password_expired: N
*************************** 4. row ***************************
Host: ::1
User: root
Password: *C2D0106C508BFCE6FE72C44482224A9C223BC873
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
Shutdown_priv: Y
Process_priv: Y
File_priv: Y
Grant_priv: Y
References_priv: Y
Index_priv: Y
Alter_priv: Y
Show_db_priv: Y
Super_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Execute_priv: Y
Repl_slave_priv: Y
Repl_client_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Create_user_priv: Y
Event_priv: Y
Trigger_priv: Y
Create_tablespace_priv: Y
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_native_password
authentication_string: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
password_expired: N
4 rows in set (0.02 sec)
ERROR:
No query specified
mysql>
发现Password和 authentication_string的值不一致，就试着又把Password也改成和authentication_string一样的值，然后去掉skip-grant-tables=1的配置，再次重启mysql后
果然能用修改后的密码登录了