Upgrade MySQL 5.1 to 5.5

MySQL 5.5 已在 12/15 時 GA
以下環境為 CentOS 5.5 x64

備份資料
把資料庫目錄先搬到其他地方, 讓安裝 RPM 時重建 msyql db, 因為 5.5 有多了一些 table, 例如 slow_log

停止 MySQL Service
service mysql stop

移除 MySQL 舊版 RPM
rpm -qa | grep -i '^mysql-' | xargs rpm --nodeps -ev

下載官方 MySQL 5.5.8 RPM
wget http://mysql.cdpa.nsysu.edu.tw/Downloads/MySQL-5.5/MySQL-client-5.5.8-1.rhel5.x86_64.rpm
wget http://mysql.cdpa.nsysu.edu.tw/Downloads/MySQL-5.5/MySQL-devel-5.5.8-1.rhel5.x86_64.rpm
wget http://mysql.cdpa.nsysu.edu.tw/Downloads/MySQL-5.5/MySQL-server-5.5.8-1.rhel5.x86_64.rpm
wget http://mysql.cdpa.nsysu.edu.tw/Downloads/MySQL-5.5/MySQL-shared-5.5.8-1.rhel5.x86_64.rpm
wget http://mysql.cdpa.nsysu.edu.tw/Downloads/MySQL-5.5/MySQL-shared-compat-5.5.8-1.rhel5.x86_64.rpm
wget http://mysql.cdpa.nsysu.edu.tw/Downloads/MySQL-5.5/MySQL-test-5.5.8-1.rhel5.x86_64.rpm

安裝 MySQL 5.5.8
rpm -ivh MySQL*

把所需的資料庫目錄搬回

啟動 MySQL 5.5
service mysql start

升級後檢查 Tables
mysql_upgrade
最後會遇到幾行 SQL syntax error, 不過不影響
ERROR 1064 (42000) at line 31: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(14), Table_priv set('Select','Insert','Update','Delete','Create','Drop','Grant'' at line 1
ERROR 1064 (42000) at line 33: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(14), Column_priv set('Select','Insert','Update','References') COLLATE utf8_gene' at line 1
ERROR 1064 (42000) at line 65: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(14), PRIMARY KEY (Host,Db,User,Routine_name,Routine_type), KEY Grantor (Grantor' at line 1
ERROR 1064 (42000) at line 417: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(14) AFTER Proc_priv' at line 2

以下是用 rpm -U 時會出現的提示畫面
******************************************************************
A MySQL server package (MySQL-server-community-5.1.54-1.rhel5) is installed.

Upgrading directly from MySQL 5.1 to MySQL 5.5 may not
be safe in all cases. A manual dump and restore using mysqldump is
recommended. It is important to review the MySQL manual's Upgrading
section for version-specific incompatibilities.

A manual upgrade is required.

- Ensure that you have a complete, working backup of your data and my.cnf
files
- Shut down the MySQL server cleanly
- Remove the existing MySQL packages. Usually this command will
list the packages you should remove:
rpm -qa | grep -i '^mysql-'

You may choose to use 'rpm --nodeps -ev ' to remove
the package which contains the mysqlclient shared library. The
library will be reinstalled by the MySQL-shared-compat package.
- Install the new MySQL packages supplied by Oracle and/or its affiliates
- Ensure that the MySQL server is started
- Run the 'mysql_upgrade' program

This is a brief description of the upgrade process. Important details
can be found in the MySQL manual, in the Upgrading section.
******************************************************************