Copy mysql schemas to a new instance of a new version

Question

I have a few schemas, Which are included in mysql 4 instance, And i want to move them to other mysql instance which running under a newer version - mysql 5.6. This instance is already including some schemas. I want to move these schemas from the old version to the new version, And so to get rid of the old mysql instance.

What is the best practice to do so? Do i need to upgrade each version (4-->5.0, 5.0-->5.1, etc), And only after the schemas will be updated to version 5.6 i will be able to move them to the new instance?

I don't have any experience with mysql upgrading, So any tip would be great.

Answer 1

You should be able to just mysqldump the individual databases and load them straight into a MySQL instance running on the latest version. I have recommended this before:

• May 26, 2015 : mysql upgrading from 5.1 --> 5.6 do I have to do mysqldump before upgrade?
• Oct 17, 2014 : Any known issues upgrading from MySQL 5.1.73 to 5.6.21?
• Apr 18, 2013 : Steps to upgrade MySQL from version 5.0 to version 5.5
• Apr 11, 2013 : MySQL upgrade 5.0.88 to latest

When doing this, please do not move the grants by dumping the mysql schema. The number of columns have changes between versions :

• Oct 10, 2014 : MySQL service stops after trying to grant privileges to a user
• Jun 13, 2012 : Fastest way to move a database from one server to another

Try to extracts the grants with pt-show-grants or emulate it as suggested

Answer 2

If possible, use the latest mysqldump to dump the 4.x data. I think this will prevent a syntax error on CREATE TABLE due to TYPE=... changing to ENGINE=....

Is that 4.0? or 4.1? 4.0 had no concept of CHARACTER SET, so there could be other issues.

Will such a huge leap work? There may be a small number of incompatibilities that need to be worked out. But mostly it should work. Open a new Q&A if you are stumped on it; a few of us are "old timers" and should be able to help you. (I started with 3.53.)

If possible, simply start over with fresh GRANT statements; there have been changes in almost every major release.