MySQL数据库引擎：MyISAM用于information_schema，但InnoDB用于其他数据库

I am currently using InnoDB for all my databases in MySQL, however I just noticed that my information_schema database uses MyISAM for tables that are not MEMORY.

我目前正在为MySQL中的所有数据库使用InnoDB,但是我注意到我的information_schema数据库使用MyISAM来处理非MEMORY的表。

I am in the process of investigating an InnoDB/MyISAM issue. While I don't think this is the cause of it, I am worried about this mixing. The database was originally set up with MyISAM. Later the my.cnf file was updated to reset the engine to InnoDB. I am using MySQL 5.5.10.

我正在调查InnoDB / MyISAM问题。虽然我不认为这是它的原因,但我担心这种混合。该数据库最初是使用MyISAM设置的。之后更新了my.cnf文件,将引擎重置为InnoDB。我正在使用MySQL 5.5.10。

What are the possible issues that could occur with having your information_schema database set to MyISAM, but all your other databases set to MySQL?

将information_schema数据库设置为MyISAM可能会出现哪些问题,但是所有其他数据库都设置为MySQL?

For those looking for help: If you came across this question in search of an answer or you want to know more, to see your default database engine:

对于那些寻求帮助的人:如果您在寻找答案时遇到此问题或想了解更多信息,请查看您的默认数据库引擎:

show variables;

To see the engine assigned to tables in a database:

要查看分配给数据库中表的引擎:

show table status;

My my.cnf settings:

我的my.cnf设置:

[client]
default-character-set=utf8

[mysqld]
log=/usr/local/var/mysql/mysqld.log
character-set-server = utf8
collation-server = utf8_general_ci
lower_case_table_names=2
default_storage_engine=InnoDB

# Performance hacks:
innodb_flush_method=nosync
innodb_flush_log_at_trx_commit=0

1 个解决方案

#1

The storage engine used is on a per-table basis. The default storage engine is used when creating a new table and you don't specify a storage engine for the new table.

使用的存储引擎基于每个表。创建新表时使用默认存储引擎,而您没有为新表指定存储引擎。

The system tables would have been MyISAM regardless of what you set the default storage engine to.

无论您将默认存储引擎设置为什么,系统表都将是MyISAM。

From MySQL documentation:

来自MySQL文档:

Important

Do not convert MySQL system tables in the mysql database (such as user or host) to the InnoDB type. This is an unsupported operation. The system tables must always be of the MyISAM type.

不要将mysql数据库(例如用户或主机)中的MySQL系统表转换为InnoDB类型。这是一项不受支持的操作。系统表必须始终为MyISAM类型。

If you are not querying the information_schema tables directly, there is no concern about mixing the types.

如果您没有直接查询information_schema表,则无需考虑混合类型。

#1