Tell me more ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.
up vote 2 down vote favorite

I've got a MySQL server running version X (X=5.0.22) and for misc. reasons the underlying hardware needs to be upgraded. Give that this is going to be done, how risky would it be to also upgrade the MySQL version at the same time to version Y (Y>=5.1.6)? In particular, is there a list of things that can break in more or less silent ways (as opposed to the DB restore falling over on it's side)?

I'm need an answer for my specific values but a more general matrix of breaking changes/things to worry about would be better in general.

Also I have very little DB admin experience and even less experience on this DB so a fairly precise checklist of thing to look for would make things much easier for me.

Links I've found so far:

share|improve this question

migrated from stackoverflow.com Dec 13 '10 at 23:31

4 Answers

up vote 2 down vote accepted

Don't expect it to go seamlessly and move all your data over correctly. In my experience changing major revisions of MySQL tends to cause data loss. Take a backup, stop the server, and restore your backup. (Hint: Use --extended-inserts with mysqldump, or the 'restore your backup' step is going to take forever)

I've never run into issues where moving from 5.0->5.1 would break things, but that doesn't necessarily mean you won't.

Since you said you are upgrading hardware as well, I'd suggest creating the new server with 5.1, restoring your backup, then pointing a test version of your site at it. If you don't have a test version of your site, you can always just swap the database server IP for the live one. I wouldn't really suggest this, but if you do it, make sure to do it at an off-peak time.

share|improve this answer
I think the site is also going to be moved onto new hardware so that become a near no-op. :) – BCS Dec 15 '10 at 4:22
up vote 1 down vote

The only suggestion I can offer is: "Do one thing at a time."

Upgrade the hardware, first. Once you are certain there are no issues from this upgrade, then move on to the next one.

As far as upgrade a MySQL database goes, you can use mysqldump (a command-line tool) to dump all of the data from the DB, then import the data on a new, testing MySQL server. This is my preferred way to ensure software upgrades; witness it working on the new server, via testing.

Don't forget to make sure the various programs that access the MySQL database, and perform queries and such on it, still work as expected on the test server.

share|improve this answer
The hardware in question is a standard item around here, the only thing that will be different than a standard install will be MySQL its self. Is there some reason I should expect hitting new HW/SW interaction to be more likely with 5.1.x than 5.0.x? – BCS Dec 13 '10 at 18:25
Also, would there be some downside (aside from having to build the DB twice and whatnot) to building a "testing" version and, if it works, moving it into the production position? – BCS Dec 13 '10 at 18:28
up vote 0 down vote

Assuming you can tolerate downtime it's easy to mitigate the risk to 0 : take a backup before you upgrade and if stuff breaks go back to the old version.

share|improve this answer
Give I'm moving hardware, I can not even bring the new version down until I know it works. (I guess in that case, the primary issue becomes how do I check that it works?) – BCS Dec 13 '10 at 18:29
up vote 0 down vote

As long as you are aware of anything that might get broken due to incompatibilities arising out of an upgrade there should be no problem, provided you use sensible methodology. e.g. Doing so using a file level backup is ill advised. Use the time tested and proven method of dump and load.

Regardless of just how you go about it, ensure you have a good (read: tested) backup to allow going back if things go seriously pear shaped.

share|improve this answer
I guess I'm asking for a list of anything that might get broken due to incompatibilities arising out of an upgrade because I have no idea what that might include. – BCS Dec 14 '10 at 1:46
@BCS, we can't really give you such a list simply because we have no way of knowing just what features of MySQL you're using. Only you can compare such a list with the changes docs. – John Gardeniers Dec 14 '10 at 3:09

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.