mysql停电_MySQL断电后报错处理

文章目录

前言

后记

前言

问题保留，如果有大佬会可以留言教我一下！

因为做的cacti没有做完，回家的时候就将电脑从休眠状态起不来。于是我就断电重启。自此mysql就不正常了，一直出现报错。因为之前部署zabbix出现过同样的问题，当时直接重装了系统。这次希望能用心解决一下，积累自己的排错经验。

1.查看端口状态，看是否开启：

[root@server1 mariadb]# netstat -tnlp

tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 45129/mysqld

2.服务状态running：

[root@server1 mariadb]# systemctl status mariadb

● mariadb.service - MariaDB database server

Loaded: loaded (/usr/lib/systemd/system/mariadb.service; disabled; vendor preset: disabled)

Active: active (running) since Thu 2020-07-09 09:39:24 CST; 10min ago

Process: 32031 ExecStartPost=/usr/libexec/mariadb-wait-ready $MAINPID (code=exited, status=0/SUCCESS)

Process: 31999 ExecStartPre=/usr/libexec/mariadb-prepare-db-dir %n (code=exited, status=0/SUCCESS)

Main PID: 32030 (mysqld_safe)

Tasks: 32

CGroup: /system.slice/mariadb.service

├─32030 /bin/sh /usr/bin/mysqld_safe --basedir=/usr

├─46481 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql ...

└─46641 addr2line -C -f -e /usr/libexec/mysqld

Jul 09 09:39:21 server1 systemd[1]: Starting MariaDB database server...

Jul 09 09:39:21 server1 mariadb-prepare-db-dir[31999]: Database MariaDB is prob...

Jul 09 09:39:22 server1 mysqld_safe[32030]: 200709 09:39:22 mysqld_safe Loggin....

Jul 09 09:39:22 server1 mysqld_safe[32030]: 200709 09:39:22 mysqld_safe Starti...l

Jul 09 09:39:24 server1 systemd[1]: Started MariaDB database server.

Hint: Some lines were ellipsized, use -l to show in full.

看起来并没有异常。

3.用root用户登录mysql

[root@server1 mariadb]# mysql -uroot -p

Enter password:

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connection id is 1

Server version: 5.5.60-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

可以正常登录

4.show databases；！！！！出现报错

MariaDB [(none)]> show databases;

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id: 2

Current database: *** NONE ***

+--------------------+

| Database |

+--------------------+

| information_schema |

| cacti |

| mysql |

| performance_schema |

| test |

| zabbix |

+--------------------+

6 rows in set (0.01 sec)

分析：

mysql没了，我们之前的端口检查和服务检查都看到我们的MySQL了，这里怎么没了？

我准备先查系统日志，再查mysql日志。

系统日志：

[root@server1 mariadb]# date

Thu Jul 9 09:58:17 CST 2020

[root@server1 mariadb]# journalctl -xe

Jul 09 09:57:34 server1 kernel: traps: mysqld[60957] general protection ip:55c1a613ffc1 sp:7f904201d5c0 error:0 in mysqld[55c1a5a05000+cb2000]

Jul 09 09:57:34 server1 abrt-hook-ccpp[60980]: Process 60129 (mysqld) of user 27 killed by SIGSEGV - dumping core

Jul 09 09:57:37 server1 abrt-hook-ccpp[60980]: Failed to create core_backtrace: waitpid failed: No child processes

Jul 09 09:57:37 server1 abrtd[7224]: Size of '/var/spool/abrt' >= 1000 MB (MaxCrashReportsSize), deleting new directory 'ccpp-2020-07-09-09:57:34

Jul 09 09:57:48 server1 kernel: traps: mysqld[61511] general protection ip:5632be90ffc1 sp:7f78080e95c0 error:0 in mysqld[5632be1d5000+cb2000]

Jul 09 09:57:48 server1 abrt-hook-ccpp[61520]: Process 61068 (mysqld) of user 27 killed by SIGSEGV - ignoring (repeated crash)

Jul 09 09:57:58 server1 kernel: traps: mysqld[61852] general protection ip:55bb0c387fc1 sp:7f3c4426e5b0 error:0 in mysqld[55bb0bc4d000+cb2000]

Jul 09 09:57:59 server1 abrt-hook-ccpp[61861]: Process 61544 (mysqld) of user 27 killed by SIGSEGV - dumping core

Jul 09 09:58:01 server1 abrt-hook-ccpp[61861]: Failed to create core_backtrace: waitpid failed: No child processes

Jul 09 09:58:01 server1 abrtd[7224]: Size of '/var/spool/abrt' >= 1000 MB (MaxCrashReportsSize), deleting new directory 'ccpp-2020-07-09-09:57:59

lines 1469-1490/1490 (END)

mysql的日志，有error的部分为：

InnoDB: End of page dump

200709 10:06:22 InnoDB: Page checksum 1575996416 (32bit_calc: 2253597184), prior-to-4.0.14-form checksum 1371122432

InnoDB: stored checksum 0, prior-to-4.0.14-form stored checksum 0

InnoDB: Page lsn 0 0, low 4 bytes of lsn at page end 0

InnoDB: Page number (if stored to page already) 0,

InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 0

InnoDB: Page may be a freshly allocated page

200709 10:06:22 [ERROR] mysqld got signal 11 ;

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.

To report this bug, see http://kb.askmonty.org/en/reporting-bugs

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.

Server version: 5.5.60-MariaDB

key_buffer_size=134217728

read_buffer_size=131072

max_used_connections=12

max_threads=153

thread_count=12

It is possible that mysqld could use up to

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

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

Thread pointer: 0x563221b9cc30

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

InnoDB: Page directory corruption: infimum not pointed to

200709 10:06:22 InnoDB: Page dump in ascii and hex (16384 bytes):

到现在，我终于看到了一条error：

200709 10:06:22 [ERROR] mysqld got signal 11 ;

详细查看一下：

[root@server1 mariadb]# perror 11

OS error code 11: Resource temporarily unavailable

说是资源暂时不可用。

现在journal中的红色报错也没解决。

再使用用户身份登录时：

断电前创建:

unknown [(none)]> use zabbix;

No connection. Trying to reconnect...

Connection id: 1

Current database: *** NONE ***

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

断电后创建:

MariaDB [(none)]> use cacti;

No connection. Trying to reconnect...

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111)

ERROR: Can't connect to the server

两个报错是不一样的。查日志：

InnoDB: End of page dump

200709 10:19:41 InnoDB: Page checksum 1575996416 (32bit_calc: 2253597184), prior-to-4.0.14-form checksum 1371122432

InnoDB: stored checksum 0, prior-to-4.0.14-form stored checksum 0

InnoDB: Page lsn 0 0, low 4 bytes of lsn at page end 0

InnoDB: Page number (if stored to page already) 0,

InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 0

InnoDB: Page may be a freshly allocated page

200709 10:19:41 [ERROR] mysqld got signal 11 ;

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.

To report this bug, see http://kb.askmonty.org/en/reporting-bugs

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.

Server version: 5.5.60-MariaDB

key_buffer_size=134217728

read_buffer_size=131072

max_used_connections=14

max_threads=153

thread_count=14

It is possible that mysqld could use up to

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

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

Thread pointer: 0x55902ed56b10

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 = 0x7f639c9d9d80 thread_stack 0x48000

其实问题都一样，日志报错也一样。

现在自己问题已经出来了，我先做个记录。暂停一下，去搜索有没有人遇到和我一样的问题。

嘻嘻，发现这种问题大家遇到的还挺多的，开心！

1小时后，发现经过修改后启都启不来了，因为还要照顾进度，就不杠了，重装，真香^ - ^

参考博客：

后记