MySQL Migration – Move Data from MySQL 5.0 to MySQL 5.7 Server

migrationMySQLmysqldump

We are switching servers from a Windows Server 2003 (32-bit) with a MySQL 5.0.51b to a Windows Server 2016 (64-bit) on which I will install the latest MySQL version (5.7).

The database contains just tables (no views, foreign keys…) which I want to move to the new database. Should I be able to use mysqldump and mysqlimport (as per the documentation) even though the difference in MySQL versions?

Best Answer

mysqldump and mysqlimport (as described by the documentation you cited) should be fine for your regular tables, especially since you don't have any foreign keys.

However, due to the differences in MySQL versions, the tables in the mysql database will be different, so I suspect migrating that database probably can't be done with this particular method.

If you need to migrate the mysql database, there are better ways of doing that.

Related Solutions

Mysql – Appropriate Windows version for Mysql

64 bit always. You do understand 32 vs 64 bit generally?
Windows Server 2008 R2 of course. Windows 7 is desktop, Windows 2003 is obsolete

Have you considered Linux though?

MySQL – Fastest Way to Move Database Between Servers

If you are changing versions, DO NOT MOVE THE mysql SCHEMA.

Why should you not move the mysql folder? It has to do with the authentication privileges.

The number of columns in mysql.user is different from version to version

If you run desc mysql.user

You will see 31 rows for MySQL 4.1
You will see 37 rows for MySQL 5.0
You will see 39 rows for MySQL 5.1
You will see 42 rows for MySQL 5.5

I wrote about this before

It is OK to move everything else. On the new machine that has MySQL 5.5.24, do this:

mv /var/lib/mysql /var/lib/mysql/mysql55
mkdir /var/lib/mysql
<scp or rsync /var/lib/mysql of MySQL 5.1.41 over to /var/lib.mysql of MySQL 5.5.24>
rm -f /var/lib/mysql/mysql/*
cp /var/lib/mysql/mysql55/* /var/lib/mysql/mysql/*
chown -R mysql:mysql /var/lib/mysql
service mysql start

So, the question remains:

How do you move the User Privileges in the old MySQL 5.1.41 to MySQL 5.5.24 ???

There are two ways to do this starting on the MySQL 5.1.41 machine:

METHOD #1 : Use pt-show-grants

This Percona Toolkit program move print out the User Permission in Pure SQL. You could run the result output into a Text File. Then, execute the Text File in MySQL 5.5.24. End of Story.

pt-show-grants ... > MySQLUserGrants.sql

METHOD #2 : Emulate pt-show-grants

I made my own technique for pt-show-grants

mysql -hhostaddr -umyuserid -pmypassword --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | mysql -hhostaddr -umyuserid -pmypassword --skip-column-names -A | sed 's/$/;/g' > MySQLUserGrants.sql

Either way, move MySQLUserGrants.sql over to the MySQL 5.5.24 machine and execute the script

I wrote about this before : importing myisam 5.0 database into a 5.5 innodb server