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
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…
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 😛