Mysql – Yum update thesql from 5.1 to 5.5

MySQLmysql-5.1mysql-5.5upgrade

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:

  1. yum update is safe from 5.1 to 5.5 without huge data rebuilds
  2. 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.

Best Answer

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:

  1. Convert Master/Master to Master/Slave
  2. Upgrade to MySQL 5.5 on the Slave
  3. Point all apps at the Slave (now called NewMaster)
  4. service mysql stop on the Master
  5. Upgrade to MySQL 5.5 on the Master (now called NewSlave) but do not start mysql
  6. service mysql stop on NewMaster
  7. rsync NewMaster's /var/lib/mysql to NewSlaves's /var/lib/mysql
  8. rm /var/log/mysql/master.info on NewMaster
  9. rm /var/log/mysql/master.info on NewSlave
  10. service mysql start --skip-networking on NewMaster
  11. run RESET MASTER on NewMaster
  12. service mysql restart on NewMaster
  13. service mysql start on NewSlave
  14. run RESET MASTER on NewSlave
  15. 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:

  1. I would mysqldump everything EXCEPT the mysql schema.

  2. 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

  3. Uninstall MySQL 5.1

  4. Install MySQL 5.5 from RPMs by hand.
  5. Load /root/MySQLGrants.sql into MySQL 5.5
  6. Load the mysqldump'd data back into MySQL 5.5
Related Question