1

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.

0

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).

| improve this answer | |
  • Is this the right command to convert the tables ALTER TABLE table_name ENGINE=InnoDB;? Also, can I run this command while the website is live? Does it matter converting one table at a time while the remaining tables of WordPress are MyISAM? Thanks, – mhweb May 17 at 12:26
  • Yes, that is the right command. You can run it while the site is live, but writes to the table will be blocked while the table is rebuilt. How long the table rebuild will take depends on the table size. Make sure you set innodb_file_per_table=1 in your config file and restart mysqld before you do it. There is no problem with converting one table at a time. – Gordan Bobic May 17 at 12:39
  • Thank you and you meant to include innodb_file_per_table=1 in the my.cnf file, right? – mhweb May 17 at 12:45
  • Yes, in the [mysqld] section. Make sure you restart mysql for it to take effect before you convert the tables. – Gordan Bobic May 17 at 13:34
  • So, tried, for I'm getting errors on some tables: mysql> ALTER TABLE wp_comments ENGINE=InnoDB; ERROR 1067 (42000): Invalid default value for 'comment_date' mysql> ALTER TABLE wp_links ENGINE=InnoDB; ERROR 1067 (42000): Invalid default value for 'link_updated' mysql> ALTER TABLE wp_posts ENGINE=InnoDB; ERROR 1067 (42000): Invalid default value for 'post_date' mysql> ALTER TABLE wp_users ENGINE=InnoDB; ERROR 1067 (42000): Invalid default value for 'user_registered' – mhweb May 17 at 13:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.