迁移php4/mysql4到php5/mysql5：切换到InnoDB？
我有一个遗留的Web应用程序php4/mysql4（MyISAM，数据库包含一些cms，一些用户数据，一些日历应用程序）。 现在我要迁移到带有 php5/mysql5 的新服务器。
迁移mysql数据库时是否应该更改为InnoDB？ - 预期的优点/缺点/风险？ - MyISAM 是否已被弃用或者有一天会被弃用？ 或者我可以安全地保留它吗？ - 我听说MyISAM很容易崩溃，那么InnoDB呢，它抗崩溃吗？ - InnoDB 的备份和恢复是否更容易或更安全？
当我进行更改（破坏 SQL 查询）或破坏逻辑（因为更改了锁定机制）时，它会破坏我的代码吗？如果是，典型场景是什么？
（对于 php 问题，我创建了一个不同的问题：迁移 php4/ mysql4 到 php5/mysql5：预期的 php 问题？）
I have a legacy web application php4/mysql4 (MyISAM, db contains some cms, some user data, some calendar application). Now I am going to migrate to a new server with php5/mysql5.
Should I change to InnoDB while migrating a mysql database?
- expected advantages / disadvantages / risks?
- Is MyISAM deprecated or will it some day be? Or can I safely keep it?
- I heard MyISAM easily corrupts, what about InnoDB, is it crash resistant?
- Is InnoDB easier or more safely to backup and restore?
Can it break my code when I do change (break the SQL queries) or break the logic (because of the changed locking mechanism), and if so what are typical scenarios?
(For the php issues I created a different question: Migrating php4/mysql4 to php5/mysql5: expected php issues?)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您应该研究的关键问题是您的数据库是如何使用的。 如果读多于写，那么你应该坚持使用MyISAM，如果写多于读，你应该考虑InnoDB。
如果您想了解 InnoDB 和 MyISAM 之间的区别，那么 Wikipedia 有一个很棒的列表差异。
They key thing you should look into is, how is your database used. If it is more Read than Write, then you should stick with MyISAM, if its more Write than Read, you should look into InnoDB.
If you want to know the difference between InnoDB and MyISAM, then Wikipedia has a great list of their differences.
将 MySQL v4/v5 升级与表升级分开是值得的。 这减少了潜在问题的范围。
也就是说，如果重新启动数据库的情况很少发生，那么无论如何，在 v4/v5 升级之前花一些时间检查 InnoDB 服务器选项，因为其中许多都需要重新启动数据库。 推荐的两个是 innodb_file_per_table=1 和 innodb_flush_log_at_tx_commit=1 （查找它们），您还应该查看 innodb_buffer_pool_size，因为如果没有人更改它，它几乎肯定会太低。
MyISAM 将会存在很长一段时间。 它是一种非常强大的磁盘格式，具有在许多情况下有用的一些品质。 特别是，它具有快速的
SELECT，这对于没有更新或更新很少的小型表非常有用。 也就是说，一个非常热的表（大量的 SELECT）将受益于迁移到 InnoDB，因为 MyISAM 不支持并发读取。MyISAM 几乎总能在数据库崩溃时幸存下来，只需要
REPAIR TABLE即可。 InnoDB并不总是那么幸运。 MyISAM也可以从数据库下备份出来； 即使您事先没有锁定表，您也很可能会得到一个可以正常工作的文件。 InnoDB 文件就不那么友善了； 这就是 innodb_hot_copy 存在的原因。我们最近进行了 MySQL v4/v5 升级，只有一个 SQL 问题：混合模式
JOIN。 当将隐式表连接与显式 LEFT JOIN 子句混合时，版本 4 的解析器相当宽容。 版本 5 就没那么宽容了。 因此，我们借此机会搜索应用程序并将所有JOIN升级为显式JOIN。 除了错过一两个地方之外，这一切都非常成功。我建议您设置一个测试环境，使用 PHP 4 与 MySQL v5 进行通信。 这将让您测试所有这些。
It is worthwhile separating the MySQL v4/v5 upgrade from the table upgrades. That reduces the potential range of problems.
That said, if restarting the database is a rare occurance, then take some time to review the InnoDB server options before the v4/v5 upgrade, anyway, because many of them need a database restart. The two recommended are innodb_file_per_table=1 and innodb_flush_log_at_tx_commit=1 (look them up) and you should also look at innodb_buffer_pool_size, as it is almost certainly going to be too low if no-one has changed it.
MyISAM is going to be around for a long time. It is a very robust on-disk format that has some qualities useful for many situations. In particular, it has a fast
SELECTwhich can be useful for a smallish table that has no or very few updates. That said, a very hot table (lots and lots ofSELECTs) will benefit from being migrated to InnoDB because MyISAM does not support concurrent read.MyISAM almost always survives a database crash with nothing more than a
REPAIR TABLEneeded. InnoDB is not always so lucky. The MyISAM can also be backed-up out from under the database; even if you don't lock the table beforehand, you will very likely get a file that will just work. InnoDB files are not so kind; this is why innodb_hot_copy exists.We recently went through a MySQL v4/v5 upgrade and we only had one SQL problem: mixed-mode
JOINs. Version 4's parser was fairly forgiving when mixing implicit table joins with explicitLEFT JOINclauses. Version 5 is not so forgiving. So we took the opportunity to scour the app and upgrade all theJOINs to explicitJOINs. Apart from one or two spots that were missed, this was very successful.I'd recommend you setup a test environment with PHP 4 talking to MySQL v5. This will let you test all this out.