This will mass convert all MyISAM tables to InnoDB
SQLSTMT="SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;')"
SQLSTMT="${SQLSTMT} FROM information_schema.tables WHERE engine='MyISAM' AND"
SQLSTMT="${SQLSTMT} table_schema NOT IN "
SQLSTMT="${SQLSTMT} ('information_schema','mysql','performance_schema')"
SQLSTMT="${SQLSTMT} ORDER BY (data_length+index_length)"
echo "SET SQL_LOG_BIN = 0;" > ConvertMyISAMToInnoDB.sql
mysql -uroot -p -ANe"${SQLSTMT}" > ConvertMyISAMToInnoDB.sql
mysql -uroot < ConvertMyISAMToInnoDB.sql
Why does running ALTER TABLE mydb.mytable ENGINE=InnoDB;
take long ?
Under the hood it runs something like this:
CREATE TABLE mydb.mytabletmp LIKE mydb.mytable;
INSERT INTO mydb.mytabletmp SELECT * FROM mydb.mytable;
DROP TABLE mydb.mytable;
ALTER TABLE mydb.mytabletmp RENAME mydb.mytable;
The delay comes when building a brand new Clustered Index (home of PRIMARY KEY entries)
If you want to totally automate the conversion of just one table, you need this:
SET @mydb = 'mydb';
SET @mytb = 'mytable';
SET @newengine = 'InnoDB';
SELECT IF(dbtb2='.',CONCAT('ALTER TABLE ',dbtb1,' ENGINE=InnoDB'),
CONCAT('SELECT ''',dbtb1,' is Already ',engine,''' as \"No Need to Convert\"'))
INTO @ConvertEngineSQL FROM (SELECT CONCAT(A.db,'.',A.tb) dbtb1,
CONCAT(IFNULL(B.db,''),'.',IFNULL(B.tb,'')) dbtb2,engine
FROM (SELECT table_schema db,table_name tb,engine FROM information_schema.tables
WHERE table_schema=@mydb and table_name=@mytb) A LEFT JOIN
(SELECT table_schema db,table_name tb FROM information_schema.tables
WHERE table_schema=@mydb and table_name=@mytb AND engine=@newengine) B
USING (db,tb)) AA;
SELECT @ConvertEngineSQL;
PREPARE st FROM @ConvertEngineSQL; EXECUTE st; DEALLOCATE PREPARE st;
Here is a sample from my PC's MySQL setup
mysql> show create table codewaggle.mytable\G
*************************** 1. row ***************************
Table: mytable
Create Table: CREATE TABLE `mytable` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> SET @mydb = 'codewaggle';
Query OK, 0 rows affected (0.00 sec)
mysql> SET @mytb = 'mytable';
Query OK, 0 rows affected (0.00 sec)
mysql> SET @newengine = 'InnoDB';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT IF(dbtb2='.',CONCAT('ALTER TABLE ',dbtb1,' ENGINE=InnoDB'),
-> CONCAT('SELECT ''',dbtb1,' is Already ',engine,''' as \"No Need to Convert\"'))
-> INTO @ConvertEngineSQL
-> FROM (SELECT CONCAT(A.db,'.',A.tb) dbtb1,
-> CONCAT(IFNULL(B.db,''),'.',IFNULL(B.tb,'')) dbtb2,engine
-> FROM (SELECT table_schema db,table_name tb,engine FROM information_schema.tables
-> WHERE table_schema=@mydb and table_name=@mytb) A LEFT JOIN
-> (SELECT table_schema db,table_name tb FROM information_schema.tables
-> WHERE table_schema=@mydb and table_name=@mytb AND engine=@newengine) B
-> USING (db,tb)) AA;
Query OK, 1 row affected (0.00 sec)
mysql> SELECT @ConvertEngineSQL;
+----------------------------------------------+
| @ConvertEngineSQL |
+----------------------------------------------+
| ALTER TABLE codewaggle.mytable ENGINE=InnoDB |
+----------------------------------------------+
1 row in set (0.00 sec)
mysql> PREPARE st FROM @ConvertEngineSQL; EXECUTE st; DEALLOCATE PREPARE st;
Query OK, 0 rows affected (0.00 sec)
Statement prepared
Query OK, 0 rows affected (1.70 sec)
Records: 0 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected (0.00 sec)
mysql> SET @mydb = 'codewaggle';
Query OK, 0 rows affected (0.00 sec)
mysql> SET @mytb = 'mytable';
Query OK, 0 rows affected (0.00 sec)
mysql> SET @newengine = 'InnoDB';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT IF(dbtb2='.',CONCAT('ALTER TABLE ',dbtb1,' ENGINE=InnoDB'),
-> CONCAT('SELECT ''',dbtb1,' is Already ',engine,''' as \"No Need to Convert\"'))
-> INTO @ConvertEngineSQL
-> FROM (SELECT CONCAT(A.db,'.',A.tb) dbtb1,
-> CONCAT(IFNULL(B.db,''),'.',IFNULL(B.tb,'')) dbtb2,engine
-> FROM (SELECT table_schema db,table_name tb,engine FROM information_schema.tables
-> WHERE table_schema=@mydb and table_name=@mytb) A LEFT JOIN
-> (SELECT table_schema db,table_name tb FROM information_schema.tables
-> WHERE table_schema=@mydb and table_name=@mytb AND engine=@newengine) B
-> USING (db,tb)) AA;
Query OK, 1 row affected (0.01 sec)
mysql> SELECT @ConvertEngineSQL;
+-----------------------------------------------------------------------+
| @ConvertEngineSQL |
+-----------------------------------------------------------------------+
| SELECT 'codewaggle.mytable is Already InnoDB' as "No Need to Convert" |
+-----------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> PREPARE st FROM @ConvertEngineSQL; EXECUTE st; DEALLOCATE PREPARE st;
Query OK, 0 rows affected (0.00 sec)
Statement prepared
+--------------------------------------+
| No Need to Convert |
+--------------------------------------+
| codewaggle.mytable is Already InnoDB |
+--------------------------------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql>
Give it a Try !!!