Migrating MySQL from Windows to Linux

Question

I'm new to MySQL. However I am very familiar with Oracle and MSSQL. This is what I have done so far:

1. Installed MySQL 5.7 on RHEL 7.2
2. Created a test db called "mydb" and test table called "employee".
3. Location of these files are /usr/var/lib/mydb /usr/var/lib/mydb/employee.frm /usr/var/lib/mydb/employee.idb
4. Able to connect to MySQL from a client machine and can see the "mydb".

So my server and connectivity are fine. Now I want to move some of the MySQL 5.0 databases from an old Windows box to this new RHEL Linux box.

Sample of old MySQL 5.0 database files:

5. region.myd region.myi region.frm

I don't have much knowledge of the MySQL database architecture. I just noticed the MySQL 5.0 has extensions of .frm, .myd and myi while the MySQL 5.7 has .frm and the .ibd

My question is: How can I quickly migrate those two "region" files to my new RHEL box so they conform to the .frm and .ibd file format?

Thank you

Answer 1

OK Brace yourself. You can migrate the table from Windows by copying it and converting it.

STEP 01

Copy those three(3) files into the data folder /usr/var/lib/mydb in Linux

You should have

• /usr/var/lib/mydb/region.frm
• /usr/var/lib/mydb/region.myd
• /usr/var/lib/mydb/region.myi

STEP 02

Change the filename name extensions of two of the files

cd /usr/var/lib/mydb
mv region.myd region.MYD
mv region.myi region.MYI

Required for MyISAM tables in Linux

• See my old post Error When Restoring Backed Up Tables
• See the chat session that I used to troubleshoot this question

STEP 03

Change the owner of the three files

cd /usr/var/lib/mydb
chown mysql:mysql region.frm
chown mysql:mysql region.MYD
chown mysql:mysql region.MYI

STEP 04

Convert the Table to InnoDB

mysql> use mydb
mysql> ALTER TABLE region ENGINE=InnoDB;

That's it.

GIVE IT A TRY !!!

Answer 2

You are migrating from 5.0 to 5.7, so the safest way is to dump and restore using mysqldump, you can find this link useful http://mysqlserverteam.com/upgrading-directly-from-mysql-5-0-to-5-7-with-mysqldump/