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

51次阅读

共计 5750 个字符，预计需要花费 15 分钟才能阅读完成。

操作之前首先要做好重新安装 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://blog.csdn.net/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

复原后对数据进行查看。

正文完
php
发表至： php
2020-11-18
 0
关于php:ModStartBlog-v700-网站简单统计支持博客分享
关于php:PHP数据结构栈和队列的应用
关于php:长期有耐心ModStart开源一周年
关于php:使用softaculous创建WordPress临时站点