Change Table Types from MyISAM to InnoDB in MySQL Dump File

innodbmariadbmyisamMySQL

I have a few hundred systems running running on a MariaDB 10.1.x. On these systems there is a master database which, for various legacy reasons, has to have MyISAM tables. I wish to produce slaves for these masters which have InnoDB versions of all the master MyISAM tables. For operational reasons, I would prefer not to have to perform various ALTER TABLE operations on the slaves.

I was thinking (possibly simplistically) of taking the MySQLdump file from the master required as part of the initial slave setup, and then using some sort of script simply to alter its CREATE TABLE commands to specify a table type of InnoDB rather than MyISAM.

Is this a sensible approach, and has anyone else tried it? Is there a better approach which would get me a slave which uses InnoDB without having to do loads of ALTER TABLEs.

I am keen for this to be something which can be automated/script based, as we have literally hundreds of these systems in the field and so a manual upgrade process would be labour intensive and very error prone.

As an aside, I note that MySQL/MariaDB itself uses many MyISAM tables internally e.g. the mysql.user table. Do they use MyISAM for any particular reason, or could these tables also safely be converted to InnoDB. Or am I better ensuring that any script I use to do the conversion avoids the tables in the mysql database altogether?

Best Answer

I have created scripts where I flip the storage engine from MyISAM to InnoDB as follows:

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

If you want to see if this really works, just dump the schema as follows:

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

With reference to the mysql schema, please don't touch it. Why ???

In MySQL 5.5, all tables in the mysql schema were 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)

In MySQL 5.6, that began to change

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)

Here are the non-MyISAM tables for MySQL 5.6:

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)

The two CSV tables are the slow log and error log, should your change log_output to FILE. The 5 InnoDB tables were introduced to support crash-safe replication (See Documentation on this).

It does not stop there. MySQL 5.7 introduced more InnoDB tables.

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)

Here are the non-MyISAM tables for MySQL 5.7:

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)

Please note that there are still 10 MyISAM in MySQL 5.7. Here they are:

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)

Why are there 10 tables still in MyISAM ?

  • The tables user, db,procs_priv, proxies_priv, tables_priv, and columns_priv are tables that store user grants.
  • func contains stored functions
  • proc contains stored procedures
  • event contains schedules for executing events
  • ndb_binlog_index contains info needed for the NDB storage engine (MySQL Cluster)

These 10 tables are rarely used. Their contents are loaded into RAM on startup and then referenced from RAM. There is no need for them to be InnoDB because they are system-level tables that should not be changed via transaction.

Look at the potential problems that could occur if these 10 tables were InnoDB:

  • Running a GRANT command in a transaction and it rolls back
  • Creating a stored procedure in a transaction and it rolls back
  • Creating a new user and the user table crashes and gets corrupted

These and other problems can result and you would not even know it until mysqld restarts and nobody can log in, execute a stored procedure, or schedule a cron'd event.

Therefore, with good reason, these 10 tables were left as MyISAM. Again, please don't change the storage engine of anything in the mysql schema.

Related Question