mysql8.0开放权限

1.登录mysql机器

mysql -u root -p

2.切换到Mysql数据库

mysql->use mysql;
Database changed

3.查看是否运行远程访问

可以看到第一行root 用户的host为localhost，要远程访问，需要将它改成%

mysql->select host,user,plugin from user;
+-----------+------------------+-----------------------+
| host      | user             | plugin                |
+-----------+------------------+-----------------------+
| localhost | mysql.infoschema | caching_sha2_password |
| localhost | mysql.session    | caching_sha2_password |
| localhost | mysql.sys        | caching_sha2_password |
| localhost | root             | mysql_native_password |
+-----------+------------------+-----------------------+
4 rows in set (0.00 sec)

4.将host改为%

mysql> update user set host='%' where user='root';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select host,user,plugin from user;
+-----------+------------------+-----------------------+
| host      | user             | plugin                |
+-----------+------------------+-----------------------+
| %         | root             | mysql_native_password |
| localhost | mysql.infoschema | caching_sha2_password |
| localhost | mysql.session    | caching_sha2_password |
| localhost | mysql.sys        | caching_sha2_password |
+-----------+------------------+-----------------------+
4 rows in set (0.00 sec)

5.刷新权限

mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec)

6.如果报错Authentication plugin 'caching_sha2_password' cannot be loaded（按照如下解决），因为caching_sha2_password加密方式在远程访问时候不支持。
需要改成：mysql_native_password

mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'Admins110!';
Query OK, 0 rows affected (0.01 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.03 sec)

mysql> select host,user,plugin from user;
+-----------+------------------+-----------------------+
| host      | user             | plugin                |
+-----------+------------------+-----------------------+
| %         | root             | mysql_native_password |
| localhost | mysql.infoschema | caching_sha2_password |
| localhost | mysql.session    | caching_sha2_password |
| localhost | mysql.sys        | caching_sha2_password |
+-----------+------------------+-----------------------+
4 rows in set (0.00 sec)