Upgrading MySQL

Summary

This page will show you how to remove your old version of MySQL, install a newer version of MySQL on your UW account, and move your databases over to your new MySQL installation.

About Version 5.0.27

Refer to the Release Notes for MySQL Community Server 5.0.27 for a list of new additions and bug fixes.

Instructions

The upgrading process can be broken down into three parts:

Shut down existing server, and back-up files.
Install and start new version of MySQL, copying back-up files back into their original places.
Remove back-up files and finish configuring new version of MySQL.

Shut down existing server, and back-up files.

Log in to your Homer or Dante account with Tera Term or another terminal emulator.
Press the O key for Other, then press the W key to drop into the Web development environment. Stay in your home directory; at no point during this process should you cd to public_html.
If your MySQL server is running, shut it down. Read the Basic MySQL Administration for instructions on doing this.
If you are using a .my.cnf file, temporarily change its name:
mv ~/.my.cnf ~/.my.cnf.temp
Rename your old MySQL symbolic link:

mv ~/mysql ~/mysql-bak

Warning: You must follow this step, or your new MySQL installation will not work properly.

Install and start new version of MySQL

Follow the instructions to install the latest version of MySQL, paying attention to the instructions for those upgrading an existing version of MySQL.

Remove back-up files and finish configuring new version of MySQL.

Once you have verified that your new installation is working and that you successfully copied over your old databases (step 9 of the installation instructions), you can remove the old MySQL program files:

rm -R ~/mysql-bak

Optional: This entire step is not required, and your database will function fine if you skip it. But, if you are upgrading from a 3.x to a 4.x release of MySQL, you should consider converting some of your tables to a more current format.

This is needed only if you want to use the new GRANT functions, CREATE AGGREGATE FUNCTION or want to use the more secure passwords. It is described in the MySQL documentation.

To do this with your installation, edit the bin/mysql_fix_privilege_tables script in your MySQL directory:

pico -w ~/mysql/bin/mysql_fix_privilege_tables

Near the top of this script, you will see this:

if test -z "$1" ; then
  cmd="/usr/local/mysql/bin/mysql -f --user=$user --host=$host mysql"
else
  root_password="$1"
  cmd="/usr/local/mysql/bin/mysql -f --user=$user --password=$root_password --host=$host mysql"
fi

Change the highlighted text (/usr/local/mysql/bin/mysql) on both lines to read simply bin/mysql. It should look like this:

if test -z "$1" ; then
  cmd="bin/mysql -f --user=$user --host=$host mysql"
else
  root_password="$1"
  cmd="bin/mysql -f --user=$user --password=$root_password --host=$host mysql"
fi

Write the file and exit Pico.

Now enter the mysql directory and run the script.

cd ~/mysql
bin/mysql_fix_privilege_tables mypassword

Be sure to replace mypassword with your root password. You will see the following:

This script updates all the mysql privilege tables to be usable by
MySQL 4.0 and above.

This is needed if you want to use the new GRANT functions, 
CREATE AGGREGATE FUNCTION, stored procedures, or more secure passwords in 4.1.

If you get 'Access denied' errors, you should run this script again
and give the MySQL root user password as an argument!
Converting all privilege tables to MyISAM format

If your tables are already up to date or partially up to date you will
get some warnings about 'Duplicated column name'. You can safely ignore these!
ERROR 1060 at line 2: Duplicate column name 'File_priv'

...etc...

Note: The errors you will likely see can be ignored, as this script explains before generating them.

The script may take up to a minute to finish. When it is complete, your database should have had its system tables fixed.

Additional Information

The MySQL upgrade page has important details about using the new software.

You may also want to upgrade to the latest version of phpMyAdmin.