Analysis and solution of the problem that MySQL instance cannot be started
PrefaceA 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 SummarizeAt 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:
|
<<: Implementation of CSS3 3D cool cube transformation animation
>>: Solution to the Docker container cannot be stopped and deleted
Recommend
Detailed explanation of Vite's new experience
What is Vite? (It’s a new toy on the front end) V...
Windows uses VMware to create a Linux virtual machine and install the CentOS7.2 operating system
Table of contents 1. Follow the wizard to create ...
Index Skip Scan in MySQL 8.0
Preface MySQL 8.0.13 began to support index skip ...
How to reference external CSS files and iconfont in WeChat applet wxss
cause The way to import external files into a min...
Design Theory: Ten Tips for Content Presentation
<br /> Focusing on the three aspects of text...
Detailed explanation of the relationship between React and Redux
Table of contents 1. The relationship between red...
How to upgrade https under Nginx
Purchase Certificate You can purchase it from Ali...
How to use a game controller in CocosCreator
Table of contents 1. Scene layout 2. Add a handle...
Solve the problem that Docker pulls MySQL image too slowly
After half an hour of trying to pull the MySQL im...
Comparative Analysis of IN and Exists in MySQL Statements
Background Recently, when writing SQL statements,...
Understanding and example code of Vue default slot
Table of contents What is a slot Understanding of...
Solve the problem of docker images disappearing
1. Mirror images disappear in 50 and 93 [root@h50...
Detailed explanation of publicPath usage in Webpack
Table of contents output output.path output.publi...
CSS3 uses animation attributes to achieve cool effects (recommended)
animation-name animation name, can have multiple ...
Introduction to Javascript DOM, nodes and element acquisition
Table of contents DOM node Element node: Text nod...