折腾：
【未解决】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)
root@VM-158-80-ubuntu:/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> 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)
设置
root@VM-158-80-ubuntu:/var/log/mysql# ulimit -n 65535 root@VM-158-80-ubuntu:/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)
需要先去：
vi /etc/security/limits.conf
root@VM-158-80-ubuntu:/var/log/mysql# ll /etc/security/limits.conf -rw-r--r-- 1 root root 2150 Mar 17 2016 /etc/security/limits.conf
root@VM-158-80-ubuntu:/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
不是服务器硬盘不够的问题
root@VM-158-80-ubuntu:/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
不对，是拷贝后：
root@VM-158-80-ubuntu:/var/log/mysql# ll /lib/systemd/system/mysql.service -rw-r--r-- 1 root root 411 Feb 4 2017 /lib/systemd/system/mysql.service root@VM-158-80-ubuntu:/var/log/mysql# cp /lib/systemd/system/mysql.service /etc/systemd/system/ cp: not writing through dangling symlink '/etc/systemd/system/mysql.service' root@VM-158-80-ubuntu:/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' root@VM-158-80-ubuntu:/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 root@VM-158-80-ubuntu:/var/log/mysql# cp /lib/systemd/system/mysql.service /lib/systemd/system/mariadb.service root@VM-158-80-ubuntu:/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
root@VM-158-80-ubuntu:/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
root@VM-158-80-ubuntu:/var/log/mysql# sudo systemctl daemon-reload
和：
root@VM-158-80-ubuntu:/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