如何从MyIsam将MAIN mysql数据库转换为InnoDB [英] How to convert MAIN mysql database to InnoDB from MyIsam
问题描述
我正在尝试管理mysql组复制，并且在操作用户和授予时发现问题.主mysql数据库中的10个主要mysql表是MyIsam.所以我无法添加数据库或用户权限，因为它们会失败并且不会复制. 主-主组复制需要所有InnoDB.
I am trying to manage mysql group replication and I noticed a problem when manipulating users and grants. 10 of the main mysql tables in the main mysql database are MyIsam. So I cant add databases or user permissions because they fail and wont replicate. Master-master group replication requirs everything InnoDB.
ALTER TABLE在常规的自定义数据库/表上可以正常工作，但是如何在主mysql数据库上解决此问题?
ALTER TABLE works fine on regular custom databases/tables but how do you fix this on the main mysql database?
我尝试了此方法，但都失败了:
I tried this but they all fail:
ALTER TABLE mysql.db ENGINE = InnoDB;
ALTER TABLE mysql.tables_priv ENGINE = InnoDB;
ALTER TABLE mysql.user ENGINE = InnoDB;
错误::错误1726(HY000):存储引擎'InnoDB'不支持系统表.
ERROR: ERROR 1726 (HY000): Storage engine 'InnoDB' does not support system tables.
运行CREATE USER的另一个错误...
Another error running CREATE USER...
[错误] 插件group_replication报告:'表db不使用InnoDB存储引擎.这与组复制不兼容"
[ERROR] Plugin group_replication reported: 'Table db does not use the InnoDB storage engine. This is not compatible with Group Replication'
错误 3098(HY000):该表不符合外部插件group_replication的要求.
ERROR 3098 (HY000): The table does not comply with the requirements by an external plugin group_replication.
服务器版本:5.7.23-log MySQL Community Server
Server version: 5.7.23-log MySQL Community Server
推荐答案
请勿更改系统表的引擎
MySQL尚未对代码进行足够的更改，以允许mysql.*成为MyISAM以外的任何其他内容. MySQL 8.0通过将表(数据字典")转换为InnoDB表来进行更改，其结构和功能完全不同.
MySQL has not yet changed the code enough to allow for mysql.* to be anything other than MyISAM. MySQL 8.0 makes the change by turning the tables (the "data dictionary") into a InnoDB tables, with radically different structure and capabilities.
由于您位于5.7.23，因此距8.0.xx仅一步之遥.考虑升级.
Since you are at 5.7.23, you are only one (big) step away from 8.0.xx. Consider upgrading.
复制可与MyISAM表一起使用，但但群集复制不起作用-Galera和组复制以其他方式处理那些MyISAM表.请参阅有关GRANT，CREATE USER等情况的文档.请勿使用UPDATE和INSERT来处理与登录相关的表.
Replication works with MyISAM tables, but clustering replication does not -- Galera and Group Replication deal with those MyISAM tables in other ways. See the documentation on what happens with GRANT, CREATE USER, etc. Do not use UPDATE and INSERT to manipulate the login-related tables.
(此问题的作者似乎已通过卸载插件解决了该问题.)
(The Author of this Question seems to have fixed the problem by uninstalling a plugin.)
这篇关于如何从MyIsam将MAIN mysql数据库转换为InnoDB的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
