无法在MyISAM中将引擎更改为MySQL

Question

我已经在MacOSX10.6.5上安装了MySQL 5.6.10。我的问题是，MySQL使用InnoDB作为它的默认引擎。

我检查了MySQL中支持的下列引擎：

show engines\G


*************************** 1. row ***************************
      Engine: FEDERATED
     Support: NO
     Comment: Federated MySQL storage engine
Transactions: NULL
          XA: NULL
  Savepoints: NULL
*************************** 2. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ***************************
      Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 4. row ***************************
      Engine: BLACKHOLE
     Support: YES
     Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 5. row ***************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 6. row ***************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 7. row ***************************
      Engine: ARCHIVE
     Support: YES
     Comment: Archive storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 8. row ***************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 9. row ***************************
      Engine: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
Transactions: NO
          XA: NO
  Savepoints: NO

我想将它更改为MyISAM，因此我已经更改了my.cnf中的设置：

[mysqld]
default-storage-engine=MyISAM

之后，我从系统首选项重新启动了MySQL。但是如果我再次检查引擎并创建一个表，它仍然是用InnoDB创建的，而不是用MyISAM创建的。

有人能告诉我怎么把它改成MyISAM吗？

以下是我的my.cnf文件内容：

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

[mysqld]
default-storage-engine=MyISAM

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
# socket = .....

# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M

# sql_mode=STRICT_TRANS_TABLES 

Answer 1

MySQL 5.6.10对我来说也是很新的，所以请接受我的回答

我在Linux上安装了MySQL 5.6.10

我将default-storage-engine=MyISAM添加到my.cnf并重新启动MySQL

从这一点出发：

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | 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         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

to 

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MyISAM             | DEFAULT | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | 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         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| InnoDB             | YES     | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

没问题。

如果你把

default-storage-engine=MyISAM

在my.cnf中，重新启动MySQL，而默认的是InnoDB，那么只有一个解释:您将default-storage-engine=MyISAM放在了my.cnf的错误部分

有一次，我在ServerFault的一个问题：集群中遇到了这样一个错误

确保该选项位于米舍尔德节的my.cnf下面

[mysqld]
default-storage-engine=MyISAM

建议

我认为MyISAM在MySQL 5.6.10中是作为插件运行的。检查您的错误日志是否有插件错误。

此外，这样做：

在一个SSH会话中，tail -f /var/log/mysqld.log

在另一个SSH会话中，service mysql restart

查找与MyISAM相关的任何插件消息。例如，请注意，当我运行关机时，我看到了以下消息：

2013-02-26 15:20:13 1725 [Note] InnoDB: Starting shutdown...
2013-02-26 15:20:15 1725 [Note] InnoDB: Shutdown completed; log sequence number 1600309
2013-02-26 15:20:15 1725 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
2013-02-26 15:20:15 1725 [Note] Shutting down plugin 'CSV'
2013-02-26 15:20:15 1725 [Note] Shutting down plugin 'MEMORY'
2013-02-26 15:20:15 1725 [Note] Shutting down plugin 'MRG_MYISAM'
2013-02-26 15:20:15 1725 [Note] Shutting down plugin 'MyISAM'
2013-02-26 15:20:15 1725 [Note] Shutting down plugin 'sha256_password'
2013-02-26 15:20:15 1725 [Note] Shutting down plugin 'mysql_old_password'
2013-02-26 15:20:15 1725 [Note] Shutting down plugin 'mysql_native_password'
2013-02-26 15:20:15 1725 [Note] Shutting down plugin 'binlog'
2013-02-26 15:20:15 1725 [Note] /usr/sbin/mysqld: Shutdown complete

130226 15:20:15 mysqld_safe mysqld from pid file /var/lib/mysql/chtn-web2v.pid ended

请注意，MyISAM是一个插件。如果你没看到像

2013-02-26 15:20:15 1725 [Note] Shutting down plugin 'MyISAM'

您的MyISAM插件可能与Mac版的MySQL有问题。

Answer 2

这个可以用

修改表t1 ENGINE = InnoDB；

尽管您必须使用一系列sql语句来更改表的引擎。