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

Use simple jQuery + CSS to create a custom a tag title tooltip

Introduction Use simple jQuery+CSS to create a cus...

vue+elementui implements the complete code of adding and modifying a shared bullet box

Table of contents 1. New II. Modification element...

How to modify the scroll bar style in Vue

Table of contents First of all, you need to know ...

How to build lnmp environment in docker

Create a project directory mkdir php Create the f...

Introduction to deploying selenium crawler program under Linux system

Table of contents Preface 1. What is selenium? 2....

Detailed explanation of how to connect Java to Mysql version 8.0.18

Regarding the connection method between Java and ...

The difference between Div and table in HTML (discussed in detail in all aspects)

1: Differences in speed and loading methods The di...

Mini Program to implement Token generation and verification

Table of contents process Demo Mini Program Backe...

Linux Basic Tutorial: Special Permissions SUID, SGID and SBIT

Preface For file or directory permissions in Linu...

Detailed example of getting the maximum value of each group after grouping in MySQL

Detailed example of getting the maximum value of ...

Detailed explanation of nginx upstream configuration and function

Configuration Example upstream backend { server b...

Introduction to Enterprise Production MySQL Optimization

Compared with other large databases such as Oracl...

Vue Beginner's Guide: Creating the First Vue-cli Scaffolding Program

1. Vue--The first vue-cli program The development...

Docker installation and configuration command code examples

Docker installation Install dependency packages s...

Multiple ways to change the SELECT options in an HTML drop-down box

After the form is submitted, the returned HTML pag...