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

Pure HTML+CSS to achieve Element loading effect

This is the effect of the Element UI loading comp...

Understanding render in Vue scaffolding

In the vue scaffolding, we can see that in the ne...

What does this.parentNode.parentNode (parent node of parent node) mean?

The parent node of the parent node, for example, t...

Record of the actual process of packaging and deployment of Vue project

Table of contents Preface 1. Preparation - Server...

A detailed introduction to Linux system configuration (service control)

Table of contents Preface 1. System Service Contr...

CSS3 Bezier Curve Example: Creating Link Hover Animation Effects

We will use CSS3 animated transitions to create a...

N ways to center elements with CSS

Table of contents Preface Centering inline elemen...

Detailed explanation of mysql.user user table in Mysql

MySQL is a multi-user managed database that can a...

Two ways to prohibit clearing the input text input cache in html

Most browsers will cache input values ​​by defaul...

MySQL query_cache_type parameter and usage details

The purpose of setting up MySQL query cache is: C...

HTML+CSS box model example (circle, semicircle, etc.) "border-radius" is simple and easy to use

Many friends found that the box model is a square...

Several implementation methods and advantages and disadvantages of SQL paging query in MySQL

【SQL】SQL paging query summary The need for paging...

How to use ECharts in WeChat Mini Programs using uniapp

Today, we use uniapp to integrate Echarts to disp...

Detailed discussion of memory and variable storage in JS

Table of contents Preface JS Magic Number Storing...

Detailed explanation of TypeScript's basic types

Table of contents Boolean Type Number Types Strin...