MySQL viewing and modifying the storage engine

Recently, the company's BBS pressure is growing, table deadlock more and more serious.

In the case of other optimizations, the MyISAM engine to the InnoDB engine is also included in the plan.

The reference URL is as follows: simple, but pay attention to the backup ...

============

Http://www.linuxidc.com/Linux/2012-10/72884.htm

http://jfbcb.com/article/detail/change-the-mysql-table-storage-engine/

============

MySQL, as the most commonly used database, often encounters a variety of problems. What we're going to talk about today is the modification of the table storage engine. There are three ways, the list is as follows.

1. Real-change. It is slower when the data is many and affects read performance when modified. My_table is the table of operations, and InnoDB is the new storage engine.

ALTER TABLE my_table ENGINE=InnoDB

2. Export, import. This is relatively easy to operate, directly to the SQL file to change, and then back. With mysqldump, Maple elder brother commonly used is navicate that easier to get started. Friendship reminds you of a greater risk.

3. Create, insert. This is faster than the first speed, security than the second high, recommended. 2-Step operation

A. Create a table, create a table that is the same as the table you want to manipulate, and then change the storage engine to the target engine.

CREATE TABLE my_tmp_table  LIKE my_table; ALTER TABLE my_tmp_table ENGINE=InnoDB;


B. Insert. For security and speed, it is best to add transactions and limit the ID (primary key) range.

INSERTINTO my_tmp_table SELECT * FROMmy_table


Here, I hope to be helpful to the students who need it.

=====================

1 Viewing system-supported storage engines

Show engines;

2 Viewing the storage engine used by the table

Two methods:

A, Show table status from Db_name where name= ' table_name ';

b, show create TABLE table_name;

If the displayed format is not good-looking, you can use \g instead of the end of line semicolon

Some people say that using the second method is not accurate, I tried to shut down the original default InnoDB engine is not able to execute the show CREATE TABLE table_name instruction, because it was built InnoDB table, after the default with the MyISAM engine, Causes the InnoDB table data to not be read correctly.

3 Modifying the Table engine method

ALTER TABLE table_name ENGINE=INNODB;

4 shutting down the InnoDB engine method

Turn off MySQL service: net stop MySQL

Locate the My.ini file in the MySQL installation directory:

Find Default-storage-engine=innodb instead of Default-storage-engine=myisam

Find #skip-innodb instead of Skip-innodb

Start MySQL service: net start MySQL

MySQL viewing and modifying the storage engine