Analysis and solution of the problem that MySQL instance cannot be started

Analysis and solution of the problem that MySQL instance cannot be started

Preface

A few days ago, a friend contacted me on WeChat and told me that a production database instance failed to start after the machine was restored from a downtime, and that the instance did not have any high availability, disaster recovery, or backup features, which had a huge impact on the business. He hoped that I could help investigate, and I immediately joined the investigation.

Scenario Analysis

(1) First check the error log. The error message is very clear: "Could not open log file". The log file cannot be opened.

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) After seeing the above error, you should of course check whether the my.cnf configuration is correct, the log directory and permissions are correct, but no problems were found.

# 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) There is a very strange point in the error message: file 'something is definitely wrong and this may fail.' Why is the log file name so strange? What you need to know here is that mysql-bin.index records binlog related information. When the MySQL instance is started, you need to read this file to obtain information. Then check the file and find that there is indeed a problem. The second half of mysql-bin.index erroneously writes the content of the error log, which causes the instance to read the error content (treated as a binlog log file) when it is started and report an error failure.

# 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) After locating the cause, the solution is to back up the mysql-bin.index file, manually repair it, and then start the instance successfully.

# ./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

Summarize

At this point, the problem has been solved. As for why the error log content is written to mysql-bin.index, I personally suspect that the file is disordered due to a crash (the file system of other virtual machines on the host machine is damaged). Finally, it must be emphasized that the production system must be taken seriously, and backup, high availability, and disaster recovery are all indispensable.

The above is the detailed analysis and solution of the problem that MySQL instance cannot be started. For more information about MySQL instance cannot be started, please pay attention to other related articles on 123WORDPRESS.COM!

You may also be interested in:
  • Detailed explanation of MySQL startup options and system variables examples
  • Example analysis of MySQL startup and connection methods
  • How to start multiple MySQL instances in CentOS 7.0 (mysql-5.7.21)
  • MYSQL slow query and log example explanation
  • MySQL select results to perform update example tutorial
  • A complete example of mysql querying batch data from one table and inserting it into another table
  • Springboot configures mysql connection example code
  • Detailed explanation of how to gracefully delete a large table in MySQL
  • MySQL trigger usage scenarios and method examples
  • A brief analysis of SQL examples for finding uncommitted transactions in MySQL

<<:  Implementation of CSS3 3D cool cube transformation animation

>>:  Solution to the Docker container cannot be stopped and deleted

Recommend

Javascript tree menu (11 items)

1. dhtmlxTree dHTMLxTree is a feature-rich Tree M...

CSS sample code to achieve circular gradient progress bar effect

Implementation ideas The outermost is a big circl...

Docker enables multiple port mapping commands

as follows: docker run -d -p 5000:23 -p 5001:22 -...

Summary of several APIs or tips in HTML5 that cannot be missed

In previous blog posts, I have been focusing on so...

How does the composite index of MySQL take effect?

Table of contents background Understanding compos...

Detailed explanation of GaussDB for MySQL performance optimization

Table of contents background Inspiration comes fr...

Detailed explanation of efficient MySQL paging

Preface Usually, a "paging" strategy is...

Vue implements bottom query function

This article example shares the specific code of ...

How to set MySQL foreign keys for beginners

Table of contents The role of foreign keys mysql ...