How can I upgrade my MySQL 5.1 to MySQL 5.6, and migrate data

Solution 1:

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

Solution 2:

This worked for me. Almost same scenario. MySQL 5.1 to 5.6 only I run into a problem upgrading one of my databases table (the biggest one), did not dump correctly and was rejected during upload to new server, took forever and timed out several times, so I had to do something out of the books. (Before you do download your database, do a complete backup, repair, check and all the maintenance required so you dont have messed up data in your new upload. Also, do a direct wire transfer, dont use wireless with large databases.

1 - All my tables, except the biggest one, uploaded without a problem (regular export then regular import thru phpmyadmin from 5.1 to 5.6 MySQL different servers side by side with my laptop in the middle)

2 - I went into the MySQL source data directory (depends on your system but on Centos 7 is in /var/lib/mysql/) and search the table with the problem, it has 3 files named after the table (table_name.MYD, table_name.MYI and table_name.FRM) you need to download those 3 manually thru FTP like Filezilla or copy into a USB)

3 - Just in case, create a target blank temp_database and give all the rights to a user. MySQL creates an additional directory called temp_database where you are to copy those 3 files for that table. (dont need to mess with good upgraded tables, if you need to do upload all the tables, you can proceed to copy all the tables files into that new temp directory, is faster when you do copy directly without using MySQL)

4 - Once you transfer the table files into that new target temp directory, then you will be able to see them on phpmyadmin (on the new server) as temp_database, check the table, make sure all data and records are there correctly, displayed and accounted for.

5 - Since we created a copy thru FTP or a USB plain copy, then obviously the file is READ ONLY, and performing a check or repair table it will tell you so. You need to do CHMOD thru shell on those files so MySQL can process the repair. I guess, while repairing the file, it does the repair and upgrade of table format or any other version problems.

6 - After you are happy with the table, and you can insert, update and delete one record as test, then you can just move that table or tables onto the right database using phpmyadmin to make everything work as usual. Remove that temp database and you are set.

I have only performed that in your same scenario, 5.1 to 5.6 upgrade. I don't know if same procedure will work for other versions, havent tryied, but anyone reading this article, can follow my procedure and update us if it does work with other versions of MySQL or other databases such as MariaDB, let us know.

Hope it helps. Cheers.