Modifying the MySQL database storage engine under the Linux environment

Today in the execution of the Oracle database migration to the MySQL database The Times has an error message:

Long  bytes

Baidu found that the original need to change the MySQL database storage engine for InnoDB, query the current storage engine information:

[[Email protected]121~]# Mysql-u Root-Penter Password:welcome to the MySQL Monitor. Commands End With; or \g.your MySQL connectionIDIsTenServer Version:5.1. inSource Distributioncopyright (c) -, -, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or Itsaffiliates. Other names trademarks of their respectiveowners. Type'Help ;'Or'\h'  forHelp. Type'\c'ToClearThe 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 MySQL3.23With great Performance | NO | NO | NO | | InnoDB | YES | Supports transactions, Row-level locking, and foreign keys | YES | YES | YES | | MEMORY | YES | Hash based, storedinchMemory, useful forTemporary Tables | NO | NO | NO |+------------+---------+------------------------------------------------------------+--------------+------+ + -----------+5RowsinchSet (0.00sec) MySQL>

The query found that the default storage engine is MyISAM, and the currently installed MySQL database provides support for the InnoDB engine.

The change method is as follows:

Modify the "/etc/my.cnf" configuration file

[Email protected]121cat /etc/my.cnf [mysqld]datadir=/var/lib/mysqlsocket=/ var/lib/mysql/Mysql.sockuser=mysql# Disabling symbolic-links are 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

Add the "default-storage-engine=innodb" option below the [MYSQLD] node.

To restart the MySQL service:

Service mysqld Restart

Review the data store engine information again:

Mysql>show engines; ERROR2006(HY000): MySQL server has gone awayno connection. Trying to reconnect ... ConnectionID:TenCurrent 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 MySQL3.23With great Performance | NO | NO | NO | | InnoDB | DEFAULT | Supports transactions, Row-level locking, and foreign keys | YES | YES | YES | | MEMORY | YES | Hash based, storedinchMemory, useful forTemporary Tables | NO | NO | NO |+------------+---------+------------------------------------------------------------+--------------+------+ + -----------+5RowsinchSet (0.01Sec

Discover that the database storage engine has been switched to InnoDB.

No error message occurred while migrating the database again.

Summary of key information:

1. Log in to MySQL console console

Mysql–u root–p

2. View the database storage engine

Show engines;

3. mysql Database configuration file path

/etc/my.cnf

4. Add data storage engine configuration items

Default-storage-engine=innodb

5. Restart MySQL

Service mysqld Restart

Modifying the MySQL database storage engine under the Linux environment