在生产中运行应用程序时更改mysql存储引擎的后果 [英] consequences of changing mysql storage engine while application is running in production

问题描述

我们有一个当前正在运行的应用程序，该应用程序在多线程环境中使用Mysql.我们大约有25个表，其中20个表使用InnoDb作为存储引擎，而5个表使用MyISAM作为存储引擎.

We have a currently running application which is using Mysql in multithreaded environment. We have approx 25 tables out of which 20 tables are using InnoDb as storage engine while 5 tables are using MyISAM as storage engine.

我们将把这5个表的存储引擎更改为InnoDB.如果在运行应用程序期间更改它，是否会引起一些问题

We are going to change storage engine of these 5 tables to InnoDB. Will it cause some issue if change it during running application

推荐答案

您对ALTER TABLE进行的任何需要表副本的更改都将锁定该表.这是应用程序中最重要的问题.任何应用程序都无法通过查询访问该表.

Any change you make with ALTER TABLE that requires a table-copy will lock the table. This is the most significant issue for applications. The table will be inaccessible from queries by any application.

此锁的持续时间取决于表的大小，服务器的性能以及服务器承受的其他负载.

How long this lock lasts depends on the size of the table, and the performance of your server, and the other load the server is under.

如果使用在线模式更改工具(例如

You can make changes with minimal downtime if you use an online schema change tool like pt-online-schema-change.

另一种策略是对副本数据库实例上的表进行更改.表可以在副本数据库上与主数据库上具有不同的存储引擎是可以的.您不必担心表被锁定，它只会使复制滞后，然后在完成更改后，复制应会赶上.完成所有操作后，您可以将应用程序切换为使用副本，然后停用先前的主数据库实例.

Another strategy is to make alterations to tables on a replica database instance. It's okay for tables to have a different storage engine on a replica than on the master. You don't care about tables being locked, it will just make replication lag behind and then when the alterations are done, replication should catch up. When that's all done, you can switch your application to use the replica, and then decommission the former master database instance.

这篇关于在生产中运行应用程序时更改mysql存储引擎的后果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！