mysql磁盘空间满了会崩吗_磁盘空间不够导致mysql崩溃重启

起因:

群里有人提了句pt-ioprofile,我不知道,就查了查,想测一测,想以后可能会有帮助。

为了能看到效果,我选择了我虚拟机上最大的压测表Sbtest1,该表有100w数据,执行update sbtest1 set k=k+1;

并且通过pt-ioprofile查看到了想要的结果,然后就干别的去了,下午了,看update sbtest1 set k=k+1;这个窗口的光标还闪着,以为还没执行完,不停地回车,crtl c,各种不好用。过了一会儿,报错了,并且提示mysql已经重启了。

我去他嘞

报错信息为:Binary logging not possible. Message: An error occurred during flush stage of the commit.‘binlog_error_action‘ is set to ‘ABORT_SERVER‘. Hence aborting the server.

给出这个,我也看不明白

查看错误日志:

2018-11-01T11:25:54.493321+08:00 895 [Note] Access denied for user [email protected] (using password: NO)

2018-11-01T11:29:17.566331+08:00 896 [ERROR] Disk is full writing ‘/data/mysql/mysql-bin.000025‘ (Errcode: 16026912 - No space left on device). Waiting for someone to free space...

2018-11-01T11:29:17.566355+08:00 896 [ERROR] Retry in 60 secs. Message reprinted in 600 secs

2018-11-01T11:30:17.567664+08:00 896 [ERROR] Disk is full writing ‘/data/mysql/mysql-bin.000025‘ (Errcode: 16026912 - No space left on device). Waiting for someone to free space...

2018-11-01T11:30:17.567705+08:00 896 [ERROR] Retry in 60 secs. Message reprinted in 600 secs

2018-11-01T11:40:17.641906+08:00 896 [ERROR] Disk is full writing ‘/data/mysql/mysql-bin.000025‘ (Errcode: 16026912 - No space left on device). Waiting for someone to free space...

2018-11-01T11:40:17.642048+08:00 896 [ERROR] Retry in 60 secs. Message reprinted in 600 secs

2018-11-01T11:40:17.707079+08:00 896 [ERROR] Disk is full writing for someone to free space...

2018-11-01T11:40:17.642048+08:00 896 [ERROR] Retry in 60 secs. Message reprinted in 600 secs

2018-11-01T11:50:19.000088+08:00 896 [ERROR] /usr/local/mysql/bin/mysqld: Binary logging not possible. Message: An error occurred during flush stage of the commit. ‘binlog_error_action‘ is set to ‘ABORT_SERVER‘. Hence aborting the server.

06:08:19 UTC - mysqld got signal 6 ;

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.

Attempting to collect some information that could help diagnose the problem.

As this is a crash and something is definitely wrong, the information

collection process might fail.

key_buffer_size=134217728

read_buffer_size=524288

max_used_connections=3

max_threads=10000

thread_count=1

connection_count=1

It is possible that mysqld could use up to

key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 15624665 K bytes of memory

Hope that‘s ok; if not, decrease some variables in the equation.

Thread pointer: 0x5aa4a70

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 = 7fa568c81e58 thread_stack 0x40000

/usr/local/mysql/bin/mysqld(my_print_stacktrace+0x35)[0xf4a495]

/usr/local/mysql/bin/mysqld(handle_fatal_signal+0x4a4)[0x7ce2f4]

/lib64/libpthread.so.0[0x3c9bc0f670]

/lib64/libc.so.6(gsignal+0x3e)[0x3c9b4322fe]

/lib64/libc.so.6(abort+0x175)[0x3c9b433745]

/usr/local/mysql/bin/mysqld[0xee249a]

/usr/local/mysql/bin/mysqld(_ZN13MYSQL_BIN_LOG33handle_binlog_flush_or_sync_errorEP3THDb+0x163)[0xef0bd3]

/usr/local/mysql/bin/mysqld(_ZN13MYSQL_BIN_LOG14ordered_commitEP3THDbb+0x3ca)[0xef106a]

/usr/local/mysql/bin/mysqld(_ZN13MYSQL_BIN_LOG6commitEP3THDb+0x585)[0xef1825]

/usr/local/mysql/bin/mysqld(_Z15ha_commit_transP3THDbb+0x174)[0x81f594]

/usr/local/mysql/bin/mysqld(_Z17trans_commit_stmtP3THD+0x32)[0xdd1272]

/usr/local/mysql/bin/mysqld(_Z21mysql_execute_commandP3THDb+0x707)[0xd161f7]

/usr/local/mysql/bin/mysqld(_Z11mysql_parseP3THDP12Parser_state+0x40d)[0xd1af7d]

/usr/local/mysql/bin/mysqld(_Z16dispatch_commandP3THDPK8COM_DATA19enum_server_command+0x119a)[0xd1c19a]

/usr/local/mysql/bin/mysqld(_Z10do_commandP3THD+0x194)[0xd1d044]

/usr/local/mysql/bin/mysqld(handle_connection+0x29c)[0xded7ac]

/usr/local/mysql/bin/mysqld(pfs_spawn_thread+0x174)[0xf707b4]

/lib64/libpthread.so.0[0x3c9bc06cea]

/lib64/libc.so.6(clone+0x6d)[0x3c9b4d7fad]

Trying to get some variables.

Some pointers may be invalid and cause the dump to abort.

Query (5b066e0): update sbtest1 set k=k+1

Connection ID (thread ID): 896

Status: KILL_QUERY

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.

2018-11-01T14:08:19.463179+08:00 0 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled

2018-11-01T14:08:19.463280+08:00 0 [Note] /usr/local/mysql/bin/mysqld (mysqld 5.7.21-log) starting as process 54682 ...

就现象看,即使磁盘空间满了,mysql不会立刻宕机,而是每一分钟做一次检查,在600秒内将错误信息记录到错误日志。直到我瞎按,触发了重启。

由于不知所措,期间并没有执行show full processlist查看,一切都是后知后觉

重新测试一下:

空间写满

a7134b7432f044f4a0f773d22601cb9d.png

错误日志开始循环打印信息

73dd568fd4692d0a3b2930e946e0093b.png

State query end

095a3263df8bbea80182fd7bdba3dfa0.png

日志不记录了,手动ctrl c

d3ba3614b640f4f0c70bd11184c4f675.png

4706564c9e59cb57c72e652534e10882.png

日志开始kill query

2e2878fa67d8f31a6d61d42d1543b47e.png

Mysql重启

78fe9567237ef80bb76d23638a9fbeed.png

空间还原

7467c7df870927c56661f8b402f8cc86.png

Binlog之前记录的也被清理了,重启后生成个新的27

前:

49d701f0902c3c5de606052fc72e5839.png

后:

3ccdd93c07767958e47d03034522019f.png

如果手动kill query是不是不会导致restart?

62d384ab465a58f1313f771b83818ae9.png

执行kill操作依然触发了重启

2b4dad068993ff023ce7c2f2901433f4.png

结论:

所以还是要实时监控空间大小啊。

原文:http://blog.51cto.com/8370646/2312534

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值