Home / server / Questions / 411673
In Process
carillonator
Asked: 2012-07-27 16:28:26 +0800 CST

MySQL upgrade - dump/load vs. alter table

  • 772

Upgrading MySQL 5.1->5.5, all InnoDB, 500GB data, linux.

To rebuild the tables/indexes for 5.5, I can either dump and load everything, or run ALTER TABLE t1 ENGINE=innodb on every table (see MySQL docs).

MySQL recommends the dump and load method here (second paragraph) as does Percona here.

I completely understand the necessity of a good backup, but I will have a clean and reliable storage snapshot serving that purpose. Also, dump/load takes much much longer and requires extra disk space and I/Os.

Is there any other good reason to prefer the dump/load method?

(same question as this one, but dealing with a much larger dataset and existing backup: MySQL Upgrade Method)

linux

1 Answers

  1. In many cases binary upgrade will work, however in some cases dump and reload is necessary. Here listed some cases: http://dev.mysql.com/doc/refman/5.1/en/checking-table-incompatibilities.html

    • 0