mysql> create user root@'localhost' identified by 'mysql';
ERROR 1396 (HY000): Operation CREATE USER failed for 'root'@'localhost'
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql> create user root@'localhost' identified by 'mysql';
ERROR 1396 (HY000): Operation CREATE USER failed for 'root'@'localhost'
尼玛...什么玩意儿？
于是去逛了一圈stackoverflow,都说这是一个BUG，于是操作如下：
mysql> drop user root@'localhost'; --是的没错，虽然没有root@'localhost'用户，但你还是要删一遍。
Query OK, 0 rows affected (0.05 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)
mysql> create user root@'localhost' identified by 'mysql';
Query OK, 0 rows affected (0.03 sec)
mysql> grant all on *.* to root@'localhost' with grant option;
Query OK, 0 rows affected (0.03 sec)
终于改完密码了...去掉参数文件的skip-grant-tables参数，service mysqld restart重启服务。
五、终于设置好密码了，建一个测试用户leo，然后尝试远程连接下吧：
$ mysql -uleo -pmysql -h192.168.1.193
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2059 (HY000): Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib/mysql/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory
我这5.7的mysql工具都连不上，这就尴尬了，查看认证相关参数：
mysql> show variables like '%auth%';
+-------------------------------+-----------------------+
| Variable_name | Value |
+-------------------------------+-----------------------+
| default_authentication_plugin | caching_sha2_password |
+-------------------------------+-----------------------+
1 row in set (0.02 sec)
查看官网发现此值的取值如下：
官网还说此值影响create user不显式指定auth plugin时密码的默认加密算法，卧槽...那岂不是说我之前创建的leo用户使用的是默认的caching_sha2_password认证，查看一下：
mysql> select user,host,plugin from mysql.user;
+------------------+-----------+-----------------------+
| user | host | plugin |
+------------------+-----------+-----------------------+
| leo | % | caching_sha2_password |
| mysql.infoschema | localhost | mysql_native_password |
| mysql.session | localhost | mysql_native_password |
| mysql.sys | localhost | mysql_native_password |
| root | localhost | caching_sha2_password |
+------------------+-----------+-----------------------+
完了，全TM完了......
显然不能直接update plugin，因为这可能导致加密的密码无法被正确解密，你所有的密码都会变异，因此除root@'localhost'外全部删掉重建。
首先需要在my.cnf里添加：default_authentication_plugin=mysql_native_password，然后service mysqld restart重启服务：
mysql> drop user leo;
Query OK, 0 rows affected (0.10 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> create user leo identified by 'mysql';
Query OK, 0 rows affected (0.02 sec)
mysql> grant all on *.* to leo;
Query OK, 0 rows affected (0.08 sec)
$mysql -V
mysql Ver 14.14 Distrib 5.7.20, for Linux (x86_64) using EditLine wrapper
$ mysql -uleo -pmysql -h192.168.1.193
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 8.0.11 MySQL Community Server - GPL
Copyright (c) 2000, 2017, 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>
至此远程连接正常。