How to speed up altering storage engine and DB export/import in MySQL

Question

I have a MySQL database with around 250GB of data running on Linux platform (12GB Ram). I am upgrading MySQL to 8.0.32 from 5.7.42-1 and to do so, altering one of the largest table with partitioning and MyISAM storage engine in my DB. Here is the table structure:

| devstats| CREATE TABLE `devstats` (
  `time` datetime(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000',
  `s_num` int(11) NOT NULL DEFAULT '0',
  `devid` mediumint(9) NOT NULL DEFAULT '0',
  `grpid` mediumint(9) NOT NULL DEFAULT '0',
  `subid` mediumint(9) NOT NULL DEFAULT '0',
  `value` double NOT NULL DEFAULT '0',
  KEY `devid` (`devid`),
  KEY `grpid` (`grpid`),
  KEY `time` (`time`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (to_days(time))
(PARTITION p0000 VALUES LESS THAN (0) ENGINE = MyISAM,
 PARTITION p20230522 VALUES LESS THAN (739028) ENGINE = MyISAM,
-----------------------
-----------------------

Following logical approach hence performing below operations:

1. Alter table (change MyISAM to InnoDB)
2. Backup the DB using mysqldump
3. Upgrade mysql binaries
4. Import back the DB data

configured innodb_buffer_pool_size = 8GB

With these all setup, the whole process takes almost couple of days. So, can someone please help me to speed up these all operations without loosing any data.

Thanks

Answer 1

Skip steps 2 (except for having a backup) and 4.

Step 4 is redundant since the table is already InnoDB ready for use.

I see no PRIMARY KEY; that is possible but not advised. These will help in designing a PK that works well:

• What are typical queries that are applied to the table?
• Is there any 'churn', or is this a write-once, but never update or delete, type of dataset?
• Is some combination of columns already Unique?
• Something like WHERE devid=123 AND time BETWEEN... begs for a composite INDEX(devid, time)

More

• Partitioning is unlikely to help performance. Let's see a query that you think would benefit from Partitioning. More on: Partitioning
• My question about DROP was because the table(s) seem to still be intact as you start step 4.