mysql8.0使用总结

1. 初始化数据库后，想导入数据，发现报错：

ERROR 1227 (42000) at line 75612: Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation

解决办法：

grant system_user on *.* to 'root';

2. 发现数据库data目录下多了个localhost.log，一直在输出查询sql语句

解决办法：

通用查询日志general_log

通用查询日志记录在MySQL上执行过的SQL语句，包含查询语句与启动时间。建议不是在调试环境下不要开启查询日志，因为它会不断占据磁盘空间，并且产生大量的IO，一般是在需要采样分析或者调试的时候才开启。

通用日志的开启方法：

(1)执行命令开启：

set global general_log=1;//=0就是关闭通用查询日志

此时在默认在mysql的data目录中生成了localhost.log文件，该文件就是通用查询日志文件

（2）my.cnf中配置的方式，在my.cnf文件的[mysqld]下面任意一行增加或修改配置：

general_log-file[=path/[filename]] //=后面都是可选的，即有默认的保存日志的文件

general_log=1 //表示开启通用查询日志

推荐使用第一种方式开启或关闭通用查询日志，因为my.cnf的修改要生效需要重启mysql服务，并且这种通用查询日志的开启不需要一直开启而是短时间开启就需要关闭，所以在 my.cnf关闭时又要重启mysql服务。

3. 使用过程中出现大量的错误日志

2022-12-01T06:48:50.800624Z 49327770 [Warning] [MY-010055] [Server] IP address '115.221.203.210' could not be resolved: Name or service not known
2022-12-01T07:12:03.745942Z 49330376 [Warning] [MY-010055] [Server] IP address '183.142.14.222' could not be resolved: Name or service not known
2022-12-01T07:26:57.635959Z 49331378 [Warning] [MY-010055] [Server] IP address '167.71.130.21' could not be resolved: Name or service not known
2022-12-01T07:41:14.265157Z 49332628 [Warning] [MY-010055] [Server] IP address '192.241.202.131' could not be resolved: Name or service not known
2022-12-01T07:55:15.111019Z 49333961 [Warning] [MY-010055] [Server] IP address '164.92.175.77' could not be resolved: Name or service not known
2022-12-01T08:22:46.582257Z 49336220 [Warning] [MY-010057] [Server] IP address '34.78.6.216' has been resolved to the host name '216.6.78.34.bc.googleusercontent.com', which resembles IPv4-address itself.
2022-12-01T09:44:42.891097Z 49340747 [Warning] [MY-010055] [Server] IP address '213.230.107.9' could not be resolved: Name or service not known
2022-12-01T10:03:32.598497Z 49341644 [Warning] [MY-010055] [Server] IP address '183.6.92.13' could not be resolved: Name or service not known
2022-12-01T10:55:57.510266Z 49344582 [Warning] [MY-010055] [Server] IP address '154.89.5.113' could not be resolved: Name or service not known
2022-12-01T11:42:42.952440Z 49346312 [Warning] [MY-010055] [Server] IP address '154.39.106.112' could not be resolved: Name or service not known

# 因为mysql默认会反向解析DNS，对于访问者Mysql不会判断是hosts还是ip都会进行dns反向解析，频繁地查询数据库和权限检查，这大大增加了数据库的压力，导致数据库连接缓慢，严重的时候甚至死机，出现“连接数据库时出错”等字样。

解决办法：

禁用dns反查即可

vim my.cnf

[mysqld]
skip-name-resolve