MySQL Server Upgrade

[Ronald Bradford]

Hi All,

I'm doing a  upgrade of my primary dedicated hosted server tomorrow, starting with a re-image of the new OS.  (given I've had it 3 years, it's long overdue)

As part of the process, I'm going to move all MySQL databases to 5.1.11,  with 5.0.22 installed as a backup.
Presently I'm running most production databases on 4.0.13 (rather ashamed to say), and a number of newer sites on 5.1.4

My question seeking clarification is people's best approach from going from 4.0 to 5.1?

I've heard the best approach is to do major release at a time,  i.e. install 4.1, upgrade, install 5.0 upgrade, install 5.1 upgrade. Is this recommended?
Should I also go to the last 4.0.27 first?
Should I just bypass the upgrade process and do a mysqldump in 4.0 and reload in 5.1  (the downside obviously is the user security, which should be redone anyway)?


Any input would be helpful.

Regards

Ronald

[Beat Vontobel]

Hi Ronald

I never bypassed any major release when upgrading, however, with the
exception of 4.1 -> 5.0 all the upgrades were _really_ painless. Not
even a dump was ever needed there.

For the upgrade from 4.1 to 5.0 everything was different: A dump is
definitely needed here, as there are still some serious bugs
(crashes, data corruption...) in 5.x regarding the compatibility with
the old table formats (especially MyISAM), even though some of them
have been fixed recently.

Pay also special attention to the many new reserved words (http://
www.futhark.ch/mysql/105.html). I would do a grep over all table
definitions or the general query log to make sure nothing collides
with one of the new reserved words and make sure they are properly
escaped if they do.

If DECIMAL datatypes are used anywhere, be sure to change the table
definitions to the new semantics.

I think these are the three most important steps that could really
hurt you.

So, with the dump needed anyway for 5.0, I personally would give it a
try to bypass the upgrade to 4.1 and just load the 4.0 dump (5.0 ->
5.1 seems to be better again), even if the one-major-upgrade-at-a-
time is the official policy. Just be sure to get the character set/
collation settings right and I think you should be quite fine.

But of course this also depends on the complexity of your
applications. And you should be prepared to have access to the code
of all the applications accessing the database, because you'll
probably have to fix some minor things anyway: The optimizer
sometimes takes a surprisingly different path and so some queries
might need to be rewritten, small syntax changes (e.g. JOINs) might
also affect you. But I found this to be rather easy to deal with.

Good luck!
Beat

-----

"The major difference between a thing that might go wrong and a thing
that cannot possibly go wrong is that when a thing that cannot
possibly go wrong goes wrong it usually turns out to be impossible to
get at or repair." (Douglas Adams, Mostly Harmless)

MeteoNews AG, Beat Vontobel (IT, Research & Development)

E-Mail: b.von...@meteonews.ch
IT Department: +41 (0)43 288 40 54
Main phone: +41 (0)43 288 40 50
Mobile: +41 (0)79 738 79 70

[sheeri kritzer]

The manual is pretty detailed, actually, and gives the example of moving from 4.0 to 4.1 then moving to 5:

http://dev.mysql.com/doc/refman/5.1/en/upgrade.html

The tool[s] to check tables for upgrade compatibility:

http://dev.mysql.com/doc/refman/5.0/en/mysql-fix-privilege-tables.html
and
http://dev.mysql.com/doc/refman/5.1/en/mysql-upgrade.html

-Sheeri

[Markus Popp]

Hi,

 

I guess it shouldn't even be a problem to perform a cross update through a dump as long as you ensure that the grant tables have the right format after the update.

 

This should work this way:

 

* Dump all databases except the mysql schema

* Dump the data (not the table structures) of the mysql schema separately using the complete-insert option (to ensure that all fields in the grant tables of the new version are filled correctly, even if the structure is different)

* Install the new server

* Restore the databases (except the mysql schema)

* Restore the data of the mysql schema

* Look for new privileges and fill the missing data in the mysql schema

* Flush the privileges

 

That's how I performed my updates and I had no problem with this method ;-).

 

Cheers,

Markus

[sheeri kritzer]

Just make sure when you go that route that you specify everything you want.  Read up on the mysqldump options -- for instance, if you don't specify --create-options, you won't get MySQL's table creation options -- ENGINE and DEFAULT CHARSET.  Big bummer when all your tables end up being MyISAM and you don't understand why.  (this happened to me!)

-Sheeri

[Markus Popp]

Yep - I agree that this method is slightly more dangerous than the way that's described in the manual. So keeping the old server up for backup reasons would sound like a good decision to me ;-).

 

Another question from me ... somebody commented an older blog article of mine and asked whether it's possible to log schema changes:

 

http://db4free.blogspot.com/2006/01/creating-log-tables-with-triggers.html

 

In theory, a nice solution to this would be a trigger on information_schema views, but currently this doesn't seem to work (sounds like a nice feature request - however, there are unfortunately still many other problems with information_schema that have a higher priority - mainly its performance). Does one of you have an idea how this could still be done?

 

Thanks,

[sheeri kritzer]

The only ways I know of are diff'ing 2 "schema" files (mysqldump --no-data) or grepping through the binary logs (or general logs, if folks have that turned on) to find out who changed what.

The bigger question is, "What is this person trying to accomplish?"  They could easily make only one user in the db have powers to change schemas; then have one person in charge of changing schemas, and folks make requests to that person.  If it's something like auditing the DB, then they'd have to search the logs.  Also, do they want real-time or periodic checks?   Triggers run in real-time, something like grep'ing a log is done periodically (though you can simulate real-time by having it run very frequently). 

Does this person just want a report of who changed what, daily or weekly?  If so, that's easily gotten from the logs (and it encompasses what you'd need in an audit anyway).

[Markus Popp]

Sheeri, thanks, this makes sense ;-). I don't know what his exact business requirements are, but it's nice to think about solutions for this in general.

 

It's a bit of a pity that it seems to be necessary to use means outside MySQL (like parsing the binary log file) which could as well work from inside MySQL (at least in MySQL 5.1). Apart from the trigger/information_schema approach another idea _could_ be to use the general log table - but this fails because all queries are logged regardless whether they were successful or not. Once again I thought it would be very nice if it was possible to separate successful from failed queries inside there.

 

Hey, but it's nice to play around and think about things like that - that's something that I haven't done for a while (shame on me).

[Farhan]

Thank you Sheeri and Markus for this discussion.

When I upgraded MySQL, I found it easier to remove old installation (after data backup) and install a fresh one. But that's just me...

Frank

[mysqldba]

The best way to avoid any issues when upgrading from 4.0 to 5.0 or 5.1
is to run a mysql dump and import. You might want to run your mysql DB
dump and import seprorate. After the mysql DB import make sure to run
mysql_fix_grant_tables.

Run your mysqldump like this:
mysqldump -uroot -p -S /tmp/mysql.sock --opt --skip-lock-tables
--quote-names -Q -a --all-databases > all_dbs_in_old_mysql.sql

Keep in mind that there are huge differeneces between 4.0 and 5.0.
Joins are different, fulltext searching, char and varchar are different
along with timestamps and password hashes.

Don't worry about going to 4.0.27. That version just fixes some remote
buffer overflows.