MySQL - Convert tables from MyISAM to InnoDb

Convert All Tables From MyIsam To Innodb

Run this from linux to generate a script called MyISAM_To_Innodb.sql.  This script can then be run from within mysql command line using source and pointing to the script.
To convert all tables for all databases in an instance:
mysql -h... -u... -p.... -A --skip-column-names -e"SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;') InnoDBConversionSQL FROM information_schema.tables WHERE engine='MyISAM' AND table_schema NOT IN ('information_schema','mysql','performance_schema') ORDER BY (data_length+index_length)" > MyISAM_To_InnoDB.sql
To convert all tables for a specified database:
mysql -h<hostname> -uroot -p<password> -S <socket path and file> -A --skip-column-names -e"SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;') InnoDBConversionSQL FROM information_schema.tables WHERE engine='MyISAM' AND table_schema = '<database name>' ORDER BY (data_length+index_length)" > MyISAM_To_InnoDB.sql
The above commands can be modified for other purposes such as OPTIMIZE TABLE for reindexing.

Comments

Popular posts from this blog

Using DBCA silent install and disabling automatic memory management

SQL Server Builds Information

SQL SERVER – Event ID 107- Report Server Windows Service (MSSQLSERVER) cannot connect to the report server database.