2

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?

AllisonC
  • 125
  • 2
  • 12
  • I don't get what "load help tables" mean there. IMHO, you can skip mysqlcheck if you don't see any errors on the log. it is good practice to always review your error log. – jerichorivera Jan 14 '16 at 09:53
  • again you can restore a mysqldump file from a 5.0 to a 5.6/5.7 instance if you follow the instructions on the link I shared. – jerichorivera Jan 14 '16 at 09:54
  • I'm curious how your experience was with this upgrade and which method you used. I'm planning something similar for a few thousand DBs on 10 database servers and am hungry for any experience I can find. – flickerfly Mar 09 '17 at 23:45
  • @flickerfly - hasn't happened yet, still haven't started the upgrade process unfortunately due to things beyond my control – AllisonC Mar 14 '17 at 14:04
  • @AllisonC, Thanks for getting back to me. I feel your pain. – flickerfly Mar 15 '17 at 01:23

2 Answers2

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
jerichorivera
  • 489
  • 1
  • 4
  • 12
  • I'm genuinely curious, why doing a dump/restore to 5.1 and then in place upgrade is better than just dump/restore to 5.7? – user9517 Jan 13 '16 at 07:14
  • personally i've never tried direct dump/restore from 5.0 to 5.7 that's why i suggested above steps. but user can choose to dump/restore directly to 5.7 – jerichorivera Jan 13 '16 at 07:19
  • @jerichorivera I have updated my question with the information you have provided. Can you please take a look and verify? – AllisonC Jan 13 '16 at 15:57
0

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.

ColinM
  • 701
  • 8
  • 19