Everythig from MyISAM to INNODB, how?

Richard G

Richard G

Verified User
Joined
Jul 6, 2008
Messages
13,529
Location
Maastricht
Seems mysql already does not support MyIsam anymore.
Until now I had in my /etc/my.cnf file:
default-storage-engine=MyISAM

I'm now upgrading from Mysql 5.6 to 5.7 and want to change everything to Innodb instead of MyISAM so I can work more default.

I've seen lots of examples for changes, but don't trust them because most of them are for a single database and I want to change this for all databases.


What is the safest way to change all databases from MyISAM to InnoDB?
Or is it more easy to do when upgrading from 5.6 to 5.7 some how?
 
MyISAM is supported by MySQL. It's not the default engine anymore but it is supported even in MySQL 8 and will continue to be.
 
Yes, but MyISAM is more then 22 years old, and for example MariaDB says this: "MyISAM has a small footprint and allows for easy copying between systems. MyISAM is MySQL's oldest storage engine. There is usually little reason to use it except for legacy purposes." Quote from: https://mariadb.com/kb/en/library/choosing-the-right-storage-engine/

And already in october 2016 Percona announced they would only offer limited support for MyISAM: https://www.percona.com/blog/2016/10/11/mysql-8-0-end-myisam/

We have recently changed to InnoDB as default storage engines on all our shared hosting servers. However we are running MariaDB 10.2.x, wich by default now also is using InnoDB.

@Richard G, Maybe someone can give you a better answer. But personally I feel it is safest to convert one and one database at the time, and also test the site for each database you convert. That is how I am doing it for my customers, as it gives me more control to make sure everything works correct.
 
Hello,

Dump DBs into dump files, use perl/sed or any other text editor and replace ENGINE=MyISAM to ENGINE=InnoDB, then re-import dumps, assuming dumps have instructions to DROP/CREATE tables.
 
@Wattie: It will get out, going to be limited and like Ditto said. It's already very old.

@Zeiter: Phuuu... oke that's now my thing, don't know how to do this. Not sure about the drop/create either.
Can't they automatically be converted when upgrading from 5.6 to 5.7 some how?

@Ditto: You are doing it database by database. Will be a lot of work, but maybe if there is no easier or better way, I have to do it like that. Do you have a good working instruction for me on how to do this?
Because I'm finding so much on Google, I don't know what's safe to use.

Is it also good to change the MyISAM line to InnoDB now in my.cnf like this:
default-storage-engine=Innodb
or better to leave it empty?
 
No, they can't. You should do it manually and with a script.

Code: 
cd /usr/local/directadmin/custombuild
./build mysql_backup

should create dumps of DBs in the needed format with drop/create instructions.

You will need to go through all the dumps and replace ENGINE=MyISAM with ENGINE=InnoDB, then re-import dumps.

More details on how to create and restore dumps using custombuild: https://help.poralix.com/articles/how-to-manage-sql-dumps-console-directadmin


Or use ALTER:

https://dev.mysql.com/doc/refman/8.0/en/converting-tables-to-innodb.html
 
Thank you Alex.
I did this backup creation yesterday too when upgrading one of the servers to Mysql 5.7 so that part is easy.

Do you know by any chance the correct perl or sed command do them all at once? Otherwise I have to use nano on every single file, I never used perl or sed, except for copy and paste commands which I found here on the forums.

I also read somewhere that information_schema and mysql themselves better not be converted is that correct?

Would this instruction be also correct and safe for a good change when doing it via Phpmyadmin logged in as da_admin?
 
Yep, that is indeed what I used it for some time ago, changing DNS records.

Thank you Alex. I think I'm going to try it tonight.

@Ditto: if you have a nice explanation for your method I would appreciate that too, then I can chose what's easier for me to do or which method I feel better with.
 
Richard G said:
[...]
Is it also good to change the MyISAM line to InnoDB now in my.cnf like this:
default-storage-engine=Innodb
or better to leave it empty?
Click to expand...

I just removed the text default-storage-engine=MyISAM and then restarted MariaDB. Because InnoDB is default in MariaDB 10.2.x, it is not needed to specify. However I don't know about MySQL.
 
Richard G said:
[cut..]
@Ditto: if you have a nice explanation for your method I would appreciate that too, then I can chose what's easier for me to do or which method I feel better with.
Click to expand...

Please note that I only do one and one database, and also please note that I am using the newest phpMyAdmin version. With that in mind, here is the steps:

Log on phpMyAdmin and click in the left column at the database you want to convert. Then click in horizontal menu on "SQL" and paste the following code (you need to replace YOUR_DATABASENAME with the name of the database):

Code: 
SELECT CONCAT('ALTER TABLE ',TABLE_SCHEMA,'.', table_name, ' ENGINE=InnoDB;') AS sql_statements
FROM information_schema.tables
WHERE TABLE_SCHEMA NOT IN ('information_schema', 'performance_schema')
AND `ENGINE` = 'MyISAM'
AND `TABLE_TYPE` = 'BASE TABLE'
AND table_schema = 'YOUR_DATABASENAME'
ORDER BY TABLE_SCHEMA, table_name DESC;

Then click on "Go" button. You will then get a list of lines wich start with the words "ALTER TABLE...", right above that list click on "Options", then click on "Full texts", then click on "Go" (Please note that if you have more then 25 tables, they will not all be visible, then you would need to click on "Show all" at the bottom below the "ALTER TABLE..." lines).

Select and copy all the lines that start witht the text "ALTER TABLE...".

Click at the left column on the name of the database, click on "SQL" in the horizontal menu at the top, and then paste in all the lines you copied previous (the "ALTER TABLE..." lines). Then click on "Go" button.

You are done. Click on the database name again to look at the tables, and you will see they all display InnoDB under the column heading "Type".
 
Last edited:
Thank you ditto.

And thank you both for instructions. I will have a try at it and see what works best for me.
 
Just some addition questions, I did 2 databases now. The first said 1 row changed after converting, the second database had less tables but said "7 rows changed".
Is that a bad thing or is that normal?

I think Zeiters method is a bit quicker, but:
I would leave information_schema and mysql as they are, i.e.without modification.
Click to expand...
I could move out the mysql.sql to another place temporarily. But there was no information_schema.sql between the backups created. Is that normal, should I worry about that or not?
 
For future references or for anybody wanting to do this too, this is how I've done it and it worked perfectly.

Code: 
cd /usr/local/directadmin/custombuild
./build set mysql_backup yes
./build mysql_backup
mv mysql_backups mysql_backups.`date +%F`
./build mysql_backup
cd mysql_backups
perl -pi -e 's/ENGINE=MyISAM/ENGINE=InnoDB/' *.sql
wget http://files1.directadmin.com/services/all/mysql/restore_sql_files.sh
chmod 755 restore_sql_files.sh
./restore_sql_files.sh
service mysqld restart

That's all, but take in consideration that the restore can take some time, especially when large databases are present.

The command:
Code: 
mv mysql_backups mysql_backups.`date +%F`
which you see in between, is to move your backup to a seperate directory with a date because you don't want to work on your original backup.
That's why the backup_mysql command is given a second time. Then you have a new backup which you can adjust.
The restore_sql_files.sh won't restore the mysql.sql file when started like above, without extra command lines. You can better leave the mysql.sql file left alone, so just remove it from your backups directory or create a new backup later on.

Check that everything is working fine. If yes, it would be advisable to create new backups after the changes so you have fresh working backups.
 
Last edited:
Richard G said:
Just some addition questions, I did 2 databases now. The first said 1 row changed after converting, the second database had less tables but said "7 rows changed".
Is that a bad thing or is that normal? [...cut]
Click to expand...

Yes, that is normal. The databases likely contained both MyISAM tables and InnoDB tables, so the second database was having 7 MyISAM tables, and the other database was only having 2 MyISAM tables.
 
Thank you.

Do you by any chance now which characterset Mariadb is on by default? I just change the mysql 5.7 to utf8mb4 like this in my.cnf under the [mysqld] tag:
Code: 
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
skip-character-set-client-handshake
 
My servers running MariaDB 10.2.x has "InnoDB latin1_swedish_ci" as default. However I am not sure if a new clean install in 2019 would have different defaults. Edit: I found a confirmation here: https://mariadb.com/kb/en/library/setting-character-sets-and-collations/ wich says: "In MariaDB, the default character set is latin1, and the default collation is latin1_swedish_ci"

May I ask you a question: When you was running the restore_sql_files.sh script. Did the script drop all databases before restore? If not, I would think that would be needed to do before restore?
 
Last edited:
Hmmz... Won't databases with utf8mb4 get mixed up then with latin1 when new tables are added? Or doesn't it matter what is used as default character sets?

Did the script drop all databases before restore?
Click to expand...
No there is no drop command in the restore_sql_files.sh script.

I presume a restore does overwrite everything since the results looked fine and things are working as designed.
 
Richard G said:
Hmmz... Won't databases with utf8mb4 get mixed up then with latin1 when new tables are added? Or doesn't it matter what is used as default character sets?
Click to expand...

That is not a problem. It is the script that is installing the tables wich decide what character set and collation that should be created. If the script does not specify that, then they will be created with the defaults. Same thing goes for MyISAM and InnoDB. Even you now has InnoDB as default, nothing is preventing a script to specify that new tables should be created as MyISAM, however if they do not specify that, your default InnoDB would be used when creating new tables.
 
This will list all MyISAM tables:

SELECT CONCAT(TABLE_SCHEMA, ".", TABLE_NAME) AS myisam_tables
FROM INFORMATION_SCHEMA.TABLES
WHERE ENGINE = 'MyISAM' AND TABLE_SCHEMA <> "mysql"
Click to expand...

You can generate list of ALTER TABLE commands like the following way:

SELECT CONCAT("ALTER TABLE ", TABLE_SCHEMA, ".", TABLE_NAME, " SET ENGINE=InnoDB") AS commands
FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE = 'MyISAM' AND TABLE_SCHEMA <> "mysql"
Click to expand...

If you are brave enough, you can even directly execute it as an alter table command.

There's no need to go through the slow process of backup/restore. These alters are enough.
 
You must log in or register to reply here.
Back
Top