Mysql – Upgrading MySQL 5.1 to 5.5 on Centos 6 without removing dependencies

You may find this surprising, but MySQL 5.1 may outperform MySQL 5.5 under certain circumstances.

Percona performed a bake-off among multiple releases of MySQL

• MySQL 4.1
• MySQL 5.0
• MySQL 5.1 (with built-in InnoDB)
• MySQL 5.1 with InnoDB-plugin
• MySQL 5.5
• MySQL 5.6

All tests were performed with MySQL unconfigured (In other words, no my.cnf was made). The results?

• MySQL 4.1 performs the best single-threaded
• MySQL 5.1 with InnoDB plug-in scales on multiple cores better than 5.1 InnoDB built, 5.5 and 5.6

If you want newer versions of MySQL to perform better, you must tune for it. In fact, I described in the DBA StackExchange the idea of performing a MySQL Bakeoff.

What do I mean tune for it?

In MySQL 5.5, there are new InnoDB options for utilizing more dedicated read threads, write threads, and overall I/O capacity. This can engage more CPUs in multicore servers. Left unconfigured, MySQL 5.5 would operate on the same level playing field, in most cases, as older versions of MySQL. Sometimes, it could perform worse.

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