change engine type from MyISAM to InnoDB

user_9972860 0
Viewed 191

I want to change engine type from MyISAM to InnoDB. What I Did: Method 1:

  1. Copy table structure in a new database.
  2. Change table engine from MyISAM to InnoDB.
  3. Export data from existing table (MyISAM).
  4. Import data in a new table (InnoDB). enter image description here enter image description here enter image description here

Here, I can see the total rows of a table and the size of the table. But not see any record on browse.

Method 2:

  1. Copy table structure in a new database.

  2. Export data from the existing database.

  3. Import data in a new database.
  4. Change table engine from MyISAM to InnoDB. enter image description here enter image description here

Here, I notice after change engine type many records are deleted. In customer table imported records are 310749 after change engine type, I see only 243898, loss total 66851 records.

What is wrong with this? Any other way to change the type from MyISAM to InnoDB without loss data.

myisam phpmyadmin innodb mysql
1 Answers

Simply do ALTER TABLE foo ENGINE=InnoDB; But that does it 'in-place'. If you want the new table in a different database:

CREATE TABLE db2.foo LIKE db1.foo;
ALTER TABLE  db2.foo ENGINE=InnoDB;  -- and possibly other changes, see blog below
INSERT INTO  db2.foo
    SELECT * FROM db1.foo;       -- copy data over

SELECT COUNT(*) FROM db1.foo;
SELECT COUNT(*) FROM db2.foo;    -- compare exact number of rows

The number of rows -- If you are using SHOW TABLE STATUS to see that, be aware that MyISAM provides an exact number of rows, but InnoDB only approximates the number. Use SELECT COUNT(*) FROM foo to get the exact number of rows.

Here, let me knock the cobwebs off my old blog on moving from MyISAM to InnoDB: http://mysql.rjweb.org/doc.php/myisam2innodb

user_17668310
Related