错误'未知表引擎'InnoDB''查询。重启mysql后
I have mysql DB on server S1 (mysql version 5.1.41-3ubuntu12.7-log), i have created master-slave for this DB on server S2 (mysql version 5.1.54-1ubuntu4-log).
the DB on S1 was using one data file (ibdata). after dumping the DB to S2 i set innodb_file_per_table=1. this made every table to have its own ibd file. now everything went fine and smoothly.
but after restarting mysql on S2, i faced a problem with getting this error:
Error 'Unknown table engine 'InnoDB'' on query. Default database: MyDB
and when i try to show engines
我在服务器S1上有mysql DB(mysql版本5.1.41-3ubuntu12.7-log),我在服务器S2上创建了这个DB的主从(mysql版本5.1.54-1ubuntu4-log)。 S1上的DB使用一个数据文件(ibdata)。在将数据库转储到S2之后,我设置了innodb_file_per_table = 1。这使得每个表都有自己的ibd文件。现在一切都很顺利。但是在S2上重启mysql之后,我遇到了一个问题:获取此错误:查询时出现错误'未知表引擎'InnoDB''。默认数据库:MyDB,当我尝试显示引擎时
show engines; +------------+---------+----------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +------------+---------+----------------------------------------------------------------+--------------+------+------------+ | MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | NO | NO | NO | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | | CSV | YES | CSV storage engine | NO | NO | NO | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | +------------+---------+----------------------------------------------------------------+--------------+------+------------+
innodb is not listed.
in error log i can see this:
innodb未列出。在错误日志中,我可以看到:
InnoDB: Database physically writes the file full: wait... InnoDB: Cannot initialize created log files because InnoDB: data files are corrupt, or new data files were InnoDB: created when the database was started previous InnoDB: time but the database was not shut down InnoDB: normally after that. 111016 8:24:11 [ERROR] Plugin 'InnoDB' init function returned error. 111016 8:24:11 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. 111016 8:24:11 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=S2-relay-bin' to avoid this problem.
I have tried to delete ib_logfiles but this didn't work as well.
anybody faced such issue before?? any idea is highly appreciated
Thanks
我试图删除ib_logfiles,但这不起作用。有谁在面对这样的问题?任何想法都非常感谢谢谢
5 个解决方案
#1
17
You can delete the InnoDB log files in your mysql data directory called ib_logfile0 and ib_logfile1. Don't delete the InnoDB data file (ibdata1) though.
您可以删除名为ib_logfile0和ib_logfile1的mysql数据目录中的InnoDB日志文件。不要删除InnoDB数据文件(ibdata1)。
After that, InnoDB will try to recover after you restart mysqld.
之后,InnoDB将在您重启mysqld后尝试恢复。
look at main log file:
看看主日志文件:
120413 17:34:47 InnoDB: Initializing buffer pool, size = 64.0M
120413 17:34:47 InnoDB: Completed initialization of buffer pool
120413 17:34:47 InnoDB: Log file .\ib_logfile0 did not exist: new to be created
InnoDB: Setting log file .\ib_logfile0 size to 32 MB
InnoDB: Database physically writes the file full: wait...
120413 17:34:48 InnoDB: Log file .\ib_logfile1 did not exist: new to be created
InnoDB: Setting log file .\ib_logfile1 size to 32 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
120413 17:34:49 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
更多相关文章
- MySQL数据库多表查询
- MySQL数据目录结构
- mysql数据库连接查询
- 使用JDBC处理MySQL大数据
- 关于mysql无法添加中文数据的问题以及解决方案
- MySQL学习笔记_时间,多表更新,数据库元数据
- 如何使用mysql 命令行 查看mysql表大小、数据大小、索引大小
- Mysql 数据分页处理(Node.js的实现)
- MySQL数据库root账户的设置和管理