Re: Major upgrade on MySQL (5.0.xx to 5.1.xx)

Hi Pete,

I've been meaning to get back to you on this but got swamped with other
work.

With this kind of problem the devil is always in the details, and without
knowing the details of your configuration and the errors you encountered,
it's hard for me to analyze precisely what happened.

The upgrade from Zend-distributed MySQL 5.0 to directly-downloaded MySQL
5.1 involves four significant configuration changes.
1) Base install directory: /usr/local/mysql ==>
/QOpenSys/usr/local/mysql/mysql
2) Data directory: /usr/local/mysqldata ==>
/QOpenSys/usr/local/mysql/data
3) Configuration file location: /usr/local/mysql/bin/my.cnf ==>
/etc/my.cnf
4) MySQL system table format: 5.0 => 5.1

Strictly speaking, only the last item in this list is imperative. For
example, as you've found out, you can make things work by upgrading the
data directory in place. The downside is that it is much harder to use the
Zend Core management functions to start and stop MySQL. Also, if you plan
to use the IBMDB2I storage engine to store your MySQL data in DB2 tables,
I recommend that you have your data directory somewhere in QOpenSys so
that upper-case schema and table names retain their case in DB2.

So, for the future reference of anyone interested, below are steps I
performed to upgrade to MySQL 5.1 after initially installing Zend Core and
MySQL. This isn't any kind of official documentation. It is just what
worked for me in a rather vanilla environment. By following these steps, I
was able to continue using the Zend Core management functions with MySQL
5.1.

1) End the MySQL server (using the Zend management tools)
2) Edit the startup script to use the new directory paths by modifying the
DEFS_FILE value at the top of /usr/local/mysql/bin/mysql.sh
#DEFS_FILE=${BASEDIR}/bin/my.cnf
DEFS_FILE=/etc/my.cnf
3) Edit the configuration file in /etc/my.cnf and add the following line
lower_case_table_names=0
4) Start the MySQL server
5) Backup the entire MySQL database by executing from PASE:
cd /usr/local/mysql
bin/mysqldump --user=root --password=your_password -A >
/tmp/mysqlbackup.sql
6) End the MySQL server.
7) Rename the existing configuration file
mv /etc/my.cnf /etc/my.cnf2
8) Install the new version of MySQL using the documented SAVF install
process and taking the defaults on MYSQLINST/INSMYSQL.
9) Copy the Zend-provided configuration and startup script files to the
new installation.
cp /usr/local/mysql/bin/my.cnf p/usr/local/mysql/bin/mysql.sh
/QOpenSys/usr/local/mysql/mysql/bin
10) Replace the existing symbolic link at /usr/local/mysql with one
pointing to the new base directory.
rm /usr/local/mysql
ln -s /QOpenSys/usr/local/mysql/mysql /usr/local/mysql
11) Edit the startup script to use the new directory paths by modifying
the following values at the top of
/QOpenSys/usr/local/mysql/mysql/bin/mysql.sh
BASEDIR=/QOpenSys/usr/local/mysql/mysql # was /usr/local/mysql
DATADIR=/QOpenSys/usr/local/mysql/data # was /usr/local/mysqldata
PID_PATH=/usr/local/mysqldata/zmysql.pid # was $DATADIR/zmysql.pid
12) Start the MySQL server
13) Restore the backed-up data
cd /QOpenSys/usr/local/mysql/mysql
bin/mysql -u root < /tmp/mysqlbackup.sql
14) Upgrade the system table format
bin/mysql_upgrade


If you run into problems and need to downgrade, you should be able to
return to your previous version of MySQL simply by
1) Reverting a symbolic link:
rm /usr/local/mysql
ln -s /usr/local/mysql-5.0.* /usr/local/mysql
2) Undoing the changes made in steps 2 and 3


Tim

date: Wed, 06 Jan 2010 19:26:19 -0700
from: Pete Helgren <Pete@xxxxxxxxxx>
subject: Re: [WEB400] Major upgrade on MySQL (5.0.xx to 5.1.xx)

Tim,

Thanks! I have arrived where you told me I would (a harder place). So,
here is what I know so far:

The upgrade DID work,sort of, using your instructions with the only
wrinkle being that the mysql_upgrade stumbled a little. I should have
saved the error message when I ran mysqld_safe so it might help the next

person. The reality was, as far as I could tell, MySQL really didn't
start so when I ran mysql_upgrade and nothing happened it was because
the DB wasn't running. Not sure why. So recovery was what I was
after. I checked the status of MySQL using netstat and found the port
not in use. I tried to start it through the Zend menus but nothing
happened. So I made sure that everything regarding MySQL was quiesced
and then I used QP2TERM to run the mysqld_safe command and this time it

seemed to start with no errors and then I ran the upgrade. I got a few
errors on tables that indicated that my Wordpress instance was running
so I killed the Apache instances and then ran the REPAIR TABLE command
to fix the tables that were in error.

I still got errors when Navicat tried to access the tables so I ended
the MySQL instance again and then restarted MySQL (again, using
QP2TERM). Just to be on the safe side, I ran the mysql_upgrade again
and it returned with nothing to upgrade. Navicat worked, my blogs and
web pages were back up so I assumed that all was well. So, I shut down
the instances and tried to start them using the Zend menus. The error I

eventually found was that the Zend method of starting the MySQL instance

was looking for a mysql.sh file which was not in the bin folder. I
copied the old one over but it still wouldn't start although I could run

the script from QP2TERM. So, I have just left it at that. I'll tweak
my startup routines if necessary so MySQL will start at IPL but for the
moment the dust has settled and things seem to be humming along.

The most curious thing in all of this was that my installation is in
/usr/local/ (rather than QOpenSys/usr/local) and I didn't have a my.cnf
file in etc. It is simply named my .

Yes, the MySQL site docs could use some tweaking. You may want to pull
the Upgrade instructions out of the Installation section and move them,
corrected, to the upgrade section with a subsection on i5/OS. I don't
know where the most sensible placement would be.

If there is something I still should do to ensure that the database is
properly updated, let me know.

Pete