Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from serverfault.com Feb 9 '12 at 10:31

This question came from our site for professional system and network administrators.

    
I suggest migrating to serverfault, seems more up their alley; they'll probably ask where you got your mysql packages and what the package trigger scripts look like. yum is just the tool that wraps your rpm transactions. :) –  sarnold May 24 '11 at 22:43
    
What distribution? –  ghm1014 May 24 '11 at 23:13

1 Answer 1

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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.