Summary of Operation Record of Mysql Replacing MyISAM Storage Engine with Innodb

1 In general, mysql will provide a variety of storage engines by default, which can be viewed through the following:

1) Check to see if the innodb plug-in is installed on mysql.

As you can see from the following command results, the innodb plug-in has been installed.

mysql> show plugins; 
+------------+--------+----------------+---------+---------+ 
| Name  | Status | Type   | Library | License | 
+------------+--------+----------------+---------+---------+ 
| binlog  | ACTIVE | STORAGE ENGINE | NULL | GPL  | 
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL  | 
| CSV  | ACTIVE | STORAGE ENGINE | NULL | GPL  | 
| MEMORY  | ACTIVE | STORAGE ENGINE | NULL | GPL  | 
| InnoDB  | ACTIVE | STORAGE ENGINE | NULL | GPL  | 
| MyISAM  | ACTIVE | STORAGE ENGINE | NULL | GPL  | 
| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL  | 
+------------+--------+----------------+---------+---------+ 
7 rows in set (0.00 sec)

----------------------------------------------------------------------
If you find that the innodb plug-in is not installed, you can install it by executing the following statement:
mysql > install plugin innodb soname 'ha_innodb.so';
----------------------------------------------------------------------

2) See what storage engine mysql now provides:

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)

3) View the current default storage engine for mysql:

mysql> show variables like '%storage_engine%'; 
+----------------+--------+ 
| Variable_name | Value | 
+----------------+--------+ 
| storage_engine | MyISAM | 
+----------------+--------+ 
1 row in set (0.00 sec) 

4) See what engine is used for a table (the following parameter engine in the display result indicates the storage engine currently used for the table):

mysql > show create table table name;

mysql> show create table wx_share_log; 
+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 
| Table  | Create Table                                                                          | 
+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 
| wx_share_log | CREATE TABLE `wx_share_log` ( 
 `id` int(11) NOT NULL AUTO_INCREMENT COMMENT ' WeChat sharing log increases ID', 
 `reference_id` int(11) NOT NULL COMMENT ' Recommended broker id', 
 `create_time` datetime NOT NULL COMMENT ' Creation time ', 
 PRIMARY KEY (`id`) 
) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=utf8     | 
+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 
1 row in set (0.00 sec) 

5) How to import the MyISAM library into the INNODB engine format:

Replace all ENGINE=MyISAM with ENGINE=INNODB in the backup xxx. sql file
Just import it again.

6) The command to convert the table:

mysql > alter table table name engine=innodb;

As can be found above, the storage engine used by this mysql is the default MyISAN. Due to business needs, it is necessary to change its storage engine to Innodb first.

Operation records are as follows:

1) Turn off mysql in safe mode

[root@dev mysql5.1.57]# mysqladmin -uroot -p shutdown
Enter password:
[root@dev mysql5.1.57]# ps -ef|grep mysql

2) Back up my. cnf

[root@dev mysql5.1.57]# cp my.cnf my.cnf.old

3) Modify the my. cnf configuration file

[root@dev mysql5.1.57]# vim my.cnf
.....
[mysqld]//Add the following 1 line in this configuration area to specify the storage engine as innodb
default-storage-engine = InnoDB

4) Delete ib_logfile0, ib_logfile1 in the /mysql/data directory. Delete or cut elsewhere.

[root@dev var]# mv ib_logfile0 ib_logfile1 /tmp/back/

5) Start mysql and log in mysql to verify whether the storage engine has been switched

[root@dev var]# /Data/app/mysql5.1.57/bin/mysqld_safe --defaults-file=/Data/app/mysql5.1.57/my.cnf &

mysql> show variables like '%storage_engine%'; 
+----------------+--------+ 
| Variable_name | Value | 
+----------------+--------+ 
| storage_engine | InnoDB | 
+----------------+--------+ 
1 row in set (0.00 sec)