数据库mysq服务无法启动
临时解决办法：
经人提醒用mysqld_safe方式直接启动(自打脸1024次)
事件回顾
赞到爆活动粉丝猛增导致数据库主机CPU满载，系统卡顿
解决过程
- 将数据库关闭，操作命令:systemctl stop mysqld
- 将数据库重启一次检验是否可正常重启 systemctl start mysqld可正常启动
- 将数据库关闭
- 在阿里云控制台将数据库主机扩容至16C/32G高性能计算型ECS
- 登陆数据库主机systemctl start mysqld无法启动，日志报错如下
mysql报错信息
|
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
|
key_buffer_size=8388608read_buffer_size=131072max_used_connections=0max_threads=300thread_count=0connection_count=0It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 127390 K bytes of memoryHope that's ok; if not, decrease some variables in the equation.
Thread pointer: 0x0Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something wentterribly wrong...stack_bottom = 0 thread_stack 0x40000/usr/sbin/mysqld(my_print_stacktrace+0x35)[0xf4c845]
/usr/sbin/mysqld(handle_fatal_signal+0x4a4)[0x7dd584]
/lib64/libpthread.so.0(+0xf370)[0x7f814fcb7370]
/usr/lib64/mysql/plugin/server_audit.so(get_db_mysql57+0x12)[0x7f81107123e2]
/usr/lib64/mysql/plugin/server_audit.so(+0xa541)[0x7f8110712541]
/usr/sbin/mysqld[0x7dd6c6]
/usr/sbin/mysqld(_Z24plugin_foreach_with_maskP3THDPFcS0_P13st_plugin_intPvEijS3_+0x1b9)[0xd3e349]
/usr/sbin/mysqld(_Z18mysql_audit_notifyP3THD30mysql_event_general_subclass_tiPKcm+0x3d6)[0x7de326]
/usr/sbin/mysqld(my_message_sql+0x6f)[0x7cf28f]
/usr/sbin/mysqld(my_error+0xf6)[0xf47246]
/usr/sbin/mysqld(my_register_filename+0x29a)[0xf49b5a]
/usr/sbin/mysqld(my_create+0x6c)[0xf46a9c]
/usr/sbin/mysqld[0x7d0c60]
/usr/sbin/mysqld(_Z11mysqld_mainiPPc+0x16c0)[0x7d7fb0]
/lib64/libc.so.6(__libc_start_main+0xf5)[0x7f814e697b35]
/usr/sbin/mysqld[0x7cd49d]
information that should help you find out what is causing the crash.
2018-05-11T06:49:23.670507Z mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
|
被上图中的第8行日志
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 127390 K bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
所迷惑，我们一直在修改上面的几个参数，但是数据库仍然无法启动
根据原因探索
- 检查/etc/init.d/mysqld系统启动脚本，其中有一段代码是判断PID文件是否存在，如果/etc/my.cnf文件里无配置就会用/var/run/mysqld/mysqld.pid默认配置文件
代码如下
mysqld启动脚本
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
get_mysql_option(){ result=$(/usr/bin/my_print_defaults "$1" | sed -n "s/^--$2=//p" | tail -n 1)
if [ -z "$result" ]; then
# not found, use default
result="$3"
fi
}get_mysql_option mysqld datadir "/var/lib/mysql"
datadir="$result"
get_mysql_option mysqld socket "$datadir/mysql.sock"
socketfile="$result"
get_mysql_option mysqld_safe log-error "/var/log/mysqld.log"
errlogfile="$result"
get_mysql_option mysqld_safe pid-file "/var/run/mysqld/mysqld.pid"
mypidfile="$result"
|
- 检查/var/run下并无此文件，因为主机重启后系统会自动将此文件删除
三种解决方案：
原理解析：/etc/init.d/mysqld原理其实就是调用mysqld_safe来启动数据库
- 方法一：给/etc/my.cnf添加配置让mysql到自己的数据目录下写/mydata/mysqld.pid
[mysqld_safe]
log-error=/mydata/mysqld.log
pid-file=/mydata/mysqld.pid
结果：systemctl start mysqld会一直卡在那，systemctl不正常结束返回，此时数据库是已经正常起来了，而操作系统命令systemctl找不到/var/run/mysqld/mysqld.pid而卡在那（失败） - 方法二：更换启动方式，直接用mysqld_safe --defaults-file=/etc/my.cnf启动，此方法是默认到/mydata目录下创建mysqld.pid文件，但是关闭时用
mysqladmin shutdown -uroot -P6033 -p方式来关闭，还要输入密码较麻烦而且做开机启动也麻烦 -
方法三：修改/etc/init.d/mysqld启动脚本添加自动创建/var/run/mysqld文件夹
如果文件夹不存在就自动创建123456test-d/var/run/mysqldif[ $? -eq1 ]thenmkdir/var/run/mysqld/chownmysql:mysql/var/run/mysqldfi
主机重启可自动将数据库拉起。
总结
此次故障处理心得
<p class="title">心得</p>
<span class="aui-icon icon-warning">Icon</span>
<div class="message-content">
<p>1：一直被mysql的报错迷惑不断调试各个buffer的size大小，</p><p>2：忘记了尝试用mysqld_safe来启动</p><p>3：从库没能在第一时间切换成主库，这个在接下来的时候进行方案设计和实施</p><p>4：业务逻辑没有实现读写分离从库一直空闲而主库却已经累死</p><p>5：慢查询超时未告警部署</p><p>6：直接导致丢失大量可能性粉丝</p>
</div>
</div>