Mysql – PPA for MySQL 5.5 for ubuntu 10.10 and 11.10

The simplest option is to just back up everything in the filesystem and carry it over.

To find out where your data is, look at the datadir variable with in mysql

test> show variables like 'datadir';
+---------------+-------------------+
| Variable_name | Value             |
+---------------+-------------------+
| datadir       | /var/lib/mysql/   |
+---------------+-------------------+
1 row in set (0.00 sec)

Note Your actual value may differ.

Then from your shell shut down mysql, back it up and copy it over. Something like:

> sudo service mysql shutdown
> cd /var/lib/mysql
> sudo tar -czf /path/with/enough/space/db.tgz *

Copy your db.tgz over to your new server and uncompress it on your new servers datadir while it is shutdown

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