今天发现MySQL突然无法访问,启动服务报错
Starting MySQL.. ERROR! The server quit without updating PID file[FAILED]....pid
查看MySQL进程,报错
MySQL is not running,but lock file (/var/lock/subsys/mysql[FAILED]
删除这个mysql文件也没用。
最后发现原来是MySQL日志撑爆了磁盘空间,导致无法写入日志,删除部分日志即可。
其实可以设置日志记录策略,如果单机MySQL则完全没有必要日志,注释日志记录配置
注释/etc/my.cnf中
#log-bin=mysql-bin
#binlog_format=mixed
或者设置日志过期时间,保留7天日志
在/etc/my.cnf中添加
expire_logs_days = 7
排错记录如下:
起初找不到错误日志,设置记录错误日志
/etc/my.cnf
添加内容
log_error = /var/log/mysql/error.log
增加错误日志目录
mkdir /var/log/mysql
再次启动服务,发现日志
180112 11:11:28 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
180112 11:11:29 [Note] Plugin 'FEDERATED' is disabled.
180112 11:11:29 InnoDB: The InnoDB memory heap is disabled
180112 11:11:29 InnoDB: Mutexes and rw_locks use GCC atomic builtins
180112 11:11:29 InnoDB: Compressed tables use zlib 1.2.3
180112 11:11:29 InnoDB: Using Linux native AIO
180112 11:11:29 InnoDB: Initializing buffer pool, size = 128.0M
180112 11:11:29 InnoDB: Completed initialization of buffer pool
180112 11:11:29 InnoDB: highest supported file format is Barracuda.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
180112 11:11:29 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Last MySQL binlog file position 0 831375219, file name ./mysql-bin.000016
180112 11:11:29 InnoDB: Waiting for the background threads to start
180112 11:11:30 InnoDB: 5.5.31 started; log sequence number 9124484533
180112 11:11:30 [Note] Recovering after a crash using mysql-bin
180112 11:11:32 [ERROR] Error in Log_event::read_log_event(): 'read error', data_len: 428, event_type: 2
180112 11:11:32 [Note] Starting crash recovery...
180112 11:11:32 [Note] Crash recovery finished.
03:11:32 UTC - mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed,
something is definitely wrong and this may fail.
key_buffer_size=16777216
read_buffer_size=262144
max_used_connections=0
max_threads=151
thread_count=0
connection_count=0
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 134077 K bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
Thread pointer: 0x0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0 thread_stack 0x40000
/usr/sbin/mysqld(my_print_stacktrace+0x35)[0x7aa975]
/usr/sbin/mysqld(handle_fatal_signal+0x4a4)[0x6831a4]
/lib64/libpthread.so.0[0x3894c0f710]
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
180112 11:11:32 mysqld_safe mysqld from pid file /var/lib/mysql/******.pid ended
最终尝试寻找磁盘空间问题
运行df -lh
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/vg_research2-lv_root 50G 47G 5.3M 100% /
tmpfs 16G 0 16G 0% /dev/shm
/dev/sda1 485M 39M 421M 9% /boot
/dev/mapper/vg_research2-lv_home 484G 308G 152G 67% /home
删除部分MySQL操作日志,服务启动成功
[root@cm ~]# df -lh
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/vg_research2-lv_root 50G 44G 3.1G 94% /
tmpfs 16G 0 16G 0% /dev/shm
/dev/sda1 485M 39M 421M 9% /boot
/dev/mapper/vg_research2-lv_home 484G 308G 152G 67% /home
[root@cm ~]# service mysql restart
Shutting down MySQL.... [ OK ]
Starting MySQL.. [ OK ]