Summary of the operation records of changing MyISAM storage engine to Innodb in MySQL

Summary of the operation records of changing MyISAM storage engine to Innodb in MySQL

In general, MySQL provides a variety of storage engines by default, which can be viewed as follows:

1) Check whether the InnoDB plug-in is installed in MySQL.

From the command results below, we can see 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)

----------------------------------------------------------------------
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 'ha_innodb.so';
----------------------------------------------------------------------

2) Check what storage engines 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)

3) View the current default storage engine of MySQL:

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

4) Check what engine a table uses (the parameter after engine 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 'WeChat sharing log auto-increment 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 database into the INNODB engine format:

In the backed up xxx.sql file, change ENGINE=MyISAM to ENGINE=INNODB
Just import it again.

6) Commands for conversion table:

mysql> alter table table name engine = innodb;

As can be seen above, the storage engine used by this local MySQL is the default MyISAN. Due to business needs, its storage engine must be changed to Innodb first.

The operation record is as follows:

1) Shut down 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 line in this configuration area to specify the storage engine as innodb
default-storage-engine = InnoDB

4) Delete ib_logfile0 and ib_logfile1 in the /mysql/data directory. Delete it or cut it somewhere else.

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

5) Start MySQL and 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 &

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

The above summary of the operation record of replacing MyISAM storage engine with Innodb in Mysql is all the content that the editor shares with you. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM.

You may also be interested in:
  • Summary of the differences between MySQL storage engines MyISAM and InnoDB
  • Detailed explanation of the difference between MyISAM and InnoDB in MySQL storage engine
  • MySQL storage engine, the difference between myisam and innodb
  • 9 differences between MySQL storage engines MyISAM and InnoDB
  • How to convert MySQL database MyISAM storage engine to Innodb
  • The difference between MySQL storage engines InnoDB and MyISAM
  • A brief analysis of the advantages and disadvantages of choosing InnoDB and MyISAM as MySQL storage engines
  • MySQL storage engines InnoDB and MyISAM

<<:  Several ways to generate unique IDs in JavaScript

>>:  Detailed explanation of Nginx regular expressions

Recommend

Several common redirection connection example codes in html

Copy code The code is as follows: window.location...

B2C website user experience detail design reference

Recently, when using Apple.com/Ebay.com/Amazon.co...

In-depth analysis of homology and cross-domain, jsonp (function encapsulation), CORS principle

Table of contents Same Origin Policy Ajax request...

MySQL learning notes: complete select statement usage example detailed explanation

This article uses an example to illustrate the co...

MySQL complete collapse: detailed explanation of query filter conditions

Overview In actual business scenario applications...

The relationship between web page production and steamed buns (sharing experience)

I slept late yesterday and was awake the whole da...

Detailed explanation of JavaScript axios installation and packaging case

1. Download the axios plugin cnpm install axios -...

The idea and process of Vue to realize the function of remembering account and password

Table of contents Implementation ideas There are ...

Solve the problem that vue project cannot carry cookies when started locally

Solve the problem that the vue project can be pac...

Copy fields between different tables in MySQL

Sometimes, we need to copy a whole column of data...

Detailed explanation of JavaScript clipboard usage

(1) Introduction: clipboard.js is a lightweight J...

JavaScript canvas implements moving the ball following the mouse

This article example shares the specific code of ...

Analysis of slow insert cases caused by large transactions in MySQL

【question】 The INSERT statement is one of the mos...

Sql query MySql database table name and description table field (column) information

The following is an introduction to using SQL que...

Detailed steps to expand LVM disk in Linux

1. Add a hard disk 2. Check the partition status:...