Mysql replaces MyISAM storage engine with Innodb operation record sample code sharing-Mysql Tutorial-php.cn

Mysql replaces MyISAM storage engine with Innodb operation record sample code sharing

黄舟

Release： 2017-03-23 13:40:23

Original

1435 people have browsed it

The following editor will bring you a summary of the operation records of Mysqlchanging the MyISAM storage engine to Innodb. The editor thinks it is quite good, so I will share it with you now and give it as a reference for everyone. Let’s follow the editor to take a look.

Under normal circumstances, mysql will provide multiple storage engines by default, which can be viewed through the following:

1) Check whether mysql has the innodb plug-in installed.

#It can be seen from the following command results that 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)

Copy after login

---------------------------------------- ----------------------------------If you find that the innodb plug-in is not installed, you can execute the following statement to install it:

mysql> install plugin innodb soname &#39;ha_innodb.so&#39;;

Copy after login

------------------------------------- ----------------------------------

2) Check what storage engine mysql currently 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)

Copy after login

3) Check the current default storage engine of mysql:

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

Copy after login

4) Check what engine a certain table uses (the parameter after the engine parameter in the displayed results indicates the storage engine currently used by 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 &#39;微信分享日志自增ID&#39;, 
 `reference_id` int(11) NOT NULL COMMENT &#39;推荐的经纪人id&#39;, 
 `create_time` datetime NOT NULL COMMENT &#39;创建时间&#39;, 
 PRIMARY KEY (`id`) 
) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=utf8     | 
+--------------+--------------------------------------------------------------------------------------------------------------------------------------+ 
1 row in set (0.00 sec)

Copy after login

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

In the backed up xxx.sql file, replace all ENGINE=MyISAM with ENGINE=INNODB

Just import it again.

6) Command to convert table:

mysql> alter table table name engine=innodb;

As you can see above, the storage engine used by mysql on this machine is the default MyISAN. Due to business needs, the storage engine must be changed to Innodb first.

The operation record is as follows:

1) Close mysql in safe mode

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

Copy after login

2) Back up my.cnf

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

Copy after login

3) Modify my.cnf Configuration file

[root@dev mysql5.1.57]# vim my.cnf
.....
[mysqld]                                      
  //在这个配置区域添加下面一行，指定存储引擎为innodb
default-storage-engine = InnoDB

Copy after login

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

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

Copy after login

5) Start mysql, log in to 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 &

Copy after login

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

Copy after login

The above is the detailed content of Mysql replaces MyISAM storage engine with Innodb operation record sample code sharing. For more information, please follow other related articles on the PHP Chinese website!