【Linux】【mysql】mysql8.0开启远程访问及常见问题

1.连接数据库

 1 [root@localhost ~]# mysql -uroot -p
 2 Enter password: 
 3 Welcome to the MySQL monitor.  Commands end with ; or g.
 4 Your MySQL connection id is 17
 5 Server version: 8.0.17 MySQL Community Server - GPL
 6 
 7 Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
 8 
 9 Oracle is a registered trademark of Oracle Corporation and/or its
10 affiliates. Other names may be trademarks of their respective
11 owners.
12 
13 Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
14 
15 mysql>

2.在mysql8版本中更改用户密码需要加入with mysql_native_password

1 mysql>  alter user 'root'@'localhost' identified with mysql_native_password by '***
';
2 Query OK, 0 rows affected (0.04 sec)

3.修改root用户的host为‘%’

 1 mysql> show databases;
 2 +--------------------+
 3 | Database           |
 4 +--------------------+
 5 | information_schema |
 6 | mysql              |
 7 | performance_schema |
 8 | sys                |
 9 +--------------------+
10 4 rows in set (0.01 sec)
11 
12 mysql> use mysql;
13 Reading table information for completion of table and column names
14 You can turn off this feature to get a quicker startup with -A
15 
16 Database changed
17 
18 mysql> select user,host from user;
19 +------------------+-----------+
20 | user             | host      |
21 +------------------+-----------+
22 | mysql.infoschema | localhost |
23 | mysql.session    | localhost |
24 | mysql.sys        | localhost |
25 | root             | localhost |
26 +------------------+-----------+
27 4 rows in set (0.00 sec)
28 
29 mysql> update user set host = '%' where user = 'root';
30 Query OK, 1 row affected (0.00 sec)
31 Rows matched: 1  Changed: 1  Warnings: 0
32 
33 mysql> flush privileges;
34 Query OK, 0 rows affected (0.00 sec)

再查看root用户的host

 1 mysql> select user,host from user;
 2 +------------------+-----------+
 3 | user             | host      |
 4 +------------------+-----------+
 5 | root             | %         |
 6 | mysql.infoschema | localhost |
 7 | mysql.session    | localhost |
 8 | mysql.sys        | localhost |
 9 +------------------+-----------+
10 4 rows in set (0.01 sec)

至此，root用户的远程访问已开启。

4.测试下navicat连接

连接成功！

常见问题解决

1.解决sql_mode=only_full_group_by问题

[Err] 1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'mingbyte.u.USER_ID' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

连接数据库，通过 select version(), @@sql_mode; 查看mode

 1 [root@localhost ~]# mysql -uroot -p
 2 Enter password: 
 3 Welcome to the MySQL monitor.  Commands end with ; or g.
 4 Your MySQL connection id is 89
 5 Server version: 8.0.17 MySQL Community Server - GPL
 6 
 7 Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
 8 
 9 Oracle is a registered trademark of Oracle Corporation and/or its
10 affiliates. Other names may be trademarks of their respective
11 owners.
12 
13 Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
14 
15 mysql> select version(), @@sql_mode;
16 +-----------+-----------------------------------------------------------------------------------------------------------------------+
17 | version() | @@sql_mode                                                                                                            |
18 +-----------+-----------------------------------------------------------------------------------------------------------------------+
19 | 8.0.17    | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
20 +-----------+-----------------------------------------------------------------------------------------------------------------------+
21 1 row in set (0.00 sec)
22 
23 mysql>

发现上文中查看的mode中有ONLY_FULL_GROUP_BY。将其删除

sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'

修改mysql的配置文件，重启依旧生效

vi /etc/my.cnf

将上述sql_mode插入[mysqld]下方，保存，重启数据库，问题解决。

或者

1 mysql> set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
2 Query OK, 0 rows affected (0.01 sec)
3 
4 mysql> set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
5 Query OK, 0 rows affected (0.00 sec)

问题解决。