首页 > 代码库 > Linux环境下修改MySQL数据库存储引擎
Linux环境下修改MySQL数据库存储引擎
今天在执行Oracle数据库迁移至MySQL数据库时报出了一个错误信息：
Specified key was too long; max key length is 1000 bytes
百度发现，原来需要更改MySQL数据库的存储引擎为InnoDB，查询目前现有的存储引擎信息：
[root@test-121 ~]# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 10 Server version: 5.1.71 Source distribution Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement. mysql> show engines; +------------+---------+------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +------------+---------+------------------------------------------------------------+--------------+------+------------+ | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | CSV | YES | CSV storage engine | NO | NO | NO | | MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | NO | NO | NO | | InnoDB | YES | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | +------------+---------+------------------------------------------------------------+--------------+------+------------+ 5 rows in set (0.00 sec) mysql>
查询发现，默认的存储引擎为MyISAM，目前安装的MySQL数据库提供了对InnoDB引擎的支持。
更改方法如下：
修改“/etc/my.cnf”配置文件
[root@test-121 mysql]# cat /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 default-storage-engine=InnoDB [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid
在[mysqld]节点下面增加“default-storage-engine=InnoDB”选项。
重启MySQL服务：
service mysqld restart
再次查看数据存储引擎信息：
mysql> show engines; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 10 Current database: *** NONE *** +------------+---------+------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +------------+---------+------------------------------------------------------------+--------------+------+------------+ | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | CSV | YES | CSV storage engine | NO | NO | NO | | MyISAM | YES | Default engine as of MySQL 3.23 with great performance | NO | NO | NO | | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | +------------+---------+------------------------------------------------------------+--------------+------+------------+ 5 rows in set (0.01 sec)
发现已经将数据库存储引擎切换至InnoDB。
再次迁移数据库没有发生错误信息。
关键信息总结：
1、登录MySQL Console控制台
mysql –u root –p
2、查看数据库存储引擎
show engines;
3、MySQL数据库配置文件路径
/etc/my.cnf
4、增加数据存储引擎配置项
default-storage-engine=InnoDB
5、重启MySQL
service mysqld restart
Linux环境下修改MySQL数据库存储引擎
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。