How to upgrade the MySQL 5.1 to MySQL 5.6, and migrate data

MySQLupgrade

I previously installed MySQL 5.1 using the installer, since then created a number of users/databases, and now would like to upgrade to 5.6. What is the procedure to do so?

Best Answer

The following assume your MySQL 5.1 is installed in /usr/local/mysql-5.1.46-osx10.6-x86_64 and that MySQL 5.6 will install in /usr/local/mysql-5.6.11-osx10.7-x86_64. The exact directory names might differ depending on the exact version number you are using.

  • Download the MySQL 5.6 installer, for instance in DMG format, and run mysql-5.6.11-osx10.7-x86_64.pkg
  • Stop the server
    • With MySQL Workbench, go to Server Administration
    • open mysql@localhost (add connection if not there)
    • under Startup / Shupdown, click Stop Server
  • Create backup of 5.6 data directory: sudo mv /usr/local/mysql-5.6.11-osx10.7-x86_64/data /usr/local/mysql-5.6.11-osx10.7-x86_64/data.save
  • Copy data directory from 5.1: sudo cp -a /usr/local/mysql-5.1.46-osx10.6-x86_64/data /usr/local/mysql-5.6.11-osx10.7-x86_64/
  • Start server (similar to stopping, see above)
  • Run mysql_upgrade, which checks all the tables and upgrades system tables
  • Check data there, with MySQL Workbench
    • In Server Administration, under Users and Privileges, check the expected users are present
    • In SQL Development, open a connection to the database, and run a few queries to check the expected data is present.
  • In MySQL Workbench, if getting "Error deleting password entry error when connecting with MySQL Workbench", upgrade to the latest version of MySQL Workbench
Related Question