Description:
We are planning to upgrade MySQL from our current version 4.1.24 to 5.1.x
From the documentation that I have read it seems the recommended way of doing this is to dump all our data and reload after we upgrade to 5.1
We have a few MyISAM tables that are about 60 GB each and hence will probably take a very long time to do the whole dump and reload DB process.
(Our Data Directory is almost 900 GB). Being in an industry where DB downtime is not an option I would like to know my options of upgrading if even possible.
(FYI, We use a mix of InnoDB and MyISAM tables)
How to repeat:
On a side note, I was trying to check how our current database would work with MySQL 5.1 without a dump and reload. I created a table under 4.1
CREATE TABLE `test1` (
`id` int(11) NOT NULL auto_increment,
`val` int(11) default NULL,
`data` varchar(255) default NULL,
PRIMARY KEY (`id`),
KEY `val_data_idx` (`val`,`data`),
KEY `id_val_idx` (`id`,`val`)
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=latin1
Added a few rows,
insert into test1 values (null, 100, 'row1'), (null, 200, 'row2'), (null, 300, 'row3');
Stopped 4.1. Start MySQL 5.1 pointing to the same datadir as 4.1.
Started a 5.1 client and ran the following:
mysql> select * from test1;
+----+------+------+
| id | val | data |
+----+------+------+
| 1 | 700 | row1 |
| 2 | 200 | row2 |
| 3 | 300 | row3 |
+----+------+------+
3 rows in set (0.00 sec)
mysql> check table test1;
+-------------------------+-------+----------+------------------------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+-------------------------+-------+----------+------------------------------------------------------------------------------------+
| mysql_myisam_test.test1 | check | error | Table upgrade required. Please do "REPAIR TABLE `test1`" or dump/reload to fix it! |
+-------------------------+-------+----------+------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> insert into test1 values (null, 400, 'row1');
Query OK, 1 row affected (0.00 sec)
mysql> select * from test1;
+----+------+------+
| id | val | data |
+----+------+------+
| 1 | 700 | row1 |
| 2 | 200 | row2 |
| 3 | 300 | row3 |
| 4 | 400 | row1 |
+----+------+------+
4 rows in set (0.00 sec)
What I don't understand is, if I can still select/insert data into that table and seems to behave like it does with 4.1, then why
do I need to dump and reload the data? And if I need to do that what are my options for big tables.
Suggested fix:
None I could think of.