mysql给root开启远程访问权限(mysql5.7或5.6)

1、在连接服务器后，操作mysql系统数据库

[root@VM-0-14-centos bin]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 34431
Server version: 5.7.24 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, 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
mysql>
2、查询用户表命令：select User,authentication_string,Host from user;

mysql> select  User,authentication_string,Host from user;
+---------------+-------------------------------------------+--------------+
| User          | authentication_string                     | Host         |
+---------------+-------------------------------------------+--------------+
| root          | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | localhost    |
| mysql.sys     | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost    |
+---------------+-------------------------------------------+--------------+
2 rows in set (0.00 sec)
这里也可以看出host默认都是localhost访问权限
3、接下来就是最重要的部分了
1. GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456';
这里的123456为给root设置的密码，也可以改成自己的密码，%代表所有主机，也可以具体到你的主机ip地址
2.flush privileges;
这一步一定要做，不然无法成功！ 这句表示从mysql数据库的grant表中重新加载权限数据
因为MySQL把权限都放在了cache中，所以在做完更改后需要重新加载。
3.执行完这两步，再次查询用户表命令：
select User,authentication_string,Host from user;

4、如果是mysql8，遇到root连不上数据库的问题，可能是加密规则变了，试试如下处理，这里修改加密规则，这里密码用的是123456，可以改成自己的密码

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456' PASSWORD EXPIRE NEVER; 
Query OK, 0 rows affected (0.01 sec)

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

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