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