ServerAdminBlog

If you’re using RH based distros, you’ll probably notice that their habbit is to keep the same software versions in one release.  For example, if you need PHP on CentOS 5.x, # yum install php will install PHP 5.1.6  (Latest PHP version available on http://php.net is 5.3.x).  If you need MySQL, you can count on MySQL 5.0.xx branch and any other wishes will force you to use independent repos (like http://www.jasonlitka.com/yum-repository/). Another option is to use  “do-it-yourself” method.

In this post I’ll write about manual upgrade steps from rpm archives. Please keep in mind that this procedure works for me and please do not send me the private messages. I can’t help you on that way. Only options is to post your comments here and I will try to solve your problem(s).

First thing you need to do is to see what mysql packages do you have installed

# rpm -qa | grep -i ^mysql

You will get something like

mysql-connector-odbc-3.51.12-2.2
mysql-5.0.77-4.el5_5.3
MySQL-python-1.2.1-1
mysql-server-5.0.77-4.el5_5.3
mysql-devel-5.0.77-4.el5_5.3
mysql-bench-5.0.77-4.el5_5.3

Then backup all your databases, save them on the safe location, protect with alarms, guards, poison dogs, cobras, ninjas, etc…

Download rpms from http://www.mysql.com/downloads/mysql/ (MySQL-client-community-5.1.50-1.rhel5.i386.rpm, MySQL-devel-community-5.1.50-1.rhel5.i386.rpm, MySQL-server-community-5.1.50-1.rhel5.i386.rpm, MySQL-shared-community-5.1.50-1.rhel5.i386.rpm)

Note: if you have 32-bit OS, download i386 rpms, if you have 64bit download x86_64 rpms)

Stop mysql server with

# service mysqld stop

Then remove mysql rpms but with –nodeps option (in case you use yum remove mysql, you will need to reinstall a lot of apps because they will be deleted too)

# rpm -e --nodeps mysql

Repeat the same with other mysql packages (devel, bench, client,…). Then you need to install downloaded rpms with rpm -i mysql… and do it.

It is possible to receive the errors like

ls: /var/lib/mysql/*.err: No such file or directory
ls: /var/lib/mysql/*.err: No such file or directory
ERROR: 1136  Column count doesn't match value count at row 1
100910 10:24:00 [ERROR] Aborting
 
100910 10:24:00 [Note] /usr/sbin/mysqld: Shutdown complete
 
Installation of system tables failed!  Examine the logs in /var/lib/mysql for more information.

This will be fixed later…

Now you need to add .my.cnf file to your root dir so you can execute mysql_upgrade command (note that . before m means that file is hidden). Add next lines inside this file (and don’t forget to replace rootpass with your real mysql root pass)

[client]
user=root
password=rootpass

(chmod .my.cnf to 600 for security reasons)

Then exec next command

# service mysql start

# mysql_upgrade

This will produce the similar output

Looking for 'mysql' as: mysql
Looking for 'mysqlcheck' as: mysqlcheck
Running 'mysqlcheck with default connection arguments
Running 'mysqlcheck with default connection arguments
blabla.table1                            OK
blabla.table2                             OK
blabla.table3                                OK
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.event                                        OK
mysql.func                                         OK
mysql.general_log
Error    : You can't use locks with log tables.
status   : OK
mysql.help_category
error    : Table upgrade required. Please do "REPAIR TABLE `help_category`" or dump/reload to fix it!
mysql.help_keyword
error    : Table upgrade required. Please do "REPAIR TABLE `help_keyword`" or dump/reload to fix it!
mysql.help_relation                                OK
database2.cache
error    : Table upgrade required. Please do "REPAIR TABLE `cache`" or dump/reload to fix it!
database2.contacts                             OK
database2.identities                           OK
database2.messages
error    : Table upgrade required. Please do "REPAIR TABLE `messages`" or dump/reload to fix it!
database2.session
error    : Table upgrade required. Please do "REPAIR TABLE `session`" or dump/reload to fix it!
database2.users
error    : Table upgrade required. Please do "REPAIR TABLE `users`" or dump/reload to fix it!
 
Repairing tables
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_topic                                   OK
mysql.proc                                         OK
mysql.time_zone_name                               OK
database2.cache
note     : The storage engine for the table doesn't support repair
database2.messages
note     : The storage engine for the table doesn't support repair
database2.session
note     : The storage engine for the table doesn't support repair
database2.users
note     : The storage engine for the table doesn't support repair
Running 'mysql_fix_privilege_tables'...
OK

As you can see, the database database2 can’t be repaired and you should drop all tables inside this database and import your backup. After this, you can check is everything ok with mysql_upgrade –force

Once again, restart mysql with service mysql restart and check logs. Test is everything ok, try ti create a new database, optimize your installation, eat something…

Note:
I had a lot of problems with this upgrade. I had to upgrade PHP to 5.3.x, I had to recompile postfix with MySQL support, I had to download and recompile Dovecot because Dovecot from CentOS repos is compiled with mysql 5.0 branch. Some versions of RoundCube doesn’t work with php 5.3.x so you should download latest, etc etc… It can be done but please be careful with this. Who knows which nuclear reactor will explode after this 😛