Bash Shell Script to Convert All MySQL Tables to use the Innodb Engine

The following, when run in the Bash shell, will convert the engine for all of your MySQL tables to InnoDB.

mysql -B -N -e "SHOW TABLES" -u   --password=  | while read table; \
do \
     echo "+ Converting Table $table"; \
     mysql -B -N -e "alter table $table engine=innodb" -u  --password= ; \ 
done;

Often, if you have a heavily used database, you will want to consider the InnoDB engine. By default, the MySQL database engine is MyISAM. However, the InnoDB engine has many advantages; particularly for high utilization environments. With InnoDB data-integrity is maintained throughout the entire query process because it is transaction safe.

InnoDB also provides row-locking, as opposed to table-locking. With row-locking, while one query is busy updating or inserting a row, another query can update a different row at the same time. Hence InnoDB has superior features for multi-user concurrency and performance.

Leave a comment

Your email address will not be published. Required fields are marked *