Notes on MySQL 5.5 upgrade

Prerequisites

CentOS 5.6 64bit
Logged in a root
Binary install of MySQL
Already have a binary install running version of 4.1.22 in /usr/local/mysql41_22

Important

When doing binary installs it is very important that the MySQL yum package is not installed because it will set a /etc/my.cnf which will override your /usr/local/mysql55_20/my.cnf. Check for the presence of /etc/my.cnf and if it is there remove it rm -f /etc/my.cnf After that the machine should be regularly updated via yum --exclude=mysql update. After each one of these updates make sure that no file was installed at /etc/my.cnf. This is unlikely since the --exclude=mysql was included in the command, however, checking takes a couple of seconds. If so remove it as it will take precedence over the ../data/my.cnf and MySQL will start incorrectly. You may also edit the /etc/yum.conf file and add the exclude statement so you don't forget and can just do the normal yum update.

# vim /etc/yum.conf
obsoletes=1
gpgcheck=1
plugins=1
exclude=mysql*

Download for binary install

This is in a slightly different place then I remember for 5.1. Goto 5.5 Community Server downloads for and select "Generic Linux" from the select box. Go to the bottom of the page and select the link for Linux - Generic 2.6 (x86, 64-bit), Compressed TAR Archive (mysql-5.5.20-linux2.6-x86_64.tar.gz). Follow until you get to the mirror sites and right click one of them and copy the link.

cd ~/software # my generic place for downloading and installing software
wget http://dev.mysql.com/get/Downloads/MySQL-5.5/mysql-5.5.20-linux2.6-x86_64.tar.gz/from/http://mysql.mirrors.pair.com/
tar xvzf mysql-5.5.20-linux2.6-x86_64.tar.gz -C /usr/local
cd /usr/local
# Change into shorter friendlier directory name
mv mysql-5.5.20-linux2.6-x86_64 mysql55_20

Install

cd mysql55_20
# Put 4.1.22 my.cnf in /usr/local/mysql55_20/my.cnf
cp [dir location of 4.1.22]/my.cnf .

Edit 4.1.22 my.cnf

vim my.cnf
# Change "port" and "socket" var to
port    = 3307 # 4.1.22 is running on 3306
socket    = /tmp/mysql55_20.sock
# Also change innodb dirs
innodb_data_home_dir = /usr/local/mysql55_20/data/
innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
innodb_log_group_home_dir = /usr/local/mysql55_20/data/
innodb_log_arch_dir = /usr/local/mysql55_20/data/

Edit ./support-files/mysql.server

# Set proper basedir and datadir
basedir=/usr/local/mysql55_20
datadir=$basedir/data

Finish install

# Set correct permissions
# Note: if don't have a mysql user (check by doing a cat /etc/passwd) 
#       then you need to make one:
# groupadd mysql
# adduser -g mysql mysql
chown -R root .;chown -R mysql data;chown -R root bin;chgrp -R mysql .;
./scripts/mysql_install_db --user=mysql

Start it up

./support-files/mysql.server start

Failed to start

# Look at error log to see why it didn't start
less data/*.err
# Error
unknown option '--skip-locking'
# skip-locking has been change to "--skip-external-locking"

Add "skip-external-locking" and start up

./support-files/mysql.server start 
# Failed again .. check data/*.err
# Error
'set-variable=long_query_time=1'
# Take the set-variable out in 5.5 and just do:
long_query_time=1

Add "long_query_time=1" and start up

./support-files/mysql.server start 
# Failed again .. check data/*.err
# Error
'innodb_log_arch_dir=/usr/local/mysql55_20/data/'
# The variable innodb_log_arch_dir has been unsused since 4.0.6 or so, and has been deprecated since 5.0.24: Take it out.

Starting MySQL.. SUCCESS!

Add root password and give to a non local box

# add root passwd
./bin/mysqladmin password '*****' --socket=/tmp/mysql55_20.sock
# test login in
./bin/mysql -u root -p --socket=/tmp/mysql55_20.sock
# this gives you mysql prompt
create database www_db;
# Give web servers on 192.168.10.x access to the www_db database as www_user
GRANT ALL PRIVILEGES ON www_db.* TO www_user@'192.168.10.%' IDENTIFIED BY '*****'
flush privileges;

Open up 3307 on the firewall

# Set Firewall
system-config-securitylevel-tui
#  a. Select "Customize".
#  b. Other port line should look like this = "mysql:tcp 3307:tcp"
#  c. Click Ok
# Note: after clicking OK iptables will be restarted
cat /etc/sysconfig/iptables # check that 3307 was added
-A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 3307 -j ACCEPT

Start MySQL 5.5 on reboot

# Old school : stick it in /etc/rc.local
vim /etc/rc.local
/usr/local/mysql55_20/support-files/mysql.server start