MySQL给root账号开启远程访问权限(MySQL 5.7或5.6)
提示:如果server和MySQL在同一个主机上,那么就不用设置root远程访问权限了,在配置文件里可以用localhost连接数据库就可以,如下所示
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;