在MyISAM文件中将表类型从InnoDB更改为MySQLdump

Question

我有几百个系统运行在MariaDB 10.1.x上。在这些系统上，有一个主数据库，由于各种遗留原因，它必须有MyISAM表。我希望为这些具有所有主InnoDB表的MyISAM版本的主程序生成奴隶。出于操作上的原因，我希望不必对从站执行各种ALTER TABLE操作。

我考虑(可能简单地)从主服务器获取MySQLdump文件，作为初始从设置的一部分，然后使用某种脚本来更改其CREATE TABLE命令，以指定InnoDB的表类型，而不是MyISAM。

这是一种明智的做法吗?还有其他人尝试过吗？有没有更好的方法，可以让我成为一个奴隶，不用做大量的InnoDB ALTER TABLEs。

我非常希望这是一种可以自动化/基于脚本的系统，因为我们在实地确实有数百个这样的系统，因此人工升级过程将是劳动密集和非常容易出错的过程。

顺便说一句，我注意到MySQL/MariaDB本身在内部使用许多MyISAM表，例如mysql.user表。他们是否出于特定的原因使用MyISAM，或者这些表也可以安全地转换为InnoDB。还是更好地确保用于转换的任何脚本完全避免mysql数据库中的表？

Answer 1

我创建了脚本，将存储引擎从MyISAM翻转到InnoDB，如下所示：

mysqldump ... | sed 's/ENGINE=MyISAM/ENGINE=InnoDB/' > dump.sql

如果您想知道这是否有效，只需按如下方式转储模式：

mysqldump --no-data ... | sed 's/ENGINE=MyISAM/ENGINE=InnoDB/' > schema.sql

关于mysql模式，请不要碰它。为什么？

在MySQL 5.5中，中的所有表都是MyISAM。

mysql> select count(1) table_count,ifnull(engine,'Total') eng
    -> from information_schema.tables where table_schema='mysql'
    -> group by engine with rollup;
+-------------+--------+
| table_count | eng    |
+-------------+--------+
|          22 | MyISAM |
|          22 | Total  |
+-------------+--------+
2 rows in set (0.03 sec)

在MySQL 5.6中，这种情况开始改变。

mysql> select count(1) table_count,ifnull(engine,'Total') eng
    -> from information_schema.tables where table_schema='mysql'
    -> group by engine with rollup;
+-------------+--------+
| table_count | eng    |
+-------------+--------+
|           2 | CSV    |
|           5 | InnoDB |
|          21 | MyISAM |
|          28 | Total  |
+-------------+--------+
4 rows in set (0.00 sec)

下面是用于MySQL 5.6的非MyISAM表：

mysql> select table_name,engine from information_schema.tables
    -> where table_schema='mysql' and engine in ('InnoDB','CSV')
    -> order by engine,table_name;
+----------------------+--------+
| table_name           | engine |
+----------------------+--------+
| general_log          | CSV    |
| slow_log             | CSV    |
| innodb_index_stats   | InnoDB |
| innodb_table_stats   | InnoDB |
| slave_master_info    | InnoDB |
| slave_relay_log_info | InnoDB |
| slave_worker_info    | InnoDB |
+----------------------+--------+
7 rows in set (0.00 sec)

如果您将日志_输出更改为文件，则两个CSV表是慢速日志和错误日志。引入5个InnoDB表是为了支持崩溃安全复制(请参阅这方面的文件。)。

它不会止步于此。MySQL 5.7引入了更多的InnoDB表。

mysql> select count(1) table_count,ifnull(engine,'Total') eng
    -> from information_schema.tables where table_schema='mysql'
    -> group by engine with rollup;
+-------------+--------+
| table_count | eng    |
+-------------+--------+
|           2 | CSV    |
|          19 | InnoDB |
|          10 | MyISAM |
|          31 | Total  |
+-------------+--------+
4 rows in set (0.01 sec)

下面是MySQL 5.7的非MyISAM表：

mysql> select table_name,engine from information_schema.tables
    -> where table_schema='mysql' and engine in ('InnoDB','CSV')
    -> order by engine,table_name;
+---------------------------+--------+
| table_name                | engine |
+---------------------------+--------+
| general_log               | CSV    |
| slow_log                  | CSV    |
| engine_cost               | InnoDB |
| gtid_executed             | InnoDB |
| help_category             | InnoDB |
| help_keyword              | InnoDB |
| help_relation             | InnoDB |
| help_topic                | InnoDB |
| innodb_index_stats        | InnoDB |
| innodb_table_stats        | InnoDB |
| plugin                    | InnoDB |
| servers                   | InnoDB |
| server_cost               | InnoDB |
| slave_master_info         | InnoDB |
| slave_relay_log_info      | InnoDB |
| slave_worker_info         | InnoDB |
| time_zone                 | InnoDB |
| time_zone_leap_second     | InnoDB |
| time_zone_name            | InnoDB |
| time_zone_transition      | InnoDB |
| time_zone_transition_type | InnoDB |
+---------------------------+--------+
21 rows in set (0.17 sec)

请注意，在MyISAM 5.7中仍然有10 MyISAM。下面是它们：

mysql> select table_name,engine from information_schema.tables
    -> where table_schema='mysql' and engine='MyISAM';
+------------------+--------+
| table_name       | engine |
+------------------+--------+
| columns_priv     | MyISAM |
| db               | MyISAM |
| event            | MyISAM |
| func             | MyISAM |
| ndb_binlog_index | MyISAM |
| proc             | MyISAM |
| procs_priv       | MyISAM |
| proxies_priv     | MyISAM |
| tables_priv      | MyISAM |
| user             | MyISAM |
+------------------+--------+
10 rows in set (0.00 sec)

为什么MyISAM中还有10个表？

• 表user、db、procs_priv、proxies_priv、tables_priv和columns_priv是存储用户授权的表。
• func包含存储函数
• proc包含存储过程
• event包含执行事件的计划。
• ndb_binlog_index包含NDB存储引擎(MySQL集群)所需的信息。

这10个表很少使用。它们的内容在启动时加载到RAM中，然后从RAM中引用。它们不需要是InnoDB，因为它们是不应该通过事务更改的系统级表。

看看如果这10个表是InnoDB，可能出现的潜在问题：

• 在事务中运行GRANT命令并回滚
• 在事务中创建存储过程并回滚
• 创建一个新用户，user表崩溃并损坏

这些问题和其他问题都可能导致，直到mysqld重新启动，没有人能够登录、执行存储过程或调度cron'd事件，您甚至都不会知道。

因此，有充分的理由，这10个表被保留为MyISAM。同样，请不要更改mysql模式中任何内容的存储引擎。