windows 启动 MySQL服务bat

转载

晨曦微露s 2024-10-22 07:37:13

文章标签 MySQL 错误日志 Server 文章分类 MySQL 数据库

Windows无法启动MySQL服务(位于本地计算机上) - 错误1067:进程意外终止

windows 启动 MySQL服务bat_MySQL

在Mysql数据文件中有后缀名为err的文件记录错误日志路径：C:\ProgramData\MySQL\MySQL Server 5.5\data

windows 启动 MySQL服务bat_错误日志_02

报错详情

201023 15:24:36 [Note] Plugin 'FEDERATED' is disabled.
201023 15:24:36 InnoDB: The InnoDB memory heap is disabled
201023 15:24:36 InnoDB: Mutexes and rw_locks use Windows interlocked functions
201023 15:24:36 InnoDB: Compressed tables use zlib 1.2.3
201023 15:24:36  InnoDB: Error: unable to create temporary file; errno: 2
201023 15:24:36 [ERROR] Plugin 'InnoDB' init function returned error.
201023 15:24:36 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
201023 15:24:36 [ERROR] Unknown/unsupported storage engine: InnoDB
201023 15:24:36 [ERROR] Aborting

201023 15:24:36 [Note] MySQL: Shutdown complete

尝试分析中。。。。。。

发现关键点：InnoDB: The InnoDB memory heap is disabled

回想当时修改了MySQL的初始化文件my.ini中默认引擎为INNODB

注释INNODB 启用MYISAM

windows 启动 MySQL服务bat_Server_03

服务启动成功

windows 启动 MySQL服务bat_Server_04

错误日志

201023 16:01:40 [Note] Plugin 'FEDERATED' is disabled.
201023 16:01:40 InnoDB: The InnoDB memory heap is disabled
201023 16:01:40 InnoDB: Mutexes and rw_locks use Windows interlocked functions
201023 16:01:40 InnoDB: Compressed tables use zlib 1.2.3
201023 16:01:40  InnoDB: Error: unable to create temporary file; errno: 2
201023 16:01:40 [ERROR] Plugin 'InnoDB' init function returned error.
201023 16:01:40 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
201023 16:01:40 [Note] Event Scheduler: Loaded 0 events
201023 16:01:40 [Note] MySQL: ready for connections.
Version: '5.5.13'  socket: ''  port: 3306  MySQL Community Server (GPL)

Version: ‘5.5.13’ socket: ‘’ port: 3306 MySQL Community Server (GPL)

CMD命令查看使用的数据库引擎 show variables like ‘%storage_engine%’;

windows 启动 MySQL服务bat_Server_05

注释所有引擎默认配置

windows 启动 MySQL服务bat_MySQL_06

错误日志此时回归至开始的情况

201023 15:57:53 [Note] Plugin 'FEDERATED' is disabled.
201023 15:57:53 InnoDB: The InnoDB memory heap is disabled
201023 15:57:53 InnoDB: Mutexes and rw_locks use Windows interlocked functions
201023 15:57:53 InnoDB: Compressed tables use zlib 1.2.3
201023 15:57:53  InnoDB: Error: unable to create temporary file; errno: 2
201023 15:57:53 [ERROR] Plugin 'InnoDB' init function returned error.
201023 15:57:53 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
201023 15:57:53 [ERROR] Unknown/unsupported storage engine: InnoDB
201023 15:57:53 [ERROR] Aborting

201023 15:57:53 [Note] MySQL: Shutdown complete

默认INNODB引擎

注意一开始未注释tmpdir 会因1067无法启动服务

windows 启动 MySQL服务bat_错误日志_07

注释tmpdir临时文件路径

windows 启动 MySQL服务bat_MySQL_08

# The default storage engine that will be used when create new tables when
# 创建新表时将使用的默认存储引擎
# default-storage-engine=MYISAM
 default-storage-engine=INNODB
 
#错误日志:InnoDB: The InnoDB memory heap is disabled 	
#使用了操作系统的内存分配器,禁用了InnoDB的内置内存分配器所至 
#设置为0：表示Innodb使用自带的内存分配程序 设置为1：表示InnoDB使用操作系统的内存分配程序 
innodb_use_sys_malloc=1

终于解决了困扰许久的疑虑，但是人生总是步入大喜之后必有大悲的定律

一波刚平一波又起心理一万个。。。马赛克。。。飘过

问题一：Navicat正常连接并可以查看相关表，查询表详情失败

1 - Can’t create/write to file 'C:\Program Files (x86)\MySQLMySQL Server 5.5\Temp#sql3d50 3. a.MYI (Errcode: 2)

windows 启动 MySQL服务bat_MySQL_09

无法读写自己配置的临时文件故注释后临时文件路径却引发问题三

windows 启动 MySQL服务bat_错误日志_10

问题二：无法访问以前的使用INNODB创建的表数据

ERROR 1286 (42000): Unknown storage engine 'InnoDB’

windows 启动 MySQL服务bat_错误日志_11

问题三： show tables 显示表名，但是查询的时候却提示此表不存在

windows 启动 MySQL服务bat_MySQL_12

windows 启动 MySQL服务bat_Server_13

错误日志

201023 18:22:48 [ERROR] Cannot find or open table snacknet/admininfo from
the internal data dictionary of InnoDB though the .frm file for the
table exists. Maybe you have deleted and recreated InnoDB data
files but have forgotten to delete the corresponding .frm files
of InnoDB tables, or you have moved .frm files to another database?
or, the table contains indexes that this version of the engine
doesn't support.
See http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting.html
how you can resolve the problem.

原因：
1、无法从中找到或打开表InnoDB的内部数据字典表存在。
2、已经删除并重新创建了InnoDB数据文件，
3、忘记删除相应的.frm文件或者你已经把.frm文件移到另一个数据库了？

科普MySQL文件组织架构

windows 启动 MySQL服务bat_Server_14