Converting live MyISAM table to InnoDB

Question

I have a table that tracks web traffic. The table is constantly written and read from. Since table is pretty big in size I have to make sure the application will not "freeze" in the process of converting the table's storage engine from MyISAM -> InnoDB. Below is what I'm planning to do based on information I could gather from other posts. Please tell me if I'm missing anything.

1. mysqldump -u -p --lock-tables=false mydb tracking > tracking.sql - from what I understand this should not lock "tracking" table
2. Rename table name in the sql file to tracking_new
3. mysql -u -p mydb < tracking.sql
4. ALTER TABLE mydb.tracking_new ENGINE=InnoDB;

5. Insert new rows to "tracking_new" table that were written to the original table while performing dump

   SELECT @last_id := tracking.id FROM mydb.tracking_new ORDER BY tracking.id DESC LIMIT 1;    
   INSERT INTO mydb.tracking_new
   SELECT * FROM mydb.tracking WHERE tracking.id > @last_id;
   

Rename tables

RENAME TABLE 
    mydb.tracking TO mydb.tracking_old, 
    mydb.tracking_new TO mydb.tracking;

Answer 1

Assuming you don't have any triggers on that table, the simplest way to do this without locking the table is to use pt-online-schema-change.

Make sure you have enough extra disk space to accommodate this change. The table will be larger when you convert it to InnoDB, and you need to maintain both copies of the table during the conversion. For example, if your table is 100 GB make sure you have at least 200+ GB of free disk space.

You should test this out in a non-production environment first to make sure it works for you and to find out exactly how much disk space you need.

The syntax for your case is quite simple.

First do a dry run to verify that it will work:

pt-online-schema-change --alter "ENGINE=InnoDB" D=mydb,t=tracking --dry-run

Then execute it:

pt-online-schema-change --alter "ENGINE=InnoDB" D=mydb,t=tracking --execute

Answer 2

If the tracking table is MyISAM, you would probably have to rely on concurrent_insert. What that does is let the tracking table add new rows without locking for hole in the middle of the MyISAM table. It would just do a quick-and-dirty appending of all incoming rows.

If you set concurrent_insert to 2, you should be able to write to the tracking table and still copy data.

Here is an alternate suggestion:

STEP 01 : Activate concurrent_size to always append

mysql -Dmydb -ANe"SET GLOBAL concurrent_insert = 2"

STEP 02 : Create InnoDB version of the table

SQL="CREATE TABLE tracking_innodb LIKE tracking;"
SQL="${SQL} ALTER TABLE tracking_innodb ENGINE=InnoDB;"
mysql -Dmydb -ANe"${SQL}"

STEP 03 : Perform copy of tracking table directly into the new table

mysqldump --no-create-info mydb tracking | sed 's/tracking/tracking_innodb' | mysql -Dmydb

STEP 04 : Rename tracking table to prevent new inserts

mysql -Dmydb -ANe"ALTER TABLE tracking RENAME tracking_old"

STEP 05 : Retrieve Last ID in tracking_old

SQL="SELECT id FROM tracking_new ORDER BY tracking.id DESC LIMIT 1"
LASTID=`mysql -Dmydb -ANe"`

STEP 06 : Retrieve remaining data

SQL="INSERT INTO tracking_new SELECT * FROM tracking_odl WHERE id > ${LASTID}"
mysql -Dmydb -ANe"${SQL}"

STEP 07 : Rename tracking_new to tracking

mysql -Dmydb -ANe"ALTER TABLE tracking_new RENAME tracking"

CAVEAT

Your original proposal is actually just as good. Most of my answer looks like your anyway. The only difference is that I introduce an outage to guarantee no new inserts starting at STEP 04. An outage comes a little earlier in your plan (STEP 02).

I added concurrent_insert to speed up inserts into the MyISAM table duing the mysqldump.

You can either go with your plan or mine. Just add concurrent_insert=2 to the mix.

Give it a Try !!!

Answer 3

Instead of loading your MyISAM table and ALTER it after, change the MySQL engine directly in your SQL dump file (on table definition) :

The table will be restored directly in InnoDB engine :

MyISAM

CREATE TABLE `test` (
  `t` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1

InnoDB

CREATE TABLE `test` (
  `t` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1

The step 5 seems to be good.

Best,

Max.