Mysql – Migrating MySQL from Windows to Linux

innodbmyisamMySQLmysql-5.6mysql-5.7

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:

  1. 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

Best Answer

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

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 !!!

Related Question