刚安装的MySQL使用Navicat操作数据库遇到的问题

我不是女神ヾ 2024-02-19 11:29 60阅读 0赞

#### 刚安装的MySQL使用Navicat操作数据库遇到的问题 ####

*  一、编辑连接保存报错
 *  二、打开数据表很慢
 *  三、MySQL的进程出现大量“sleep”状态的进程
 *  四、执行sql脚本报错，部分表导不进去
 *  五、查询数据很慢
 *  六、当前MySQL配置文件

## 一、编辑连接保存报错 ##

连接上了数据库，编辑连接保存报错，如下图：

![在这里插入图片描述][10f18230cfdc4fed954da9c4f599e7fa.png]

解决办法：

重启Navicat。

## 二、打开数据表很慢 ##

第一次打开数据表时速度还能接受，过几分钟再次打开就变得很慢很慢，且查询很少的数据都很耗时。比如我只想查询用户表中的十条数据，竟然查了8秒。

![在这里插入图片描述][e9a8d4fa8b914aacb43b2d637ebd5446.png]

解决办法：

打开关闭数据库，编辑数据库连接，默认是240秒，我这里改为了30秒，还觉得慢可以改为0秒。

![在这里插入图片描述][8c65b097ce5445e39552fb7cb08729a5.png]

这样就会让数据库连接保持随时待命的状态，不会出现因为间隔几分钟没有打开数据表，再次打开时变得很慢很慢了的情况了。

再次查询用户表的10条数据

![在这里插入图片描述][93b67fd6b0e24204834f8ee0a929c7c2.png]

## 三、MySQL的进程出现大量“sleep”状态的进程 ##

![在这里插入图片描述][7dfe16ffca314abcbda6a517b08c477e.png]

这可能是刚安装的MySQL还没有优化，查询MySQL的连接超时时间

show variables like 'wait_timeout';

show variables like 'interactive_timeout';

解释：

> wait\_timeout：用于指定服务器等待客户端发送命令的时间的上限。  
> interactive\_timeout：用于指定MySQL服务器在等待客户端发送命令的时间后关闭非交互式连接的超时时间。

![在这里插入图片描述][a97bca2453e0433fba0a3a4c08d29fc4.png]

MySQL默认的连接超时时间是8小时，太长了，没有进行数据操作的时候应该释放资源，可以将超时时间调小一些，注意需要上面两个参数同时设置才能生效。

打开MySQL的配置文件

vim /etc/my.cnf

在“\[mysqld\]”中加入如下配置

#减少超时时间，避免使用“SHOW PROCESSLIST”命令出现很多“sleep”状态的进程，这里设置为120秒
    wait_timeout=120
    interactive_timeout=120

这是我的  
![在这里插入图片描述][b35a34f67ada4a0e931ae0e688405d27.png]

然后重启MySQL

service mysql restart

![在这里插入图片描述][9941f2fb2e75461bb3c028f90c1c03c7.png]

再次查询MySQL的进程

SHOW PROCESSLIST

![在这里插入图片描述][411b94f677654236a7df85b5905e7d44.png]

可以看到前面的很多“sleep”状态的进程已经不见了。

## 四、执行sql脚本报错，部分表导不进去 ##

我的问题是这样的，新安装的MySQL从Navicat执行sql脚本，结束时出现失败的错误，看一下执行结束后的数据库，大部分表都导入了，只是部分数据表没能导入。

这是导入时的报错

![在这里插入图片描述][654a5b4eb1104a879c502aec068dab0f.png]

一开始也没看出那里的问题，于是将就着用，等项目连接上的时候报缺少某个数据表，看了一下数据库中还真没有，就把没导入的数据表从原数据库中单独导出sql文件，再单独导入，结果也是报类似的错

![在这里插入图片描述][a8d7d59ffaf344059f19ee40ee46b6d2.png]

看来导入数据库的时候就是这些表出的问题了。

然后就想着既然sql脚本有问题，那我通过执行sql的形式导入，这些应该总行了吧，但是报如下错：

Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW\_FORMAT=DYNAMIC or ROW\_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.

![在这里插入图片描述][4e9e61d600a740418ecb9c887735b162.png]

看报错提示似乎字段的长度不够，需要改为“TEXT“或者”BLOB”类型，我直接把我内容比较多的字段类型改为“LONGTEXT”，这样应该承得下了把，但执行sql时还是报这个错。

后面发现这是InnoDB 存储引擎的一个配置参数“innodb\_strict\_mode”，是否采取严格模式的问题，只要把它关了就可以导入了。

解释：

> innodb\_strict\_mode 是 MySQL 中 InnoDB 存储引擎的一个配置参数，它用于控制 InnoDB 存储引擎在处理某些可能的不规范或错误的数据时是否采取严格模式，默认是开启。

关闭采取严格模式的办法如下：

在“\[mysqld\]”下加入如下配置

#处理 MySQL导入数据库1118错误解决方案[ERR] 1118 - Row size too large (＞ 8126). Changing some columns to TEXT or BLOB
    innodb_strict_mode=0

保存退出，再重启MySQL

service mysql restart

![在这里插入图片描述][f28ed93d1a4b447cb5ae8016f37d022e.png]

删除原来的数据库，再次导入

![在这里插入图片描述][87566676c6a84c36b8a04db00ca73e2b.png]

成功。

## 五、查询数据很慢 ##

导入数据库之后，我尝试着查询某张表中的数据，第一次查询

![在这里插入图片描述][fd1bb9c87cbd4d0f862c8a9ce45079be.png]

查询100条数据都用了13秒多，感觉有点久。

进入MySQL配置文件

vim /etc/my.cnf

加上如下配置：

skip-name-resolve

> skip-name-resolve 用于禁止MySQL解析主机名。当MySQL服务器收到一个连接请求时，它通常会尝试解析客户端的主机名以获取IP地址。然而，在某些情况下，解析主机名可能会导致性能问题或延迟。在这种情况下，可以设置skip-name-resolve参数，以告诉MySQL跳过主机名的解析过程，从而提高连接效率。

然后重启MySQL

service mysql restart

![在这里插入图片描述][d387857159914f9a8f0e11b3999e4be9.png]

再次查询

![在这里插入图片描述][885f3078d980405495a94cba32ab55dd.png]

额，好像比之前更久了。不过短时间内再次查询时间就短了很多，应该是缓存生效了。

![在这里插入图片描述][f0c9364ff4e84c78b5414ea7529fbc7d.png]

然后试着加入如下配置：

max_connections=500
    max_connect_errors=100
    open_files_limit=65535

> max\_connections 指的是允许的最大并发连接数。这意味着MySQL服务器同时能够处理的客户端连接数上限。  
> max\_connect\_errors 用于控制MySQL服务器允许用户最大的连接错误次数。当一个客户端尝试连接到MySQL服务器时，如果出现了错误，例如用户名或密码不正确，那么这个错误的次数会被计数。max\_connect\_errors参数就是用来设置这个计数器的最大值。  
> open\_files\_limit 用于控制MySQL进程可以打开的文件数量上限。它可以确保MySQL能够正确地打开所需数量的文件。在MySQL中，每个打开的文件都需要消耗操作系统资源，因此合理地设置open\_files\_limit可以提高MySQL的性能和稳定性。

保存退出，重启MySQL，再次查询

![在这里插入图片描述][6db0279502f84673a577e8ca73f30b54.png]

隔一段时间再次查询

![在这里插入图片描述][dff66cb32f4f42f8ab0fd35ee299f738.png]

虽然查询还是有点长，不过比起最初的13秒多已经好一些了。

然后再加入如下配置：

innodb_buffer_pool_size=512M
    innodb_log_file_size=256M

> innodb\_buffer\_pool\_size：设置InnoDB存储引擎的缓冲池大小。增加这个值可以缓存更多的数据页，减少磁盘I/O操作，从而提高查询性能。  
> innodb\_log\_file\_size：设置InnoDB存储引擎的日志文件大小。适当增加这个值可以提高写入性能，但需要注意监控和调整以确保日志同步和恢复。

保存退出，重启MySQL，再次查询

![在这里插入图片描述][293c315577394528aa675fbf15305d37.png]

好家伙，好像又快了一点，先别急着高兴，说不定是缓存的问题，前面设置的查询连接时间为两分钟，等两分钟后再查查看。

![在这里插入图片描述][9886275216104c07acd69761891a2491.png]

好像也是大差不差。

## 六、当前MySQL配置文件 ##

[mysqld]
    port=3306
    basedir=/usr/local/mysql/mysql-8.0.35  # MySQL根目录
    datadir=/usr/local/mysql/mysql-8.0.35/data/  # MySQL的data目录
    socket=/tmp/mysql.sock
    character-set-server=UTF8MB4
    symbolic-links=0
    
    # 配置大小写不敏感，配合MySQL初始化时使用
    lower-case-table-names=1
    
    # 减少超时时间，避免使用“SHOW PROCESSLIST”命令出现很多“sleep”状态的进程，这里设置为120秒
    wait_timeout=120
    interactive_timeout=120
    
    # 解决“java.sql.SQLException: Expression #1 of ORDER BY clause is not in SELECT list,references column”
    sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'
    
    # 处理 MySQL导入数据库1118错误解决方案[ERR] 1118 - Row size too large (＞ 8126). Changing some columns to TEXT or BLOB
    innodb_strict_mode=0
    
    # 解决本地连接MySQL数据库非常慢的问题
    #skip-name-resolve
    
    # 允许的最大并发连接数
    max_connections=500
    
    # 控制MySQL服务器允许用户最大的连接错误次数，默认值是100
    max_connect_errors=100
    
    # 用于控制MySQL进程可以打开的文件数量上限
    open_files_limit=65535
    
    # 线程池中的线程缓存数量
    # thread_cache_size=64
    
    # 设置InnoDB存储引擎的缓冲池大小。增加这个值可以缓存更多的数据页，减少磁盘I/O操作，从而提高查询性能。
    innodb_buffer_pool_size=512M
    
    # 设置InnoDB存储引擎的日志文件大小。适当增加这个值可以提高写入性能，但需要注意监控和调整以确保日志同步和恢复。
    innodb_log_file_size=256M

[10f18230cfdc4fed954da9c4f599e7fa.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/19/7cf3174650904b08b7b4a38ca5803720.png
[e9a8d4fa8b914aacb43b2d637ebd5446.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/19/ab757ae028eb4ec299e873b15eee9899.png
[8c65b097ce5445e39552fb7cb08729a5.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/19/d84bca40022749f1a19ae1b643fa3e0e.png
[93b67fd6b0e24204834f8ee0a929c7c2.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/19/9c7202efa0e64dcb9d99ed243c48266e.png
[7dfe16ffca314abcbda6a517b08c477e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/19/92ee1ac0aeaf408787d4f89f45f35e3d.png
[a97bca2453e0433fba0a3a4c08d29fc4.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/19/80605c3a1a354a628ecb51b65e1c76a7.png
[b35a34f67ada4a0e931ae0e688405d27.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/19/30b2d7b79ed240758704f198457d4fdb.png
[9941f2fb2e75461bb3c028f90c1c03c7.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/19/122118d89b2345dfaab73ec1da6bfe7e.png
[411b94f677654236a7df85b5905e7d44.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/19/b3a570eacd434b24b892cc507ff8ec1f.png
[654a5b4eb1104a879c502aec068dab0f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/19/48878029c5744d78ae100cb56129ff28.png
[a8d7d59ffaf344059f19ee40ee46b6d2.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/19/2bfb08c3a3f6472c8045a55af5a9bcad.png
[4e9e61d600a740418ecb9c887735b162.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/19/12431eae17364e1faaf438e82ba64e71.png
[f28ed93d1a4b447cb5ae8016f37d022e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/19/06c13cc8cdde42a79330e3413179c862.png
[87566676c6a84c36b8a04db00ca73e2b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/19/7d547d1e3d874211ac264a86cafcd2e9.png
[fd1bb9c87cbd4d0f862c8a9ce45079be.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/19/f225ce7cff974d4e824052bdf1b41cd1.png
[d387857159914f9a8f0e11b3999e4be9.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/19/70c7cb03420e46f4ad174350d03199a8.png
[885f3078d980405495a94cba32ab55dd.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/19/714d87b265c349f9bb4a37836ed5b47f.png
[f0c9364ff4e84c78b5414ea7529fbc7d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/19/f239c109087043199bc43d2dbb11fa17.png
[6db0279502f84673a577e8ca73f30b54.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/19/e7866a3c2d3b41ebb4d84d02d6b821e2.png
[dff66cb32f4f42f8ab0fd35ee299f738.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/19/2e475078bdf04e8d8dd8df0a354ffca6.png
[293c315577394528aa675fbf15305d37.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/19/8d4d3ff140f54e4e9b5af3f40269c357.png
[9886275216104c07acd69761891a2491.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/19/8c5111db11f54bdaa71359e1a3f9740a.png