1.

Solve : MySQL 5.0 to 5.6 upgrade questions before moving forward?

Answer»

So I have a database created under MySQL Community Edition 5.0, and just recently found out that MySQL 5.0 will not launch under Windows 7 64-bit, but has no problems with Windows 7 32-bit. I want to migrate the database to the latest MySQL 5.6 which supports running under 64-bit OS, and through my search on Google before hand I found that its not a simple upgrade from 5.0 to 5.6, but of which I will have to migrate the database forwards to 5.1, then 5.5, then 5.6.

Has anyone gone through this process to warn me before hand of any details I need to know before migrating the database forward? In the past I have simply performed a MySQL dump command at command shell of MySQL prompt and dumped a copy of the database which can later be imported back if needed. But this was sticking with the same version of MySQL.

Before going through this process I will be making a backup of the database to not have to WORRY about trashing my only copy etc. This will be done on the old build which is 32-bit Windows 7in which the MySQL 5.0 service ran with no problems on prior. Then I will have to upgrade from 5.0 to 5.1 on this 32-bit old build and then import it and verify that it migrated to 5.1 without any problems.

When it comes to MySQL as a stand alone local database I wasnt concerned over upgrading it from 5.0 to 5.1 and so on because it worked flawless under 32-bit OS, and it wasnt until the recent introduction of Windows 7 64-bit that I found that MySQL 5.0 will not run under 64-bit even when trying to run it in XP Mode etc.

So before jumping into this I figured I'd check here to see if anyone has gone through a database migration like this one from 5.0 to a more modern version and any special commands that I need to execute at the MySQL prompt to pepare the data for 5.1 etc or a later version so its not say a legacy dump file that cant be imported etc?

There is plenty of info at the links here below, but figured that maybe someone here may have helpful info to add that may not be listed, as for it wouldnt be the first time I went through a migration from old to new and found out after the fact that I could have saved myself from troubles by running it past others who say... "Oh, last time I went through this, process select manual options and select a checkmark in a list prior to moving forward vs going with default settings", and later finding out that I now need to either uninstall and reinstall or perform a registry edit to enable or disable something.

5.6 http://dev.mysql.com/doc/refman/5.6/en/faqs-migration.html

5.5 http://dev.mysql.com/doc/refman/5.5/en/faqs-migration.html

5.1 http://dev.mysql.com/doc/refman/5.1/en/faqs-migration.html

5.0 http://dev.mysql.com/doc/refman/5.0/en/faqs-migration.html

*Also looking for a 64-bit version of MySQL 5.0 it appears that the oldest available at mysql.com is 5.1, yet there are some other HITS on Google from lesser trusted sources that I havent checked out yet which may offer MySQL 5.0 64-bit as seen here: https://www.google.com/#q=mysql+5.0+64-bit which might be the easiest way to make it run under Windows 7 64-bit, but of which I would rather get the download from a trusted source vs a Google hit from potentially shady source. Then of which there are 6 rev versions of 5.0 as:

5.0.4
5.0.41
5.0.51
5.0.67
5.0.95
5.0.96

And of which it would be best to run the latest of the obsolete version 5.0.96 but of which will I run into problems importing from an earlier Rev to a later Rev of the same version 5.0 if I went this route? ( I will have to boot up the old build to see exactly what Rev of MySQL 5.0 the database is running on. )

Its too bad there is no migration tool that is free to assist with this very manual migration that I see before me..LOL  Just an update. I dumped by database on the older 32-bit MySQL 5.0 and imported it to 64-bit MySQL 5.0.96 and its working with no problems.

http://downloads.skysql.com/archive/index/p/mysql/v/5.0.96

Based on the background of SkySQL below I gave it a try and it works flawless.

Quote

Why SkySQL?

Founded by the team behind MySQL
SkySQL was founded by many of the original MySQL AB team members INCLUDING Michael 'Monty' Widenius and David Axmark - the guys who first started MySQL AB.

After MySQL AB was sold to Sun Microsystems, and then subsequently to Oracle there were concerns within the original team that the new owners wouldn't continue the ethos of the original business.

SkySQL exists as the natural new home for former MySQL AB customers and those looking to exploit MySQL for the first time. We're also the technical and commercial foundry for MariaDB - the future of open source SQL.

So right now because it works I am probably not going to mess with bringing it forward to 5.6 since there is nothing to gain out of making extra work for myself. However I am saving copies of each MySQL version if for some reason in the future I need to migrate it to an even newer build and need to finally migrate away from 5.0.96 due to some sort of problem with old database on new OS etc, such as I havent tried this on Windows 8.1 yet, but its fine on Windows 7 64 bit with no immediate reason to force it to be on Win 8.x
I've found I prefer PostGres to MySQL overall. I certainly Prefer pgAdmin to 'MySQL Workbench'.Thanks BC for sharing that info. Never worked with PostGres before, but will check into that. I have been using mainly MySQL and SQLyog Community Edition as the Database GUI tool to go in and manage with nice GUI interface, way better than manually working with SQL command shell.

Looking here  http://www.wikivs.com/wiki/MySQL_vs_PostgreSQL at PostGres vs MySQL, I will have install it to check it out. I assumed that MariaDB was the future for solid and well performing Open Source Databases, and it took a similar spin off to that of the OpenOffice to LibreOffice Spin off. In which I am still running the old OpenOffice 3.3 since I have no need to update to the newest even when its free. Maybe this is comparing apples to oranges, but I just assumed that it was related to the same split for the same reasons.Yeah MariaDB is to MySQL what LibreOffice is to OpenOffice, I believe. MariaDB is a Fully Open Sourced Fork of MySQL that isn't controlled/directed by anybody at Oracle.

From what I can tell, MySQL dates back to around 1995, Postgres can trace itself back to 1986. I had always thought it was actually "younger" than MySQL.

Main reason I'm familiar with postgres is because it is what we are using for a database in our software, so I've been working with postgres queries, statements, the .NET API and such for the last few months. I used MySQL for some freelance projects before that. Of course they both work just fine- I see no specific reason that we are using PostgreSQL over MySQL except that we had to CHOOSE one of them (I was not around when the decision was made anyway).

One 'advantage' I found is that searching for postgreSQL information usually comes up with more relevant results- MySQL searches are usually filled with a bunch of PHP Stuff. That's a rather silly reason but it exists nonetheless.

I prefer pgAdmin III to MySQL WorkBench (or community edition or WHATEVER the UI front-end MySQL comes with is) mostly because it's more straightforward. the MySQL variant has a lot of 'fancying up' to make it look pretty but I always have to think about what I'm doing and how I need to do it as a result. With PgAdmin I'm right in there with a tree view for any configured servers and can expand those as necessary, whereas with MySQL's variant I need to wander about some other dialogs and options and wizards and stuff for things.

of course one can easily get aftermarket products that do pretty much the same thing, or even let you use various different databases using the same interface. From what I understand the query syntax and capabilities for Postgres are better than MySQL, but I've only used Stored procedures in PostGres so I don't know whether that is the case first-hand.


Discussion

No Comment Found

Related InterviewSolutions