【电脑自动关机】开机后Mysql再也启动不起来了。问题解决

1、错误日志

在mysql 的data目录下找到 .err错误日志。

2022-05-24T10:37:39.547477Z 0 [System] [MY-010116] [Server] D:\SoftwareImplement\mysql-8.0.13\bin\mysqld (mysqld 8.0.13) starting as process 25896
2022-05-24T10:37:39.549957Z 0 [Warning] [MY-013242] [Server] --character-set-server: 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
2022-05-24T10:38:01.080804Z 1 [ERROR] [MY-013183] [InnoDB] Assertion failure: dict0dict.cc:3289:for_table || ref_table thread 14612
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/8.0/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
10:38:01 UTC - mysqld got exception 0x80000003 ;
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.
Attempting to collect some information that could help diagnose the problem.
As this is a crash and something is definitely wrong, the information
collection process might fail.

key_buffer_size=8388608
read_buffer_size=131072
max_used_connections=0
max_threads=2000
thread_count=1
connection_count=0
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 796176 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x2bc0c660010
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...
7ff6eda266e2    mysqld.exe!my_sigabrt_handler()[my_thr_init.cc:372]
7ff9bdc2cb7d    ucrtbase.dll!raise()
7ff9bdc2db81    ucrtbase.dll!abort()
7ff6edc81df8    mysqld.exe!ut_dbg_assertion_failed()[ut0dbg.cc:90]
7ff6edbc4a9e    mysqld.exe!dict_foreign_add_to_cache()[dict0dict.cc:3289]
7ff6edcde262    mysqld.exe!dd_table_load_fk_from_dd()[dict0dd.cc:3324]
7ff6edcddc19    mysqld.exe!dd_table_load_fk()[dict0dd.cc:3372]
7ff6edcd19ef    mysqld.exe!dd_open_table_one<dd::Table>()[dict0dd.cc:4138]
7ff6edcd0126    mysqld.exe!dd_open_table<dd::Table>()[dict0dd.cc:4276]
7ff6edcde7a0    mysqld.exe!dd_table_open_on_dd_obj()[dict0dd.cc:369]
7ff6edcdf3c1    mysqld.exe!dd_table_open_on_id_low()[dict0dd.cc:515]
7ff6edcdeea9    mysqld.exe!dd_table_open_on_id()[dict0dd.cc:634]
7ff6edca2f36    mysqld.exe!trx_resurrect_locks()[trx0trx.cc:710]
7ff6edb860c9    mysqld.exe!srv_dict_recover_on_restart()[srv0start.cc:2563]
7ff6edb6e278    mysqld.exe!innobase_dict_recover()[ha_innodb.cc:3443]
7ff6ed9d3586    mysqld.exe!`anonymous namespace'::DDSE_dict_recover()[bootstrapper.cc:142]
7ff6ed9d9dc2    mysqld.exe!dd::bootstrap::restart()[bootstrapper.cc:2306]
7ff6ed913c24    mysqld.exe!dd::upgrade_57::restart_dictionary()[upgrade.cc:969]
7ff6ed90f05b    mysqld.exe!dd::upgrade_57::do_pre_checks_and_initialize_dd()[upgrade.cc:1211]
7ff6ecce573f    mysqld.exe!handle_bootstrap()[bootstrap.cc:348]
7ff6ede18937    mysqld.exe!pfs_spawn_thread()[pfs.cc:2839]
7ff6eda2667c    mysqld.exe!win_thread_start()[my_thread.cc:52]
7ff9bdbe0e82    ucrtbase.dll!_beginthreadex()
7ff9bf427bd4    KERNEL32.DLL!BaseThreadInitThunk()
7ff9c070ce51    ntdll.dll!RtlUserThreadStart()

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (0): 
Connection ID (thread ID): 1
Status: NOT_KILLED

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.

连接Mysql错误

Can't connect to MySQL server on 'localhost' (10061)'

在这里插入图片描述

问题解决

在CSDN上搜到的是让把data目录清空,这是解决方案吗?我把data清空了,我历史数据怎么办?
还有的mysql的重新初始化命令mysqld --initialize,都不可行。
搜来搜去,总算是找到一个解决方案。
搜索关键字

ucrtbase.dll!_beginthreadex()
KERNEL32.DLL!BaseThreadInitThunk()
解决方法首选是修改 innodb_force_recovery 的值,这个值的说明如下:

innodb_force_recovery 会影响整个InnoDB存储引擎的恢复状况。默认为0,表示当需要恢复时执行所有的

innodb_force_recovery可以设置为1-6,大的数字包含前面所有数字的影响。当设置参数值大于0后,可以对表进行

select,create,drop操作,但insert,update或者delete这类操作是不允许的。

值为1:(SRV_FORCE_IGNORE_CORRUPT):忽略检查到的corrupt页。
值为2:(SRV_FORCE_NO_BACKGROUND):阻止主线程的运行,如主线程需要执行full purge操作,会导致crash。
值为3:(SRV_FORCE_NO_TRX_UNDO):不执行事务回滚操作。
值为4:(SRV_FORCE_NO_IBUF_MERGE):不执行插入缓冲的合并操作。
值为5:(SRV_FORCE_NO_UNDO_LOG_SCAN):不查看重做日志,InnoDB存储引擎会将未提交的事务视为已提交。
值为6:(SRV_FORCE_NO_LOG_REDO):不执行前滚的操作。

修改my.ini文件
在[mysqld]下面加上一行

innodb_force_recovery = 1

然后保存my.ini,重启mysql。
如果设为1未成功启动,那就改变值为2……6,
我的是到5恢复启动的。建议从1开始试,未成功再变成2、3、4…我理解的是,数字越大,恢复的数据就离最新版本越远。
最后,记得把my.ini里的 innodb_force_recovery 值改回0,重启mysql。
最后再追加一些内容:当innodb_force_recovery = 5时,数据库虽然启动了,但是即使再把innodb_force_recovery = 0时,仍然启动不了。此时,只能把当前库废弃掉。把innodb_force_recovery =5,启动后,把数据做备份。但是备份也会丢失一些数据。这些数据库可能造成了数据库的损坏。
备份我们常用mysql 的原生命令:

// 备份全部数据库的全部表和结构
mysqldump -uroot -p123456 -A > /data/mysqlDump/mydb.sql
// 备份全部数据库的结构(加 -d 参数)
mysqldump -uroot -p123456 -A -d > /data/mysqlDump/mydb.sql
//备份全部数据库的数据(加 -t 参数)
mysqldump -uroot -p123456 -A -t > /data/mysqlDump/mydb.sql
// 备份单个数据库的数据和结构(,数据库名mydb)
mysqldump -uroot-p123456 mydb > /data/mysqlDump/mydb.sql
// 备份单个数据库的结构
mysqldump -uroot -p123456 mydb -d > /data/mysqlDump/mydb.sql
// 备份单个数据库的数据
mysqldump -uroot -p123456 mydb -t > /data/mysqlDump/mydb.sql
// 备份多个表的数据和结构(数据,结构的单独备份方法与上同)
mysqldump -uroot -p123456 mydb t1 t2 > /data/mysqlDump/mydb.sql
// 一次备份多个数据库
mysqldump -uroot -p123456 --databases db1 db2 > /data/mysqlDump/mydb.sql

备份完成后,重新形成构建新的库,使用navicat执行导入SQL命令,重新构建一个库。

参考文献

https://www.jianshu.com/p/26e0fb67c21b?utm_campaign=maleskine&utm_content=note&utm_medium=seo_notes&utm_source=recommendation
https://www.pc-daily.com/xitong/74631.html

  • 1
    点赞
  • 6
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值