Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I will be upgrading a large database (9GB) from MySQL 4.1 to MySQL 5.1. I have 2 options (that I know of). Which is the better option and why?

  1. Dump entire DB using mysqldump, upgrade the server, then import the mysqldump file. This ensures that the tables are in the new 5.1 format.
  2. Use the same table files on the new server, but update them using "REPAIR TABLE" command. Not sure about this method's reliability.

All tables are MyISAM.

share|improve this question

migrated from stackoverflow.com Jan 13 '10 at 23:29

This question came from our site for professional and enthusiast programmers.

    
I am unable to test all the production data on a test server, but I was able to test method 1 with 1.5GB of data. The dump took about 10 minutes and the import into the upgraded server took about 15 minutes. Could I infer that an import of the full 9GB should take about 1.5 hours? –  smusumeche Jan 15 '10 at 17:22
    
The upgrade went very well, thanks for your help. I did have a table called "condition" which no longer worked because it is a reserved word in 5.1 but wasn't in 4.1. Other than that, no problems detected so far. –  smusumeche Jan 18 '10 at 1:41
    
How long did it take when you did the full 9GB? What kind of harddrives did you have? –  Echo Apr 18 '12 at 17:59
    
Considering this was over 2 years ago, I really don't remember, sorry. I think probably around an hour. –  smusumeche Apr 19 '12 at 19:33

4 Answers 4

up vote 1 down vote accepted

Option 1. Not only is it the safest, it also ensures you have a backup in case it all goes pear shaped. With 9GB, depending on schema(s) used, I would probably be inclined to dump each database and possibly even each table to a separate dump file.

Option 2 leaves too much room for things to go wrong.

share|improve this answer

Try both! But be sure to have a backup copy of all files so you easily can roll back if you destroy something. Try it on a test server first.

share|improve this answer

In My Opinion Method 1 is the safest and fastest.

share|improve this answer

Method 1.

Here's why. At my company we upgraded from MySQL 4.1 -> 5.1. We had ALOT of databases that became corrupt and the repair table command would not repair the table. The only thing we could do was zip up the databases, move them to another server running 4.1 and repair them, than dump and import into new databases on 5.1.

Method 1. Definitely.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.