我在将 Ubuntu 10.04 上的 MySQL 5.1 中的 innodb 设置为默认数据库引擎时遇到了问题。一旦我切换到 innodb 作为默认引擎,服务器就会拒绝启动。这是我在启用 innodb 后尝试启动 MySQL 时在系统日志中看到的内容:
Oct 6 21:18:29 artemis init: mysql main process ended, respawning
Oct 6 21:18:58 artemis init: mysql post-start process (5825) terminated with status 1
Oct 6 21:18:59 artemis init: mysql main process (5943) terminated with status 7
Oct 6 21:18:59 artemis init: mysql main process ended, respawning
Oct 6 21:19:28 artemis init: mysql post-start process (5944) terminated with status 1
Oct 6 21:19:29 artemis init: mysql main process (6056) terminated with status 7
Oct 6 21:19:29 artemis init: mysql main process ended, respawning
Oct 6 21:19:58 artemis init: mysql post-start process (6057) terminated with status 1
Oct 6 21:20:00 artemis init: mysql main process (6163) terminated with status 7
Oct 6 21:20:00 artemis init: mysql main process ended, respawning
Oct 6 21:20:28 artemis init: mysql post-start process (6164) terminated with status 1
Oct 6 21:20:30 artemis init: mysql main process (6239) terminated with status 7
Oct 6 21:20:30 artemis init: mysql main process ended, respawning
Oct 6 21:20:59 artemis init: mysql post-start process (6240) terminated with status 1
Oct 6 21:21:00 artemis init: mysql main process (6358) terminated with status 7
Oct 6 21:21:00 artemis init: mysql main process ended, respawning
Oct 6 21:21:29 artemis init: mysql post-start process (6359) terminated with status 1
Oct 6 21:21:30 artemis init: mysql main process (6434) terminated with status 7
Oct 6 21:21:30 artemis init: mysql main process ended, respawning
Oct 6 21:21:59 artemis init: mysql post-start process (6435) terminated with status 1
Oct 6 21:22:01 artemis init: mysql main process (6540) terminated with status 7
Oct 6 21:22:01 artemis init: mysql main process ended, respawning
为了删除所有干扰程序,我卸载了 apparmor 并重新启动。在进行全新安装后,实际上删除了目录 /etc/mysql 和 /var/lib/mysql 以确保没有陈旧文件,服务器以 MyIsam 作为默认引擎正常启动。显示引擎时,InnoDB 是可用的引擎之一:
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB | YES | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| 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 |
| MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | NO | NO | NO |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
8 rows in set (0.00 sec)
我尝试过的 InnoDB 配置附加在 mysql-server-5.1 包附带的默认 my.cnf 中的 [mysqld] 下,如下所示:
# TEST #####################################################
init_connect='SET collation_connection = utf8_general_ci; SET NAMES utf8;'
default-character-set=utf8
character-set-server=utf8
collation-server=utf8_general_ci
skip-character-set-client-handshake
max_allowed_packet = 16M
# Network buffer length (I think this is the linux default)
#net_buffer_length = 8K
# Query caching
query_cache_type = 1
# Default table storage engine when creating new tables
# (comment out when running mysql_install_db)
#default_storage_engine=InnoDB
#default_table_type=InnoDB
# Default transaction isolation level, levels available are:
# READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE
# see: http://dev.mysql.com/doc/refman/5.1/en/set-transaction.html
transaction_isolation = READ-COMMITTED
innodb_data_file_path = ibdata1:50M:autoextend
# TEST #####################################################
我可以添加上面的代码片段而不会造成任何麻烦,但只要我取消注释 default_storage_engine=InnoDB 或 default_table_type=InnoDB,服务器就不会启动。再次注释这些行并重新启动后,列出的引擎中缺少 InnoDB。
mysql> 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 |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
7 rows in set (0.00 sec)
我做错了什么?缺少了什么?任何能对此有所启发的提示都将不胜感激。
干杯!// 约翰
答案1
我安装 MySQL 服务器(Ubuntu 10.04):
apt-get install mysql-server
然后检查默认引擎(MyISAM):
mysql> show engines;
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB | YES | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| 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 |
| MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | NO | NO | NO |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
然后更改 /etc/mysql/my.cnf - 在 mysqld 部分添加 default-storage-engine = innodb:
[mysqld]
#
# * Basic Settings
#
#
# * IMPORTANT
# If you make changes to these settings and your system uses apparmor, you may
# also need to also adjust /etc/apparmor.d/usr.sbin.mysqld.
#
user = mysql
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
skip-external-locking
default-storage-engine = innodb
有用:
mysql> show engines;
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| 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 |
| MyISAM | YES | Default engine as of MySQL 3.23 with great performance | NO | NO | NO |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+