铁力项目mysql异常处理过程记录
地区:铁力
故障:
2019-06-26 10:19:34 139921514837760 [ERROR] mysqld: Error writing file 'mysql-bin' (errno: 11 "Resource temporarily unavailable")
2019-06-26 10:19:34 139921514338048 [ERROR] mysqld: Error writing file 'mysql-bin' (errno: 11 "Resource temporarily unavailable")
2019-06-26 10:19:35 139921514338048 [ERROR] mysqld: Error writing file 'mysql-bin' (errno: 11 "Resource temporarily unavailable")
2019-06-26 10:21:26 139921514837760 [ERROR] mysqld: Error writing file 'mysql-bin' (errno: 11 "Resource temporarily unavailable")
2019-06-26 10:21:29 139921514338048 [ERROR] mysqld: Error writing file 'mysql-bin' (errno: 11 "Resource temporarily unavailable")
2019-06-26 10:21:47 139918920025856 [ERROR] mysqld: Error writing file 'mysql-bin' (errno: 11 "Resource temporarily unavailable")
==============================================================
对比好用的机器:
10.10.14.159
MariaDB [(none)]> show variables like '%open\_%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| innodb_open_files | 16384 |
| open_files_limit | 16384 |
| table_open_cache | 512 |
+-------------------+-------+
3 rows in set (0.01 sec)
==============================================================
铁力故障服务器
MariaDB [(none)]> show variables like '%open\_%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| innodb_open_files | 1024 |
| open_files_limit | 10245 |
| table_open_cache | 1024 |
+-------------------+-------+
3 rows in set (0.00 sec)
问题已经很明确,是文件打开数不足。
==============================================================
替换10.10.14.159的/etc/my.cnf,问题解决。
检查一下:
mysql -uroot -p*******
MariaDB [(none)]> show variables like '%open\_%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| innodb_open_files | 16384 |
| open_files_limit | 16384 |
| table_open_cache | 512 |
+-------------------+-------+
3 rows in set (0.00 sec)
附件:好用my.cnf https://files.cnblogs.com/files/littlehb/my.zip
2019年6月27日以上报不好用,黄海继续检查之:
[root@localhost proc]# ps -ef | grep mysql --->12083
[root@localhost proc]# cat /proc/12083/limits
Limit Soft Limit Hard Limit Units
Max cpu time unlimited unlimited seconds
Max file size unlimited unlimited bytes
Max data size unlimited unlimited bytes
Max stack size 10485760 unlimited bytes
Max core file size 0 unlimited bytes
Max resident set unlimited unlimited bytes
Max processes 65535 65535 processes
Max open files 65535 65535 files
Max locked memory 65536 65536 bytes
Max address space unlimited unlimited bytes
Max file locks unlimited unlimited locks
Max pending signals 63383 63383 signals
Max msgqueue size 819200 819200 bytes
Max nice priority 0 0
Max realtime priority 0 0
Max realtime timeout unlimited unlimited us
vi /etc/my.cnf
innodb_open_files=65535
open_files_limit=65535
max_connections=2000
max_user_connection=1500
[Warning] mysqld: Disk is full writing '/tmp/#sql_9ba7_0.MAD' (Errcode: 28 "No space left on device"). Waiting for someone to free space... (Expect up to 60 secs delay for server to continue after freeing disk space)
2019-06-27 1:35:28 140157200050944 [Warning] mysqld: Retry in 60 secs. Message reprinted in 600 secs
2019-06-27 1:37:42 140157981453056 [Warning] mysqld: Disk is full writing './mysql-bin.000015' (Errcode: 28 "No space left on device"). Waiting for someone to free space... (Expect up to 60 secs delay for server to continue after freeing disk space)
2019-06-27 1:37:42 140157981453056 [Warning] mysqld: Retry in 60 secs. Message reprinted in 600 secs
2019-06-27 1:39:42 140157981453056 [ERROR] mysqld: Error writing file 'mysql-bin' (errno: 28 "No space left on device")
2019-06-27 1:39:42 140157870316288 [Warning] mysqld: Disk is full writing './mysql-bin.000015' (Errcode: 28 "No space left on device"). Waiting for someone to free space... (Expect up to 60 secs delay for server to continue after freeing disk space)
10.10.14.199
tmpfs 7.8G 5.6M 7.8G 1% /dev/shm
铁力
tmpfs 7.8G 972M 6.8G 13% /dev/shm
Linux下增加/tmp文件的方法
修改 /etc/fstab 文件
tmpfs /dev/shm tmpfs defaults 0 0
---->
tmpfs /tmp tmpfs defaults,size=15G 0 0
相关文章
- 11.PHP与MySQL
- solr 7+tomcat 8 + mysql实现solr 7基本使用(安装、集成中文分词器、定时同步数据库数据以及项目集成)
- MySQL中varchar最大长度是多少
- 【JAVA】JSP操作数据库简单实例和mysql-connector-java-5.0.3-bin.jar项目配置方法
- 在idea中SpringBoot2.4.1项目下,会默认使用mysql-connector-java-8.0.22驱动的错误原因
- (转)主从同步常遇见问题处理-线上MYSQL同步报错故障处理总结
- 微服务轮子项目(28) -MySQL 高可用方案
- 微服务轮子项目(27) -MySQL 主从复制、主从切换、主主复制
- 107分布式电商项目 - MySQL优化(数据库结构优化)
- 121分布式电商项目 - MySQL主从复制
- 《MySQL DBA修炼之道》——导读
- MySQL: Fractional Seconds
- maven项目中添加MySql依赖
- MySQL查看触发器
- MySQL存储引擎
- 【mysql报错】ERROR 2002 (HY000): Can‘t connect to local MySQL server through socket ‘/tmp/mysql.sock‘
- Kafka + Canal + MySQL 集群部署
- 四、MYSQL数据类型
- 记一次Mysql大数据分页优化问题
- 2013-Lost connection to MySQL server at 'reading initial communication packet', system error:0 "internal error/check (Not system error)"
- 《PHP、MySQL和Apache入门经典(第5版)》一一2.6 安装故障排除
- 基于Python(Django )+VUE+MySQL实现多功能美颜 Web 应用【100010556】
- MySQL中的一些非常实用的函数、语法
- Amoeba是一个类似MySQL Proxy的分布式数据库中间代理层软件,是由陈思儒开发的一个开源的java项目
- 【3.1】【mysql基本实验】mysql复制(主从复制/异步复制/半同步复制,一主一从)
- mysql 变量set
- mysql忘记密码怎样重置
- mysql中独立表空间与共享表空间之前如何切换
- Mysql 调优小技巧
- mysql Column count doesn't match value count at row 1