2 ways to InnoDB MySQL MyISAM

2 ways to InnoDB MySQL MyISAM

What is the difference between MyISAM and InnoDB in MySQL? One is like a convenience store, one is like a big shopping mall, they exist to adapt to different occasions. When the traffic is small, we can use MyISAM, when the traffic is large, we may be in the database, all the table or part of the table into InnoDB. Here are 2 practical ways to accomplish this conversion.

One, use ALTER TABLE to achieve.

1. mysql> ALTER TABLE merchant Engine=innodb;

The conversion method is simple. The MyISAM has three files stored on the disk. MYD,. Myi,.frm file, will the three files not disappear after conversion? Does the shared tablespace or stand-alone table space in InnoDB not increase?

1. [[email protected] test]# ls |grep merhcant|xargs-i du {}
2. Merchant.frm
3. 690 Merchant. MYD
4. 144 Merchant. MYI

Above is the file size before conversion, after conversion. MYD,. The myi disappeared. The InnoDB shared table space is increased by 8M.

1. [[email protected] data]# ls |grep ibdata |xargs-i du-m {}
2. Ten ibdata1 //Pre-conversion
4. [[email protected] data]# ls |grep ibdata |xargs-i du-m {}
5. Ibdata1 //converted

MySQL tables are stored on disk in a way that does not need to be converted. this is more convenient. InnoDB Here I set a shared table space, the size of the converted table is not more than 1M, but the shared table space automatically increased 8M, so the size of the shared table space, not according to the size of the table to increase, there is a base. We can set it through Innodb_autoextend_increment.

Second, export the data, re-build the table

1, export table structure and data, delete cousin, re-build table

2. In Method 1, we can copy the SQL that created the table and replace it with Engine=myisam, Engine=innodb
4. Mysql> Show CREATE TABLE merchant\g;
6. 1. Row ***************************
8. Table:merchant
10. Create table:create Table ' merchant ' (
12. ' Btid ' int (5) unsigned zerofill not NULL auto_increment,
14. ' Btname ' varchar (+) not NULL,
16. ' Btnote ' text,
18. PRIMARY KEY (' Btid '),
20. UNIQUE KEY ' btname ' (' Btname ')
22. ) Engine=myisam DEFAULT Charset=utf8
24. 1 row in Set (0.00 sec)
28. Method 2, directly export the table structure, replace a myisam in the file
30. Mysqldump-udba-p-D test Merchant >/tmp/test/mytest/merchant.sql;

2. Exporting data

1. Mysqldump-udba-p--no-create-info test Merchant >/tmp/test/mytest/merchant_data.sql;

3, delete cousin, re-build table, import data, this will not say ,linux simple MySQL backup and import, as well as file backup and import

2 ways to InnoDB MySQL MyISAM