Yum update mysql from 5.1 to 5.5

I was just in the process of 'yum updating' a server. One of the updates its suggesting to carry out is an upgrade from mysql 5.1 to 5.5.

The mysql server operates in a master-master replication setup with another 5.1 mysql instance. I would also update the other master to 5.5, but want to keep one online at all times. This master also has a number of 5.1 mysql slaves replicating from it.

Does anyone know if:

• yum update is safe from 5.1 to 5.5 without huge data rebuilds

• if the above replication setup will be a problem.

Edit:
This is a centos 5.4 install. However the updates are coming in from the non-standard remi repo.

The replication setup you have cannot be maintained when upgrding to MySQL 5.5

You will have to go with Master/Slave. Here is the reason:

Someone asked a question about gibberish appearing in a binary log and I answered this question

In my answer, I explained that binary logs start at different positions in different versions of MySQL

Here are those positions:

• 107 for MySQL 5.5

• 106 for MySQL 5.1

• 98 for MySQL 5.0 and back

With replication, these numbers apply to the relay logs as well. The net effect is that a Slave can replicate from an older version of MySQL, but not the other way around.

You are better off doing the following:

• Convert Master/Master to Master/Slave

• Upgrade to MySQL 5.5 on the Slave

• Point all apps at the Slave (now called NewMaster)

• service mysql stop on the Master

• Upgrade to MySQL 5.5 on the Master (now called NewSlave) but do not start mysql

• service mysql stop on NewMaster

• rsync NewMaster's /var/lib/mysql to NewSlaves's /var/lib/mysql

• rm /var/log/mysql/master.info on NewMaster

• rm /var/log/mysql/master.info on NewSlave

• service mysql start --skip-networking on NewMaster

• run RESET MASTER on NewMaster

• service mysql restart on NewMaster

• service mysql start on NewSlave

• run RESET MASTER on NewSlave

• setup Master/Master Replication using position 107 as start master_log_pos

With regard to using yum to upgrade, I run in panic and scream in horror at the thought of it.

Here is my more conservative approach:

• I would mysqldump everything EXCEPT the mysql schema.

-
Run this to generate SQL for all mysql user grants:

mysql -hhostaddr -umyuserid -pmypassword --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | mysql -hhostaddr -umyuserid -pmypassword --skip-column-names -A | sed 's/$/;/g' > /root/MySQLGrants.sql

-
Uninstall MySQL 5.1

• Install MySQL 5.5 from RPMs by hand.

• Load /root/MySQLGrants.sql into MySQL 5.5

• Load the mysqldump'd data back into MySQL 5.5

StackExchange Database Administrators Q#12528, answer score: 2