mysql安装时服务无法启动(MySQL 实例无法启动的问题分析及解决)
mysql安装时服务无法启动
MySQL 实例无法启动的问题分析及解决前言
前几天,有位朋友微信联系我,告知一个生产数据库,在机器宕机恢复后,实例启动失败,而且该实例没有做任何的高可用、容灾、备份等,对业务影响非常大,希望能够协助排查一下,我也在第一时间就加入到排查中。
场景分析
(1)首先查看错误日志,报错很清晰"could not open log file",无法打开日志文件
|
2021-01-06 13:23:51 20464 [error] failed to open log (file 'something is definitely wrong and this may fail.' , errno 2) 2021-01-06 13:23:51 20464 [error] could not open log file 2021-01-06 13:23:51 20464 [error] can't init tc log 2021-01-06 13:23:51 20464 [error] aborting |
(2)看到上述报错后,当然就应该想到,检查下my.cnf配置是否正确、日志目录和权限是否正确,但排查并未发现问题
|
# less my.cnf datadir=/var/lib/mysql log-bin=mysql-bin relay-log=relay-bin # ls -lrt -rw-rw ---- 1 mysql mysql 1073761373 jan 4 06:18 mysql-bin.007351 -rw-rw ---- 1 mysql mysql 1073755587 jan 4 09:26 mysql-bin.007352 -rw-rw ---- 1 mysql mysql 1073777045 jan 4 12:07 mysql-bin.007353 -rw-rw ---- 1 mysql mysql 1073742801 jan 4 15:12 mysql-bin.007354 -rw-rw ---- 1 mysql mysql 1074087344 jan 4 18:13 mysql-bin.007355 -rw-rw ---- 1 mysql mysql 1073869414 jan 4 21:32 mysql-bin.007356 -rw-rw ---- 1 mysql mysql 1073771900 jan 5 00:16 mysql-bin.007357 -rw-rw ---- 1 mysql mysql 213063247 jan 5 01:00 mysql-bin.007358 -rw-rw ---- 1 mysql mysql 1073753668 jan 5 02:11 mysql-bin.007359 -rw-rw ---- 1 mysql mysql 671219722 jan 5 03:31 mysql-bin.007360 -rw-rw ---- 1 mysql mysql 1073774928 jan 5 07:34 mysql-bin.007361 -rw-rw ---- 1 mysql mysql 1073845285 jan 5 11:33 mysql-bin.007362 -rw-rw ---- 1 mysql mysql 1073756444 jan 5 15:37 mysql-bin.007363 -rw-rw ---- 1 mysql mysql 1073790555 jan 5 19:37 mysql-bin.007364 -rw-rw ---- 1 mysql mysql 1073768027 jan 5 23:59 mysql-bin.007365 -rw-rw ---- 1 mysql mysql 311398643 jan 6 01:00 mysql-bin.007366 -rw-rw ---- 1 mysql mysql 1071242043 jan 6 03:31 mysql-bin.007367 -rw-rw ---- 1 mysql mysql 1010516229 jan 6 07:27 mysql-bin.007368 -rw-rw ---- 1 mysql mysql 1651 jan 6 07:27 mysql-bin.index -rw-rw ---- 1 mysql mysql 1073741824 jan 6 12:08 ib_logfile1 -rw-r --r-- 1 mysql mysql 183 jan 6 13:23 vm_58_10_centos-slow.log -rw-rw ---- 1 mysql mysql 1073741824 jan 6 13:23 ib_logfile0 -rw-rw ---- 1 mysql mysql 7492941 jan 6 13:23 vm_58_10_centos.err |
(3)报错有一个非常奇怪的点,file 'something is definitely wrong and this may fail.' ,为什么日志文件名会那么奇怪呢?这里需要知道的是,mysql-bin.index记录了binlog相关信息,mysql实例启动时需要读取该文件获取信息;那接下来就检查一下该文件,发现果然有问题,mysql-bin.index后半部分错误写入了错误日志的内容,从而导致实例启动时读取到错误内容(当作binlog日志文件进行处理)报错失败
|
# cat mysql-bin. index ./mysql-bin.007351 ./mysql-bin.007352 ./mysql-bin.007353 ./mysql-bin.007354 ./mysql-bin.007355 ./mysql-bin.007356 ./mysql-bin.007357 ./mysql-bin.007358 ./mysql-bin.007359 ./mysql-bin.007360 ./mysql-bin.007361 ./mysql-bin.007362 ./mysql-bin.007363 ./mysql-bin.007364 ./mysql-bin.007365 ./mysql-bin.007366 ./mysql-bin.007367 ./mysql-bin.007368 23:27:31 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. 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=3145728 max_used_connections=523 max_threads=800 thread_count=522 connection_count=522 it is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 9037821 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 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. |
(4)原因定位后,解决办法就是将mysql-bin.index文件备份后,手动进行修复,随后启动实例成功
|
# ./mysql start starting mysql.... success! checking mysql connection : connection ok! # ps -ef | grep mysqld root 22955 1 0 13:30 pts/5 00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/vm_58_10_centos.pid mysql 23733 22955 24 13:30 pts/5 00:00:05 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/lib/mysql/vm_58_10_centos.err --open-files-limit=20000 --pid-file=/var/lib/mysql/vm_58_10_centos.pid --socket=/var/lib/mysql/mysql.sock --port=3306 root 32075 14929 0 13:30 pts/5 00:00:00 grep mysqld |
总结
至此,问题得到解决,至于为什么错误日志内容会写入到mysql-bin.index,个人怀疑是宕机导致文件错乱(该宿主机上的其他虚拟机有出现文件系统损坏的情况);最后,还是得强调一下,生产系统还是得重视起来,备份、高可用、容灾都是不可或缺的。
以上就是mysql 实例无法启动的问题分析及解决的详细内容,更多关于mysql 实例无法启动的资料请关注开心学习网其它相关文章!
原文链接:https://cloud.tencent.com/developer/inventory/2101/article/1769876
- mysql5.7.20非安装版教程(MySQL5.5 部署的一个问题)
- mysql8使用技巧(一篇学会MySQL 8.0 ROLE管理)
- 将SQL Server数据迁移到MySQL的常见工具
- mysql数据库下载安装步骤(MySQL数据库压缩版本安装与配置详细教程)
- mysql首次登录不上怎么办(Mysql匿名登录无法创建数据库问题解决方案)
- mysql 多表连接查询(MySQL多表查询的具体实例)
- mysql开启审计日志会导致性能下降(MySQL 一则慢日志监控误报的问题分析与解决)
- hive分析后如何录入mysql(hive从mysql导入数据量变多的解决方案)
- mysql整体架构设计(MySQL 整体架构介绍)
- mysql触发器入门(MySQL中触发器和游标的介绍与使用)
- mysql中默认排序教程(基于mysql 默认排序规则的坑)
- 如何查找MySQL中查询慢的SQL语句
- mysql怎么建立主键外键(mysql 实现设置多个主键的操作)
- mysql缓冲池(详解MySQL中的缓冲池buffer pool)
- mysql5.7.36详细安装(CenOS6.7下mysql 8.0.22 安装配置方法图文教程)
- mysql变量技巧(mysql用户变量与set语句示例详解)
- 宋轶除了演过于曼丽,原来还演过一个青楼女子(宋轶除了演过于曼丽)
- 赵丽颖第一部当女主的戏,主角配角个个都是实力演员(赵丽颖第一部当女主的戏)
- 乾隆为何这么喜爱白塔原因是什么(乾隆为何这么喜爱白塔原因是什么)
- 逐渐消失的东北八大怪现象,进步的社会里我们遗失的是什么(逐渐消失的东北八大怪现象)
- 今日大雪,大雪养生这样做,一年都有好气色(大雪养生这样做)
- 粗盐是什么 粗盐的功效与作用(粗盐的功效与作用)
热门推荐
- 云主机和免备案空间(租用国内免备案云服务器空间靠谱吗?)
- pyqt5实例讲解(PyQt5实现简易计算器)
- 如何用python编写抽奖(详解用python写一个抽奖程序)
- python正则表达式该怎么学习(Python面向对象总结及类与正则表达式详解)
- 服务器虚拟化需要哪些技术(认识云服务器的虚拟化实现途径)
- windows10hyper-v怎么关闭(windows10下关闭Hyper-V服的几种方法)
- 虚拟环境python详细教程(python创造虚拟环境方法总结)
- centosdocker镜像安装mysql(linux下利用Docker安装mysql的步骤)
- 接到面试通知后必须做哪些准备
- mysqldecimal类型转换(mysql中decimal数据类型小数位填充问题详解)
排行榜
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9