怎么解决mysql中的ERROR 1135 HY000 报错问题
发布时间：2021-12-17 12:23:08 所属栏目：MySql教程 来源：互联网
导读：这篇文章主要讲解了怎么解决mysql中的ERROR 1135 (HY000)报错问题，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习怎么解决mysql中的ERROR 1135 (HY000)报错问题吧！ 收到报错： [root@i-iivphroy ~]# mysql
|
这篇文章主要讲解了“怎么解决mysql中的ERROR 1135 (HY000)报错问题”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“怎么解决mysql中的ERROR 1135 (HY000)报错问题”吧！ 收到报错： [root@i-iivphroy ~]# mysql -uroot -p********* -h292.168.0.254 ERROR 1135 (HY000): Can't create a new thread (errno 11); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug 马上google一番，有人说可能说磁盘空间满了，经查看发现果然是磁盘满了，（这个盘一直空间很紧张（95%），我高度警惕着，天天检查，可是昨天我执行了个大事务，产生了大量的binlog，给一下子撑爆了） 马上删除了几天前的binlog和一些别的不需要的数据，空间释放到了80%，再次登录mysql [root@i-iivphroy ~]# mysql -uroot -p******* -h292.168.0.254 依旧报错： ERROR 1135 (HY000): Can't create a new thread (errno 11); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug 再次google一番，查到下面这个文档： This error was the bane of my life for a while, and it was very hard to get a definitive answer as to what was causing it, I hope this saves you some trouble. My website occasionally got large traffic spikes, and at the top of these peaks, I would start to see errors like these: MySQL error #1135: Can’t create a new thread (errno 11). If you are not out of available memory, you can consult the manual for a possible OS-dependent bug. I looked in the my.cnf file on the db server and looked at the open files limit, because a process is counted as an open file, but it seemed fine: [mysqld_safe] open-files-limit=10240 I also checked that maximum connections was high enough, it was at 2048. What the open-files-limit in my.cnf files does is it tells the init script to use ulimit to whatever number you put in there. After a lot of digging around various places, and much frustration, I discovered that by default linux has a hard limit of 1024 open files for all non super-users, so even though I had set a high open-files-limit, it was capped at 1024 by the OS. I also discovered how to raise it; /etc/security/limits.conf This file is used by PAM to set things like maximum processes, max open files, memory usage etc and these limits can be set on a per-user basis, so I added these lines: mysql soft nofile 4096 mysql hard nofile 4096 大体的意思是说，这个报错的原因：由于：mysql的配置文件/etc/my.cnf的参数open-files-limit设置的比linux的max user processes的数值大，需要通过修改linux的配置文件 /etc/security/limits.d/90-nproc.conf来扩大linux系统的限制，也就是这个错是由于linux的max user processes阈值太小了。 马上查看我的相关配置： mysql的open-files-limit，如下所示： [root@i-iivphroy ~]# cat /etc/my.cnf [mysqld_safe] open-files-limit=85216 linux的max user processes，如下所示红色部分： [root@i-iivphroy ~]# ulimit -a core file size (blocks, -c) 0 data seg size (kbytes, -d) unlimited scheduling priority (-e) 0 file size (blocks, -f) unlimited pending signals (-i) 62842 max locked memory (kbytes, -l) 64 max memory size (kbytes, -m) unlimited open files (-n) 1024 pipe size (512 bytes, -p) 8 POSIX message queues (bytes, -q) 819200 real-time priority (-r) 0 stack size (kbytes, -s) 10240 cpu time (seconds, -t) unlimited max user processes (-u) 62842 virtual memory (kbytes, -v) unlimited file locks (-x) unlimited 将查看果然是前面文档描述的情况，马上修改max user processes 方法一： [root@i-iivphroy ~]# cat /etc/security/limits.conf * soft nofile 65535 * hard nofile 65535 * soft nproc 65535 * hard nproc 65535 其中nofile对应open_files nproc对应max_user_processes 但是在Linux 6.4之后，如果只修改了该文件中的nproc，那么其他非root用户对应的max_user_processes并不会改变，仍然是1024，这个是因为受到了下面这个文件的影响 /etc/security/limits.d/90-nproc.conf 修改/etc/security/limits.d/90-nproc.conf将 * soft nproc 1024 修改为： * soft nproc 65535 或者 修改/etc/security/limits.conf，将 * soft nofile 10240 修改为 Oracle soft nofile 10240 方法二：这样为每一个运行bash shell的用户执行此文件，当bash shell被打开时,该文件被读取。也就是说，当用户shell执行了bash时，运行这个文件，如果这个服务器上有多个用户，最好是用方法一。 修改了/etc/bashrc，成功了，并且不用重启。 vi /etc/bashrc 添加 ： ulimit -u 65535 退出session,从新开session再次ulimit -a 发现已经变化了 [root@i-iivphroy ~]# ulimit -a core file size (blocks, -c) 0 data seg size (kbytes, -d) unlimited scheduling priority (-e) 0 file size (blocks, -f) unlimited pending signals (-i) 62842 max locked memory (kbytes, -l) 64 max memory size (kbytes, -m) unlimited open files (-n) 1024 pipe size (512 bytes, -p) 8 POSIX message queues (bytes, -q) 819200 real-time priority (-r) 0 stack size (kbytes, -s) 10240 cpu time (seconds, -t) unlimited max user processes (-u) 65535 virtual memory (kbytes, -v) unlimited file locks (-x) unlimited 并且把mysql的open-files-limit改小。 [root@i-iivphroy ~]# cat /etc/my.cnf [mysqld_safe] open-files-limit=65000 重启了mysql服务，问题解决。。。。 原因分析： 操作系统连接数太小。（比如centos 6 默认的 max user process只有 1024个。当mysql process大于这个值时 就会出现Can't create a new thread的问题） 连接数超限处理的办法： ulimit -a 查看max user processes 这一项 要是这个值比较的小 当mysql中process的数目超过这个数的时候 就会抱标题相应的错误。 一个过程process可以视为一个打开的文件 也就是说 下面几个参数共同控制这mysql的 create a new thread 1） mysql的 /etc/my.cnf open-files-limit=65535 2）linux 参数 open files和max user processes [root@S243 ~]# ulimit unlimited [root@S243 ~]# ulimit -a core file size (blocks, -c) 0 data seg size (kbytes, -d) unlimited scheduling priority (-e) 0 file size (blocks, -f) unlimited pending signals (-i) 1032207 max locked memory (kbytes, -l) 64 max memory size (kbytes, -m) unlimited open files (-n) 50000 pipe size (512 bytes, -p) 8 POSIX message queues (bytes, -q) 819200 real-time priority (-r) 0 stack size (kbytes, -s) 10240 cpu time (seconds, -t) unlimited max user processes (-u) 65535 virtual memory (kbytes, -v) unlimited file locks (-x) unlimited 感谢各位的阅读，以上就是“怎么解决mysql中的ERROR 1135 (HY000)报错问题”的内容了，经过本文的学习后，相信大家对怎么解决mysql中的ERROR 1135 (HY000)报错问题这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是亿速云，小编将为大家推送更多相关知识点的文章，欢迎关注！ （编辑：温州站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
