Migrate MyISAM tables from MySQL 5.7 to 8.0

The MySQL Ecosystem has been using InnoDB as the default transactional engine for many years now. If you are one of the few still using MyISAM, it is time you also converted and benefited from InnoDB.

If you plan to upgrade to MySQL 8.0, you have several options to deal with those MyISAM tables.

Please note that you should not use MyISAM tables. MyISAM is not ACID compliant and can lead to data loss. Also MyISAM only supports full table lock and no row locking.

As you may recall, in 5.7, MyISAM has MYD, MYI, FRM files. In 8.0, the FRM is replaced with SDI (Serialized Dictionnary Information).  Lets review multiple ways to migrate those MyISAM tables to 8.0:

Migration Options

There are multiple solutions to migrate the MyISAM data to 8.0:

  1. In-place upgrade (just stop mysqld, replace the binaries and restart mysqld).
  2. Logical Dump & Load (or course the preferred method is MySQL Shell)
  3. Physical copy with some extra work.

Point 2 and 3 are in case of the MySQL 5.7 instance and the 8.0 are different servers.

After the migration, it is highly recommended to alter the engine to InnoDB !

In-place Upgrade

This is the most common way to migrate from 5.7 to 8.0. You can follow this blog post.

Logical Dump & Load

In case the source of your MyISAM data on MySQL 5.7 and the MySQL 8.0 destination is on a different server, a logical dump of the data is a good way to migrate from one server to the other one.

To perform such dump and load, there is nothing better than the MySQL Shell Utility.

Here are some posts covering MySQL Dump & Load Utility:

Now if the plan is the recommended one and the final step is to have InnoDB tables, the load can be done in three steps:

  1. load the tables definition
  2. change the engine of these empty tables to InnodB
  3. load the data

Let’s see this in action, we start first with the dump:

Server version: 5.7.31 MySQL Community Server (GPL)
No default schema selected; type \use <schema> to set one.
 MySQL   127.0.0.1:5731   2021-08-02 12:10:15  
 JS  util.dumpSchemas(['my_isam_db'],'/tmp/dump')
Acquiring global read lock
Global read lock acquired
Gathering information - done
All transactions have been started
Locking instance for backup
NOTE: Backup lock is not supported in MySQL 5.7 and DDL changes will not be blocked.
The dump may fail with an error or not be completely consistent 
if schema changes are made while dumping.
Global read lock has been released
Writing global DDL files
Preparing data dump for table `my_isam_db`.`t1`
Data dump for table `my_isam_db`.`t1` will be chunked using column `id`
Writing DDL for schema `my_isam_db`
Data dump for table `my_isam_db`.`t1` will be written to 1 file
1 thds dumping - 100% (2 rows / ~2 rows), 0.00 rows/s, 0.00 B/s uncompressed, 0.00 B/s compressed
Duration: 00:00:00s                                                                              
Schemas dumped: 1                                                                                
Tables dumped: 1                                                                                 
Uncompressed data size: 55 bytes                                                                 
Compressed data size: 51 bytes                                                                   
Compression ratio: 1.1                                                                           
Rows written: 2                                                                                  
Bytes written: 51 bytes                                                                          
Average uncompressed throughput: 55.00 B/s                                                       
Average compressed throughput: 51.00 B/s

Multiple schemas or even the full instance can be dumped.

Now we connect MySQL Shell to MySQL 8.0 and we only load the table definition:

 JS  util.loadDump('/tmp/dump', {'ignoreVersion': true, 'loadData': false})
Loading DDL only from '/tmp/dump' using 4 threads.
Opening dump...
Target is MySQL 8.0.26. Dump was produced from MySQL 5.7.31
WARNING: Destination MySQL version is newer than the one where the dump was created. 
Loading dumps from different major MySQL versions is not fully supported and may not work. 
The 'ignoreVersion' option is enabled, so loading anyway.
Checking for pre-existing objects...
Executing common preamble SQL
Executing DDL script for schema `my_isam_db`
[Worker002] Executing DDL script for `my_isam_db`.`t1`
Executing common postamble SQL                      
                                                    
No data loaded.
0 warnings were reported during the load.

You can notice that we used loadData: false.

Now we will alter the engine to InnoDB:

 SQL  show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `inserted` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
1 row in set (0.0005 sec)

 SQL  alter table t1 engine=innodb default charset=utf8mb4;
Query OK, 0 rows affected (0.3546 sec)

Records: 0  Duplicates: 0  Warnings: 0

 SQL  show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) CHARACTER SET latin1 DEFAULT NULL,
  `inserted` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.0012 sec)

You can also notice that I took the opportunity to change the character set to UFT8MB4.

And now we can load the data:

 JS  util.loadDump('/tmp/dump', {'ignoreVersion': true, 'loadDDl': false})
Loading Data only from '/tmp/dump' using 4 threads.
Opening dump...
Target is MySQL 8.0.26. Dump was produced from MySQL 5.7.31
WARNING: Destination MySQL version is newer than the one where the dump was created. 
Loading dumps from different major MySQL versions is not fully supported and may not work. 
The 'ignoreVersion' option is enabled, so loading anyway.
NOTE: Load progress file detected. Load will be resumed from where it was left, assuming no external updates were made.
You may enable the 'resetProgress' option to discard progress for this MySQL instance and force it to be completely reloaded.
Executing common preamble SQL
[Worker000] my_isam_db@t1@@0.tsv.zst: Records: 2  Deleted: 0  Skipped: 0  Warnings: 0
Executing common postamble SQL                                       
                                                       
1 chunks (2 rows, 55 bytes) for 1 tables in 1 schemas were loaded in 1 sec (avg throughput 55.00 B/s)
0 warnings were reported during the load.

And it’s done !

Physical Copy

Some people would prefer to copy the data files (.MYD and .MYI) from one server to the other one.

This cannot be performed as the SDI will be missing and the table won’t be recognized. Nothing wrong will happen, the database and tables will just be ignore until you will try to create the schema:

mysql> create database my_isam_db;
ERROR 3678 (HY000): Schema directory './my_isam_db' already exists. 
This must be resolved manually (e.g. by moving the schema directory to another location).

So what is the alternative ?

The safest way is to create a dump of the table definition only (you could use the same dump as the previous section, but if you prefer to copy the files it’s maybe because you don’t want to dump the data that might be very large?). So let’s take a dump including only the table definition:

 JS  util.dumpSchemas(['my_isam_db'],'/tmp/dump_ddl', {'ddlOnly': true})
Acquiring global read lock
Global read lock acquired
Gathering information - done
All transactions have been started
Locking instance for backup
NOTE: Backup lock is not supported in MySQL 5.7 and DDL changes will not be blocked. 
The dump may fail with an error or not be completely consistent if schema changes are made while dumping.
Global read lock has been released
Writing global DDL files
Running data dump using 4 threads.
NOTE: Progress information uses estimated values and may not be accurate.
Writing DDL for table `my_isam_db`.`t1`
Writing DDL for schema `my_isam_db`
0% (0 rows / ~2 rows), 0.00 rows/s, 0.00 B/s uncompressed, 0.00 B/s compressed
Duration: 00:00:00s                                                           
Schemas dumped: 1                                                             
Tables dumped: 1                                                              
Uncompressed data size: 0 bytes                                               
Compressed data size: 0 bytes                                                 
Compression ratio: 0.0                                                        
Rows written: 0                                                               
Bytes written: 0 bytes                                                        
Average uncompressed throughput: 0.00 B/s                                     
Average compressed throughput: 0.00 B/s

Now we can load that dump. It will create empty MyISAM tables without .FRM file but with a .SDI:

 JS  util.loadDump('/tmp/dump_ddl', {'ignoreVersion': true})
Loading DDL and Data from '/tmp/dump_ddl' using 4 threads.
Opening dump...
Target is MySQL 8.0.26. Dump was produced from MySQL 5.7.31
WARNING: Destination MySQL version is newer than the one where the dump was created. 
Loading dumps from different major MySQL versions is not fully supported and may not work. The 'ignoreVersion' option is enabled, so loading anyway.
Checking for pre-existing objects...
Executing common preamble SQL
Executing DDL script for schema `my_isam_db`
[Worker000] Executing DDL script for `my_isam_db`.`t1`
Executing common postamble SQL               
                                             
No data loaded.
0 warnings were reported during the load.

We can see that the table was created:

 SQL  show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `inserted` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
1 row in set (0.0003 sec)

And on the filesystem too:

total 8.0K
-rw-r----- 1 mysql mysql 3.6K Aug  2 12:49 t1_5017.sdi
-rw-r----- 1 mysql mysql    0 Aug  2 12:49 t1.MYD
-rw-r----- 1 mysql mysql 1.0K Aug  2 12:49 t1.MYI

Now we only need to copy the MYD and MYI files on the right location. But first you need to stop MySQL of course !

[root@imac mysql]# systemctl stop mysqld
[root@imac mysql]# cp data/my_isam_db/t1.M* /var/lib/mysql/my_isam_db/
cp: overwrite '/var/lib/mysql/my_isam_db/t1.MYD'? y
cp: overwrite '/var/lib/mysql/my_isam_db/t1.MYI'? y

After the copy you can restart MySQL and the data will be there !

Extra

Sometimes, people have their data somewhere but no MySQL server running anymore (could be a backup, a disk, …).

If you are not able to perform the schemas and tables definitions dump, you still have the possibility if you have the FRM files to use an external tool that will provide you the table defintion: dbsake

dbsake is a tool I find particularly interesting not only because it can read the frm files but also about it’s fincore capabilities.

So let’s imagine we only have access to the old MySQL 5.7 directory with those files:

[root@imac data]# ls -lh my_isam_db/
total 24K
-rw-r----- 1 fred fred   65 Jul 29 08:45 db.opt
-rw-r----- 1 fred fred 8.5K Jul 29 09:29 t1.frm
-rw-r----- 1 fred fred   40 Jul 29 09:29 t1.MYD
-rw-r----- 1 fred fred 2.0K Jul 29 09:29 t1.MYI

We can use dbsake with that frm file to retrieve the table definition:

[root@imac data]# dbsake frmdump my_isam_db/t1.frm 
--
-- Table structure for table `t1`
-- Created with MySQL Version 5.7.31
--

CREATE TABLE `t1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `inserted` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

And you can do this for all MyISAM tables you need to move to MySQL 8.0

Conclusion

As you can see, it’s possible to migrate MyISAM data from older MySQL version to 8.0. But at the end, don’t forget that the most important is to change them to a more performant and secure engine: InnoDB !

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

4 Comments

  2. Innodb is not as performant as MyISAM for data load. For non-transactionnal queries, the performance loss is roughly 4%. MyISAM is faster. Google and Amazon used it, and no “data loss” occured… stop fake news.

Leave a Reply

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

As MySQL Community Manager, I am an employee of Oracle and the views expressed on this blog are my own and do not necessarily reflect the views of Oracle.

You can find articles I wrote on Oracle’s blog.

Continue