How should I go about upgrading mysql server from 5.0.67 to latest?

Question

The current version of mysql I have on my server is 5.0.67. I would like to upgrade to the latest version. Based on what I have read here: https://dev.mysql.com/doc/refman/5.5/en/upgrading.html, this is my plan:

1. Kill connections to database (stop Apache, etc)
2. Create backup of current databases (including mysql database) and log files

3. Upgrade 1 release at a time (5.0.67 -> 5.7.10)

   • Upgrade to latest 5.0.x – 5.0.96
   • Upgrade to 5.1.x
   • Upgrade to 5.5.x
   • Upgrade to 5.6.x
   • Upgrade to 5.7.x

4.Logical or in-place upgrade?

I'm not sure what option I should choose here, what really the differences are, etc?

Right now, the source RPM installed is: mysql-5.0.67-12.15.src.rpm, when I go to http://downloads.mysql.com/archives/community/, I'm not sure what I would need to download. Or can this be updated through the command line? I'm using SuSE Enterprise Linux 11.

EDIT: Here are my updated steps:

1. Upgrade to 5.1.x (mysqldump)

i. Dump all databases/schemas using mysqldump

ii. Shutdown MySQL Server

iii. Initialize a new MySQL 5.1.x server instance

iv. Load the dump file into new MySQL 5.1.x server

v. Run mysql_upgrade

vi. Load help tables

vii. Run mysqlcheck for status on all tables

2. Upgrade to 5.5.x (in-place upgrade) (repeat steps for 5.6.x and 5.7.x)

i. Shutdown MySQL server

ii. Back up MySQL server

iii. Change directory to new server version

iv. Start MySQL server

v. Run mysql_upgrade

vi. Load help tables

vii. Shut down/restart MySQL server

viii. Run mysqlcheck for status on all tables

Am I missing anything?

Answer 1

There are two blogs written about upgrading to 5.6/5.7 from 5.0 here: http://mysqlserverteam.com/upgrading-directly-from-mysql-5-0-to-5-6-with-mysqldump/ http://mysqlserverteam.com/upgrading-directly-from-mysql-5-0-to-5-7-using-an-in-place-upgrade/

My suggestion would be:

• Take a backup using mysqldump of the databases from current 5.0.x instance
• Restore the mysqldump file to a new (latest version) instance of 5.1.x
• Do an 'In-place' upgrade from 5.1.x to 5.5.x to 5.6.x to 5.7.x, make sure to run mysql_upgrade in between upgrades.
• Always check your error log before and after an upgrade

Answer 2

Since replication slaves always support one older version for the master depending on possible limitations you could also setup a chain of replication slaves:

• Master: 5.0
• Slave1 (slave of Master): 5.1
• Slave2 (slave of Slave1): 5.5
• Slave3 (slave of Slave2): 5.6
• Slave4 (slave of Slave3): 5.7

Once the slaves are caught up (no lag) you can stop the app, issue STOP SLAVE; on Slave4 and then resume the app using Slave4 as the new master. If you don't have that many machines you could do it multiple times in stages. You may be able to get away with skipping a few versions, and confirming this by just trying it out should be possible without interrupting the master.