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:
Step 1:
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
Step 2:
Using the text editor of your choice, go through the file and search for the word ENGINE and replace each occurrence of ENGINE=MyISAM with ENGINE=InnoDB.
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 InnoDB anyway:
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 mysql, information_schema or performance_schema databases - only in the databases that you've created yourself.
Step 3:
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 ()
BEGIN
DECLARE Finished BOOL DEFAULT FALSE;
DECLARE TableName VARCHAR(64);
DECLARE TablesCursor CURSOR FOR
SELECT c1.TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS c1
WHERE c1.TABLE_SCHEMA = 'my_schema';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET Finished = TRUE;
OPEN TablesCursor;
MainLoop: LOOP
FETCH TablesCursor INTO TableName;
IF Finished THEN
LEAVE MainLoop;
END IF;
SET @queryText = CONCAT('ALTER TABLE ', TableName , ' ENGINE=InnoDB'); -- may or may not need backtics
PREPARE updateQuery FROM @queryText;
EXECUTE updateQuery;
DEALLOCATE PREPARE updateQuery;
END LOOP;
CLOSE TablesCursor;
END
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.
