Mysql问题排查

1、错误日志

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

-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-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.-05-24T10:38:01.080804Z 1 [ERROR] [MY-013183] [InnoDB] Assertion failure: :3289:for_table || ref_table thread 14612InnoDB: We intentionally generate a memory trap.InnoDB: Submit a detailed bug report to .InnoDB: If you get repeated assertion failures or crashes, evenInnoDB: immediately after the mysqld startup, there may beInnoDB: corruption in the InnoDB tablespace. Please refer toInnoDB: /doc/refman/8.0/en/forcing-innodb-recovery.htmlInnoDB: 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 binaryor 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 informationcollection process might fail.key_buffer_size=8388608read_buffer_size=131072max_used_connections=0max_threads=2000thread_count=1connection_count=0It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 796176 K bytes of memoryHope that's ok; if not, decrease some variables in the equation.Thread pointer: 0x2bc0c660010Attempting backtrace. You can use the following information to find outwhere mysqld died. If you see no messages after this, something wentterribly wrong...7ff6eda266e2 mysqld.exe!my_sigabrt_handler()[:372]7ff9bdc2cb7d ucrtbase.dll!raise()7ff9bdc2db81 ucrtbase.dll!abort()7ff6edc81df8 mysqld.exe!ut_dbg_assertion_failed()[:90]7ff6edbc4a9e mysqld.exe!dict_foreign_add_to_cache()[:3289]7ff6edcde262 mysqld.exe!dd_table_load_fk_from_dd()[:3324]7ff6edcddc19 mysqld.exe!dd_table_load_fk()[:3372]7ff6edcd19ef mysqld.exe!dd_open_table_one<dd::Table>()[:4138]7ff6edcd0126 mysqld.exe!dd_open_table<dd::Table>()[:4276]7ff6edcde7a0 mysqld.exe!dd_table_open_on_dd_obj()[:369]7ff6edcdf3c1 mysqld.exe!dd_table_open_on_id_low()[:515]7ff6edcdeea9 mysqld.exe!dd_table_open_on_id()[:634]7ff6edca2f36 mysqld.exe!trx_resurrect_locks()[:710]7ff6edb860c9 mysqld.exe!srv_dict_recover_on_restart()[:2563]7ff6edb6e278 mysqld.exe!innobase_dict_recover()[:3443]7ff6ed9d3586 mysqld.exe!`anonymous namespace'::DDSE_dict_recover()[:142]7ff6ed9d9dc2 mysqld.exe!dd::bootstrap::restart()[:2306]7ff6ed913c24 mysqld.exe!dd::upgrade_57::restart_dictionary()[:969]7ff6ed90f05b mysqld.exe!dd::upgrade_57::do_pre_checks_and_initialize_dd()[:1211]7ff6ecce573f mysqld.exe!handle_bootstrap()[:348]7ff6ede18937 mysqld.exe!pfs_spawn_thread()[:2839]7ff6eda2667c mysqld.exe!win_thread_start()[: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): 1Status: NOT_KILLEDThe manual page at /doc/mysql/en/crashing.html containsinformation 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命令，重新构建一个库。

参考文献

/p/26e0fb67c21b?utm_campaign=maleskine&utm_content=note&utm_medium=seo_notes&utm_source=recommendation

https://www.pc-/xitong/74631.html