My strong advice is not to try and do this on a running database!
MyISAM isn't transactional in any case, so any operation that spans many tables can't be performed in this manner (it can in
InnoDB - your reason for wanting to change?). You will have to either dump the db and modify the text file or iterate through the tables.
MySQL is moving firmly away from
MyISAM - it's being deprecated. Not even the system tables are
MyISAM anymore (MySQL >= 8.xx).
BTW, please always include the version of MySQL that you are using - it can be very important - see below!
Therefore, I propose the following solution:
Take a backup of your database using
mysqldump - you should be doing this on a regular basis anyway - here is a reference (there are many!):
mysqldump database_name > database_name.sql
of if you have multiple databases on the same server:
mysqldump -u root -p --databases database_name_a database_name_b ... > databases_a_b.sql
Using the text editor of your choice, go through the file and search for the word
ENGINE and replace each occurrence of
If you're on
*nix, then something like this will do the trick:
sed -e 's/ENGINE=MyISAM/ENGINE=InnoDB/g' > new_file.sql
Do not do this for every occurrence of
MyISAM in the entire MySQL server! If you are using a version of MySQL which still has
MyISAM system tables - this is very risky - from here - MySQL 5.7. Will not apply for later versions which have moved the system tables to
Warning Do not convert MySQL system tables in the mysql database from
MyISAM to InnoDB tables. This is an unsupported operation. If you do
this, MySQL does not restart until you restore the old system tables
from a backup or regenerate them by reinitializing the data directory
(see Section 2.10.1, “Initializing the Data Directory”).
So, do not do the change for any tables in the
performance_schema databases - only in the databases that you've created yourself.
Restore from this backup and you're good to go! You should drop your original database and recreate an empty schema (see reference above) of the same name and then:
mysqld database_name < new_file.sql
At this point, you may wish to implement
FOREIGN KEYS - one of the many good reasons to upgrade to
InnoDB! Be careful -
MyISAM doesn't have DRI (
Declarative Referential Integrity) so there may be orphaned records in your existing system - you might want to run tests to check on this?
Running system alternative:
If you're unable to bring down the database, then a solution like this (iterating through every table in the schema) might be helpful (adapted from here and here):
CREATE PROCEDURE UpdateTables ()
DECLARE Finished BOOL DEFAULT FALSE;
DECLARE TableName VARCHAR(64);
DECLARE TablesCursor CURSOR FOR
FROM INFORMATION_SCHEMA.COLUMNS c1
WHERE c1.TABLE_SCHEMA = 'my_schema';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET Finished = TRUE;
FETCH TablesCursor INTO TableName;
IF Finished THEN
SET @queryText = CONCAT('ALTER TABLE ', TableName , ' ENGINE=InnoDB'); -- may or may not need backtics
PREPARE updateQuery FROM @queryText;
DEALLOCATE PREPARE updateQuery;
Note: this is untested - please use with caution and do your own tests before committing to a definitive solution. I would also advise doing something like this when the system load is as light as possible!
Alternatives to the alternative (again, not tested):
If you're a PHP person, this might be suitable.
Another (interesting) approach is this one by Shlomi Noach - a big hitter in the MySQL world.