MySQL Upgrade from 4.1 to 5.0 Done.. and a success!

Yesterday, I started the upgrade from mysql-4.1.21 to mysql-5.0.26. I was a little gun-shy and hesitant since the last upgrade from 4.0 to 4.1 caused all sorts of problems with charset, etc. This upgrade was pleasantly easy and pain free. There is a Gentoo MySQL upgrade guide that will be handy for upgrades that involve additional attention (ie: 4.0 to 50 instead of 4.1. to 5.0). Since my MySQL upgrade was from 4.1 to 5.0 I was able to do a straight upgrade. I’ll list what I did before (mostly provided by the Gentoo Mysql upgrade guide mentioned earlier).

Here are my USE flags for my particular build:

[ebuild U ] dev-db/mysql-5.0.26-r1 [4.1.21] USE="latin1 perl ssl -berkdb -big-tables -cluster -debug -embedded -extraengine -max-idx-128% -minimal (-selinux) -srvdir -static (-raid%)" 25,380 kB

If you’re using the latin1 charset (from the fiasco of 4.0 to 4.1), you’ll need to be sure to include that USE flag in your make.conf or add it to /etc/portage/package.use!

First I like to keep my backups, etc during these upgrades in /root, but that’s my preference.

cd /root quickpkg dev-db/mysql alias MYSQL="mysql --user=root --password='your_password'" DATADIR=$(MYSQL --batch --raw --silent --skip-column-names \ --execute='SHOW variables LIKE "datadir";' \ | sed -e 's|datadir[ \t]||') MYSQL --execute="FLUSH TABLES WITH READ LOCK;" tar -cjpvf ~/mysql.$(date +%F"T"%H-%M).tar.bz2 \ /etc/conf.d/mysql /etc/mysql/my.cnf "${DATADIR}" MYSQL --execute="UNLOCK TABLES;" tar -tjvf ~/mysql.*.tar.bz2 emerge -av ">mysql-5.0" dispatch-conf revdep-rebuild dispatch-conf /etc/init.d/mysql restart mysql_upgrade_shell --user=root --password='your_password' \ --protocol=tcp --datadir="${DATADIR}" /etc/init.d/mysql restart unset DATADIR unalias MYSQL

Now, it’s super important to do the revdep-rebuild phase since any packages built with MySQL will be looking at the 4.1 client shared object. This means that your MTA, Snort and PHP could be broken if you don’t do this. It’s also important to think about any servers that use mysql clients only, since revdep-rebuild will need to be done there too. For example, I have a Gentoo firewall using snort to connect to an internal logging server. I thought the upgrade to mysql there wouldn’t be a problem, because I did not have any databases there, etc. But snort was unable to restart the following day because it couldn’t access the old MySQL 4.1 client to connect to the database on the logging server.