Linux環境下修改MySQL資料庫儲存引擎

標籤：sorted   base   copyright   man   hash   test   遷移資料   ble   for   

今天在執行Oracle資料庫遷移至MySQL資料庫時報出了一個錯誤資訊：

Specified key was too long; max key length is 1000 bytes

百度發現，原來需要更改MySQL資料庫的儲存引擎為InnoDB，查詢目前現有的儲存引擎資訊：

[[email protected]121 ~]# mysql -u root -pEnter password: Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 10Server version: 5.1.71 Source distributionCopyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.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”設定檔

[[email protected]121 mysql]# cat /etc/my.cnf [mysqld]datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sockuser=mysql# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0default-storage-engine=InnoDB[mysqld_safe]log-error=/var/log/mysqld.logpid-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 awayNo connection. Trying to reconnect...Connection id:    10Current 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資料庫儲存引擎