长春搜索引擎网站推广,线下推广引流渠道,建设一个网站需要哪些费用吗,用c 做的网站怎么打开记一次MySQL故障解决 1 故障现象2 故障排查2.1 查看MySQL服务状态2.2 查看服务日志 3 解决方法3.1 增加 wait_timeout 和 interactive_timeout 参数的值#xff0c;确保连接不会因超时而被关闭#xff1a;3.2 检查服务已经恢复正常#xff0c;不过以上只是临时修改#xff… 记一次MySQL故障解决 1 故障现象2 故障排查2.1 查看MySQL服务状态2.2 查看服务日志 3 解决方法3.1 增加 wait_timeout 和 interactive_timeout 参数的值确保连接不会因超时而被关闭3.2 检查服务已经恢复正常不过以上只是临时修改重启服务器就会失效。3.3 永久生效的配置方法3.3.1 编辑 MySQL 配置文件3.3.2 配置更改生效需要重启 MySQL 服务 4 知识扩展4.1 wait_timeout默认值28800s即8h4.2 interactive_timeout默认值28800s即8h 1 故障现象
网站在一段时间后访问会出现数据库连接失败的现象
2 故障排查
2.1 查看MySQL服务状态 查看服务状态位activeexited
2.2 查看服务日志
#使用tail命令查看最新日志内容
tail -f /var/log/mysql/mysql-err.log......2025-02-14T02:28:18.357420Z 1246 [Warning] Host name scanner-202.hk2.censys-scanner.com could not be resolved: Name or service not known
2025-02-14T02:28:19.318764Z 1246 [Note] Got an error reading communication packets
2025-02-14T02:28:22.643813Z 1249 [Note] Got an error reading communication packets
2025-02-14T02:28:23.342097Z 1248 [Note] Got an error reading communication packets
2025-02-14T02:28:23.572455Z 1250 [Note] Got an error reading communication packets
2025-02-14T02:28:27.373513Z 1251 [Note] Got an error reading communication packets
2025-02-14T02:28:27.412663Z 1252 [Note] Got an error reading communication packets
2025-02-14T02:28:28.362485Z 1253 [Note] Got an error reading communication packets
2025-02-14T02:28:33.435945Z 1255 [Note] Got an error reading communication packets
2025-02-14T03:07:09.169182Z 2111 [Note] Aborted connection 2111 to db: traditional_payy_cn user: traditional_payy_cn host: localhost (Got an error reading communication packets)
2025-02-14T03:22:49.381178Z 3108 [Note] Aborted connection 3108 to db: traditional_payy_cn user: traditional_payy_cn host: localhost (Got an error reading communication packets)
2025-02-14T03:25:38.151436Z 0 [Warning] option max_allowed_packet: unsigned value 107374182400 adjusted to 1073741824
2025-02-14T03:25:38.151558Z 0 [Warning] NO_ZERO_DATE, NO_ZERO_IN_DATE and ERROR_FOR_DIVISION_BY_ZERO sql modes should be used with strict mode. They will be merged with strict mode in a future release.
2025-02-14T03:25:38.151563Z 0 [Warning] NO_AUTO_CREATE_USER sql mode was not set.
2025-02-14T03:25:38.151590Z 0 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled
2025-02-14T03:25:38.151629Z 0 [Note] /www/server/mysql/bin/mysqld (mysqld 5.7.40-log) starting as process 8417 ...
2025-02-14T03:25:38.172919Z 0 [Note] InnoDB: PUNCH HOLE support available
2025-02-14T03:25:38.172954Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2025-02-14T03:25:38.172958Z 0 [Note] InnoDB: Uses event mutexes
2025-02-14T03:25:38.172962Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2025-02-14T03:25:38.172966Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.12
2025-02-14T03:25:38.172970Z 0 [Note] InnoDB: Using Linux native AIO
2025-02-14T03:25:38.173799Z 0 [Note] InnoDB: Number of pools: 1
2025-02-14T03:25:38.173913Z 0 [Note] InnoDB: Using CPU crc32 instructions
2025-02-14T03:25:38.176537Z 0 [Note] InnoDB: Initializing buffer pool, total size 2G, instances 8, chunk size 128M
2025-02-14T03:25:38.295413Z 0 [Note] InnoDB: Completed initialization of buffer pool
2025-02-14T03:25:38.331930Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2025-02-14T06:45:13.900352Z 0 [Warning] option max_allowed_packet: unsigned value 107374182400 adjusted to 1073741824
2025-02-14T06:45:13.900467Z 0 [Warning] NO_ZERO_DATE, NO_ZERO_IN_DATE and ERROR_FOR_DIVISION_BY_ZERO sql modes should be used with strict mode. They will be merged with strict mode in a future release.
2025-02-14T06:45:13.900471Z 0 [Warning] NO_AUTO_CREATE_USER sql mode was not set.
2025-02-14T06:45:13.900497Z 0 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled
2025-02-14T06:45:13.900535Z 0 [Note] /www/server/mysql/bin/mysqld (mysqld 5.7.40-log) starting as process 11659 ...
2025-02-14T06:45:13.910356Z 0 [Note] InnoDB: PUNCH HOLE support available
2025-02-14T06:45:13.910397Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2025-02-14T06:45:13.911082Z 0 [Note] InnoDB: Uses event mutexes
2025-02-14T06:45:13.911087Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier从中我们筛选到这一行代码
#此行代码表示数据库连接被中断可能是由于与客户端的通信出现问题。
2025-02-14T03:07:09.169182Z 2111 [Note] Aborted connection 2111 to db: traditional_payy_cn user: traditional_payy_cn host: localhost (Got an error reading communication packets)3 解决方法
3.1 增加 wait_timeout 和 interactive_timeout 参数的值确保连接不会因超时而被关闭
SET GLOBAL wait_timeout 0;
SET GLOBAL interactive_timeout 0;3.2 检查服务已经恢复正常不过以上只是临时修改重启服务器就会失效。
systemctl status mysql.service ● mysql.service - MySQL Community ServerLoaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)Active: active (running) since Fri 2025-02-14 13:07:51 CST; 2h 39min agoProcess: 770 ExecStartPre/usr/share/mysql/mysql-systemd-start pre (codeexited, status0/SUCCESS)Main PID: 879 (mysqld)Status: Server is operationalTasks: 37 (limit: 2183)Memory: 360.8MCPU: 54.954sCGroup: /system.slice/mysql.service└─879 /usr/sbin/mysqldFeb 14 13:07:46 ubuntu systemd[1]: Starting MySQL Community Server...
Feb 14 13:07:51 ubuntu systemd[1]: Started MySQL Community Server.3.3 永久生效的配置方法
3.3.1 编辑 MySQL 配置文件
# MySQL 配置文件中永久修改 wait_timeout 和 interactive_timeout
sudo vim /etc/mysql/my.cnf......[mysqld]
wait_timeout 31536000
interactive_timeout 31536000注意 如果您将值设置为 0则 MySQL 不会断开连接但这样设置可能会导致其他不可预见的后果因此建议使用一个非常大的值。
3.3.2 配置更改生效需要重启 MySQL 服务
sudo systemctl restart mysql[rootwebserver ~]# systemctl status mysql.service
● mysql.service - MySQL Community ServerLoaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)Active: active (running) since Fri 2025-02-14 13:07:51 CST; 2h 39min agoProcess: 770 ExecStartPre/usr/share/mysql/mysql-systemd-start pre (codeexited, status0/SUCCESS)Main PID: 879 (mysqld)Status: Server is operationalTasks: 37 (limit: 2183)Memory: 360.8MCPU: 54.954sCGroup: /system.slice/mysql.service└─879 /usr/sbin/mysqldFeb 14 13:07:46 ubuntu systemd[1]: Starting MySQL Community Server...
Feb 14 13:07:51 ubuntu systemd[1]: Started MySQL Community Server.4 知识扩展
wait_timeout 和 interactive_timeout 是控制连接超时的两个重要参数
4.1 wait_timeout默认值28800s即8h
含义 wait_timeout 设置了 MySQL 服务器等待没有任何活动的连接的最长时间单位秒。如果在这个时间内没有任何查询或操作MySQL 将自动关闭连接。 应用场景 它主要用于非交互式连接例如通过应用程序或脚本连接到 MySQL。如果一个连接长时间没有任何活动它将在 wait_timeout 指定的时间后被断开。
4.2 interactive_timeout默认值28800s即8h
含义 interactive_timeout 设置了 MySQL 服务器在没有任何活动的交互式连接上等待的时间单位秒。交互式连接是指通过 MySQL 客户端例如 mysql 命令行工具或其他交互式工具如 GUI 工具建立的连接。这个参数控制这些连接的超时时间。 应用场景 interactive_timeout 专门用于交互式会话通常比 wait_timeout 的默认值大一些因为用户在交互式会话中可能需要更多时间来执行查询或等待用户输入。
