最新消息:20210816 当前crifan.com域名已被污染,为防止失联,请关注(页面右下角的)公众号

【已解决】Ubuntu中mysql警告:Warning Changed limits max_open_files 1024 requested 5000

MySQL crifan 550浏览 0评论
折腾:
【未解决】Ubuntu中mysql有时候会突然挂掉
期间,想要寻找mysql突然挂掉原因,在给mysql加上error的log:
/var/log/mysql/mysql_error.log
后,去看看error的log内容:
2019-06-07T14:03:42.869214Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000)
2019-06-07T14:03:43.022888Z 0 [Warning] The syntax '--log_warnings/-W' is deprecated and will be removed in a future release. Please use '--log_error_verbosity' instead.
2019-06-07T14:03:43.022933Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2019-06-07T14:03:43.024572Z 0 [Warning] Insecure configuration for --secure-file-priv: Location is accessible to all OS users. Consider choosing a different directory.
2019-06-07T14:03:43.024608Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.23-0ubuntu0.16.04.1-log) starting as process 4631 ...
2019-06-07T14:03:43.036761Z 0 [Note] InnoDB: PUNCH HOLE support available
2019-06-07T14:03:43.036791Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2019-06-07T14:03:43.036796Z 0 [Note] InnoDB: Uses event mutexes
2019-06-07T14:03:43.036801Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2019-06-07T14:03:43.036806Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.8
2019-06-07T14:03:43.036811Z 0 [Note] InnoDB: Using Linux native AIO
2019-06-07T14:03:43.037046Z 0 [Note] InnoDB: Number of pools: 1
2019-06-07T14:03:43.037155Z 0 [Note] InnoDB: Using CPU crc32 instructions
2019-06-07T14:03:43.041522Z 0 [Note] InnoDB: Initializing buffer pool, total size = 256M, instances = 1, chunk size = 128M
2019-06-07T14:03:43.055658Z 0 [Note] InnoDB: Completed initialization of buffer pool
2019-06-07T14:03:43.059143Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2019-06-07T14:03:43.200394Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2019-06-07T14:03:43.942483Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2019-06-07T14:03:43.942546Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2019-06-07T14:03:44.665619Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2019-06-07T14:03:44.666742Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2019-06-07T14:03:44.666757Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2019-06-07T14:03:44.671441Z 0 [Note] InnoDB: 5.7.23 started; log sequence number 29032650
2019-06-07T14:03:44.671609Z 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2019-06-07T14:03:44.671772Z 0 [Note] Plugin 'FEDERATED' is disabled.
2019-06-07T14:03:45.108874Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key
2019-06-07T14:03:45.108916Z 0 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
2019-06-07T14:03:45.108929Z 0 [Note]   - '127.0.0.1' resolves to '127.0.0.1';
2019-06-07T14:03:45.108998Z 0 [Note] Server socket created on IP: '127.0.0.1'.
2019-06-07T14:03:45.392528Z 0 [Note] Event Scheduler: Loaded 0 events
2019-06-07T14:03:45.393248Z 0 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.7.23-0ubuntu0.16.04.1-log'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)
2019-06-07T14:03:45.877298Z 2 [Note] Access denied for user 'root'@'localhost' (using password: NO)
2019-06-07T14:03:46.149171Z 0 [Note] InnoDB: Buffer pool(s) load completed at 190607 22:03:46
感觉最可疑的是第一句:
[Warning] Changed limits: max_open_files: 1024 (requested 5000)
->怀疑是:
后期打开文件超过max_open_files,导致mysql挂了?
所以就先去尽量解决这个问题
mysql max_open_files 1024 (requested 5000)
MySQL启动报“[Warning] Buffered warning: Changed limits: max_open_files: 1024 (requested 15000)” – linjin200的个人空间 – OSCHINA
[email protected]:/var/log/mysql# 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) 15145
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) 8192
cpu time               (seconds, -t) unlimited
max user processes              (-u) 15145
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited
mysql报错Changed limits: max_open_files: 5000 – linjin200的个人空间 – OSCHINA
mysql> show variables like '%files%';
+---------------------------+--------+
| Variable_name             | Value  |
+---------------------------+--------+
| character_set_filesystem  | binary |
| innodb_log_files_in_group | 2      |
| innodb_open_files         | 400    |
| keep_files_on_create      | OFF    |
| large_files_support       | ON     |
| open_files_limit          | 1024   |
+---------------------------+--------+
6 rows in set (0.03 sec)

mysql> show variables like '%connections%';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| max_connections      | 100   |
| max_user_connections | 0     |
+----------------------+-------+
2 rows in set (0.00 sec)

mysql> show variables like '%table_open_cache%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| table_open_cache           | 400   |
| table_open_cache_instances | 16    |
+----------------------------+-------+
2 rows in set (0.00 sec)
设置
[email protected]:/var/log/mysql# ulimit -n 65535
[email protected]:/var/log/mysql# 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) 15145
max locked memory       (kbytes, -l) 64
max memory size         (kbytes, -m) unlimited
open files                      (-n) 65535
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
real-time priority              (-r) 0
stack size              (kbytes, -s) 8192
cpu time               (seconds, -t) unlimited
max user processes              (-u) 15145
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited
重启mysql结果:
service mysql restart
再去看看log和结果
问题依旧
2019-06-07T14:13:21.881128Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000)
mysql – Buffered warning: Changed limits: max_connections: 214 (requested 800) – Stack Overflow
logging – MySQL warnings about max_open_files and table_open_cache – Stack Overflow
需要先去:
vi /etc/security/limits.conf
[email protected]:/var/log/mysql# ll /etc/security/limits.conf
-rw-r--r-- 1 root root 2150 Mar 17  2016 /etc/security/limits.conf
MySQL repeatedly crashing – Stack Overflow
performance – Can not increase max_open_files for Mysql max-connections in Ubuntu 15 – Stack Overflow
MySQL server erroring – Stack Overflow
[email protected]:/var/log/mysql# df -h
Filesystem      Size  Used Avail Use% Mounted on
udev            1.9G     0  1.9G   0% /dev
tmpfs           383M   40M  344M  11% /run
/dev/vda1        50G  7.7G   39G  17% /
tmpfs           1.9G   24K  1.9G   1% /dev/shm
tmpfs           5.0M     0  5.0M   0% /run/lock
tmpfs           1.9G     0  1.9G   0% /sys/fs/cgroup
tmpfs           100K     0  100K   0% /run/lxcfs/controllers
tmpfs           383M     0  383M   0% /run/user/0
不是服务器硬盘不够的问题
[email protected]:/var/log/mysql# ll /etc/systemd/system/
total 60
drwxr-xr-x 14 root root 4096 Nov 24  2017 ./
drwxr-xr-x  5 root root 4096 Dec 12  2016 ../
drwxr-xr-x  2 root root 4096 Dec 12  2016 default.target.wants/
drwxr-xr-x  2 root root 4096 Oct 26  2016 getty.target.wants/
drwxr-xr-x  2 root root 4096 Oct 26  2016 graphical.target.wants/
lrwxrwxrwx  1 root root   38 Oct 26  2016 iscsi.service -> /lib/systemd/system/open-iscsi.service
drwxr-xr-x  2 root root 4096 Feb  6  2017 mariadb.service.d/
drwxr-xr-x  2 root root 4096 Aug 20  2018 multi-user.target.wants/
lrwxrwxrwx  1 root root   35 Feb  6  2017 mysqld.service -> /lib/systemd/system/mariadb.service
lrwxrwxrwx  1 root root   35 Feb  6  2017 mysql.service -> /lib/systemd/system/mariadb.service
drwxr-xr-x  2 root root 4096 Oct 26  2016 network-online.target.wants/
drwxr-xr-x  2 root root 4096 Oct 26  2016 paths.target.wants/
-rw-r--r--  1 root root  635 Dec 12  2016 rc-local.service
drwxr-xr-x  2 root root 4096 Nov 24  2017 remote-fs.target.wants/
drwxr-xr-x  2 root root 4096 Oct 26  2016 shutdown.target.wants/
drwxr-xr-x  2 root root 4096 Nov 24  2017 sockets.target.wants/
lrwxrwxrwx  1 root root   31 Oct 26  2016 sshd.service -> /lib/systemd/system/ssh.service
drwxr-xr-x  2 root root 4096 Oct 26  2016 sysinit.target.wants/
lrwxrwxrwx  1 root root   35 Oct 26  2016 syslog.service -> /lib/systemd/system/rsyslog.service
drwxr-xr-x  2 root root 4096 Feb  6  2017 timers.target.wants/
发现此处已经有了:
lrwxrwxrwx  1 root root   35 Feb  6  2017 mysqld.service -> /lib/systemd/system/mariadb.service
lrwxrwxrwx  1 root root   35 Feb  6  2017 mysql.service -> /lib/systemd/system/mariadb.service
去编辑
发现竟然没有
/lib/systemd/system/mariadb.service
不对,是拷贝后:
[email protected]:/var/log/mysql# ll /lib/systemd/system/mysql.service
-rw-r--r-- 1 root root 411 Feb  4  2017 /lib/systemd/system/mysql.service
[email protected]:/var/log/mysql# cp /lib/systemd/system/mysql.service /etc/systemd/system/
cp: not writing through dangling symlink '/etc/systemd/system/mysql.service'
[email protected]:/var/log/mysql# cp /lib/systemd/system/mysql.service /etc/systemd/system/mysql.service
cp: not writing through dangling symlink '/etc/systemd/system/mysql.service'
[email protected]:/var/log/mysql# ll /etc/systemd/system/mysql.service
lrwxrwxrwx 1 root root 35 Feb  6  2017 /etc/systemd/system/mysql.service -> /lib/systemd/system/mariadb.service

[email protected]:/var/log/mysql# cp /lib/systemd/system/mysql.service /lib/systemd/system/mariadb.service
[email protected]:/var/log/mysql# ll /lib/systemd/system/mariadb.service
-rw-r--r-- 1 root root 411 Jun  7 22:27 /lib/systemd/system/mariadb.service

vim /lib/systemd/system/mariadb.service
[email protected]:/var/log/mysql# cat /lib/systemd/system/mariadb.service
# MySQL systemd service file

[Unit]
Description=MySQL Community Server
After=network.target

[Install]
WantedBy=multi-user.target

[Service]
User=mysql
Group=mysql
PermissionsStartOnly=true
ExecStartPre=/usr/share/mysql/mysql-systemd-start pre
ExecStart=/usr/sbin/mysqld
ExecStartPost=/usr/share/mysql/mysql-systemd-start post
TimeoutSec=600
Restart=on-failure
RuntimeDirectory=mysqld
RuntimeDirectoryMode=755

LimitNOFILE=infinity
LimitMEMLOCK=infinity
再去重启Systemd configuration
[email protected]:/var/log/mysql# sudo systemctl daemon-reload
和:
[email protected]:/var/log/mysql# service mysql restart
【总结】
此处mysql启动后,error的log中有警告:
[Warning] Changed limits: max_open_files: 1024 (requested 5000)
原因:
系统最大可打开文件数太小
解决办法:增大max_open_files
步骤:
先确认的确是:
#ulimit -a
...
open files                      (-n) 1024
然后去增加:
除了设置:
ulimit -n 65535
之后,还要去修改mysql的service中的配置。
注:
Ubuntu 15.04只有,就不用:
/etc/security/limits.conf
了,而是用:
/etc/systemd/system/mysql.service
此处Ubuntu 16.04.1的系统,发现已有:
# ll /etc/systemd/system/
...
lrwxrwxrwx  1 root root   35 Feb  6  2017 mysqld.service -> /lib/systemd/system/mariadb.service
lrwxrwxrwx  1 root root   35 Feb  6  2017 mysql.service -> /lib/systemd/system/mariadb.service
但是呢:
/lib/systemd/system/mariadb.service
却是空的。
所以就:
拷贝一份过来:
cp /lib/systemd/system/mysql.service /lib/systemd/system/mariadb.service
注:mysql相关的service文件:
/lib/systemd/system/mysql.service
可以通过:
service mysql status
看出来。
再去编辑:
vim /lib/systemd/system/mariadb.service
在最后加上:
LimitNOFILE=infinity
LimitMEMLOCK=infinity
或:
LimitNOFILE=65535
LimitMEMLOCK=65535
均可。
最后再去重启Systemd configuration:
sudo systemctl daemon-reload
和重启mysql:
sudo service mysql restart
以及确认:
# ulimit -a
...
open files                      (-n) 65535
和去看error的log:
/var/log/mysql/mysql_error.log
就没了该警告了。

转载请注明:在路上 » 【已解决】Ubuntu中mysql警告:Warning Changed limits max_open_files 1024 requested 5000

发表我的评论
取消评论

表情

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址
88 queries in 0.125 seconds, using 20.67MB memory