折腾:
【未解决】Ubuntu中mysql有时候会突然挂掉
期间,想要寻找mysql突然挂掉原因,在给mysql加上error的log:
/var/log/mysql/mysql_error.log
后,去看看error的log内容:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 | 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)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | 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 |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 | 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) |
设置
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | 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结果:
1 | service mysql restart |
再去看看log和结果
问题依旧
1 | 2019-06-07T14:13:21.881128Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000) |
需要先去:
vi /etc/security/limits.conf
1 2 | 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 |
1 2 3 4 5 6 7 8 9 10 | 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 |
不是服务器硬盘不够的问题
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | 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/ |
发现此处已经有了:
1 2 | 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
不对,是拷贝后:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | 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 |

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | 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
1 | root@VM-158-80-ubuntu: /var/log/mysql # sudo systemctl daemon-reload |
和:
1 | root@VM-158-80-ubuntu: /var/log/mysql # service mysql restart |
【总结】
此处mysql启动后,error的log中有警告:
1 | [Warning] Changed limits: max_open_files: 1024 (requested 5000) |
原因:
系统最大可打开文件数太小
解决办法:增大max_open_files
步骤:
先确认的确是:
1 2 3 | #ulimit -a ... open files (-n) 1024 |
然后去增加:
除了设置:
1 | ulimit -n 65535 |
之后,还要去修改mysql的service中的配置。
注:
Ubuntu 15.04只有,就不用:
1 | /etc/security/limits .conf |
了,而是用:
1 | /etc/systemd/system/mysql .service |
此处Ubuntu 16.04.1的系统,发现已有:
1 2 3 4 | # 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
却是空的。
所以就:
拷贝一份过来:
1 | cp /lib/systemd/system/mysql .service /lib/systemd/system/mariadb .service |
注:mysql相关的service文件:
1 | /lib/systemd/system/mysql .service |
可以通过:
1 | service mysql status |
看出来。
再去编辑:
1 | vim /lib/systemd/system/mariadb .service |
在最后加上:
1 2 | LimitNOFILE = infinity LimitMEMLOCK = infinity |
或:
1 2 | LimitNOFILE = 65535 LimitMEMLOCK = 65535 |
均可。
最后再去重启Systemd configuration:
1 | sudo systemctl daemon-reload |
和重启mysql:
1 | sudo service mysql restart |
以及确认:
1 2 3 | # 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