MySQL: InnoDB vs. MyISAM: how and why to change (Amazon RDS)?

Question

I didn't set up my database and noticed that some of my tables are innoDB whereas others are MyISAM. The reason I'm interested in changing them is because Jurg van Vliet (who writes O'Reilly books on AWS) says that Amazon RDS's automatic database backup ability hinges on all tables being innoDB tables.

What's the command to convert all tables in a database to InnoDB?

Answer 1

Here's a stored procedure to convert all MyISAM table in the current database to InnoDB:

DROP PROCEDURE IF EXISTS convert_all_tables_to_innodb;

DELIMITER //

CREATE PROCEDURE convert_all_tables_to_innodb()
DETERMINISTIC
MODIFIES SQL DATA
SQL SECURITY INVOKER
COMMENT ''
proc: BEGIN
    DECLARE b_not_found     BOOL DEFAULT FALSE;
    DECLARE v_table_name VARCHAR(255);
    DECLARE cur1 CURSOR FOR
SELECT
table_name
FROM
information_schema.tables
WHERE
table_schema = DATABASE() AND
table_type = 'BASE TABLE' AND
engine = 'MyISAM'
ORDER BY
1;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET b_not_found = TRUE;

    OPEN cur1;

    SET b_not_found = FALSE;

    loop1: LOOP
        FETCH cur1 INTO v_table_name;
        IF b_not_found THEN
            LEAVE loop1;
        END IF;

        SET @sql = CONCAT('ALTER TABLE ', v_table_name, ' ENGINE=InnoDB');

        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DROP PREPARE stmt;
    END LOOP;

    CLOSE cur1;
END;
//

DELIMITER ;

To run it, simply execute:

CALL convert_all_tables_to_innodb();