WordPress site uses MyISAM, should I switch to InnoDB necessary?

I’ve been managing a site for a long time, so the database still uses MyISAM for database engine. So, now that I transferred the site to a new server using mysql 8.0.19 (before it was 5.7), I’m wondering if I need to switch to InnoDB.

If yes, how to backup innodb db? Any different from MyISAM using mysqldump? Can I backup when the site is live?

I’m asking because I read this (WordPress use innodb by default?): It matters when it comes to backing up your WordPress site. I learned this the hard way. If the DB is innodb then R1 cloud restore type backups won’t save all the data because it has to be shut down first in order to back it up. Whereas MYISAM can be backed up no problem.

Update info for @gordan-bobic

| wp_comments | CREATE TABLE `wp_comments` (
  `comment_ID` bigint unsigned NOT NULL AUTO_INCREMENT,
  `comment_post_ID` bigint unsigned NOT NULL DEFAULT '0',
  `comment_author` tinytext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `comment_author_email` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `comment_author_url` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `comment_author_IP` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `comment_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `comment_date_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `comment_content` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `comment_karma` int NOT NULL DEFAULT '0',
  `comment_approved` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '1',
  `comment_agent` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `comment_type` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `comment_parent` bigint unsigned NOT NULL DEFAULT '0',
  `user_id` bigint unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`comment_ID`),
  KEY `comment_post_ID` (`comment_post_ID`),
  KEY `comment_approved_date_gmt` (`comment_approved`,`comment_date_gmt`),
  KEY `comment_date_gmt` (`comment_date_gmt`),
  KEY `comment_parent` (`comment_parent`),
  KEY `comment_author_email` (`comment_author_email`(10))
) ENGINE=MyISAM AUTO_INCREMENT=16593 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |

Update 2

@gordan-bobic I updated the my.cnf to remove the NO_ZERO_IN_DATE, NO_ZERO_DATE and it worked using this guide https://ixnfo.com/en/the-solution-of-error-error-1067-42000-at-line-211-invalid-default-value-for-blablabla.html

So basically, this is what I had on my installation:
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION using this command show variables like 'sql_mode';

So I updated the my.cnf file with this:
sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

Then I used the command to convert the tables to InnoDB and it worked.

You should have switched to InnoDB at some point in the past decade, but now is as good a time as you are going to find. 🙂

mysqldump will work the same way.

MyISAM could not be backed up while the database is running either. Databases generally cannot be backed up by running using a naive method like taring up the files. The data has to be static / point-in-time consistent. So if you take a snapshot and back up a snapshot, that is fine.

With InnoDB you can also use xtrabackup, which is faster to back up and much, much faster to restore than mysqldump. You can also use mysqldump –single-transaction to back up without locking the tables (mysqldump with MyISAM results in all tables being locked during the backup process).