I have been upgrading more MySQL database instances recently and have found a few more potential gotchas, which if you are not careful, can potentially be rather nasty. These are not documented explicitly by MySQL, so it may be handy for you to know if you have not come across this type of thing before.

Most of the issues are those related to upgrading MySQL instances which are replicated, either the master servers or the slaves. Some seem specific to the rpm packages I am using (MySQL enterprise or MySQL advanced rpms), though others are not.

Take care upgrading a 5.0 master when you have 5.1 slaves

It is not a good idea to run a mixed major version of mysql in a replicated environment so why would I be doing this? If you work in a replicated environment and have several slaves then it is recommended that you upgrade the slaves first. I work with quite a few slaves so the process of upgrading them all takes longer …

If you have recently downloaded or checked out a new version of Kontrollbase and find that you need to upgrade the schema from a previous version then you will probably find this post useful. If you don’t know if you need to upgrade then you probably do, so read along for a bit just to [...]

According to the official lifecycle calendar at http://www.mysql.com/about/legal/lifecycle/#calendar, active support for MySQL 5.0 (including regular binary updates) will end on December 31st, 2009, which is about 3 weeks away.

Many folks are still using MySQL 5.0.45, as until October that was the package that came with RedHat. That was released in July 2007, over 2 years ago!

Upgrading to MySQL 5.1 is not difficult, though it requires more steps than just upgrading the packages.

There is a list with all the changes made that might affect the upgrade process at http://www.pythian.com/news/1414/new-in-mysql-51-sheeris-presentation/. This includes which variable names have been deprecated and changed, as well as how to upgrade stored …

I’ve had several customers in the last week or so who need a way to verify that their application will work well after an upgrade. I’m seeking input on a new tool to help with MySQL upgrades. Please add comments, either here or on the bug report, or on the mailing list topic.

If someone wants to sponsor this work, that would also be welcomed.

Problem

With the release of MySQL 5.1, many DBAs are going to be scheduling downtime to upgrade their MySQL Server. As with all upgrades between major version numbers, it requires one of two upgrade paths:

• Dump/reload: The safest method of upgrading, but it takes out your server for quite some time, especially if you have a large data set.
• mysql_upgrade: A much faster method, but it can still be slow for very large data sets.

I’m here to present a third option. It requires minimal application downtime, and is reasonably simple to prepare for and perform.

Preparation

First of all, you’re going to need a second server (which I’ll refer to as S2). It will act as a ’stand-in’, while the main server (which I’ll refer to as S1) is upgraded. Once S2 is ready to go, you can begin the preparation:

• If you haven’t already, enable …

Background Knowledge

The following is the process I took to upgrade a web server with PHP v5.2.5 to PHP v5.2.8 running on OpenBSD. PEAR is already installed on this system and up to date. I wasn’t sure if I should exclude PEAR at install or not so therefore did not tell the configurator to exclude PEAR at install.

Installation Process

1. Download the latest stable PHP release from command prompt # wget http://ca.php.net/get/php-5.2.8.tar.gz/from/a/mirror
2. # tar -zxvf php-5.2.8.tar.gz
3. ./configure –with-mysql=/usr/local –with-mssql=/usr/local –with-apxs –with-zlib-dir=/usr/lib –with-config-file-path=/var/www/conf –with-iconv=/usr/local/bin/iconv –enable-exif –enable-mbstring …

The MySQL 5.1 DBA exam is in alpha-test right now and some of the reviewers have had questions about upgrade exams. The new exams are no longer multiple choice. They are based on a candidates performance with a real database on a virtual server.
In the past I have stated that there was not going to be an upgrade exam. This was due to the change in exam format and the history of previous upgrades not being popular. Less than forty folks took the Core to Dev upgrade exam (about 5% with that certification) and the numbers are worse for the Pro to DBA upgrade. Why spend time on something that is not going to be cost justifiable?

But my reviewers are trying to convince me to have an upgrade. It would not be that much more work to offer a hands that could be taken over the Internet. You …