将php4/mysql4迁移到php5/mysql5:切换到InnoDB吗? [英] Migrating php4/mysql4 to php5/mysql5: switch to InnoDB?

问题描述

我有一个遗留的Web应用程序php4/mysql4(MyISAM，数据库包含一些cms，一些用户数据，一些日历应用程序).现在，我将使用php5/mysql5迁移到新服务器.

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.

在迁移mysql数据库时应该更改为InnoDB吗? -预期的优势/劣势/风险? -不推荐使用MyISAM，还是有朝一日?还是我可以安全地保留它? -听说MyISAM很容易损坏，那么InnoDB可以抗崩溃吗? -InnoDB更容易或更安全地进行备份和还原吗?

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?

当我进行更改(中断SQL查询)或中断逻辑(由于更改了锁定机制)时，它是否会破坏我的代码?如果是，典型情况是什么?

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?

(对于php问题，我创建了一个不同的问题:将php4/mysql4迁移到php5/mysql5:预期的php问题?)

(For the php issues I created a different question: Migrating php4/mysql4 to php5/mysql5: expected php issues?)

推荐答案

将MySQL v4/v5升级与表升级分开是值得的.这样可以减少潜在的问题范围.

It is worthwhile separating the MySQL v4/v5 upgrade from the table upgrades. That reduces the potential range of problems.

也就是说，如果很少发生重启数据库的情况，那么在v4/v5升级之前要花一些时间检查InnoDB服务器选项，因为它们中的许多都需要重启数据库.推荐的两个参数是innodb_file_per_table = 1和innodb_flush_log_at_tx_commit = 1(查找它们)，您还应该查看innodb_buffer_pool_size，因为如果没有人更改它，几乎肯定会太低.

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将存在很长时间.它是一种非常健壮的磁盘格式，具有一些在许多情况下有用的品质.特别是，它具有快速的SELECT，这对于没有更新或更新很少的小表很有用.也就是说，由于MyISAM不支持并发读取，因此非常热的表(很多SELECT s)将受益于迁移到InnoDB.

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 SELECT which can be useful for a smallish table that has no or very few updates. That said, a very hot table (lots and lots of SELECTs) will benefit from being migrated to InnoDB because MyISAM does not support concurrent read.

MyISAM几乎总是能够在数据库崩溃后幸存下来，只需要一个REPAIR TABLE. InnoDB并不总是那么幸运. MyISAM也可以从数据库下面备份；即使您没有事先锁定表，也很可能会得到一个可以正常使用的文件. InnoDB文件不是很友好.这就是为什么innodb_hot_copy存在的原因.

MyISAM almost always survives a database crash with nothing more than a REPAIR TABLE needed. 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.

我们最近进行了MySQL v4/v5升级，但只有一个SQL问题:混合模式JOIN.当将隐式表联接与显式的LEFT JOIN子句混合使用时，版本4的解析器是相当宽容的.版本5并不是那么宽容.因此，我们借此机会搜寻了该应用程序并将所有JOIN升级到显式的JOIN.除了错过一两个景点外，这次活动非常成功.

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 explicit LEFT JOIN clauses. Version 5 is not so forgiving. So we took the opportunity to scour the app and upgrade all the JOINs to explicit JOINs. Apart from one or two spots that were missed, this was very successful.

我建议您使用与MySQL v5通讯的PHP 4设置测试环境.这将让您测试所有这些.

I'd recommend you setup a test environment with PHP 4 talking to MySQL v5. This will let you test all this out.

这篇关于将php4/mysql4迁移到php5/mysql5:切换到InnoDB吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！