MySQL Move tables to Innodb

1.    Create script cr_move_tables_to_innodb.sql in /opt/mysql/scripts

$ cd /opt/mysql/scripts

$ vi cr_move_tables_to_innodb.sql

use information_schema
select concat_ws ('', 'alter table ', table_schema, '.', table_name, ' engine=innodb;')
from tables
where engine <> 'innodb'
  and table_schema not in ('information_schema','mysql','performance_schema')
into outfile "/opt/mysql/scripts/move_tables_to_innodb.sql" ;

2.    Execute the script you created in step 1, the execution will create a script call move_tables_to_innodb.sql in the /opt/mysql/scripts location that will be a script that contains all the commands to move tables not in innodb to innodb engine that are not in the internal schemas.

Example:

$cd /opt/mysql/scripts
$ mysql -u root -p
password:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 494996
Server version: 5.6.27-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> source cr_move_tables_to_innodb.sql
Database changed
Query OK, 2 rows affected (0.46 sec)

mysql> exit




3. edit the script created by the process execution in step 2 and add a tee statement to the begging on the script so that when script is executed it will create a log that can be checked for errors.

$ cd /opt/mysql/scripts

$ vi move_tables_to_innodb.sql

tee /opt/mysql/scripts/logs/move_tables_to_innodb.out


4. Review script to make sure you are going to move the tables you expect to move

$ cd /opt/mysql/scripts

$ vi move_tables_to_innodb.sql

5. Execute the generated script move_tables_to_innodb.sql modified in step 3 and reviewed in step 4.

Example:

$mysql -u root -p
password:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 494996
Server version: 5.6.27-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> source move_tables_to_innodb.sql


6. Once process is complete review the output file for any errors and issues

$ vi /opt/mysql/scripts/logs/move_tables_to_innodb.out

7. Correct any errors or issues.