ubuntu因为意外关机导致-mysql启动不了

操作之前首先要做好重新安装mysql的筹备
步骤：在/etc/mysql/mysql.conf.d下的mysqld.cnf中配置
[mysqld]
innodb_force_recovery = 1
其中前面的值设置为1、如果1不能启动胜利，再逐渐减少为2/3/4等。直到能启动mysql为止
个别状况下到3为止应该是能够启动的，如果启动了先把数据库备份，最好从新装一下mysql，因为尽管启动了，然而当你第一次拜访我的项目当前，第二次再拜访就会报数据库错，当然你也能够依照网上的办法试试，办法在下方最初，反正我没胜利。
这里讲一下卸载数据库
首先在终端中查看MySQL的依赖项：dpkg --list|grep mysql
而后执行

sudo apt purge mysql-*
sudo rm -rf /etc/mysql/ /var/lib/mysql
sudo apt autoremove
sudo apt autoclean

在执行命令dpkg --list|grep mysql查看mysql下依赖
留下php-mysql就行了

参考连贯：记一次服务器宕机后数据库复原的过程
Ubuntu18.04彻底删除MySQL数据库
Ubuntu20.04上装置MySQL8.0

如果以上链接内容生效，就间接看上面把，我都给复制过去了

——————————————————这里我把参考链接的内容复制过去了
第一种卸载mysql

• 首先在终端中查看MySQL的依赖项：dpkg --list|grep mysql

卸载： sudo apt-get remove mysql-common

卸载：sudo apt-get autoremove --purge mysql-server-5.7

革除残留数据：dpkg -l|grep ^rc|awk '{print$2}'|sudo xargs dpkg -P

• 再次查看MySQL的残余依赖项：dpkg --list|grep mysql

持续删除残余依赖项，如：sudo apt-get autoremove --purge mysql-apt-config

至此曾经没有了MySQL的依赖项，彻底删除，Good Luck
链接：https://www.gaodaima.com/iehadoo…

第二种办法就是我写的那样

mysql启动不了办法：

景象

景象很简略，数据库服务器被宕机，当然是在没有停数据库服务的状况下。

机器重启后，试图重启MySQL服务，无果，查看谬误日志：

170920  0:30:17  InnoDB: Assertion failure in thread 140107687212800 in file /export/home/pb2/build/sb_0-2629600-1291399482.5/mysql-5.5.10/storage/innobase/include/fut0lst.ic line 83
InnoDB: Failing assertion: addr.page == FIL_NULL || addr.boffset >= FIL_PAGE_DATA
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.1/en/forcing-recovery.html
InnoDB: about forcing recovery.
170920  0:30:17 - 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=262144
max_used_connections=0
max_threads=500
thread_count=0
connection_count=0
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 406067 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd: 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 = (nil) thread_stack 0x40000
/usr/local/mysql/bin/mysqld(my_print_stacktrace+0x39)[0x916839]
/usr/local/mysql/bin/mysqld(handle_segfault+0x359)[0x4fc0d9]
/lib64/libpthread.so.0(+0xf4a0)[0x7f6d5ca9f4a0]
/lib64/libc.so.6(gsignal+0x35)[0x7f6d5be4a885]
/lib64/libc.so.6(abort+0x175)[0x7f6d5be4c065]
/usr/local/mysql/bin/mysqld[0x7d5601]
/usr/local/mysql/bin/mysqld[0x7ca012]
/usr/local/mysql/bin/mysqld[0x7ca357]
/usr/local/mysql/bin/mysqld[0x7cce1a]
/usr/local/mysql/bin/mysqld[0x7b89e8]
/usr/local/mysql/bin/mysqld[0x78d97d]
/usr/local/mysql/bin/mysqld(_Z24ha_initialize_handlertonP13st_plugin_int+0x48)[0x6683a8]
/usr/local/mysql/bin/mysqld[0x57ddba]
/usr/local/mysql/bin/mysqld(_Z11plugin_initPiPPci+0xb5d)[0x581cbd]
/usr/local/mysql/bin/mysqld[0x50212c]
/usr/local/mysql/bin/mysqld(_Z11mysqld_mainiPPc+0x3c2)[0x504742]
/lib64/libc.so.6(__libc_start_main+0xfd)[0x7f6d5be36cdd]
/usr/local/mysql/bin/mysqld[0x4fa3fa]
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.
170920 00:30:17 mysqld_safe mysqld from pid file /usr/local/mysql/data/localhost.localdomain.pid ended
170920 01:04:55 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
170920  1:04:55 [Warning] Ignoring user change to 'ser=mysql' because the user was set to 'mysql' earlier on the command line

解决过程

刚开始的重点放在了这段日志上：

It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 406067 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

认为是MySQL的一些参数设置有问题，联合Google后果，对/etc/my.cnf进行了批改，仍无果。问题解决之后想来，因为之前MySQL是运行失常的，因而配置个别不会有问题，过后也是“病急乱投医”了。

1. Forcing InnoDB Recovery

设置恢复模式启动mysql，在 /etc/my.cnf中增加如下配置：

[mysqld]
innodb_force_recovery = 1

其中前面的值设置为1、如果1不能启动胜利，再逐渐减少为2/3/4等。直到能启动mysql为止！！！

Forcing InnoDB Recovery提供了6个等级的修复模式，须要留神的是值大于3的时候，会对数据文件造成永恒的毁坏，不可复原。六个等级的介绍摘抄如下：

1. (SRV_FORCE_IGNORE_CORRUPT)

Lets the server run even if it detects a corrupt page. Tries to make SELECT * FROM tbl_name jump over corrupt index records and pages, which helps in dumping tables.

1. (SRV_FORCE_NO_BACKGROUND)

Prevents the master thread and any purge threads from running. If a crash would occur during the purge operation, this recovery value prevents it.

1. (SRV_FORCE_NO_TRX_UNDO)

Does not run transaction rollbacks after crash recovery.

1. (SRV_FORCE_NO_IBUF_MERGE)

Prevents insert buffer merge operations. If they would cause a crash, does not do them. Does not calculate table statistics. This value can permanently corrupt data files. After using this value, be prepared to drop and recreate all secondary indexes.

1. (SRV_FORCE_NO_UNDO_LOG_SCAN)

Does not look at undo logs when starting the database: InnoDB treats even incomplete transactions as committed. This value can permanently corrupt data files.

1. (SRV_FORCE_NO_LOG_REDO)

Does not do the redo log roll-forward in connection with recovery. This value can permanently corrupt data files. Leaves database pages in an obsolete state, which in turn may introduce more corruption into B-trees and other database structures.

恢复模式下启动MySQL

/usr/local/mysql/bin/mysqld_safe -user=mysql&

重启胜利后，测试数据库是否能够失常连贯：mysql -uroot -p123456

数据备份

恢复模式数据库是只读的，当然和复原级别相干。
当初须要做的是将数据库数据备份，而后革除之前的谬误数据，最初再从备份数据中进行复原。

mysqldump -uroot -p123456 --all-databases  > all_mysql_backup.sql

原数据清理或备份

清理数据前须要先将数据库服务进行。
将数据库的data目录进行备份，相当于复原到数据库刚装置实现时的状态。

mkdir data-bak
cd data
mv * ../data-bak/

数据恢复

数据库初始化

因为所有的数据都已删除掉，因而须要进行MySQL的初始化。

cd /usr/local/mysql                                         
./scripts/mysql_install_db --user=mysql&

备份数据恢复

登录MySQL：

mysql -u root -p123456

登录后，在数据库中执行下列语句，即可复原数据：

source /app/all_mysql_backup.sql

复原后对数据进行查看。

喜欢 (0)赏

[搞代码]

分享 (0)