Set new password in MySQL 5.7 for root (by quqi99)

版权声明：可以任意转载，转载时请务必以超链接形式标明文章原始出处和作者信息及本版权声明 (作者：张华 发表于：2018-10-18)

用下列方法彻底删除了mysql再重新安装了mysql, 但在登录(mysql -uroot -p -h127.0.0.1)的时候还是总报密码错误.

sudo apt-get remove --purge mysql*
sudo apt-get autoremove
sudo apt-get autoclean
sudo rm -r /var/lib/mysql
sudo apt-get remove dbconfig-mysql
sudo apt-get install mysql-server

日志里发现有下列错误:

$ cat /var/log/mysql/error.log | grep ‘password’
2018-10-18T08:16:07.581669Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.

哦, 原来mysql自5.7开始采用了auth_socket, 意味着只有系统的root用户才能作为mysql root. 所以添加sudo之后成功登录(默认密码为空):
注: 如果普通用户不在root组里, 使用上述命令也是不行的, 可以先sudo -i之后在root用户里执行: mysql -uroot -p
sudo mysql -uroot -p -h127.0.0.1

如果只想用’mysql -uroot -p -h127.0.0.1’登录, 不想前面加sudo可以采用下列命令改回使用mysql_native_password
alter user user() identified by “password”;
use mysql
ALTER USER ‘root’@‘localhost’ IDENTIFIED WITH mysql_native_password;
select User,host,plugin from mysql.user where User=‘root’;

OK, 这时就可以使用mysql -uroot -p -h127.0.0.1登录了, 但可以去掉里面的127.0.0.1登录吗? 那是因为~/.my.cnf文件里默认配置了host=172.16.2.1

$ cat ~/.my.cnf
[client]
user=root
password=password
host=127.0.0.1

从其他机器登录可能需要:

use mysql;
update user set host='%' where user='root';
FLUSH PRIVILEGES;

#GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '' WITH GRANT OPTION;
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '';
FLUSH PRIVILEGES;

mysql> SHOW GRANTS;
+-------------------------------------------------------------+
| Grants for root@%                                           |
+-------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION |
+-------------------------------------------------------------+
1 row in set (0.00 sec)