记录一下服务器断电导致mariadb崩溃的解决步骤

环境

系统:centos7
数据库:mariadb 10.2.8
数据库目录: /usr/local/mysql
数据目录: /usr/local/mysql/data
配置文件位置: /etc/mysql/my.cnf 

报错日志

210208 15:49:42 [ERROR] 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.

To report this bug, see https://mariadb.com/kb/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: 10.2.8-MariaDB-log
key_buffer_size=402653184
read_buffer_size=2097152
max_used_connections=0
max_threads=153
thread_count=0
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 1023119 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 = 0x0 thread_stack 0x49000
/usr/local/mysql/bin/mysqld(my_print_stacktrace+0x2e)[0xde62fe]
/usr/local/mysql/bin/mysqld(handle_fatal_signal+0x444)[0x7dbcc4]
/lib64/libpthread.so.0(+0xf5d0)[0x7f3f579935d0]
/lib64/libc.so.6(gsignal+0x37)[0x7f3f5679f207]
/lib64/libc.so.6(abort+0x148)[0x7f3f567a08f8]
/usr/local/mysql/bin/mysqld[0xbb00c9]
/usr/local/mysql/bin/mysqld[0xc6c233]
/usr/local/mysql/bin/mysqld[0xc7679e]
/usr/local/mysql/bin/mysqld[0xc21145]
/usr/local/mysql/bin/mysqld[0xc229fb]
/usr/local/mysql/bin/mysqld[0xc02959]
/usr/local/mysql/bin/mysqld[0xba930f]
/usr/local/mysql/bin/mysqld[0xb8f809]
/usr/local/mysql/bin/mysqld[0xb900e9]
/usr/local/mysql/bin/mysqld[0xb99466]
/usr/local/mysql/bin/mysqld[0xb952bb]
/usr/local/mysql/bin/mysqld[0xb65707]
/usr/local/mysql/bin/mysqld[0xa3fd83]
/usr/local/mysql/bin/mysqld(_Z24ha_initialize_handlertonP13st_plugin_int+0x5e)[0x7df05e]
/usr/local/mysql/bin/mysqld[0x5feff3]
/usr/local/mysql/bin/mysqld(_Z11plugin_initPiPPci+0xb90)[0x600050]
/usr/local/mysql/bin/mysqld[0x54b0ad]
/usr/local/mysql/bin/mysqld(_Z11mysqld_mainiPPc+0x526)[0x54bf56]
/lib64/libc.so.6(__libc_start_main+0xf5)[0x7f3f5678b3d5]
/usr/local/mysql/bin/mysqld(__gxx_personality_v0+0x3c9)[0x5414e9]
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.
210208 15:49:42 mysqld_safe mysqld from pid file /usr/local/mysql/data/localhost.localdomain.pid ended
210208 15:58:13 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
2021-02-08 15:58:13 140388166797120 [Warning] ‘THREAD_CONCURRENCY’ is deprecated and will be removed in a future release.
2021-02-08 15:58:13 140388166797120 [Note] /usr/local/mysql/bin/mysqld (mysqld 10.2.8-MariaDB-log) starting as process 33099 …
2021-02-08 15:58:13 140388166797120 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2021-02-08 15:58:13 140388166797120 [Note] InnoDB: Uses event mutexes
2021-02-08 15:58:13 140388166797120 [Note] InnoDB: Compressed tables use zlib 1.2.3
2021-02-08 15:58:13 140388166797120 [Note] InnoDB: Using Linux native AIO
2021-02-08 15:58:13 140388166797120 [Note] InnoDB: Number of pools: 1
2021-02-08 15:58:13 140388166797120 [Note] InnoDB: Using SSE2 crc32 instructions
2021-02-08 15:58:13 140388166797120 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2021-02-08 15:58:13 140388166797120 [Note] InnoDB: Completed initialization of buffer pool
2021-02-08 15:58:13 140387143149312 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2021-02-08 15:58:13 140388166797120 [Note] InnoDB: Highest supported file format is Barracuda.
2021-02-08 15:58:13 140388166797120 [Note] InnoDB: Starting crash recovery from checkpoint LSN=4320965066
2021-02-08 15:58:14 140388166797120 [ERROR] InnoDB: Page [page id: space=0, page number=5] log sequence number 4371427758 is in the future! Current system log sequence number 4360536238.
2021-02-08 15:58:14 140388166797120 [ERROR] InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html for information about forcing recovery.
2021-02-08 15:58:14 140388166797120 [ERROR] InnoDB: Page [page id: space=0, page number=6] log sequence number 4371022468 is in the future! Current system log sequence number 4360536238.
2021-02-08 15:58:14 140388166797120 [ERROR] InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html for information about forcing recovery.
2021-02-08 15:58:14 140388166797120 [ERROR] InnoDB: Page [page id: space=0, page number=301] log sequence number 4382997464 is in the future! Current system log sequence number 4360536238.
2021-02-08 15:58:14 140388166797120 [ERROR] InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html for information about forcing recovery.
2021-02-08 15:58:14 140388166797120 [ERROR] InnoDB: Page [page id: space=0, page number=392] log sequence number 4371022468 is in the future! Current system log sequence number 4360536238.
2021-02-08 15:58:14 140388166797120 [ERROR] InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html for information about forcing recovery.
2021-02-08 15:58:14 140388166797120 [ERROR] InnoDB: Page [page id: space=0, page number=45] log sequence number 4371022507 is in the future! Current system log sequence number 4360536238.
2021-02-08 15:58:14 140388166797120 [ERROR] InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html for information about forcing recovery.
2021-02-08 15:58:14 140388166797120 [ERROR] InnoDB: Page [page id: space=0, page number=306] log sequence number 4408654901 is in the future! Current system log sequence number 4360536238.
2021-02-08 15:58:14 140388166797120 [ERROR] InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html for information about forcing recovery.
2021-02-08 15:58:14 140388166797120 [ERROR] InnoDB: Page [page id: space=0, page number=673] log sequence number 4376348518 is in the future! Current system log sequence number 4360536238.
2021-02-08 15:58:14 140388166797120 [ERROR] InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html for information about forcing recovery.
2021-02-08 15:58:14 140388166797120 [ERROR] [FATAL] InnoDB: Trying to read page number 1919247458 in space 0, space name innodb_system, which is outside the tablespace bounds. Byte offset 0, len 16384Please check that the configuration matches the InnoDB system tablespace location (ibdata files)

恢复数据

数据恢复的办法有很多，这里使用mysqldump

编辑数据库配置文件

在配置文件[mysqld]下加入参数 innodb_force_recovery = 1 ，其中后面的值设置为1、如果1不能恢复，再逐步增加为2/3/4等。直到能启动mysql为止！！！
注意：最高值为6，但当参数值大于3的时候。会对数据文件造成永久性的破坏。

[mysqld]
innodb_force_recovery = 1

启动数据库

systemctl start mariadb

登录数据库

mysql -uroot -p123456

数据备份

此时进入的恢复模式，数据库是只读的，所以下面需要把数据库备份出来，然后清除之前损坏的数据，利用备份数据恢复
进入数据库安装目录的bin目录下 执行:
./mysqldump -uroot -p123456 --all-databases > backup.sql

执行完了 去掉 innodb_force_recovery = 1 参数

清除损坏的数据

清除之前需要把服务停止：
systemctl stop mariadb 

备份原data目录以防万一：
cp -r /usr/local/mysql/data/ usr/local/mysql/data_bak/
rm -rf /usr/local/mysql/data/*

数据恢复

上个步骤已经清空了data目录,所以此时数据库是启动不了的，需要进行初始化
进入 安装目录下进行初始化
cd /usr/local/mysql
./scripts/mysql_install_db --user=mysql&

下面就可以利用mysqldump出来的数据进行恢复了，但因为刚刚进行了初始化，之前的密码已经没用了，需要在配置文件加入 skip-grant-tables
vi /etc/mysql/my.cnf
![在这里插入图片描述](https://img-blog.csdnimg.cn/20210222171007608.png)

再次登录数据库 执行：
source /usr/local/mysql/bin/backup.sql

后续操作

1.修改root密码
2.新增之前的用户并配置权限
3.去掉 skip-grant-tables 参数